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