模板
教程
環境
性能
功能
管理
我的第二次數據庫作業,老師給了滿分!!!
SQLRun 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.):
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 Librarian
ADD 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, Year
FROM Title
WHERE CallNumber = SOME ( SELECT CallNumber
FROM Author
WhERE Lname = 'Churchill' )
/*第2題*/
SELECT Name
FROM Title
WHERE CallNumber = SOME ( SELECT CallNumber
FROM Book
WHERE BorrowerMemNo = SOME ( SELECT MemNo
FROM Member
WHERE Fname = 'Jhon' OR Fname = 'Susan' ))
/*第3題*/
SELECT Fname, Lname, DriverLicNo
FROM Member
WHERE 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, Lname
FROM Member
Where 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 PhoneNumber
FROM Member
Where 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.MemNo
FROM Member, Book
WHERE Member.MemNo = Book.BorrowerMemNo
GROUP BY Book.Book_ID, Member.Fname, Member.Lname, Member.MemNo
HAVING (SELECT COUNT(Book.BorrowerMemNo)
FROM Book
WHERE Member.MemNo = Book.BorrowerMemNo) > 3
ORDER BY COUNT ( Book.Book_ID ) DESC
/*第7題*/
SELECT Member.Fname, Member.Lname, Member.MemNo
FROM Member
WHERE 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.Fname
FROM Member
WHERE 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%'
