queries.sql

ssd7卡耐基教程

/*DELETE FROM READ_BY4;
DELETE FROM BOOK4;
DELETE FROM MEMBER4;
DELETE FROM ORDERED4;
DELETE FROM PURCHASE_ORDER4;
DELETE FROM TITLE4;
DELETE FROM SUPPLIER4;*/


/*The follow is what I did in this work*/

/*1.  Display the ID, first name, and joining date of each member whose ID begins with the letters 
"A%B" or "A&B" and who joined the library no later than Nov. 30, 1997. */

SELECT MEMBER4.libid,MEMBER4.fname,MEMBER4.join_date
FROM MEMBER4
WHERE (MEMBER4.libid LIKE 'A#%B%' ESCAPE '#' OR MEMBER4.libid LIKE 'A&B%')
AND MEMBER4.join_date < TO_DATE('1997/11/30', 'YYYY/MM/DD');

/*2.  List the name, ISBN, and call number of each title, provided it has been ordered from more 
than one supplier and the total quantity ordered is more than 10. The list should be arranged
 in non-increasing order of the total quantity ordered.*/ 

SELECT TITLE4.name,TITLE4.ISBN,TITLE4.callnumber,SUM(PURCHASE_ORDER4.qty) AS SUMOFQTY
FROM TITLE4,ORDERED4,PURCHASE_ORDER4
WHERE TITLE4.callnumber = ORDERED4.callnumber AND ORDERED4.ponum = PURCHASE_ORDER4.ponum
GROUP BY TITLE4.name,TITLE4.ISBN,TITLE4.callnumber,ORDERED4.callnumber
HAVING SUM(PURCHASE_ORDER4.qty) > 10 and COUNT(ORDERED4.callnumber) > 1 
ORDER BY SUM(PURCHASE_ORDER4.qty) DESC;

/*3.  List the IDs and first names of all the members who have read less than 5% of the total number 
of books read from the library, in non-decreasing order of the number of books read by each. */

SELECT MEMBER4.libid,MEMBER4.fname,SUM(READ_BY4.timesread) AS sumoftimeread
FROM MEMBER4,READ_BY4
WHERE MEMBER4.libid = READ_BY4.libid
GROUP BY MEMBER4.libid, MEMBER4.fname
HAVING SUM(READ_BY4.timesread) < (
SELECT SUM(READ_BY4.timesread)
FROM READ_BY4)*0.05
ORDER BY SUM(READ_BY4.timesread);


/*4.  List in alphabetical order the first names and IDs of all female members of the library who 
have read at least one title more than once. */

SELECT MEMBER4.fname, READ_BY4.libid
FROM MEMBER4, READ_BY4
WHERE MEMBER4.GENDER = 'F' AND MEMBER4.LIBID = READ_BY4.LIBID	
GROUP BY MEMBER4.FNAME, READ_BY4.LIBID
HAVING MAX(READ_BY4.TIMESREAD) > 1			
ORDER BY MEMBER4.FNAME;

/*5.  List the names of the suppliers and their respective average quantities of title orders 
received, whose average quantities of title orders received is more than the average quantity
 of an order in the system. */

SELECT SUPPLIER4.name, AVG(PURCHASE_ORDER4.qty) AS AVGQTY
FROM SUPPLIER4, ORDERED4, PURCHASE_ORDER4
WHERE SUPPLIER4.supplier_id = ORDERED4.supplier_id AND ORDERED4.ponum = PURCHASE_ORDER4.ponum
GROUP BY SUPPLIER4.supplier_id, SUPPLIER4.name
HAVING AVG(PURCHASE_ORDER4.qty) > (
SELECT AVG(PURCHASE_ORDER4.qty)
FROM PURCHASE_ORDER4);

/*6.  List alphabetically the first names and IDs of all the male members of the library who joined
 before Oct. 10, 1995, who have read only five or fewer different titles, and who have not read the 
same title twice or more. */

