Search This Blog

Tuesday, October 11, 2016

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';

No comments:

Post a Comment

Split A String By Coma (,)

Step 1.  create or replace type split_tbl as table of varchar2(32767); Step 2. create or replace function split (     p_list varchar2,...