INSERT INTO PUBLISHER VALUES ('BookWorld', 1234567890, 'Delhi');
INSERT INTO PUBLISHER VALUES ('ReadMore', 9876543210, 'Mumbai');
INSERT INTO PUBLISHER VALUES ('StoryTime', 8765432109, 'Chennai');
INSERT INTO PUBLISHER VALUES ('BookShelf', 7654321098, 'Kolkata');
INSERT INTO PUBLISHER VALUES ('PaperLand', 6543210987, 'Bangalore');
INSERT INTO PUBLISHER VALUES ('InkSpot', 5432109876, 'Pune');
INSERT INTO PUBLISHER VALUES ('PageTurners', 4321098765, 'Hyderabad');
INSERT INTO BOOK VALUES (1, 'FunBook', 'JAN-2024', 'BookWorld');
INSERT INTO BOOK VALUES (2, 'LearnBook', 'JUN-2023', 'ReadMore');
INSERT INTO BOOK VALUES (3, 'StoryBook', 'SEP-2024', 'StoryTime');
INSERT INTO BOOK VALUES (4, 'KnowBook', 'SEP-2023', 'BookShelf');
INSERT INTO BOOK VALUES (5, 'PlayBook', 'MAY-2024', 'PaperLand');
INSERT INTO BOOK_AUTHORS VALUES ('Author1', 1);
INSERT INTO BOOK_AUTHORS VALUES ('Author2', 2);
INSERT INTO BOOK_AUTHORS VALUES ('Author3', 3);
INSERT INTO BOOK_AUTHORS VALUES ('Author4', 4);
INSERT INTO BOOK_AUTHORS VALUES ('Author5', 5);
INSERT INTO LIBRARY_PROGRAMME VALUES (10, 'CityLib1', 'Delhi');
INSERT INTO LIBRARY_PROGRAMME VALUES (11, 'CityLib2', 'Mumbai');
INSERT INTO LIBRARY_PROGRAMME VALUES (12, 'CityLib3', 'Chennai');
INSERT INTO LIBRARY_PROGRAMME VALUES (13, 'CityLib4', 'Kolkata');
INSERT INTO LIBRARY_PROGRAMME VALUES (14, 'CityLib5', 'Bangalore');
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-JAN-23', '01-JUN-23', 1, 10, 101);
INSERT INTO BOOK_LENDING VALUES ('11-JAN-23', '11-MAR-23', 3, 14, 101);
INSERT INTO BOOK_LENDING VALUES ('21-FEB-24', '21-APR-24', 2, 13, 101);
INSERT INTO BOOK_LENDING VALUES ('15-MAR-23', '15-JUL-23', 4, 11, 101);
INSERT INTO BOOK_LENDING VALUES ('12-APR-23', '12-MAY-23', 1, 11, 104);
1.Retrieve details of all books in the library – id, title, name of publisher, authors, number of copies in each
programme 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 2024 to Jun 2024.
SELECT CARD_NO
FROM BOOK_LENDING
WHERE DATE_OUT BETWEEN '01-JAN-2024' AND '01-JUL-2024'
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 V_PUBLICATION AS SELECT PUB_YEAR
FROM BOOK;
5.Create a view of all books and its number of copiesthat are currently available in the Library.
CREATE VIEW V_BOOKS AS
SELECT B.BOOK_ID, B.TITLE,
SUM(C.NO_OF_COPIES) - COUNT(L.BOOK_ID) AS available_copies
FROM BOOK B
LEFT JOIN BOOK_COPIES C ON B.BOOK_ID = C.BOOK_ID
LEFT JOIN BOOK_LENDING L ON B.BOOK_ID = L.BOOK_ID
GROUP BY B.BOOK_ID, B.TITLE;
CREATE TABLE BOOK ( BOOK_ID INTEGER 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 INTEGER, PRIMARY KEY (BOOK_ID, AUTHOR_NAME), FOREIGN KEY (BOOK_ID) REFERENCES BOOK(BOOK_ID) ON DELETE CASCADE );
CREATE TABLE LIBRARY_PROGRAMME ( PROGRAMME_ID INTEGER PRIMARY KEY, PROGRAMME_NAME VARCHAR(50), ADDRESS VARCHAR2(50) );
CREATE TABLE BOOK_COPIES ( NO_OF_COPIES INTEGER, BOOK_ID INTEGER, PROGRAMME_ID INTEGER, 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 CARD ( CARD_NO INTEGER PRIMARY KEY );
CREATE TABLE BOOK_LENDING ( DATE_OUT DATE, DUE_DATE DATE, BOOK_ID INTEGER, PROGRAMME_ID INTEGER, CARD_NO INTEGER, 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) );
INSERT INTO PUBLISHER VALUES ('BookWorld', 1234567890, 'Delhi'); INSERT INTO PUBLISHER VALUES ('ReadMore', 9876543210, 'Mumbai'); INSERT INTO PUBLISHER VALUES ('StoryTime', 8765432109, 'Chennai'); INSERT INTO PUBLISHER VALUES ('BookShelf', 7654321098, 'Kolkata'); INSERT INTO PUBLISHER VALUES ('PaperLand', 6543210987, 'Bangalore'); INSERT INTO PUBLISHER VALUES ('InkSpot', 5432109876, 'Pune'); INSERT INTO PUBLISHER VALUES ('PageTurners', 4321098765, 'Hyderabad');
INSERT INTO BOOK VALUES (1, 'FunBook', 'JAN-2024', 'BookWorld'); INSERT INTO BOOK VALUES (2, 'LearnBook', 'JUN-2023', 'ReadMore'); INSERT INTO BOOK VALUES (3, 'StoryBook', 'SEP-2024', 'StoryTime'); INSERT INTO BOOK VALUES (4, 'KnowBook', 'SEP-2023', 'BookShelf'); INSERT INTO BOOK VALUES (5, 'PlayBook', 'MAY-2024', 'PaperLand');
INSERT INTO BOOK_AUTHORS VALUES ('Author1', 1); INSERT INTO BOOK_AUTHORS VALUES ('Author2', 2); INSERT INTO BOOK_AUTHORS VALUES ('Author3', 3); INSERT INTO BOOK_AUTHORS VALUES ('Author4', 4); INSERT INTO BOOK_AUTHORS VALUES ('Author5', 5);
INSERT INTO LIBRARY_PROGRAMME VALUES (10, 'CityLib1', 'Delhi'); INSERT INTO LIBRARY_PROGRAMME VALUES (11, 'CityLib2', 'Mumbai'); INSERT INTO LIBRARY_PROGRAMME VALUES (12, 'CityLib3', 'Chennai'); INSERT INTO LIBRARY_PROGRAMME VALUES (13, 'CityLib4', 'Kolkata'); INSERT INTO LIBRARY_PROGRAMME VALUES (14, 'CityLib5', 'Bangalore');
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-JAN-23', '01-JUN-23', 1, 10, 101); INSERT INTO BOOK_LENDING VALUES ('11-JAN-23', '11-MAR-23', 3, 14, 101); INSERT INTO BOOK_LENDING VALUES ('21-FEB-24', '21-APR-24', 2, 13, 101); INSERT INTO BOOK_LENDING VALUES ('15-MAR-23', '15-JUL-23', 4, 11, 101); INSERT INTO BOOK_LENDING VALUES ('12-APR-23', '12-MAY-23', 1, 11, 104);
1.Retrieve details of all books in the library – id, title, name of publisher, authors, number of copies in each programme 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 2024 to Jun 2024. SELECT CARD_NO FROM BOOK_LENDING WHERE DATE_OUT BETWEEN '01-JAN-2024' AND '01-JUL-2024' 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 V_PUBLICATION AS SELECT PUB_YEAR FROM BOOK;
5.Create a view of all books and its number of copiesthat are currently available in the Library. CREATE VIEW V_BOOKS AS SELECT B.BOOK_ID, B.TITLE, SUM(C.NO_OF_COPIES) - COUNT(L.BOOK_ID) AS available_copies FROM BOOK B LEFT JOIN BOOK_COPIES C ON B.BOOK_ID = C.BOOK_ID LEFT JOIN BOOK_LENDING L ON B.BOOK_ID = L.BOOK_ID GROUP BY B.BOOK_ID, B.TITLE;