Completely Solved C, C++ Programs Assignment.




SQL-Query performs on a Book Borrower Database system

Filed Under: ,

Problem Statement:- Query performs on a Book Borrower Database system

Consider the following employee database:-
i)Book(Bookid,Title,Author,Subject)
ii)Borrower(Bid,Bname)
iii)Borrows(Bookid,Bid,Date_of_issue,Date_of_return)

Given Queries:-
1.Give all books on Physics and Mathematics.
2.Find out the title and author of all the books issued to a person whose Bid=B001.
3.Find out the names of the person who has issued books written by C.J.Date.
4.Find all the books written by Maity Ghosh on Mathematics.
5.Find the total number of books in the library.
6.Find the name of the person who has borrowed one or more books on DBMS.

Commands for table creations of the schemas in:-
i) SQL> create table Book
2 ( bookid varchar2(4) primary key,
3 title varchar2(20) NOT NULL,
4 author varchar2(20) NOT NULL,
5 subject varchar2(20) NOT NULL );
Table Created.

Name Null? Type
----------------------------------------- -------- -------------
BOOKID NOT NULL VARCHAR2(4)
TITLE NOT NULL VARCHAR2(20)
AUTHOR NOT NULL VARCHAR2(20)
SUBJECT NOT NULL VARCHAR2(20)

SQL> INSERT INTO Book VALUES (‘C001’ , ‘DB1’ , ‘C.J.Date’ , ‘DBMS’);
1 row created.
SQL> INSERT INTO Book VALUES (‘C002’ , ‘MAT1’ , ‘Maity Ghosh’ , ‘MATH’);
1 row created.
SQL> INSERT INTO Book VALUES (‘C003’ , ‘DB2’ , ‘Korth’ , ‘DBMS’);
1 row created.
SQL> INSERT INTO Book VALUES (‘C004’ , ‘ECO’ , ‘Amartya Sen’ , ‘ECONOMICS’);
1 row created.
SQL> INSERT INTO Book VALUES (‘C005’ , ‘NET’ , ‘Forouzan’ , ‘NETWORKING’);
1 row created.
SQL> INSERT INTO Book VALUES (‘C006’ , ‘MAT2’ , ‘Ghosh Chakraborty’ , ‘MATH’);
1 row created.
SQL> INSERT INTO Book VALUES (‘C007’ , ‘MAT3’ , ‘S.N.De’ , ‘MATH’);
1 row created.
SQL> INSERT INTO Book VALUES (‘C008’ , ‘OS1’ , ‘Peter Galvin’ , ‘OPERATING SYSTEMS’);
1 row created.
SQL> INSERT INTO Book VALUES (‘C009’ , ‘PHY1’ , ‘Doyari Majumder Maity’ , ‘PHYSICS’);
1 row created.
SQL> INSERT INTO Book VALUES (‘C010’ , ‘ARC1’ , ‘J.P.Hayes’ , ‘ARCHITECTURE’);
1 row created.

BOOK TITLE AUTHOR SUBJECT
---- -------------------- -------------------- -----------------
C001 DB1 C.J.Date DBMS
C002 MAT1 Maity Ghosh MATH
C003 DB2 Korth DBMS
C004 ECO Amartya Sen ECONOMICS
C005 NET Forouzan NETWORKING
C006 MAT2 Ghosh Chakraborty MATH
C007 MAT3 S.N.De MATH
C008 OS1 Peter Galvin OPERATING SYSTEMS

ii) SQL> create table Borrower
2 ( bid varchar2(4) primary key,
3 name varchar2(20) );
Table Created.

Name Null? Type
----------------------------------------- -------- ------------
BID NOT NULL VARCHAR2(4)
NAME VARCHAR2(20)

SQL> INSERT INTO Borrower VALUES (‘B001’ , ‘Ranjan Mehra’);
1 row created.
SQL> INSERT INTO Borrower VALUES (‘B002’ , ‘Tanmoy Das’);
1 row created.
SQL> INSERT INTO Borrower VALUES (‘B003’ , ‘Tarun Saha’);
1 row created.
SQL> INSERT INTO Borrower VALUES (‘B004’ , ‘Sanjay Saha’);
1 row created.
SQL> INSERT INTO Borrower VALUES (‘B005’ , ‘Deep Dutta’);
1 row created.
SQL> INSERT INTO Borrower VALUES (‘B006’ , ‘Govind Sarkar’);
1 row created.



BID NAME
---- --------------
B001 Ranjan Mehra
B002 Tanmoy Das
B003 Tarun Saha
B004 Sanjay Saha
B005 Deep Dutta
B006 Govind Sarkar

