1. Consider the following schema for a Library Database:
BOOK (Book_id, Title, Publisher_Name, Pub_Year)
BOOK_AUTHORS (Book_id, Author_Name)
PUBLISHER (Name, Address, Phone)
BOOK_COPIES(Book_id, Programme_id, No-of_Copies)
BOOK_LENDING(Book_id, Programme_id, Card_No, Date_Out, Due_Date)
LIBRARY_PROGRAMME(Programme_id, Programme_Name,Address)
Write SQL queries to
1. Retrieve details of all books in the library id, title, name of publisher, authors, number of copies in each Programme, etc.
2. Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2017 to Jun 2017
3. Delete a book in BOOK table. Update the contents of other tables to reflect this data manipulation operation.
4. Partition the BOOK table based on year of publication. Demonstrate its working with a simple query.
5. Create a view of all books and its number of copies that are currently available in the Library.
TABLE CREATION COMMAND:
CREATE TABLE CARD (
CARD_NO VARCHAR (10) PRIMARY KEY );CREATE TABLE PUBLISHER (
NAME VARCHAR (20) PRIMARY KEY,
PHONE VARCHAR(20),
ADDRESS VARCHAR (100));CREATE TABLE BOOK (
BOOK_ID VARCHAR (10) PRIMARY KEY,
TITLE VARCHAR (20),
PUB_YEAR VARCHAR (20),
PUBLISHER_NAME VARCHAR (20),
FOREIGN KEY (PUBLISHER_NAME) REFERENCES PUBLISHER (NAME) ON DELETE CASCADE);CREATE TABLE BOOK_AUTHORS (
AUTHOR_NAME VARCHAR (20),
BOOK_ID VARCHAR (10),
PRIMARY KEY (BOOK_ID, AUTHOR_NAME),
FOREIGN KEY (BOOK_ID) REFERENCES BOOK (BOOK_ID) ON DELETE CASCADE);CREATE TABLE LIBRARY_PROGRAMME (
PROGRAMME_ID VARCHAR (10) PRIMARY KEY,
PROGRAMME_NAME VARCHAR (50),
ADDRESS VARCHAR (100));CREATE TABLE BOOK_COPIES (
NO_OF_COPIES VARCHAR (5),
BOOK_ID VARCHAR (10),
PROGRAMME_ID VARCHAR (10),
PRIMARY KEY (BOOK_ID,PROGRAMME_ID),
FOREIGN KEY (BOOK_ID) REFERENCES BOOK (BOOK_ID) ON DELETE CASCADE,
FOREIGN KEY (PROGRAMME_ID) REFERENCES LIBRARY_PROGRAMME (PROGRAMME_ID) ON DELETE CASCADE);CREATE TABLE BOOK_LENDING (
DATE_OUT DATE,
DUE_DATE DATE,
BOOK_ID VARCHAR (10),
PROGRAMME_ID VARCHAR (10),
CARD_NO VARCHAR (10),
PRIMARY KEY (BOOK_ID,PROGRAMME_ID, CARD_NO),
FOREIGN KEY (BOOK_ID) REFERENCES BOOK (BOOK_ID) ON DELETE CASCADE,
FOREIGN KEY (PROGRAMME_ID) REFERENCES LIBRARY_PROGRAMME (PROGRAMME_ID) ON DELETE CASCADE,
FOREIGN KEY (CARD_NO) REFERENCES CARD (CARD_NO) ON DELETE CASCADE);INSERTION COMMAND:
INSERT INTO CARD VALUES ('100');
INSERT INTO CARD VALUES ('101');
INSERT INTO CARD VALUES ('102');
INSERT INTO CARD VALUES ('103');
INSERT INTO CARD VALUES ('104');
INSERT INTO PUBLISHER VALUES ('MCGRAW-HILL', '9989076587','BANGALORE');
INSERT INTO PUBLISHER VALUES ('PEARSON', '9889076565','NEWDELHI');
INSERT INTO PUBLISHER VALUES ('PRENTICE HALL', '7455679345','HYEDRABAD');
INSERT INTO PUBLISHER VALUES ('WILEY', '8970862340','CHENNAI');
INSERT INTO PUBLISHER VALUES ('MIT','7756120238','BANGALORE');INSERT INTO BOOK VALUES (1,'DBMS','13-01-2017', 'MCGRAW-HILL');
INSERT INTO BOOK VALUES (2,'ADBMS','25-06-2016','MCGRAW-HILL');
INSERT INTO BOOK VALUES (3, 'CD','22-10-2016','PEARSON');
INSERT INTO BOOK VALUES (4,' ALGORITHMS ','24-10-2015','MIT');
INSERT INTO BOOK VALUES (5,'OS','08-05-2016','PEARSON');INSERT INTO BOOK_AUTHORS VALUES ('NAVATHE', '1');
INSERT INTO BOOK_AUTHORS VALUES ('NAVATHE', '2');
INSERT INTO BOOK_AUTHORS VALUES ('ULLMAN','3');
INSERT INTO BOOK_AUTHORS VALUES ('CHARLES', '4');
INSERT INTO BOOK_AUTHORS VALUES('GALVIN', '5');INSERT INTO LIBRARY_PROGRAMME VALUES ('10','VIJAY NAGAR','MYSURU');
INSERT INTO LIBRARY_PROGRAMME VALUES ('11','VIDYANAGAR','HUBLI');
INSERT INTO LIBRARY_PROGRAMME VALUES('12','KUVEMPUNAGAR','MYSURU');
INSERT INTO LIBRARY_PROGRAMME VALUES('13','RAJAJINAGAR','BANGALORE');
INSERT INTO LIBRARY_PROGRAMME VALUES ('14','MANIPAL','UDUPI');INSERT INTO BOOK_COPIES VALUES ('10', '1', '10');
INSERT INTO BOOK_COPIES VALUES ('5', '1', '11');
INSERT INTO BOOK_COPIES VALUES ('2', '2', '12');
INSERT INTO BOOK_COPIES VALUES ('5', '2', '13');
INSERT INTO BOOK_COPIES VALUES ('7', '3', '14');
INSERT INTO BOOK_COPIES VALUES ('1', '5', '10');
INSERT INTO BOOK_COPIES VALUES ('3', '4', '11');INSERT INTO BOOK_LENDING VALUES ('01-01-2017','01-06-2017', '1', '10', '101');
INSERT INTO BOOK_LENDING VALUES ('01-11-2017 ','03-11-2017', '3', '14', '101');
INSERT INTO BOOK_LENDING VALUES ('02-21-2017','04-21-2017', '2', '13', '101');
INSERT INTO BOOK_LENDING VALUES ('03-15-2017 ','07-15-2017', '4', '11', '101');
INSERT INTO BOOK_LENDING VALUES ('04-12-2017','05-12-2017', '1', '11', '104');QUERY EXECUTION PART:
1. Retrieve details of all books in the library id, title, name of publisher, authors, number of copies in each branch, etc.
SELECT B.BOOK_ID, B.TITLE, B.PUBLISHER_NAME, A.AUTHOR_NAME,
C.NO_OF_COPIES, L.PROGRAMME_ID FROM BOOK B, BOOK_AUTHORS A, BOOK_COPIES
C, LIBRARY_PROGRAMME L WHERE B.BOOK_ID=A.BOOK_ID AND
B.BOOK_ID=C.BOOK_ID AND L.PROGRAMME_ID=C.PROGRAMME_ID;2. Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2017 to Jun 2017.
SELECT CARD_NO FROM BOOK_LENDING WHERE DATE_OUT
BETWEEN '01-01-2017'AND '04-21-2017' GROUP BY CARD_NO
HAVING COUNT(*)>3;3. Delete a book in BOOK table. Update the contents of other tables to reflect this data manipulation operation.
DELETE FROM BOOK WHERE BOOK_ID=3;
4. Partition the BOOK table based on year of publication. Demonstrate its working with a simple query.
CREATE VIEW VW_PUBLICATION AS SELECT PUB_YEAR FROM BOOK;SELECT * FROM VW_PUBLICATION;5. Create a view of all books and its number of copies that are currently available in the Library.
CREATE VIEW VW_BOOKS AS SELECT B.BOOK_ID, B.TITLE, C.NO_OF_COPIES
FROM BOOK B, BOOK_COPIES C, LIBRARY_PROGRAMME L WHERE
B.BOOK_ID=C.BOOK_ID AND C.PROGRAMME_ID=L.PROGRAMME_ID;SELECT * FROM VW_BOOKS;