SQL Exercise

Central-Books is a small library company that lends books to customers. Customers may borrow books for a period of 5 days and pay fees to hire the books of between one and five pounds depending on the book. The start date of each hiring of a book, and the borrower are recorded. Customer names, gender, telephone numbers, and address need to be recorded. Each time a customer borrows a copy they will give an opinion of the book (the evaluation is a number from 0 to 5). Central-Books need to record the evaluation of each book by customers who borrow it. It is not a requirement that all books recorded on the system have copies in the library and it is also not required that they are actually borrowed. Neither is it required that every customer has to have borrowed a book. In order to cater for demand, the library stocks multiple copies of books in different locations (towns) as appropriate.

1.  Design an Entity model and construct a set of tables with suitably defined columns to support this scenario.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2.      Populate the tables with appropriate test data, bearing in mind the following:

a)      There may be some books that have yet to be borrowed.

b)      There may be some customers who have yet to borrow a book.

c)      All copies of books are uniquely identified by a single key column called copy_id

d)     Loan records are identified by a combination of foreign key columns (not by a single column surrogate key)

 

Books (BookID, Title, PurchasePrice, Hire Fees)

Copy (BookID, CopyID, Location)

Borrowed (BookID, CopyID, CustomerID, StartDate, Evaluation)

Customer (CustomerID, Cname, Gender, Telphos, Address)

 

Where        Primary Key – Underline

                  Foreign Key – Bold

 

Test Data

Books Table

BOOKID           TITLE                                                 HIREFEES   PURCHASEPRICE

52001                  Database Server Management             2                              112.5

52002                  Network sequrity                                 1                              60.5

52003                  Structured programming                      3                              1400.5

52004                  OOP programming                               1                              140

52005                  Database Management System            4                              1140

52006                 Graphic Design                                    1                              180

52007                  Operating System                                4                              1180

52008 S              Sowtware Analysis Principles              3                              650

 

Customer Table

CUSTOMERID    CNAME             GENDER    PHONO          ADDRESS

cus001                    V. Enojan            Male             0772789406    Batticaloa

cus002                    W.W.Bandara      Male             0772789412    Matara

cus003                    V. Kumara           Male             0772789325    Colombo

cus004                    B. Johncy             Female          0772789111    York

cus005                    N.Pathmasirini     Female          0772789222    London

cus006                    V. Mathan           Male             0772789406    Batticaloa

cus007                    N.Pathmasiri        Male             0772789222    London

cus008                    S.rebecka             Female          0772789345    York

cus009                    B. John                Male             0772789111    York

cus010                    C.Jayavarthana    Male             0772789325    Colombo

 

BookCopy Table

BOOKID              COPYID                CUSTOMERID STRATDATE     EVALUATION

52006                     3                              cus009                 22-OCT-19           5

52001                     1                              cus002                 10-OCT-19           5

52006                     1                              cus009                 22-NOV-19          3

52005                    2                              cus002                 11-FEB-19            2

52005                     4                              cus007                 10-OCT-19           5

52003                     3                              cus004                 22-NOV-19          3

52006                     3                              cus006                 11-FEB-19            2

52007                     1                              cus010                 10-OCT-19           5

 

Barrowed Table

BOOKID        COPYID        PLACE

52003              1                      London

52003              2                      York

52003              3                      Colombo

52001              1                      Colombo

52002              1                      Colombo

52004              1                      Colombo

52004              2                      London

52005              1                      York

52005              2                      London

52005              3                      Colombo

52005              4                      Batticloa

52006              1                      London

52007              1                      Colombo

52006              2                      York

52006              3                      York

52006              4                      Colombo

 

3.      Create appropriate primary and foreign key constraints on the tables.

