ADVANCE DATABASE LIBRARY QUERY PRACTICE

ADVANCE DATABASE MANAGEMENT SYSTEM

SQL EXERCISE

Retrieve the required information using SQL language.

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

                             

·         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"

Library Query
===================
1.
select count (title) from book_copies , library_branch where
book.title='The Lost Tribe' and library_branch.branchname='Sharpstown';

2.
select count (bookid) from book_copies, library_branch where
book.title='The Lost Tribe';
3.
select * from borrower, book_loans where borrower.cardno != book_loans.cardno ;
4.
select title from book,library_branch, book_loans,borrower where library_branch.brachname=
'Sharpstown'and book_loans.dateout='05-Oct-16' and book_loans.cardno=borrower.cardno and
book_loans.bookid=book.bookid ;
5.
select branchname,count(bookid) from book_loans, library_branch where
book_loans.branchid=library_branch.branchid ;
6.
select name,address,count(cardno) from books_loans , borrower where
borrower.cardno=book_loans.cardno ;
7.
select title ,count(bookid) from book_authors, book,library_branch where book_authors='Stephen'
and book.bookid=book.authors.bookid and library_branch='Central';