Scribd
Upload
7 views2 pages

SQL Queries for Library Management System

The document provides SQL exercises related to a library management system, including queries to retrieve information about book copies, borrowers, and library branches. It outlines specific SQL commands to answer questions such as the number of copies of a book at a branch, names of borrowers without books checked out, and details of books loaned out. Additionally, it includes index suggestions for optimizing the queries based on the database schema provided.

Uploaded by

duy trinh
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
7 views2 pages

SQL Queries for Library Management System

The document provides SQL exercises related to a library management system, including queries to retrieve information about book copies, borrowers, and library branches. It outlines specific SQL commands to answer questions such as the number of copies of a book at a branch, names of borrowers without books checked out, and details of books loaned out. Additionally, it includes index suggestions for optimizing the queries based on the database schema provided.

Uploaded by

duy trinh
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd

SQL EXERCISE - Solution

Retrieve the required information using SQL language.


Part I. Give a database schema for a library management system as the following picture.

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 as B, BOOK_COPIES as BC, LIBRARY_BRANCH as LB
WHERE [Link] = [Link] and BC. BranchId = [Link] and
Title=”The Lost Tribe” and BranchName=”Sharpstown”

Index : B. BookId (Hash-based/BTree), [Link] (Hash-based/Ordered


List), [Link] (clustered BTree), BranchName(BTree),

Mình nghĩ là cái branch nhiều nhánh nên là sẽ sử dụng btree


Bookid thì truy vấn điểm nên là dùng h
[Link] (Hash/Ordered List)
2. How many copies of the book titled The Lost Tribe are owned by each library branch?
SELECT BranchName, No_Of_Copies
FROM BOOK as B, BOOK_COPIES as BC, LIBRARY_BRANCH as LB
WHERE [Link] = [Link] and BC. BranchId = [Link] and
Title=”The Lost Tribe”

Index : [Link] (Hash-based/Ordered List), [Link] (clustered


Hash/Ordered List), [Link](BTree/Hash/OrderedList)
3. Retrieve the names of all borrowers who do not have any books checked out .
SELECT Name
FROM BORROWER as B
WHERE NOT EXIST ( SELECT *
FROM BOOK_LOANS as BL
WHERE [Link]=[Link] )

Index : [Link] (OrderedList), [Link] (Ordered List/Hash)

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 [Link], [Link], [Link]
FROM BOOKas B, LIBRARY_BRANCH as LB, BOOK_LOANS as BL, BORROWER as R
WHERE [Link]=[Link] and [Link]=[Link] and [Link]=[Link] and
[Link]=”Sharpstown” and [Link]=date()
5. For each library branch, retrieve the branch name and the total number of books loaned out from that
branch.
SELECT [Link], COUNT([Link], CardNo )
FROM BOOK_LOANS as BL, LIBRARY_BRANCH as LB
WHERE [Link]=[Link]
GROUP BY [Link]
6. Retrieve the names, addresses, and number of books checked out for all borrowers who have more than
five books checked out.
SELECT name, address, count(BookId) as Number_of_Books
FROM BORROWER as R, BOOK_LOANS as BL
WHERE [Link]=[Link]
GROUP BY [Link]
HAVING count(BookId) >5
Index : [Link], [Link] (hash/ordered list)
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 [Link], BC.No_of_Copies
FROM BOOK as B, BOOK_AUTHORS as BA, BOOK_COPIES as BC,
LIBRARY_BRANCH as LB
WHERE [Link] = BA.Book_id and [Link] = [Link] and BC. BranchId = [Link]
And [Link]=”Stephen King” and [Link]= “Central”

You might also like