CREATE TABLE Books(  

        BookID VARCHAR2(6)   

            CONSTRAINT pk_emp PRIMARY KEY,   

      Title        VARCHAR2(30) NOT NULL,   

           HireFees NUMBER(2) NOT NULL   

               CONSTRAINT BD_HireFees CHECK (HireFees > 0),   

      PurchasePrice         NUMBER(7,2)   

          CONSTRAINT BD_PurchasePrice CHECK (PurchasePrice >= 0)  

  )

Table created.

 

CREATE TABLE bookcopy(    

  BookID VARCHAR2(6),   

  CopyID number(2),    

  place varchar2(50),    

  CONSTRAINT pk_bookscopy PRIMARY KEY(BookID,CopyID),  

  CONSTRAINT fk_bookscopy FOREIGN KEY(BookID) REFERENCES Books(BookID)  

)

Table created.

 

CREATE TABLE customer (    

  customerID VARCHAR2(6), 

  cname VARCHAR2(30), 

  gender VARCHAR2(6), 

  PhoNo VARCHAR2(12), 

  address VARCHAR2(50), 

  CONSTRAINT pk_customer PRIMARY KEY(customerID) 

)

Table created.

 

CREATE TABLE borrwed(      

  BookID VARCHAR2(6),     

  CopyID number(2),  

  customerID VARCHAR2(6), 

  StratDate date,  

  evaluation number(1), 

  CONSTRAINT pk_borrowed PRIMARY KEY(BookID,CopyID,customerID),    

  CONSTRAINT fk_borowed FOREIGN KEY(BookID) REFERENCES Books(BookID) 

) 

 

INSERT INTO Books VALUES ('52001','Database Server Management',2,112.50)

select * from Books

BOOKID	TITLE	HIREFEES	PURCHASEPRICE
52001	Database Server Management	2	112.5
52002	Network sequrity	1	60.5
52003	Structured programming	3	1400.5
52004	OOP programming	1	140
52005	Database Management System	4	1140
52006	Graphic Design	1	180
52007	Operating System	4	1180
52008	Sowtware Analysis Principles	3	650

Download CSV
8 rows selected.

 

 

INSERT INTO customer VALUES ('cus001','V. Enojan','Male','0772789406','Batticaloa')

select * from customer

CUSTOMERID	CNAME	GENDER	PHONO	ADDRESS
cus001	V. Enojan	Male	0772789406	Batticaloa
cus002	W.W.Bandara	Male	0772789412	Matara
cus003	V. Kumara	Male	0772789325	Colombo
cus004	B. Johncy	Female	0772789111	York
cus005	N.Pathmasirini	Female	0772789222	London
cus006	V. Mathan	Male	0772789406	Batticaloa
cus007	N.Pathmasiri	Male	0772789222	London
cus008	S.rebecka	Female	0772789345	York
cus009	B. John	Male	0772789111	York
cus010	C.Jayavarthana	Male	0772789325	Colombo

Download CSV
10 rows selected.

 

INSERT INTO borrwed VALUES ('52006',3,'cus009','22-OCT-2019',5)

BOOKID	COPYID	CUSTOMERID	STRATDATE	EVALUATION
52006	3	cus009	22-OCT-19	5
52001	1	cus002	10-OCT-19	5
52006	1	cus009	22-NOV-19	3
52005	2	cus002	11-FEB-19	2
52005	4	cus007	10-OCT-19	5
52003	3	cus004	22-NOV-19	3
52006	3	cus006	11-FEB-19	2
52007	1	cus010	10-OCT-19	5

Download CSV

8 rows selected.

INSERT INTO bookcopy VALUES ('52003',1,'London')

select * from bookcopy

BOOKID	COPYID	PLACE
52003	1	London
52003	2	York
52003	3	Colombo
52001	1	Colombo
52002	1	Colombo
52004	1	Colombo
52004	2	London
52005	1	York
52005	2	London
52005	3	Colombo
52005	4	Batticloa
52006	1	London
52007	1	Colombo
52006	2	York
52006	3	York
52006	4	Colombo

Download CSV
16 rows selected.

 

 

 

4.      Develop a set of queries as follows:

