修改过的ex2.sql

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

/*
*@author:wangzhesi
*053597
*/
CREATE TABLE Title(
CallNumber VARCHAR(40) NOT NULL,
Name VARCHAR(200),
ISBN VARCHAR(40),
Year DATE,
Publisher VARCHAR(80),
PRIMARY KEY (CallNumber),
UNIQUE (ISBN));

CREATE TABLE Author(
CallNumber VARCHAR(40) NOT NULL,
Fname VARCHAR(40) NOT NULL,
MI VARCHAR(10),
Lname VARCHAR(40) NOT NULL,
PRIMARY KEY (CallNumber, Fname, Lname),
FOREIGN KEY (CallNumber) REFERENCES Title(CallNumber));

CREATE TABLE Member(
MemNo integer NOT NULL,
DriverLicState VARCHAR(20),
DriverLicNo VARCHAR(40),
Fname VARCHAR(20),
MI VARCHAR(10),
Lname VARCHAR(20),
Address VARCHAR(250),
PhoneNumber VARCHAR(15),
PRIMARY KEY (MemNo));

CREATE TABLE HOLD(
MemNo integer NOT NULL,
CallNumber VARCHAR(40) NOT NULL,
HoldDate DATE,
PRIMARY KEY (MemNo, CallNumber),
FOREIGN KEY (MemNo) REFERENCES Member(MemNo),
FOREIGN KEY (CallNumber) REFERENCES Title(CallNumber));

CREATE TABLE Librarian(
SSN bigint NOT NULL,
Name VARCHAR(80),
Address VARCHAR(250),
Salary NUMERIC(9,2),
Gender CHAR(1),
Birthday DATE,
SuperSSN bigint,
Section integer,
PRIMARY KEY (SSN),
FOREIGN KEY (SuperSSN) REFERENCES LIBRARIAN(SSN));

CREATE TABLE Section(
SectNo integer NOT NULL,
Name VARCHAR(80),
HeadSSN bigint,
PRIMARY KEY (SectNo),
FOREIGN KEY (HeadSSN) REFERENCES Librarian(SSN));

ALTER TABLE Librarian
ADD CONSTRAINT LibSection FOREIGN KEY (Section) REFERENCES Section(SectNo);

CREATE TABLE Dependent(
LibSSN bigint NOT NULL,
Name VARCHAR(40) NOT NULL,
Birthday DATE,
Kinship VARCHAR(40),
PRIMARY KEY (LibSSN, Name),
FOREIGN KEY (LibSSN) REFERENCES Librarian(SSN));

CREATE TABLE Book(
Book_ID integer NOT NULL,
Edition VARCHAR(80),
BorrowerMemNo bigint,
BorrowDueDate DATE,
CallNumber VARCHAR(40),
LibCheck bigint,
PRIMARY KEY (Book_ID),
FOREIGN KEY (CallNumber) REFERENCES Title(CallNumber),
FOREIGN KEY (BorrowerMemNo) REFERENCES Member(MemNo),
FOREIGN KEY (LibCheck) REFERENCES Librarian(SSN));

INSERT INTO Title VALUES ('Call123', 'Iliad', 'ISBN123', TO_DATE('1997/01/01', 'YYYY/MM/DD'), 'Homer Publishing');
INSERT INTO Title VALUES ('Call124', 'Odyssey', 'ISBN124', TO_DATE('1997/01/01', 'YYYY/MM/DD'), 'Homer Publishing');
INSERT INTO Title VALUES ('Call125', 'Database Systems', 'ISBN125', TO_DATE('1999/01/01', 'YYYY/MM/DD'), 'AWL');
INSERT INTO Title VALUES ('Call126', 'Financial Accounting', 'ISBN126', TO_DATE('1997/01/01', 'YYYY/MM/DD'), 'McGrawHill');
INSERT INTO Title VALUES ('Call127', 'Second World War', 'ISBN127', TO_DATE('1986/05/01', 'YYYY/MM/DD'), 'McGrawHill');
INSERT INTO Title VALUES ('Call128', 'Networks', 'ISBN128', TO_DATE('1986/05/01', 'YYYY/MM/DD'), 'AWL');
INSERT INTO Title VALUES ('Call129', 'Pitt Roads', 'ISBN129', TO_DATE('1986/05/01', 'YYYY/MM/DD'), 'AWL');