SELECT DISTINCT MEMBER4.fname, MEMBER4.libid, COUNT(READ_BY4.timesread) AS num_of_bookread
FROM MEMBER4, READ_BY4
WHERE MEMBER4.gender = 'M' AND MEMBER4.join_date < TO_DATE('1995/10/10', 'YYYY/MM/DD') AND 

MEMBER4.libid = READ_BY4.libid
GROUP BY READ_BY4.libid, MEMBER4.fname, MEMBER4.libid
HAVING COUNT(READ_BY4.timesread) <= 5 AND MAX(READ_BY4.timesread) <= 1
ORDER BY MEMBER4.fname;

/*
#
#                        TITLE4
#_________________________________________________________
#  callnumber  isbn  year  name  authorfname authorlname
#_________________________________________________________
*/
INSERT INTO TITLE4 VALUES ('AB123456', 'CD123456',TO_DATE('1997/01/01', 'YYYY/MM/DD'), 'Pitt Roads', 'Aon', 'Arad');
INSERT INTO TITLE4 VALUES ('AB123457', 'CD123457',TO_DATE('1996/01/01', 'YYYY/MM/DD'), 'UPitt Maps', 'Bon', 'Brad');
INSERT INTO TITLE4 VALUES ('AB123458', 'CD123458', TO_DATE('1995/01/01', 'YYYY/MM/DD'), 'UPenn Maps', 'Con', 'Crad');
INSERT INTO TITLE4 VALUES ('AB123459', 'CD123459', TO_DATE('1994/01/01', 'YYYY/MM/DD'), 'Penn Roads', 'Don', 'Drad');
INSERT INTO TITLE4 VALUES ('AB123460', 'CD123460', TO_DATE('1993/01/01', 'YYYY/MM/DD'), 'Intro to C', 'Eon', 'Erad');
INSERT INTO TITLE4 VALUES ('AB123461', 'CD123461', TO_DATE('1997/01/01', 'YYYY/MM/DD'), 'Fortan -77', 'Fon', 'Frad');
INSERT INTO TITLE4 VALUES ('AB123462', 'CD123462', TO_DATE('1996/01/01', 'YYYY/MM/DD'), 'Quick Basic', 'Stella', 'Potter');
INSERT INTO TITLE4 VALUES ('AB123463', 'CD123463', TO_DATE('1995/01/01', 'YYYY/MM/DD'), 'Visit India',  'Ravi', 'Kumar');
INSERT INTO TITLE4 VALUES ('AB123464', 'CD123464', TO_DATE('1994/01/01', 'YYYY/MM/DD'), 'Turbo Pascal', 'Angus', 'Emily');
INSERT INTO TITLE4 VALUES ('AB123465', 'CD123465', TO_DATE('1993/01/01', 'YYYY/MM/DD'), 'Indian Songs', 'Lata', 'Mangeskar');