a.       Find details of all books stocked in London

select Books.BookID, Books.Title, Books.PurchasePrice, Books.HireFees, bookcopy.place 

from Books, bookcopy 

where bookcopy.place='London' and Books.BookID = bookcopy.BookID

BOOKID	TITLE	PURCHASEPRICE	HIREFEES	PLACE
52003	Structured programming	1400.5	3	London
52004	OOP programming	140	1	London
52005	Database Management System	1140	4	London
52006	Graphic Design	180	1	London

Download CSV
4 rows selected.

b.      Find the book that has the most copies.

SELECT BookID, COUNT(BookID)  

FROM bookcopy  GROUP BY BookID  

HAVING COUNT (BookID)=( 

SELECT MAX (MostCopies) as Most  

FROM (SELECT BookID, COUNT(BookID) MostCopies     

        from bookcopy   

        GROUP BY BookID))

BOOKID	COUNT(BOOKID)
52005	4
52006	4

Download CSV
2 rows selected.

c.       Find the total number of hirings of each book.

              create view Totalhire as (SELECT  BookID, Count (BookID) as hire 

              from borrwed  

              GROUP BY BookID)

                                View created.

select Books.BookID, Books.Title, Totalhire.hire as Hire 

from Books, Totalhire 

where Books.BookID= Totalhire.BookID

BOOKID	TITLE	HIRE
52003	Structured programming	1
52007	Operating System	1
52005	Database Management System	2
52006	Graphic Design	3
52001	Database Server Management	1

Download CSV
5 rows selected

d.      Show details of the books with the total revenue that they each generate based on the hirings of copies of those books.

select Books.BookID, Books.Title, Books.HireFees*Totalhire.hire as revenue 

from Books, Totalhire 

where Books.BookID= Totalhire.BookID

BOOKID	TITLE	REVENUE
52003	Structured programming	3
52007	Operating System	4
52005	Database Management System	8
52006	Graphic Design	3
52001	Database Server Management	2

Download CSV
5 rows selected.

 

e.      Show details of customer names and the titles of the books that they have borrowed

select customer.cname, Books.Title 

from Books, customer, borrwed, bookcopy 

where   Books.BookID= bookcopy.BookID and 

        bookcopy.BookID= borrwed.BookID and 

        bookcopy.CopyID= borrwed.CopyID and 

        customer.customerID= borrwed.customerID

CNAME	TITLE
W.W.Bandara	Database Server Management
B. Johncy	Structured programming
W.W.Bandara	Database Management System
N.Pathmasiri	Database Management System
V. Mathan	Graphic Design
B. John	Graphic Design
B. John	Graphic Design
C.Jayavarthana	Operating System

Download CSV
8 rows selected.

 

f.        Write and test a query to list the title and purchase price of each book. Add a column that compares the cost of the book to the average cost i.e., shows the difference between the book cost and the average cost of all books.

select title, PurchasePrice, (PurchasePrice- (select CAST(ROUND(Avg (PurchasePrice),2) AS DEC(10,2)) 

from Books)) as Difference 

from Books 

TITLE	PURCHASEPRICE	DIFFERENCE
Database Server Management	112.5	-495.44
Network sequrity	60.5	-547.44
Structured programming	1400.5	792.56
OOP programming	140	-467.94
Database Management System	1140	532.06
Graphic Design	180	-427.94
Operating System	1180	572.06
Sowtware Analysis Principles	650	42.06

Download CSV
8 rows selected.

 

g.       List all customers who have not hired a book located in York.

select customer.cname, bookcopy.place   

from Books, customer, borrwed, bookcopy   

where bookcopy.place<>'York' and Books.BookID= bookcopy.BookID and  

        bookcopy.BookID= borrwed.BookID and  

        bookcopy.CopyID= borrwed.CopyID and  

        customer.customerID= borrwed.customerID 

