Lab04: Library Database Queries Solutions and Results | Đại học Hoa Sen

lOMoAR cPSD| 47206071
0ab6cbeffdf8d5a0420041bcc3b9fa5c.docx 1 Lab 03 Library Database
Queries (Specify the queries of Exercise 6.18)
1. How many copies of the book titled The Lost Tribe are owned by the library
branch whose name is "Sharpstown"? SELECT No_Of_Copies
FROM ( Book INNER JOIN Book_Copies ON
Book.Book_ID=Book_Copies.Book_ID) INNER JOIN Library_Branch ON
Book_Copies.Branch_ID=Library_Branch.Branch_ID
WHERE Title='The Lost Tribe' AND Branch_Name='Sharpstown' --Or SELECT No_Of_Copies
FROM Book B, Book_Copies C, Library_Branch L
WHERE B.Book_ID=C.Book_ID And C.Branch_ID=L.Branch_ID And
Title='The Lost Tribe' AND Branch_Name='Sharpstown' Result: No_Of_Copies ------------ 10
2. How many copies of the book titled The Lost Tribe are owned by each library branch?
SELECT Branch_Name, No_Of_Copies
FROM ( Book INNER JOIN Book_Copies ON
Book.Book_ID=Book_Copies.Book_ID) INNER JOIN Library_Branch ON
Book_Copies.Branch_ID=Library_Branch.Branch_ID WHERE Title='The Lost Tribe' --Or
SELECT Branch_Name, No_Of_Copies
FROM Book B, Book_Copies C, Library_Branch L
WHERE B.Book_ID=C.Book_ID And C.Branch_ID=L.Branch_ID And Title='The Lost Tribe' Result: Branch_Name No_Of_Copies
-------------------- ------------ lOMoAR cPSD| 47206071
0ab6cbeffdf8d5a0420041bcc3b9fa5c.docx 2 Sharpstown 10 Central 10
3. Retrieve the names of all borrowers who do not have any books checked out. SELECT Name FROM BORROWER B WHERE NOT EXISTS ( SELECT * FROM BOOK_LOANS L WHERE B.Card_No = L.Card_No ) Result: empty
4. For each book that is loaned out from the "Sharpstown" branch and whose
DueDate is today, retrieve the book title, the borrower's name, and the borrower's address.
SELECT B.Title, R.Name, R.Address
FROM BOOK B, BORROWER R, BOOK_LOANS BL, LIBRARY_BRANCH LB
WHERE LB.Branch_Id=BL.Branch_Id AND
BL.Card_No=R.Card_No AND BL.Book_Id=B.Book_Id
AND LB.Branch_Name='Sharpstown' AND BL.Due_Date=Getdate() Result: empty
5. For each library branch, retrieve the branch name and the total number of
books loaned out from that branch.
SELECT L.Branch_Name, COUNT(*)
FROM BOOK_COPIES B, LIBRARY_BRANCH L
WHERE B.Branch_Id = L.Branch_Id GROUP BY L.Branch_Name Result: Branch_Name
-------------------- ----------- Central 6 Sharpstown 5
6. Retrieve the names, addresses, and number of books checked out for all
borrowers who have more than five books checked out.
SELECT B.Card_No, B.Name, B.Address, COUNT(*) FROM BORROWER B, BOOK_LOANS L lOMoAR cPSD| 47206071
0ab6cbeffdf8d5a0420041bcc3b9fa5c.docx 3 WHERE B.Card_No = L.Card_No
GROUP BY B.Card_No, B.Name, B.Address HAVING COUNT(*) > 5
Result: empty (Because: count(*)=4)
7. For each book authored (or co-authored) by "Stephen King", retrieve the title
and the number of copies owned by the library branch whose name is "Central". SELECT Title, No_Of_Copies
FROM BOOK_AUTHORS BA, BOOK B, BOOK_COPIES BC, LIBRARY_BRANCH LB
WHERE BA.Book_ID = B.Book_ID AND B.Book_ID =
BC.Book_ID AND BC.Branch_ID = LB.Branch_ID
AND Author_Name = 'Stephen King' and Branch_Name = 'Central' Result: Title No_Of_Copies
------------------------------ ------------ Carrie 10 The Dark Tower VII 10
Note: Save to file Library_Queries.sql