/*
#
#                          MEMBER4  
#_______________________________________________________________
#libid   fname  lname  address  phonenumber  join_date  Gender
#_______________________________________________________________
*/
INSERT INTO MEMBER4 VALUES ('A%B124', 'Sam', 'Son', 'Pgh 15213', '4126457890', TO_DATE('1997/12/01', 'YYYY/MM/DD'), 'M');
INSERT INTO MEMBER4 VALUES ('A%B125', 'Sama', 'Sona', 'Pgh 15213', '4126347891', TO_DATE('1997/12/15', 'YYYY/MM/DD'), 'F');
INSERT INTO MEMBER4 VALUES ('A%B126', 'Cam', 'Con', 'Penn 10214', '6126457780', TO_DATE('1995/06/01', 'YYYY/MM/DD'), 'M');
INSERT INTO MEMBER4 VALUES ('A%B127', 'Cama', 'Cona', 'Penn 10214', '6126467891', TO_DATE('1995/05/01', 'YYYY/MM/DD'), 'F');
INSERT INTO MEMBER4 VALUES ('AxB127', 'Camax', 'Conax', 'PennX 10214', '6126467892', TO_DATE('1995/05/01', 'YYYY/MM/DD'), 'F');
INSERT INTO MEMBER4 VALUES ('A&B124', 'Dan', 'Don', 'Pgh 15215', '4126451190', TO_DATE('1990/11/15', 'YYYY/MM/DD'), 'M');
INSERT INTO MEMBER4 VALUES ('A&B125', 'Dana', 'Dona', 'Pgh 15215', '4126433890', TO_DATE('1990/12/15', 'YYYY/MM/DD'), 'F');
INSERT INTO MEMBER4 VALUES ('A&B126', 'Ram', 'Shyam', 'Penn 10215', '6126454690', TO_DATE('1990/05/10', 'YYYY/MM/DD'), 'M');
INSERT INTO MEMBER4 VALUES ('A&B127', 'Rama', 'Shyama', 'Penn 10215', '6126489890', TO_DATE('1990/01/10', 'YYYY/MM/DD'), 'F');
INSERT INTO MEMBER4 VALUES ('C%D124', 'San', 'Don', 'Pgh 15220', '4126457342', TO_DATE('1990/01/20', 'YYYY/MM/DD'), 'M');
INSERT INTO MEMBER4 VALUES ('C%D125', 'Sana', 'Dona', 'Penn 10220', '6126457341', TO_DATE('1990/05/20', 'YYYY/MM/DD'), 'F');


/*
#
#                             BOOK4
#__________________________________________________________________
#  bookid  edition  status  borrowerid  borrowduedate  callnumber
#__________________________________________________________________
*/
INSERT INTO BOOK4 VALUES ('Q76.97.0', 'first', 'good', 'A%B124', TO_DATE('1998/05/05', 'YYYY/MM/DD'), 'AB123456');
INSERT INTO BOOK4 VALUES ('Q76.97.1', 'third', 'good', 'A%B125', TO_DATE('1998/06/10', 'YYYY/MM/DD'), 'AB123457');
INSERT INTO BOOK4 VALUES ('Q76.97.2', 'first', 'ok', 'A%B126', TO_DATE('1998/07/15', 'YYYY/MM/DD'), 'AB123458');
INSERT INTO BOOK4 VALUES ('Q76.97.3', 'third', 'ok', 'A%B127', TO_DATE('1998/08/20', 'YYYY/MM/DD'), 'AB123459');
INSERT INTO BOOK4 VALUES ('Q76.97.4', 'first', 'good', 'A&B124', TO_DATE('1998/05/20', 'YYYY/MM/DD'), 'AB123460');
INSERT INTO BOOK4 VALUES ('Q76.97.5', 'first', 'good', 'A&B125', TO_DATE('1998/06/15', 'YYYY/MM/DD'), 'AB123461');
INSERT INTO BOOK4 VALUES ('Q76.97.6', 'third', 'good', 'A&B126', TO_DATE('1998/07/10', 'YYYY/MM/DD'), 'AB123462');
INSERT INTO BOOK4 VALUES ('Q76.97.7', 'third', 'good', 'A&B127', TO_DATE('1998/08/05', 'YYYY/MM/DD'), 'AB123463');
INSERT INTO BOOK4 VALUES ('Q76.97.8', 'second', 'bad', 'A%B124', TO_DATE('1998/08/25', 'YYYY/MM/DD'), 'AB123464');
INSERT INTO BOOK4 VALUES ('Q76.97.9', 'second', 'bad', 'A%B125', TO_DATE('1998/08/25', 'YYYY/MM/DD'), 'AB123465');

