一个关于SQL的问题，写出代码，建立sub-totals和fees outstanding

Question

写一段代码建立一个报告，把each member和他们fees paid和fee outstanding（未偿付的）费用列出来，为Pennant members 和social members建立一个sub-totals（求和表），再加上grand total(总数之和)Pennant members 每年要付的费用是75和social members每年要付的费用是40在member table出现的
CREATE TABLE member
(memberNo char(3) primary key,
FamilyName varchar(15) not null,
GivenName varchar(15),
street varchar(30),
suburb varchar(20),
postcode char(4),
phone varchar(15),
dateOfBirth date,
dateJoined date,
gender char(1),
feesPaid number(5,2),
registrationNumber number,
CONSTRAINT registrationNumber_un UNIQUE (registrationNumber));INSERT INTO member VALUES(123,'Jenkins','Harry','32 High St','Launceston','7250','[phone]',
                              '17-MAR-1968','18-JUN-1995','M', 75, 2351);
INSERT INTO member VALUES(124,'Jenkins','Susie','32 High St','Launceston','7250','[phone]',
                              '27-MAR-1971','18-JUN-1995','F', 75, 2396);
INSERT INTO member VALUES(162,'Hawkins','Steven','2 Jenkins St','Launceston','7250','[phone]',
                              '14-AUG-1972','28-JUN-2001','M', 0, 2321);
INSERT INTO member VALUES(163,'Hawkins','Susie','2 Jenkins St','Launceston','7250','[phone]',
                              '03-JAN-1971','02-JUN-2006','F', 0, 2345);
INSERT INTO member VALUES(12,'Martin','Mary','22 High St','Launceston','7250','[phone]',
                              '07-DEC-1958','18-JUL-1990','F', 75, 2352);
INSERT INTO member VALUES(14,'Martin','Kevin','22 High St','Launceston','7250','[phone]',
                              '30-NOV-1961','20-JUL-1990','M', 75, 2387);
INSERT INTO member VALUES(262,'Tucker','Tommy','2 Corner Square','Launceston','7250','[phone]',
                              '17-AUG-1972','28-JUN-2002','M', 50, 2360);
INSERT INTO member VALUES(263,'Greene','Lorne','2 Hawkins St','Launceston','7250','[phone]',
                              '10-FEB-1973','31-AUG-2002','M', 60, 2325);
INSERT INTO member VALUES(3,'Atrick','Gerry','27 Sunset Court','Launceston','7250','[phone]',
                              '22-OCT-1944','21-MAY-1990','M', 40, null);
INSERT INTO member VALUES(4,'Atrick','Maisie','27 Sunset Court','Launceston','7250','[phone]',
                              '01-AUG-1947','28-JUN-1990','F', 40, null);
INSERT INTO member VALUES(285,'Tillizer','Krys','2 Smithy Lane','Riverside','7249','[phone]',
                              '27-SEP-1983','28-APR-2005','F', 75, 2362);
INSERT INTO member VALUES(284,'Tillizer','Fleur','2 Smithy Lane','Riverside','7249','[phone]',
                              '27-SEP-1983','28-APR-2005','F', 75, 2361);
INSERT INTO member VALUES(248,'Tuckwell','Barry','12 Fountain Court','Launceston','7250','[phone]',
                              '17-MAY-1963','18-JUN-2006','M', 75, 2355);
INSERT INTO member VALUES(249,'deGrass','Moe','127 Main Road','Launceston','7250','[phone]',
                              '19-APR-1972','18-JUN-2002','M', 75, 2356);
INSERT INTO member VALUES(250,'Clippers','Neil','2 Side Street','Launceston','7250','[phone]',
                              '11-MAY-1965','18-JUN-2002','M', 75, 2357);
INSERT INTO member VALUES(251,'Smith','Quentin','12 Side Street','Launceston','7250',null,
                              '31-DEC-1963','05-DEC-2002','M', 75, 2358);
Pennant members 每年要付的费用是75和social members每年要付的费用是40
要像这样子的哈Playing members
MEM FAMILYNAME GIVENNAME SEX FEESPAID
--- --------------- --------------- --- ----------
163 Hawkins Susie F 0
124 Jenkins Susie F 75
12 Martin Mary F 75
284 Tillizer Fleur F 75
285 Tillizer Krys F 75
250 Clippers Neil M 75
263 Greene Lorne M 60
162 Hawkins Steven M 0
123 Jenkins Harry M 75
14 Martin Kevin M 75
251 Smith Quentin M 75
262 Tucker Tommy M 50
248 Tuckwell Barry M 75
249 deGrass Moe M 75
14 rows selected.
Social members
MEM FAMILYNAME GIVENNAME SEX FEESPAID
--- --------------- --------------- --- ----------
3 Atrick Gerry M 40
4 Atrick Maisie F 40
2 rows selected.

Answer 1

楼主联要求都没有表示清楚 ；
本人认为，你的要求用视图实现起来非常简单，何必用表呢？