- 1. List the titles of all books written by "Churchill" and their publication dates.
- 2. Retrieve the titles of all the books that the members named "John" or "Susan" loaned.
- 3. List the names and IDs of all members who have already borrowed the books "Iliad" and "Odyssey."
- 4. List the names and IDs of all members who have already borrowed all the books written by "Collins".Assume that a member may have already borrowed many of the same books.
- 5. Find the phone numbers of all members who have borrowed a book written by the author of the last name "Tanenbaum"
- 6. Find out who has borrowed at least three books, and list their names, IDs, and the number of books they have loaned. The results found are sorted by the number of loaned books.
- 7. List members who did not borrow any books
- 8. List the names of members of Pittsburgh residents (phone numbers starting with "412") and no and "Pitt Roads" in alphabetical order.
This is the EX2 of the SSD7 practice. It is expected that the extreme guests will write a series. The update frequency will be synchronized with the 2013 level progress of the NWPU database system experiment class.Click the tab or search "SSD7" on this site to get the updated SSD7 answer.Previous Exercise: SSD7_EX1 [Relational Database]
Run a given SQL script to create a library database.Note that each row in the Book table refers to a copy of a book. Therefore, if a database has three "DBMSs", then the Book table will have three rows, one for each row. Write SQL statements to accomplish the following operations.(Note: For each operation, you must use a SQL statement to express your query. However, a statement can contain subqueries.) Original: Run the SQL script given to you to create a Library database. Note that each row in the Book table markeds 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.): The homework is still done well by oneself. The following is the result that I do, use for exchange study, do not blindly plagiarize, it is one's own to learn!If the answer is flawed, please correct me. The level is limited and for reference only:
1. List the titles of all books written by "Churchill" and their publication dates.
|
1 2 3 |
Select t1.name,t1.year From title t1,author a1 Where t1.callnumber=a1.callnumber and a1.lname='Churchill'; |
2. Retrieve the titles of all the books that the members named "John" or "Susan" loaned.
|
1 2 3 4 5 6 7 8 9 10 |
Select name From title Where callnumber in(( Select callnumber From book Where borrowermemno in (select memno From member Where (fname='John' or fname='Susan'))) ) |
3. List the names and IDs of all members who have already borrowed the books "Iliad" and "Odyssey."
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
/* Question 3 */ Select fname, lname,memno From member Where memno in( Select borrowermemno From book Where callnumber in(( Select callnumber From title Where name='Iliad' ))and memno in( Select borrowermemno From book Where callnumber in(( Select callnumber From title Where name='Odyssey' ) ); |
4. List the names and IDs of all members who have already borrowed all the books written by "Collins".Assume that a member may have already borrowed many of the same books.
|
1 2 3 4 5 6 7 8 9 |
/* Question 4 */ Select m1.fname,m1.Lname,m1.MemNo From author a1,book b1,member m1 Where a1.lname='Collins'and b1.callnumber=a1.callnumber and m1.MemNo=b1.BorrowerMemNo GROUP BY m1.MemNo HAVING COUNT(DISTINCT b1.callnumber)=(SELECT COUNT(DISTINCT callnumber) FROM author WHERE lname='Collins' ); |
5. Find the phone numbers of all members who have borrowed a book written by the author of the last name "Tanenbaum"
|
1 2 3 4 5 6 |
/* Question 5*/ Select m1.PhoneNumber From author a1,book b1,member m1 Where a1.lname='Tanenbaum'and b1.callnumber=a1.callnumber and b1.BorrowerMemNo=m1.MemNo GROUP BY b1.BorrowerMemNo HAVING COUNT(*)=1; |
6. Find out who has borrowed at least three books, and list their names, IDs, and the number of books they have loaned. The results found are sorted by the number of loaned books.
|
1 2 3 4 5 6 7 |
/* Question 6 */ Select m1.Fname, m1.Lname, COUNT(b1.BorrowerMemNo) 'Total number of books' FROM book b1,member m1 Where m1.MemNo=b1.BorrowerMemNo GROUP BY b1.BorrowerMemNo HAVING COUNT(*)>=3 ORDER BY b1.BorrowerMemNo; |
7. List members who did not borrow any books
|
1 2 3 4 5 6 7 8 |
/* Question 7 */ Select Fname,Lname From member Where NOT EXISTS( SELECT * From book Where member.MemNo=BorrowerMemNo ); |
8. List the names of members of Pittsburgh residents (phone numbers starting with "412") and no and "Pitt Roads" in alphabetical order.
|
1 2 3 4 5 6 7 8 |
/* Item 8 */ Select Lname From member Where MemNo NOT IN (select b1.BorrowerMemNo From title t1,book b1 Where t1.Name='Pitt Roads' and t1.CallNumber=b1.CallNumber) AND PhoneNumber like '412%'; |
This article has been printed on copyright and is protected by copyright laws. It must not be reproduced without permission.If you need to reprint, please contact the author or visit the copyright to obtain the authorization. If you feel that this article is useful to you, you can click the "Sponsoring Author" below to call the author!
Reprinted Note Source: Baiyuan's Blog>>https://wangbaiyuan.cn/en/ssd7-ex2-sql-2.html
No Comment