/*
#
#               PURCHASE_ORDER4
#_________________________________________________
#  ponum  qty  orderdate  duedate  receiveddate
#_________________________________________________
*/
INSERT INTO PURCHASE_ORDER4 VALUES ('PO100000', 5, TO_DATE('1990/05/01', 'YYYY/MM/DD'), TO_DATE('1990/06/01', 'YYYY/MM/DD'), TO_DATE('1990/05/15', 'YYYY/MM/DD'));
INSERT INTO PURCHASE_ORDER4 VALUES ('PO100001', 5, TO_DATE('1990/06/01', 'YYYY/MM/DD'), TO_DATE('1990/07/01', 'YYYY/MM/DD'), TO_DATE('1990/06/15', 'YYYY/MM/DD'));
INSERT INTO PURCHASE_ORDER4 VALUES ('PO100002', 5, TO_DATE('1990/07/01', 'YYYY/MM/DD'), TO_DATE('1990/08/01', 'YYYY/MM/DD'), TO_DATE('1990/07/15', 'YYYY/MM/DD'));
INSERT INTO PURCHASE_ORDER4 VALUES ('PO100003', 15, TO_DATE('1997/01/01', 'YYYY/MM/DD'), TO_DATE('1997/03/01', 'YYYY/MM/DD'), TO_DATE('1997/05/01', 'YYYY/MM/DD'));
INSERT INTO PURCHASE_ORDER4 VALUES ('PO100004', 5, TO_DATE('1997/02/01', 'YYYY/MM/DD'), TO_DATE('1997/05/01', 'YYYY/MM/DD'), TO_DATE('1997/06/01', 'YYYY/MM/DD'));
INSERT INTO PURCHASE_ORDER4 VALUES ('PO100005', 2, TO_DATE('1998/05/01', 'YYYY/MM/DD'), TO_DATE('1998/06/01', 'YYYY/MM/DD'), TO_DATE('1998/05/15', 'YYYY/MM/DD'));
INSERT INTO PURCHASE_ORDER4 VALUES ('PO100006', 2, TO_DATE('1998/05/01', 'YYYY/MM/DD'), TO_DATE('1998/07/01', 'YYYY/MM/DD'), TO_DATE('1998/05/15', 'YYYY/MM/DD'));
INSERT INTO PURCHASE_ORDER4 VALUES ('PO100007', 2, TO_DATE('1998/05/01', 'YYYY/MM/DD'), TO_DATE('1998/08/01', 'YYYY/MM/DD'), TO_DATE('1998/05/15', 'YYYY/MM/DD'));
INSERT INTO PURCHASE_ORDER4 VALUES ('PO100008', 10, TO_DATE('1995/05/01', 'YYYY/MM/DD'), TO_DATE('1995/06/01', 'YYYY/MM/DD'), TO_DATE('1995/05/15', 'YYYY/MM/DD'));
INSERT INTO PURCHASE_ORDER4 VALUES ('PO100009', 10, TO_DATE('1995/05/01', 'YYYY/MM/DD'), TO_DATE('1995/06/01', 'YYYY/MM/DD'), TO_DATE('1995/05/15', 'YYYY/MM/DD'));

/*
#
#         SUPPLIER4
#______________________________
#  supplier_id  name  address
#______________________________
*/
INSERT INTO SUPPLIER4 VALUES ('SP000001', 'SP PITT', 'Pitt 15260');
INSERT INTO SUPPLIER4 VALUES ('SP000002', 'SP PITT', 'Pitt 15270');
INSERT INTO SUPPLIER4 VALUES ('SP000003', 'SP PENN', 'Penn 10260');
INSERT INTO SUPPLIER4 VALUES ('SP000004', 'SP PENN', 'Penn 10270');
INSERT INTO SUPPLIER4 VALUES ('SP000005', 'SP BRAD', 'Brad 20260');

