我的第二次数据库作业,老师给了满分!!!

SQL
Run the SQL script given to you to create a Library database. Note that each row in the Book table denotes a book copy. Thus, if the library carries three copies of the title "DBMS", there will be three rows in the Book table, one for each copy. Write the SQL statements to do the following against the database (Note: You must express your query in a single SQL statement for each of the following. However, that statement could have sub-queries.):
List the titles of all books written by "Churchill," along with their Year of Publication. Retrieve the titles of all books borrowed by members whose first name is "John" or "Susan". List the names and IDs of all members who have borrowed the "Iliad" and the "Odyssey"—both books. List the names and IDs of all the members who have borrowed all titles written by "Collins". Assume that a member may have borrowed multiple copies of the same title. Find the phone numbers of all members who have borrowed a book written by an author whose last name is "Tanenbaum." Find those members who have borrowed more than three books and list their names, IDs, and the number of books they borrowed. Sort the results in descending order based on the number of books borrowed. List all members who have not borrowed any book. List in alphabetical order the first names of all the members who are residents of Pittsburgh (Phone numbers starting with "412") and who have not borrowed the book titled "Pitt Roads."


To help yourself do your best on this assessment, consult this general list of grading guidelines.
Go to top of question.






题目自带的建立表格的脚本:SQL for exercise2.sql:

DROP TABLE Author ;DROP TABLE Book ;DROP TABLE HOLD ;DROP TABLE Dependent ;DROP TABLE Title ;DROP TABLE Member ;DROP TABLE Section ;DROP TABLE Librarian ;

CREATE TABLE Title(CallNumber VARCHAR(40) NOT NULL,Name VARCHAR(200),ISBN VARCHAR(40),Year DATETIME,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 NUMERIC(20) 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 NUMERIC(20) NOT NULL,CallNumber VARCHAR(40) NOT NULL,HoldDatetime DATETIME,PRIMARY KEY (MemNo, CallNumber),FOREIGN KEY (MemNo) REFERENCES Member(MemNo),FOREIGN KEY (CallNumber) REFERENCES Title(CallNumber));

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

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

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

CREATE TABLE Book(Book_ID NUMERIC(20) NOT NULL,Edition VARCHAR(80),BorrowerMemNo NUMERIC(20),BorrowDueDatetime DATETIME,CallNumber VARCHAR(40),LibCheck NUMERIC(20),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@#, @#1997/01/01@#, @#Homer Publishing@#);INSERT INTO Title VALUES (@#Call124@#, @#Odyssey@#, @#ISBN124@#, @#1997/01/01@#, @#Homer Publishing@#);INSERT INTO Title VALUES (@#Call125@#, @#Database Systems@#, @#ISBN125@#, @#1999/01/01@#, @#AWL@#);INSERT INTO Title VALUES (@#Call126@#, @#Financial Aclearcase/" target="_blank" >ccounting@#, @#ISBN126@#, @#1997/01/01@#, @#McGrawHill@#);INSERT INTO Title VALUES (@#Call127@#, @#Second World War@#, @#ISBN127@#, @#1986/05/01@#, @#McGrawHill@#);INSERT INTO Title VALUES (@#Call128@#, @#Networks@#, @#ISBN128@#, @#1986/05/01@#, @#AWL@#);INSERT INTO Title VALUES (@#Call129@#, @#Pitt Roads@#, @#ISBN129@#, @#1986/05/01@#, @#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 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 HOLD VALUES (123, @#Call123@#, @#2000-10-10@#);

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

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

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

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

INSERT INTO Dependent VALUES (203, @#Luc Whang@#, @#1998-11-11@#, @#Son@#);





我的答案DataBase2.sql:

/***DataBase2.sql written by 张磊 2005-3-28 22:11***/

/*第1题*/SELECT Name, YearFROM TitleWHERE CallNumber = SOME ( SELECT CallNumber                          FROM Author                          WhERE Lname = @#Churchill@# )

/*第2题*/SELECT NameFROM TitleWHERE CallNumber = SOME ( SELECT CallNumber                          FROM Book                          WHERE BorrowerMemNo = SOME ( SELECT MemNo                                                       FROM Member                                                       WHERE Fname = @#Jhon@# OR Fname = @#Susan@# ))

/*第3题*/SELECT Fname, Lname, DriverLicNoFROM MemberWHERE DriverLicNo = SOME ( SELECT BorrowerMemNo                           FROM Book                           WHERE CallNumber = SOME ( SELECT CallNumber                                                     FROM Title                                                     WHERE Name = @#Iliad@# ) ) AND DriverLicNo = SOME ( SELECT BorrowerMemNo                                                                                                       FROM Book                                                                                                       WHERE CallNumber =  SOME ( SELECT CallNumber       FROM Title       WHERE Name = @#Odyssey@# ) )

/*第4题*/SELECT Fname, LnameFROM MemberWhere MemNo IN (SELECT BorrowerMemNo                FROM  (SELECT BorrowerMemNo, CallNumber                       FROM Book                       WHERE CallNumber IN ( SELECT CallNumber                                                 FROM Author                                                 WHERE Lname = @#Collins@# )                       UNION                       SELECT BorrowerMemNo, CallNumber                       FROM Book                       WHERE CallNumber IN ( SELECT CallNumber                                                 FROM Author                                                 WHERE Lname = @#Collins@# )) AS MidTab                GROUP BY BorrowerMemNo                HAVING COUNT(BorrowerMemNo) = (SELECT COUNT(Lname)                                               FROM Author                                               WHERE Lname = @#Collins@#))

/*第5题*/SELECT PhoneNumberFROM MemberWhere MemNo IN ( SELECT BorrowerMemNo                 FROM Book                 WHERE CallNumber = SOME ( SELECT CallNumber                                          FROM Author                                          WHERE Lname = @#Tanenbaum@# ) )

/*第6题*/SELECT Book.Book_ID, Member.Fname, Member.Lname, Member.MemNoFROM Member, BookWHERE Member.MemNo = Book.BorrowerMemNoGROUP BY Book.Book_ID, Member.Fname, Member.Lname, Member.MemNoHAVING (SELECT COUNT(Book.BorrowerMemNo)        FROM Book        WHERE Member.MemNo = Book.BorrowerMemNo) > 3ORDER BY COUNT ( Book.Book_ID ) DESC 

/*第7题*/SELECT Member.Fname, Member.Lname, Member.MemNoFROM MemberWHERE Member.MemNo NOT IN ( SELECT  Member.MemNo                              FROM Member, Book                            WHERE Member.MemNo = Book.BorrowerMemNo                            GROUP BY Member.MemNo                            HAVING COUNT ( Book.BorrowerMemNo ) >= 1 )

/*第8题*/SELECT Member.FnameFROM MemberWHERE Member.MemNo NOT IN ( SELECT Book.BorrowerMemNo                            FROM Book                            WHERE  Book.CallNumber = (                                   SELECT  Title.CallNumber                                     FROM Title                                   WHERE Title.Name = @#Pitt Roads@# ))  AND PhoneNumber LIKE @#412%@#

原文转自：http://www.ltesting.net