INSERT INTO Member VALUES (123, 'PA', '123', 'John', '', 'Summers', '4615 Forbes Ave, Pittsburgh, PA 15213', '412-268-0001');
INSERT INTO Member VALUES (124, 'GA', '124', 'Jon', '', 'Butterworth', '10 Fifth Ave, Atlanta, GA 30332', '404-894-0001');
INSERT INTO Member VALUES (125, 'PA', '125', 'Susan', 'B', 'Carlione', '4600 Verona Road, Pittsburgh, PA 15217', '412-200-0001');
INSERT INTO Member VALUES (126, 'NC', '126', 'Mohammed', '', 'Ismail', '250 Peachtree Street, Salem, NC 15213', '421-268-0001');
INSERT INTO Member VALUES (127, 'PA', '127', 'Asterio', '', 'Tanaka', '415 Craig Street, Pittsburgh, PA 15213', '412-220-0001');

INSERT INTO HOLD VALUES (123, 'Call123', TO_DATE('2000-10-10', 'YYYY-MM-DD'));


INSERT INTO Author VALUES ('Call123', 'Hello', '', 'Homer');
INSERT INTO Author VALUES ('Call124', 'Hello', '', 'Homer');
INSERT INTO Author VALUES ('Call125', 'Jack', '', 'Collins');
INSERT INTO Author VALUES ('Call126', 'Jack', '', 'Collins');
INSERT INTO Author VALUES ('Call127', 'Winston', '', 'Churchill');
INSERT INTO Author VALUES ('Call127', 'John', '', 'Keegan');
INSERT INTO Author VALUES ('Call128', 'Jeff', '', 'Tanenbaum');
INSERT INTO Author VALUES ('Call129', 'Carlos', '', 'Tanaka');

INSERT INTO Librarian VALUES (201, 'Ashoka Savasere', '4615 Forbes Ave, Pittsburgh, PA 15213', 40000, 'F', TO_DATE('1972-06-02', 'YYYY-MM-DD'), NULL, NULL);
INSERT INTO Librarian VALUES (202, 'Alfred Watkins', '4615 Forbes Ave, Pittsburgh, PA 15213', 40000, 'M', TO_DATE('1972-07-02', 'YYYY-MM-DD'), NULL, NULL);
INSERT INTO Librarian VALUES (203, 'Yong-Chul Oh', '4600 Forbes Ave, Pittsburgh, PA 15213', 40000, 'M', TO_DATE('1960-06-02', 'YYYY-MM-DD'), NULL, NULL);
INSERT INTO Librarian VALUES (204, 'Shamkant Navathe', '4615 Forbes Ave, Pittsburgh, PA 15213', 40000, 'M', TO_DATE('1975-06-02', 'YYYY-MM-DD'), NULL, NULL);

INSERT INTO Section VALUES (1, 'CheckOut', 201);
INSERT INTO Section VALUES (2, 'Reference', 204);

UPDATE Librarian Set Section = 1
WHERE SSN < 204;
UPDATE Librarian Set Section = 2
WHERE SSN = 204;

