ssd8-3.sql

ssd7 exercise 3 ,绝对有用~欢迎下载

/*
#@author:wangzhesi
#053597
*/

CREATE TABLE TITLE4
 (callnumber  char(8) NOT NULL,
  isbn        char(8),
  year        date,
  name        char(14),
  authorfname char(14),
  authorlname char(14),
  PRIMARY KEY (callnumber)
);

CREATE TABLE MEMBER4
 (libid       char(6) NOT NULL,
  fname       char(14),
  lname       char(14),
  address     char(20),
  phonenumber char(10),
  PRIMARY KEY (libid)
);

CREATE TABLE BOOK4
 (book_id       char(8) NOT NULL,
  edition       char(10),
  status        char(5),
  borrowerid    char(6),
  borrowduedate date,
  callnumber    char(8),
  PRIMARY KEY (book_id)
);

--1:
/*
PURCHASE_ORDER4(PoNum, Qty, OrderDate, DueDate, ReceivedDate);
PK(PoNum)
*/
CREATE TABLE PURCHASE_ORDER4
 (ponum       char(8) NOT NULL,
  qty         integer,
  orderdate   date,
  duedate     date,
  receivedate date,
  PRIMARY KEY(ponum)
);

/*
SUPPLIER4(Supplier_Id, Name, Address);
PK(Supplier_Id)
*/
CREATE TABLE SUPPLIER4
 (supplier_id  char(8) NOT NULL,
  name         char(8),
  address      char(10),
  PRIMARY KEY(supplier_id)
);

/*
READ_BY4(CallNumber, LibId, TimesRead);
PK(CallNumber, LibId)
FK(CallNumber) -> TITLE4(CallNumber)
FK(LibId) -> MEMBER4(LibId)
*/
CREATE TABLE READ_BY4
 (callnumber  char(8) NOT NULL,
  libid       char(6) NOT NULL,
  timeread    integer,
  PRIMARY KEY (callnumber,libid)
);

/*
ORDERED4(CallNumber, PoNum, Supplier_Id);
PK(CallNumber, PoNum, Supplier_Id)
FK(CallNumber) -> TITLE4(CallNumber)
FK(PoNum) -> PURCHASE_ORDER4(PoNum)
FK(Supplier_Id) -> SUPPLIER4(Supplier_Id)
*/
CREATE TABLE ORDERED4
 (callnumber  char(8) NOT NULL,
  ponum       char(8) NOT NULL,
  supplier_id  char(8) NOT NULL,
  PRIMARY KEY (callnumber,ponum,supplier_id)
); 

--2:
ALTER TABLE TITLE4 ADD CONSTRAINT alterisbn UNIQUE(isbn);
ALTER TABLE TITLE4 ALTER COLUMN isbn SET NOT NULL;
ALTER TABLE TITLE4 ADD CONSTRAINT altername UNIQUE(name);
ALTER TABLE TITLE4 ALTER COLUMN name SET NOT NULL;

--3:
ALTER TABLE BOOK4 ADD FOREIGN KEY (borrowerid) REFERENCES MEMBER4(libid);
ALTER TABLE BOOK4 ADD FOREIGN KEY (callnumber) REFERENCES TITLE4(callnumber);
ALTER TABLE READ_BY4 ADD FOREIGN KEY (callnumber) REFERENCES TITLE4(callnumber);
ALTER TABLE READ_BY4 ADD FOREIGN KEY (libid) REFERENCES MEMBER4(libid);
ALTER TABLE ORDERED4 ADD FOREIGN KEY (callnumber) REFERENCES TITLE4(callnumber);
ALTER TABLE ORDERED4 ADD FOREIGN KEY (ponum) REFERENCES PURCHASE_ORDER4(ponum);
ALTER TABLE ORDERED4 ADD FOREIGN KEY (supplier_id) REFERENCES SUPPLIER4(supplier_id);

--4:
ALTER TABLE MEMBER4 ADD COLUMN join_date date;
ALTER TABLE MEMBER4 ADD COLUMN gender char(1) CHECK(gender IN('M','F'));

--5:
ALTER TABLE READ_BY4 ADD CHECK(timeread >= 0);


/*
#
#                        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');

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

--2:
SELECT TITLE4.name, TITLE4.isbn, TITLE4.callnumber
FROM TITLE4, ORDERED4, PURCHASE_ORDER4
WHERE TITLE4.callnumber = ORDERED4.callnumber AND ORDERED4.ponum = PURCHASE_ORDER4.ponum
GROUP BY TITLE4.name, TITLE4.isbn, TITLE4.callnumber
HAVING COUNT(ORDERED4.supplier_id) > 1 AND SUM(PURCHASE_ORDER4.qty) > 10
ORDER BY SUM(PURCHASE_ORDER4.qty) DESC;

--3:
SELECT MEMBER4.libid, MEMBER4.fname
FROM MEMBER4, READ_BY4
WHERE READ_BY4.libid = MEMBER4.libid
GROUP BY MEMBER4.libid, MEMBER4.fname
HAVING SUM(READ_BY4.timeread) < 0.05 * (SELECT SUM(READ_BY4.timeread) 
                                        FROM READ_BY4)
ORDER BY SUM(READ_BY4.timeread);

--4:
SELECT MEMBER4.fname, MEMBER4.libid
FROM MEMBER4, READ_BY4
WHERE READ_BY4.timeread >= 1 AND MEMBER4.gender = 'M' AND READ_BY4.libid = MEMBER4.libid
ORDER BY MEMBER4.fname;

--5:
SELECT SUPPLIER4.name, AVG(PURCHASE_ORDER4.qty) AS avg
FROM SUPPLIER4, PURCHASE_ORDER4, ORDERED4
WHERE ORDERED4.ponum = PURCHASE_ORDER4.ponum AND ORDERED4.supplier_id = SUPPLIER4.supplier_id
GROUP BY SUPPLIER4.name
HAVING AVG(PURCHASE_ORDER4.qty) > (SELECT AVG(PURCHASE_ORDER4.qty)
                                     FROM PURCHASE_ORDER4);
--6:
SELECT DISTINCT MEMBER4.fname, READ_BY4.libid
FROM MEMBER4, READ_BY4 
WHERE READ_BY4.libid = MEMBER4.libid AND MEMBER4.gender = 'M' 
      AND join_date < TO_DATE('1995/10/10', 'YYYY/MM/DD') 
      AND READ_BY4.timeread <= 2 
GROUP BY MEMBER4.fname, READ_BY4.libid
HAVING COUNT(READ_BY4.callnumber) <= 5
ORDER BY MEMBER4.fname, READ_BY4.libid;