SQL Exercise with Solutions

SQL Exercise with Solution / Exercises involving JOIN, Subqueries, Group By, Having, etc

Figure 4.6 - A relational database schema for a library database - Taken from Fundamentals of database systems by Elmasri and Navathe

[A] Write the SQL DDL statements to define this database. Include appropriate domains, constraints and referential triggered actions.

[B] Write the SQL queries for the questions given below;

1. How many copies of the book titled The Lost Tribe are owned by the library branch whose name is "Sharpstown"?
2. How many copies of the book titled The Lost Tribe are owned by each library branch?
3. Retrieve the names of all borrowers who do not have any books checked out .
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.
5. For each library branch, retrieve the branch name and the total number of books loaned out from that branch.
6. Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out.
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"

Answers

[A]

CREATE TABLE Book (

Book_id Int PRIMARY KEY,

Title Varchar(200),

Publisher_name Varchar(200),

FOREIGN KEY (Publisher_name) REFERENCES Publisher(Name)ON DELETE SET NULL ON UPDATE CASCADE

);

CREATE TABLE Book_Authors (

Book_id Int NOT NULL,

Author_name Varchar(200) NOT NULL,

PRIMARY KEY (Book_id, Author_name),

FOREIGN KEY (Book_id) REFERENCES Book(Book_id)

ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE Publisher (

Name Varchar(200) PRIMARY KEY,

Address Varchar(400),

Phone Decimal(20)

);

CREATE TABLE Book_Copies (

Book_id Int NOT NULL,

Branch_id Char(4) NOT NULL,

No_of_copies Int DEFAULT 1,

PRIMARY KEY (Book_id, Branch_id),

FOREIGN KEY (Book_id) REFERENCES Book(Book_id)

ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (Branch_id) REFERENCES Library_Branch(Branch_id)

ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE Book_Loans (

Book_id Int NOT NULL,

Branch_id Char(4) NOT NULL,

Card_no Int NOT NULL,

Date_out Date,

Due_date Date,

PRIMARY KEY (Book_id, Branch_id, Card_no),

FOREIGN KEY (Book_id) REFERENCES Book(Book_id)

ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY (Branch_id) REFERENCES Library_Branch(Branch_id)

ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY (Card_no) REFERENCES Borrower(Card_no)

ON DELETE RESTRICT ON UPDATE CASCADE

);

CREATE TABLE Library_Branch (

Branch_id Char(4) PRIMARY KEY,

Branch_name Varchar(200) NOT NULL,

Address Varchar(400)

);

CREATE TABLE Borrower (

Card_no Int PRIMARY KEY,

Name Varchar(200) NOT NULL,

Address Varchar(400),

Phone Decimal(20)

);

[B]

(1)

Solution 1:

SELECT bc.No_Of_Copies

FROM BOOK b, BOOK_COPIES bc, LIBRARY_BRANCH bl

WHERE         b.BookId = bc.BookId AND

                        bc.BranchId = bl.BranchId AND

Title='The Lost Tribe' AND BranchName='Sharpstown';

Solution 2:

SELECT No_Of_Copies

FROM ((BOOK NATURAL JOIN BOOK_COPIES ) NATURAL JOIN

LIBRARY_BRANCH )

WHERE Title='The Lost Tribe' AND BranchName='Sharpstown';

(2)

        SELECT BranchName, No_Of_Copies

FROM ((BOOK NATURAL JOIN BOOK_COPIES ) NATURAL JOIN

LIBRARY_BRANCH )

WHERE Title='The Lost Tribe';

 



(3)

Solution 1:

SELECT        Name

FROM                        BORROWER B

WHERE         CardNo NOT IN (SELECT CardNo

                                                      FROM BOOK_LOANS );

Solution 2:

SELECT        Name

FROM                        BORROWER B

WHERE         NOT EXISTS (SELECT *

                                          FROM BOOK_LOANS L

                                                WHERE B.CardNo = L.CardNo );

(4)


            SELECT B.Title, R.Name, R.Address

FROM BOOK B, BORROWER R, BOOK_LOANS BL, LIBRARY_BRANCH LB

WHERE LB.BranchName='Sharpstown' AND LB.BranchId=BL.BranchId AND

BL.DueDate='today' AND BL.CardNo=R.CardNo AND BL.BookId=B.BookId

(5)


            SELECT        L.BranchName, COUNT(*)

FROM                        LIBRARY_BRANCH L, BOOK_LOANS BL

WHERE         BL.BranchId = L.BranchId

GROUP BY   L.BranchName;

(6)


            SELECT        B.Name, B.Address, COUNT(*)

FROM                        BORROWER B, BOOK_LOANS L

WHERE         B.CardNo = L.CardNo

GROUP BY   B.CardNo, B.Name, B.Address

HAVING        COUNT(*) > 5;

(7)

SELECT        Title, No_Of_Copies 

FROM   (((BOOK_AUTHORS NATURAL JOIN BOOK) NATURAL JOIN 

BOOK_COPIES) NATURAL JOIN LIBRARY_BRANCH)WHERE 

Author_Name='Stephen King' AND BranchName='Central';