INSERT INTO Book VALUES (123, '1', 123, TO_DATE('2000-12-12', 'YYYY-MM-DD'), 'Call123', 202);
INSERT INTO Book VALUES (223, '1', 125, TO_DATE('2000-11-11', 'YYYY-MM-DD'), 'Call123', 201);
INSERT INTO Book VALUES (124, '1', 124, TO_DATE('2000-06-09', 'YYYY-MM-DD'), 'Call124', 201);
INSERT INTO Book VALUES (224, '1', 125, TO_DATE('2000-11-11', 'YYYY-MM-DD'), 'Call124', 201);
INSERT INTO Book VALUES (125, '1', 125, TO_DATE('2000-11-11', 'YYYY-MM-DD'), 'Call125', 201);
INSERT INTO Book VALUES (225, '1', NULL, NULL, 'Call125', NULL);
INSERT INTO Book VALUES (126, '1', 125, TO_DATE('2000-11-11', 'YYYY-MM-DD'), 'Call126', 201);
INSERT INTO Book VALUES (226, '1', 124, TO_DATE('2000-06-09', 'YYYY-MM-DD'), 'Call126', 202);
INSERT INTO Book VALUES (326, '1', 124, TO_DATE('2000-06-09', 'YYYY-MM-DD'), 'Call126', 202);
INSERT INTO Book VALUES (127, '1', NULL, NULL, 'Call127', NULL);
INSERT INTO Book VALUES (128, '1', 125, TO_DATE('2000-11-11', 'YYYY-MM-DD'), 'Call128', 201);
INSERT INTO Book VALUES (228, '1', 126, TO_DATE('2000-10-10', 'YYYY-MM-DD'), 'Call128', 202);
INSERT INTO Book VALUES (129, '1', 123, TO_DATE('2000-12-12', 'YYYY-MM-DD'), 'Call129', 202);
INSERT INTO Book VALUES (229, '1', 125, TO_DATE('2000-12-12', 'YYYY-MM-DD'), 'Call129', 202);

INSERT INTO Dependent VALUES (203, 'Luc Whang', TO_DATE('1998-11-11', 'YYYY-MM-DD'), 'Son');

--1
SELECT CallNumber,Name
FROM Title
WHERE Title.CallNumber IN (SELECT CallNumber
                           FROM Author
			   WHERE Author.Lname='Churchill' 
                           )
ORDER BY Title.Year;

--2
SELECT CallNumber,Name
FROM Title
WHERE Title.CallNumber IN (SELECT CallNumber
                           FROM Book
                           WHERE Book.BorrowerMemNo IN (SELECT Memno
                                                        FROM Member
                                                        WHERE Member.Fname='John' OR Member.Fname='Susan'
                                                        )
                            );

--3
SELECT Memno,Fname,Lname
FROM Member
WHERE Member.Memno IN ((SELECT BorrowerMemNo
                       FROM Book
                       WHERE Book.CallNumber IN (SELECT CallNumber
                                                 FROM Title
                                                 WHERE Title.Name='Iliad'
                                                 )
		        )

                    INTERSECT
                      (SELECT BorrowerMemNo
                       FROM Book
                       WHERE Book.CallNumber IN (SELECT CallNumber
                                                 FROM Title
                                                 WHERE Title.Name='Odyssey'
                                                 )
		        ));

--4
SELECT Memno,Fname,Lname
FROM   Member
WHERE  NOT EXISTS (SELECT *
               FROM  Author
               WHERE Author.Lname='Collins' 
               AND NOT EXISTS (SELECT *
                           FROM Book
                           WHERE Member.Memno=Book.BorrowerMemNo
                           AND Author.CallNumber=Book.CallNumber                                  
                           )
	       );

--5
SELECT PhoneNumber
FROM Member,Book,Author,Title
WHERE Member.Memno=Book.BorrowerMemNo 
      AND Author.CallNumber=Title.CallNumber
      AND Author.Lname='Tanenbaum'
      AND Title.CallNumber=Book.CallNumber;

--6
SELECT Newtable.Fname, Newtable.Lname, Newtable.Memno, Newtable.NumberOfBook
FROM(SELECT Fname, Lname, Memno, COUNT(Memno) AS NumberOfBook
     FROM Member,Book
     WHERE Member.Memno = Book.BorrowerMemNo 
     GROUP BY Fname,Lname,Memno
     ORDER BY NumberOfBook) AS Newtable
WHERE Newtable.NumberOfBook > 3
ORDER BY Newtable.NumberOfBook;

--7
SELECT Member.*
FROM Member
WHERE Member.Memno NOT IN(SELECT Book.BorrowerMemNo
                          FROM Book
                          WHERE Member.Memno = Book.BorrowerMemNo 
                          );
--8
SELECT Member.*
FROM Member
WHERE Member.PhoneNumber LIKE '412%'
EXCEPT
SELECT Member.*
FROM Member,Title,Book
WHERE Title.CallNumber=Book.CallNumber 
AND Member.Memno = Book.BorrowerMemNo
AND Title.Name='Pitt Roads'
ORDER BY Fname;