exe2(2).sql

ssd7卡耐基教程

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 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');

		/*The follow code is what i did in this work*/

/*1. List the titles of all books written by "Churchill," along with their Year of Publication.*/
SELECT Title.name,Title.Year
FROM Title,Author
WHERE Author.lname='Churchill' AND Author.callnumber=title.callnumber

/*
The result is:
"Second World War";"1986-05-01"
*/

/*2.Retrieve the titles of all books borrowed by members whose first name is "John" or "Susan".*/

SELECT DISTINCT Title.name
FROM Title,MEMBER,Book
WHERE (MEMBER.fname='John' or MEMBER.fname='Susan' ) AND MEMBER.MemNo=Book.borrowerMemNo AND Book.callnumber=Title.callnumber
/*
The result is:
"Database Systems"
"Financial Accounting"
"Iliad"
"Networks"
"Odyssey"
"Pitt Roads"

*/
/*3.List the names and IDs of all members who have borrowed the "Iliad" and the "Odyssey"—both books. */
SELECT MEMBER.Fname,MEMBER.MemNo
FROM MEMBER--,Book,Title
WHERE MEMBER.MemNo IN (
SELECT MEMBER.MemNo
FROM MEMBER,Title,Book
WHERE Title.name='Iliad' AND MEMBER.MemNo=Book.borrowerMemNo AND Book.callnumber=Title.callnumber
) AND MEMBER.MemNo IN (
SELECT MEMBER.MemNo
FROM MEMBER,Title,Book
WHERE Title.name='Odyssey' AND MEMBER.MemNo=Book.borrowerMemNo AND Book.callnumber=Title.callnumber
);
/*
The result is:
"Susan";125
*/
/*4.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.*/

SELECT m.fname,m.Lname,m.MemNo
FROM MEMBER m
WHERE NOT EXISTS (
SELECT *
FROM Author a
WHERE NOT EXISTS
(SELECT *
FROM Book b
WHERE(a.Lname<>'Collins') OR m.MemNo=b.borrowerMemNo AND a.callnumber=b.callnumber
)
)
/*
The result is:
"Susan";"Carlione";125
*/
/*5.Find the phone numbers of all members who have borrowed a book written by an author whose last name is "Tanenbaum." */

SELECT Member.PhoneNumber,Member.Fname,Member.Lname
FROM Member
Where MemNo IN 
(SELECT BorrowerMemNo
 FROM Book
WHERE CallNumber in
(SELECT CallNumber
FROM Author
WHERE Author.Lname = 'Tanenbaum' ) )
/*
The result is:
"412-200-0001";"Susan";"Carlione"
"421-268-0001";"Mohammed";"Ismail"

*/
/*6.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. */

SELECT Member.Fname,Member.Lname,Member.MemNo,COUNT(Book.CallNumber) AS borrowednum
FROM Member,Book
WHERE Member.MemNo=Book.borrowerMemNo
GROUP BY Member.Fname,Member.Lname,Member.MemNo
HAVING COUNT(Book.CallNumber)>3
ORDER BY COUNT (Book.CallNumber) DESC 
/*
The result is:
"Susan";"Carlione";125;6
*/
/*7.List all members who have not borrowed any book.*/

SELECT Member.*
FROM Member
WHERE MemNo NOT IN(SELECT BorrowerMemNo
FROM Book
) IS NOT FALSE
/*
The result is:
127;"PA";"127";"Asterio";"";"Tanaka";"415 Craig Street, Pittsburgh, PA 15213";"412-220-0001"
*/
/*8.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." */

SELECT DISTINCT m.Fname
FROM Member m
WHERE m.Phonenumber LIKE '412%' AND m.memno NOT IN (
SELECT borrowermemno
FROM book b,Title t
WHERE t.Callnumber = b.Callnumber AND b.borrowermemno = m.memno AND t.name = 'Pitt Roads' 
)
ORDER BY m.Fname ASC
/*
The result is:
"Asterio"
*/