/*
#
#            READ_BY4
#_________________________________
#  callnumber  libid  timesread
#_________________________________
*/
INSERT INTO READ_BY4 VALUES ('AB123456','A%B124', 2);
INSERT INTO READ_BY4 VALUES ('AB123457','A%B124', 2);
INSERT INTO READ_BY4 VALUES ('AB123458','A%B124', 2);
INSERT INTO READ_BY4 VALUES ('AB123459','A%B124', 2);
INSERT INTO READ_BY4 VALUES ('AB123460','A%B124', 2);
INSERT INTO READ_BY4 VALUES ('AB123461','A%B124', 5);
INSERT INTO READ_BY4 VALUES ('AB123462','A%B124', 5);
INSERT INTO READ_BY4 VALUES ('AB123463','A%B124', 5);
INSERT INTO READ_BY4 VALUES ('AB123464','A%B124', 5);
INSERT INTO READ_BY4 VALUES ('AB123465','A%B124', 5);
INSERT INTO READ_BY4 VALUES ('AB123456','A%B125', 5);
INSERT INTO READ_BY4 VALUES ('AB123457','A%B125', 5);
INSERT INTO READ_BY4 VALUES ('AB123458','A%B125', 5);
INSERT INTO READ_BY4 VALUES ('AB123459','A%B125', 5);
INSERT INTO READ_BY4 VALUES ('AB123460','A%B125', 5);
INSERT INTO READ_BY4 VALUES ('AB123461','A%B125', 10);
INSERT INTO READ_BY4 VALUES ('AB123462','A%B125', 10);
INSERT INTO READ_BY4 VALUES ('AB123463','A%B125', 10);
INSERT INTO READ_BY4 VALUES ('AB123464','A%B125', 10);
INSERT INTO READ_BY4 VALUES ('AB123465','A%B125', 10);
INSERT INTO READ_BY4 VALUES ('AB123460','A%B126', 1);
INSERT INTO READ_BY4 VALUES ('AB123461','A%B126', 1);
INSERT INTO READ_BY4 VALUES ('AB123462','A%B126', 1);
INSERT INTO READ_BY4 VALUES ('AB123463','A%B126', 1);
INSERT INTO READ_BY4 VALUES ('AB123464','A%B126', 1);
INSERT INTO READ_BY4 VALUES ('AB123465','A%B126', 1);
INSERT INTO READ_BY4 VALUES ('AB123460','A%B127', 2);
INSERT INTO READ_BY4 VALUES ('AB123461','A%B127', 2);
INSERT INTO READ_BY4 VALUES ('AB123462','A&B124', 1);
INSERT INTO READ_BY4 VALUES ('AB123463','A&B124', 1);
INSERT INTO READ_BY4 VALUES ('AB123462','A&B125', 1);
INSERT INTO READ_BY4 VALUES ('AB123463','A&B125', 1);
INSERT INTO READ_BY4 VALUES ('AB123464','A&B126', 1);
INSERT INTO READ_BY4 VALUES ('AB123465','A&B126', 1);
INSERT INTO READ_BY4 VALUES ('AB123464','A&B127', 1);
INSERT INTO READ_BY4 VALUES ('AB123465','A&B127', 1);
INSERT INTO READ_BY4 VALUES ('AB123456','C%D124', 2);
INSERT INTO READ_BY4 VALUES ('AB123457','C%D124', 2);
INSERT INTO READ_BY4 VALUES ('AB123458','C%D124', 1);
INSERT INTO READ_BY4 VALUES ('AB123456','C%D125', 2);
INSERT INTO READ_BY4 VALUES ('AB123457','C%D125', 2);

/*
#
#             ORDERED4
#__________________________________
#  callnumber  ponum  supplier_id
#__________________________________
*/
INSERT INTO ORDERED4 VALUES ('AB123456','PO100000','SP000001');
INSERT INTO ORDERED4 VALUES ('AB123456','PO100001','SP000002');
INSERT INTO ORDERED4 VALUES ('AB123456','PO100002','SP000003');
INSERT INTO ORDERED4 VALUES ('AB123457','PO100003','SP000001');
INSERT INTO ORDERED4 VALUES ('AB123458','PO100004','SP000002');
INSERT INTO ORDERED4 VALUES ('AB123459','PO100005','SP000002');
INSERT INTO ORDERED4 VALUES ('AB123459','PO100006','SP000003');
INSERT INTO ORDERED4 VALUES ('AB123459','PO100007','SP000004');
INSERT INTO ORDERED4 VALUES ('AB123460','PO100008','SP000001');
INSERT INTO ORDERED4 VALUES ('AB123460','PO100009','SP000002');