CNAME	PLACE
B. Johncy	Colombo
W.W.Bandara	Colombo
W.W.Bandara	London
N.Pathmasiri	Batticloa
B. John	London
C.Jayavarthana	Colombo

Download CSV
6 rows selected.

 

h.      Find the copies of books that have their number of hirings below the average hirings for copies of that book.

SELECT CopyID, COUNT(copyID)   

FROM bookcopy  GROUP BY CopyID   

HAVING COUNT (CopyID)>( SELECT AVG (CopyID)   

        from bookcopy    

        ) 

COPYID	COUNT(COPYID)
1	7
2	4
3	3

Download CSV
3 rows selected.

 

i.         Write and test a query to list the customer ID and name of every Customer along with the books that they have hired within the past 200 days. Include starting date, ending date, and location name for those hirings. All customer details (ID and name) should be included in the output, whether or not they have actually borrowed any books.

select customer.cname, customer.customerID, borrwed.StratDate,borrwed.StratDate+INTERVAL '5' DAY as EndDate,bookcopy.place    

from Books, customer, borrwed, bookcopy    

where (sysdate - to_date(borrwed.StratDate, 'yyyy-mm-dd'))>200 and Books.BookID= bookcopy.BookID and   

        bookcopy.BookID= borrwed.BookID and   

        bookcopy.CopyID= borrwed.CopyID and   

        customer.customerID= borrwed.customerID  

CNAME	CUSTOMERID	STRATDATE	ENDDATE	PLACE
B. Johncy	cus004	22-NOV-19	27-NOV-19	Colombo
W.W.Bandara	cus002	10-OCT-19	15-OCT-19	Colombo
W.W.Bandara	cus002	11-FEB-19	16-FEB-19	London
N.Pathmasiri	cus007	10-OCT-19	15-OCT-19	Batticloa
B. John	cus009	22-NOV-19	27-NOV-19	London
C.Jayavarthana	cus010	10-OCT-19	15-OCT-19	Colombo
V. Mathan	cus006	11-FEB-19	16-FEB-19	York
B. John	cus009	22-OCT-19	27-OCT-19	York

Download CSV
8 rows selected.

 

j.        Find names of all female customers who have borrowed a book in 2012.

select customer.cname  

from Books, customer, borrwed, bookcopy    

where customer.gender>'Female' and  

       EXTRACT (YEAR FROM TO_DATE(borrwed.StratDate, 'DD-MON-RR')) > 2012 and 

        Books.BookID= bookcopy.BookID and   

        bookcopy.BookID= borrwed.BookID and   

        bookcopy.CopyID= borrwed.CopyID and   

        customer.customerID= borrwed.customerID  

CNAME

B. John

W.W.Bandara

B. John

W.W.Bandara

N.Pathmasiri

V. Mathan

C.Jayavarthana

Download CSV
7 rows selected.

5.      Develop a view that shows details of all books on record, along with the details of their copies. Include books for which there are presently no copies in the library. Display the contents of the view.

create view BooksReg as 

(select Books.*

from Books, bookcopy   

where Books.BookID= bookcopy.BookID)

 

select BooksReg.* 

from BooksReg 

BOOKID	TITLE	HIREFEES	PURCHASEPRICE
52001	Database Server Management	2	112.5
52002	Network sequrity	1	60.5
52003	Structured programming	3	1400.5
52004	OOP programming	1	140
52005	Database Management System	4	1140
52006	Graphic Design	1	180
52007	Operating System	4	1180

Download CSV
16 rows selected.

Test the view to show its ability to support DML activity. This should include examples of:

        I.            Separate updates on each individual column within the view

UPDATE books SET HireFees = HireFees * 1.1;

8 row(s) updated.

 

      II.            Inserts of new rows into the view

INSERT INTO Books (BookID, Title,HireFees, PurchasePrice)

VALUES ('52010','Database Server Management',2,112.50)

1 row(s) inserted

 

    III.            Deletes of rows from the view

       DELETE FROM books where BookID='52010'

        1 row(s) deleted