iii) SQL> create table Borrows
2 ( bookid varchar2(4),
3 bid varchar2(4),
4 date_of_issue date,
5 date_of_return date,
6 constraints fk_book_bookid
7 FOREIGN KEY (bookid) references Book(bookid),
8 constraints fk_borrower_bid
9 FOREIGN KEY (bid) references Borrower(bid) );
Table Created.

Name Null? Type
----------------------------------------- -------- -----------
BOOKID VARCHAR2(4)
BID VARCHAR2(4)
DATE_OF_ISSUE DATE
DATE_OF_RETURN DATE

SQL> INSERT INTO Borrows VALUES (‘C001’ , ‘B001’ , ’02-May-09’ , ’09-May-09’);
1 row created.
SQL> INSERT INTO Borrows VALUES (‘C002’ , ‘B002’ , ’03-May-09’ , ’10-May-09’);
1 row created.
SQL> INSERT INTO Borrows VALUES (‘C003’ , ‘B003’ , ’03-May-09’ , ’10-May-09’);
1 row created.
SQL> INSERT INTO Borrows VALUES (‘C001’ , ‘B005’ , ’04-May-09’ , ’11-May-09’);
1 row created.
SQL> INSERT INTO Borrows VALUES (‘C009’ , ‘B006’ , ’05-May-09’ , ’12-May-09’);
1 row created.
SQL> INSERT INTO Borrows VALUES (‘C003’ , ‘B001’ , ’07-May-09’ , ’14-May-09’);
1 row created.

BOOK BID DATE_OF_I DATE_OF_R
---- ---- --------- ---------
C001 B001 02-MAY-09 09-MAY-09
C002 B002 03-MAY-09 10-MAY-09
C003 B003 03-MAY-09 10-MAY-09
C001 B005 04-MAY-09 11-MAY-09
C003 B001 07-MAY-09 14-MAY-09


Solution of the queries:-

Query Number 1 :
Give all books on Physics and Mathematics.

SQL Statement
SQL> select bookid,title
2 from Book
3 where subject=’PHYSICS’ or subject=’MATH’;

Output to Query number 1

BOOK TITLE
--------- ----------------------
C002 MAT1
C006 MAT2
C007 MAT3
C009 PHY1


Query Number 2 :
Find out the title and author of all the books issued to a person whose Bid=B001.

SQL Statement
SQL> select title,author
2 from Book,Borrows
3 where Book.bookid=Borrows.bookid and
4 bid=’B001’;

Output to Query number 2

TITLE AUTHOR
---------------------- -----------------------
DB1 C.J.Date
DB2 Korth


Query Number 3 :
Find out the names of the person who has issued books written by C.J.Date.

SQL Statement
SQL> select distinct bname
2 from Borrower,Borrows,Book
3 where Borrower.bid=Borrows.bid and
4 Borrows.bookid=Book.bookid and
5 author=’C.J.Date’;

Output to Query number 3

BNAME
-----------------------------
Deep Dutta
Ranjan Mehra



Query Number 4 :
Find all the books written by Maity Ghosh on Mathematics.

SQL Statement
SQL> select title
2 from Book
3 where author=’Maity Ghosh’ and
4 subject=’MATH’;

Output to Query number 4

TITLE
-----------------------------
MAT1


Query Number 5 :
Find the total number of books in the library.

SQL Statement
SQL> select count(bookid) total_no_of_books
2 from Book;

Output to Query number 5

TOTAL_NO_OF_BOOKS
--------------------------------
10


Query Number 6 :
Find the name of the person who has borrowed one or more books on DBMS.

SQL Statement
SQL> select distinct bname
2 from Borrower,Borrows,Book
3 where Borrower.bid=Borrows.bid and
4 Borrows.bookid=Book.bookid and
5 subject=’DBMS’;

Output to Query number 6

BNAME
-----------------------------
Deep Dutta
Ranjan Mehra
Tarun Saha


Discussion:-
The above database created is used to find out different information about a libraries books, borrowers and borrows.
We have solved some given queries using this database, some of which have alternative methods.
Example:-
Query no. 6 as written above to find the name of the person who has borrowed one or more books on DBMS can also be done by selecting the borrower name whose id is the same with the id of borrower who borrowed the book whose book id is the same with that of the book with name ‘DBMS’.
We can write this as follows:-
“select bname from borrower where bid in(select bid from borrows where bookid in(select bookid from book where subject='DBMS'));”

This would also give us the same output we desire.
 Back to main directory: Software Practical | Structured Query Language | DBMS


Get Free Programming Tutorials and Solved assignments