alter_tables.sql

Advanced SQL For this exercise, we will use the library schema. It includes information about book

--drop table read_by4;
--drop table PURCHASE_ORDER4 cascade;
--drop table SUPPLIER4 cascade;
--drop table ORDERED4;

ALTER TABLE BOOK4 
ADD CONSTRAINT BookMember1 FOREIGN KEY(BorrowerID) REFERENCES MEMBER4(LibID),
ADD CONSTRAINT BookMember2 FOREIGN KEY(CallNumber) REFERENCES TITLE4(CallNumber);

CREATE TABLE PURCHASE_ORDER4(
PoNum char (9) NOT NULL,
Qty char (2),
OrderDate DATE,
DueDate DATE,
ReceivedDate DATE,
PRIMARY KEY (PoNum));

CREATE TABLE SUPPLIER4(
Supplier_ID char(9) NOT NULL,
Name char(14) NOT NULL,
Address char(20),
PRIMARY KEY (Supplier_ID));

CREATE TABLE READ_BY4(
CallNumber char(8) NOT NULL,
LibID char(6) NOT NULL,
Timesread char(4),
PRIMARY KEY (CallNumber,LibID),
FOREIGN KEY (CallNumber) REFERENCES TITLE4(CallNumber),
FOREIGN KEY (LibID) REFERENCES MEMBER4(LibID)
);

CREATE TABLE ORDERED4(
CallNumber char(8) NOT NULL,
PoNum char(9) NOT NULL,
Supplier_ID char(9)NOT NULL,
PRIMARY KEY (CallNumber,PoNum,Supplier_ID),
FOREIGN KEY (PoNum) REFERENCES PURCHASE_ORDER4(PoNum),
FOREIGN KEY (Supplier_ID) REFERENCES SUPPLIER4(Supplier_ID),
FOREIGN KEY (CallNumber) REFERENCES TITLE4(CallNumber)
);


ALTER TABLE TITLE4 
ALTER COLUMN ISBN SET NOT NULL,
ALTER COLUMN Name SET NOT NULL;

--ALTER TABLE TITLE4
--ADD CONSTRAINT TitleRead_by1 FOREIGN KEY(CallNumber) REFERENCES READ_BY4(CallNumber),
--ADD CONSTRAINT TitleRead_by2 FOREIGN KEY(CallNumber) REFERENCES ORDERED4(CallNumber);

--ALTER TABLE TITLE4 ADD CONSTRAINT TITLE4 

ALTER TABLE MEMBER4 
ADD COLUMN Join_date DATE,
ADD COLUMN Gender char(2);

ALTER TABLE READ_BY4 
ADD CONSTRAINT TimesReadchk CHECK(TimesRead > 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 ('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');