VTU DBMS LAB PROGRAM 1
BOOK (BOOK_ID, TITLE, PUBLISHER_NAME, PUB_YEAR)
BOOKAUTHORS(BOOK_ID,AUTHOR_NAME)
PUBLISHER(NAME,ADDRESS,PHONE)
BOOK_COPIES(BOOK_ID,BRANCH_ID,NO_OF_COPIES)
BOOK_LENDING(BOOK_ID,BRANCH_ID,CARD_NO,DATE_OUT,DUE_DATE)
LIBRARY_BRANCH(BRANCH_ID,BRANCH_NAME,ADDRESS)
Table of Contents
VTU DBMS LAB -Create Table SQL Commands
Create the tables for all of the above attributes
VTU DBMS LAB – Create Table for BOOK
CREATE TABLE BOOK(BOOK_ID NUMBER(5),
TITLE VARCHAR2(25),
PUBLISHER_NAME VARCHAR2(25),
PUB_YEAR NUMBER(4),
CONSTRAINT PK_BID PRIMARY KEY(BOOK_ID),
CONSTRAINT FK_N FOREIGN KEY (PUBLISHER_NAME) REFERENCES
PUBLISHER (NAME) ON DELETE CASCADE
);
Create Table for BOOKAUTHORS
CREATE TABLE BOOK_AUTHORS(BOOK_ID NUMBER(5),
AUTHOR_NAME VARCHAR2(25),
CONSTRAINT FK_B FOREIGN KEY(BOOK_ID) REFERENCES BOOK(BOOK_ID) ON DELETE CASCADE
);
VTU DBMS LAB -Create Table for PUBLISHER
CREATE TABLE PUBLISHER(NAME VARCHAR2(15),
ADDRESS VARCHAR2(25),
PHONE NUMBER(10),
CONSTRAINT PK_PN PRIMARY KEY(NAME)
);
Create Table for BOOK_COPIES
CREATE TABLE BOOK_COPIES(BOOK_ID NUMBER(5),
BRANCH_ID NUMBER (5),
NO_OF_COPIES NUMBER (2),
CONSTRAINT CPK_BBI PRIMARY KEY(BOOK_ID,BRANCH_ID),
CONSTRAINT FK_BI FOREIGN KEY(BOOK_ID) REFERENCES BOOK(BOOK_ID) ON DELETE CASCADE,
CONSTRAINT FK_I FOREIGN KEY(BRANCH_ID) REFERENCES
LIBRARY_BRANCH(BRANCH_ID) ON DELETE CASCADE
);
Create Table for BOOK_LENDING
CREATE TABLE BOOK_LENDING(BOOK_ID NUMBER(5),
BRANCH_ID NUMBER(5),
CARD_NO NUMBER(3),
DATE_OUT DATE,
DUE_DATE DATE,
CONSTRAINT CPK_BBC PRIMARY KEY (BOOK_ID, BRANCH_ID, CARD_NO),
CONSTRAINT FK_A FOREIGN KEY (BOOK_ID) REFERENCES BOOK(BOOK_ID) ON DELETE CASCADE,
CONSTRAINT FK_C FOREIGN KEY (BRANCH_ID) REFERENCES
LIBRARY_BRANCH(BRANCH_ID) ON DELETE CASCADE
);
Create Table for LIBRARY_BRANCH
CREATE TABLE LIBRARY_BRANCH(BRANCH_ID NUMBER(5),
BRANCH_NAME VARCHAR(5),
ADDRESS VARCHAR2(15),
CONSTRAINT PK_ID PRIMARY KEY(BRANCH_ID)
);
VTU DBMS LAB -SQL Insertion Commands for LIBRARY
Enter ‘ / ‘ to continue to insert the values
VTU DBMS LAB -Insert Values into PUBLISHER
Insert Command
INSERT INTO PUBLISHER VALUES('&NAME','&ADDRESS',&PHONE);
Enter the input values
Enter value for name: PHI
Enter value for address: PUNE,INDIA
Enter value for phone: 908070
old 1: INSERT INTO PUBLISHER VALUES('&NAME','&ADDRESS',&PHONE)
new 1: INSERT INTO PUBLISHER VALUES('PHI','PUNE,INDIA',908070)
row-1 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for name: PEARSON
Enter value for address: MUMBAI,INDIA
Enter value for phone: 8080707060
old 1: INSERT INTO PUBLISHER VALUES('&NAME','&ADDRESS',&PHONE)
new 1: INSERT INTO PUBLISHER VALUES('PEARSON','MUMBAI,INDIA',8080707060)
row-2 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for name: MCGRAWHILL
Enter value for address: HOUSTIN,USA
Enter value for phone: 120707070
old 1: INSERT INTO PUBLISHER VALUES('&NAME','&ADDRESS',&PHONE)
new 1: INSERT INTO PUBLISHER VALUES('MCGRAWHILL','HOUSTIN,USA',120707070)
row-3 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for name: WILEY
Enter value for address: CALIFORNIA,USA
Enter value for phone: 1108080808
old 1: INSERT INTO PUBLISHER VALUES('&NAME','&ADDRESS',&PHONE)
new 1: INSERT INTO PUBLISHER VALUES('WILEY','CALIFORNIA,USA',1108080808)
row-4 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for name: SSI
Enter value for address: FLORIDA
Enter value for phone: 1208080909
old 1: INSERT INTO PUBLISHER VALUES('&NAME','&ADDRESS',&PHONE)
new 1: INSERT INTO PUBLISHER VALUES('SSI','FLORIDA',1208080909)
row-5 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for name: SP
Enter value for address: BANGALORE,INDIA
Enter value for phone: 9090909080
old 1: INSERT INTO PUBLISHER VALUES('&NAME','&ADDRESS',&PHONE)
new 1: INSERT INTO PUBLISHER VALUES('SP','BANGALORE,INDIA',9090909080)
row-6 created
Command to select all 6 rows of PUBLISHER
SELECT * FROM PUBLISHER;
NAME ADDRESS PHONE --------------- ------------------------- - --------- PHI PUNE,INDIA 908070 PEARSON MUMBAI,INDIA 8080707060 MCGRAWHILL HOUSTIN,USA 120707070 WILEY CALIFORNIA,USA 1108080808 SSI FLORIDA 1208080909 SP BANGALORE,INDIA 9090909080
6-rows selected
Insert Values into LIBRARY_BRANCH
Insert Command
INSERT INTO LIBRARY_BRANCH VALUES(&BRANCH_ID,'&BRANCH_NAME','&ADDRESS');
Enter the input values
Enter value for branch_id: 1000 Enter value for branch_name: SMVIT Enter value for address: HUNASEMARANAHALLI old 1: INSERT INTO LIBRARY_BRANCH VALUES(&BRANCH_ID,'&BRANCH_NAME','&ADDRESS') new 1: INSERT INTO LIBRARY_BRANCH VALUES(1000,'SMVIT','HUNASEMARANAHALLI')
row-1 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for branch_id: 2000 Enter value for branch_name: SVIT Enter value for address: DODDABALLAPUR old 1: INSERT INTO LIBRARY_BRANCH VALUES(&BRANCH_ID,'&BRANCH_NAME','&ADDRESS') new 1: INSERT INTO LIBRARY_BRANCH VALUES(2000,'SVIT','DODDABALLAPUR')
row-2 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for branch_id: 3000 Enter value for branch_name: BMSIT Enter value for address: AVANAHALLI old 1: INSERT INTO LIBRARY_BRANCH VALUES(&BRANCH_ID,'&BRANCH_NAME','&ADDRESS') new 1: INSERT INTO LIBRARY_BRANCH VALUES(3000,'BMSIT','AVANAHALLI')
row-3 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for branch_id: 4000 Enter value for branch_name: SVCE Enter value for address: VIDYANAGAR old 1: INSERT INTO LIBRARY_BRANCH VALUES(&BRANCH_ID,'&BRANCH_NAME','&ADDRESS') new 1: INSERT INTO LIBRARY_BRANCH VALUES(4000,'SVCE','VIDYANAGAR')
row-4 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for branch_id: 5000 Enter value for branch_name: MSCE Enter value for address: CHIKKAJALA old 1: INSERT INTO LIBRARY_BRANCH VALUES(&BRANCH_ID,'&BRANCH_NAME','&ADDRESS') new 1: INSERT INTO LIBRARY_BRANCH VALUES(5000,'MSCE','CHIKKAJALA')
row-5 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for branch_id: 6000 Enter value for branch_name: NMIT Enter value for address: YELAHANKA old 1: INSERT INTO LIBRARY_BRANCH VALUES(&BRANCH_ID,'&BRANCH_NAME','&ADDRESS') new 1: INSERT INTO LIBRARY_BRANCH VALUES(6000,'NMIT','YELAHANKA')
row-6 created
Command to select all 6 rows of LIBRARY_BRANCH
SELECT * FROM LIBRARY_BRANCH;
BRANCH_ID BRANCH_NAME ADDRESS
---------- ----- ---------------
1000 SMVIT HUNASEMARANAHALLI
2000 SVIT DODDABALLAPUR
3000 BMSIT AVANAHALLI
4000 SVCE VIDYANAGAR
5000 MSCE CHIKKAJALA
6000 NMIT YELAHANKA
6-rows selected
VTU DBMS LAB -Insert Values into BOOK
Insert Command
INSERT INTO BOOK VALUES(&BOOK_ID,'&TITLE','&PUBLISHER_NAME',&PUB_YEAR);
Enter the input values
Enter value for book_id: 1111 Enter value for title: FUNNDAMENTALS OF DATABASE Enter value for publisher_name: PHI Enter value for pub_year: 2009 old 1: INSERT INTO BOOK VALUES(&BOOK_ID,'&TITLE','&PUBLISHER_NAME',&PUB_YEAR) new 1: INSERT INTO BOOK VALUES(1111,'FUNNDAMENTALS OF DATABASE','PHI',2009)
row-1 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 2222 Enter value for title: BASICS OF LOGIC DESIGN Enter value for publisher_name: PEARSON Enter value for pub_year: 2009 old 1: INSERT INTO BOOK VALUES(&BOOK_ID,'&TITLE','&PUBLISHER_NAME',&PUB_YEAR) new 1: INSERT INTO BOOK VALUES(2222,'BASICS OF LOGIC DESIGN','PEARSON',2009)
row-2 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 3333 Enter value for title: DATASTRUCTURES Enter value for publisher_name: MCGRAWHILL Enter value for pub_year: 2015 old 1: INSERT INTO BOOK VALUES(&BOOK_ID,'&TITLE','&PUBLISHER_NAME',&PUB_YEAR) new 1: INSERT INTO BOOK VALUES(3333,'DATASTRUCTURES','MCGRAWHILL',2015)
row-3 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 4444 Enter value for title: ARTIFICIAL INTELLIGENCE Enter value for publisher_name: WILEY Enter value for pub_year: 2017 old 1: INSERT INTO BOOK VALUES(&BOOK_ID,'&TITLE','&PUBLISHER_NAME',&PUB_YEAR) new 1: INSERT INTO BOOK VALUES(4444,'ARTIFICIAL INTELLIGENCE','WILEY',2017)
row-4 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 5555 Enter value for title: PROGRAMMING SKILLS Enter value for publisher_name: SSI Enter value for pub_year: 2014 old 1: INSERT INTO BOOK VALUES(&BOOK_ID,'&TITLE','&PUBLISHER_NAME',&PUB_YEAR) new 1: INSERT INTO BOOK VALUES(5555,'PROGRAMMING SKILLS','SSI',2014)
row-5 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 6666 Enter value for title: DESIGN OF ALGORITHMS Enter value for publisher_name: SP Enter value for pub_year: 2013 old 1: INSERT INTO BOOK VALUES(&BOOK_ID,'&TITLE','&PUBLISHER_NAME',&PUB_YEAR) new 1: INSERT INTO BOOK VALUES(6666,'DESIGN OF ALGORITHMS','SP',2013)
row-6 created
Command to select all 6 rows of BOOK
SELECT * FROM BOOK;
BOOK_ID TITLE PUBLISHER_NAME PUB_YEAR
---------- ------------------------- -----------------
1111 FUNNDAMENTALS OF DATABASE PHI 2009
2222 BASICS OF LOGIC DESIGN PEARSON 2009
3333 DATASTRUCTURES MCGRAWHILL 2015
4444 ARTIFICIAL INTELLIGENCE WILEY 2017
5555 PROGRAMMING SKILLS SSI 2014
6666 DESIGN OF ALGORITHMS SP 2013
6-rows selected
Insert Values into BOOK_AUTHORS
Insert Command
INSERT INTO BOOK_AUTHORS VALUES(&BOOK_ID,'&AUTHOR_NAME');
Enter the input values
Enter value for book_id: 1111 Enter value for author_name: NAVATHE old 1: INSERT INTO BOOK_AUTHORS VALUES(&BOOK_ID,'&AUTHOR_NAME') new 1: INSERT INTO BOOK_AUTHORS VALUES(1111,'NAVATHE')
row-1 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 2222 Enter value for author_name: GODSE old 1: INSERT INTO BOOK_AUTHORS VALUES(&BOOK_ID,'&AUTHOR_NAME') new 1: INSERT INTO BOOK_AUTHORS VALUES(2222,'GODSE')
row-2 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 3333 Enter value for author_name: SAHANI old 1: INSERT INTO BOOK_AUTHORS VALUES(&BOOK_ID,'&AUTHOR_NAME') new 1: INSERT INTO BOOK_AUTHORS VALUES(3333,'SAHANI')
row-3 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 4444 Enter value for author_name: RITCHIE KNIGHT old 1: INSERT INTO BOOK_AUTHORS VALUES(&BOOK_ID,'&AUTHOR_NAME') new 1: INSERT INTO BOOK_AUTHORS VALUES(4444,'RITCHIE KNIGHT')
row-4 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 5555 Enter value for author_name: BALAGURUSWAMY old 1: INSERT INTO BOOK_AUTHORS VALUES(&BOOK_ID,'&AUTHOR_NAME') new 1: INSERT INTO BOOK_AUTHORS VALUES(5555,'BALAGURUSWAMY')
row-5 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 6666 Enter value for author_name: COREMEN old 1: INSERT INTO BOOK_AUTHORS VALUES(&BOOK_ID,'&AUTHOR_NAME') new 1: INSERT INTO BOOK_AUTHORS VALUES(6666,'COREMEN')
row-6 created
Command to select all 6 rows of PUBLISHER
SELECT * FROM BOOK_AUTHORS;
BOOK_ID AUTHOR_NAME
---------- -------------------------
1111 NAVATHE
2222 GODSE
3333 SAHANI
4444 RITCHIE KNIGHT
5555 BALAGURUSWAMY
6666 COREMEN
6-rows selected
Insert Values into BOOK_COPIES
Insert Command
INSERT INTO BOOK_COPIES VALUES(&BOOK_ID,&BRANCH_ID,&NO_OF_COPIES);
Enter the input values
Enter value for book_id: 1111 Enter value for branch_id: 1000 Enter value for no_of_copies: 10 old 1: INSERT INTO BOOK_COPIES VALUES(&BOOK_ID,&BRANCH_ID,&NO_OF_COPIES) new 1: INSERT INTO BOOK_COPIES VALUES(1111,1000,10)
row-1 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 2222 Enter value for branch_id: 2000 Enter value for no_of_copies: 5 old 1: INSERT INTO BOOK_COPIES VALUES(&BOOK_ID,&BRANCH_ID,&NO_OF_COPIES) new 1: INSERT INTO BOOK_COPIES VALUES(2222,2000,5)
row-2 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 3333 Enter value for branch_id: 3000 Enter value for no_of_copies: 7 old 1: INSERT INTO BOOK_COPIES VALUES(&BOOK_ID,&BRANCH_ID,&NO_OF_COPIES) new 1: INSERT INTO BOOK_COPIES VALUES(3333,3000,7)
row-3 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 4444 Enter value for branch_id: 4000 Enter value for no_of_copies: 9 old 1: INSERT INTO BOOK_COPIES VALUES(&BOOK_ID,&BRANCH_ID,&NO_OF_COPIES) new 1: INSERT INTO BOOK_COPIES VALUES(4444,4000,9)
row-4 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 5555 Enter value for branch_id: 5000 Enter value for no_of_copies: 6 old 1: INSERT INTO BOOK_COPIES VALUES(&BOOK_ID,&BRANCH_ID,&NO_OF_COPIES) new 1: INSERT INTO BOOK_COPIES VALUES(5555,5000,6)
row-5 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 6666 Enter value for branch_id: 6000 Enter value for no_of_copies: 12 old 1: INSERT INTO BOOK_COPIES VALUES(&BOOK_ID,&BRANCH_ID,&NO_OF_COPIES) new 1: INSERT INTO BOOK_COPIES VALUES(6666,6000,12)
row-6 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 2222 Enter value for branch_id: 1000 Enter value for no_of_copies: 15 old 1: INSERT INTO BOOK_COPIES VALUES(&BOOK_ID,&BRANCH_ID,&NO_OF_COPIES) new 1: INSERT INTO BOOK_COPIES VALUES(2222,1000,15)
row-7 created
Command to select all 7 rows of BOOK_COPIES
SELECT * FROM BOOK_COPIES;
BOOK_ID BRANCH_ID NO_OF_COPIES
---------- ---------- ------------
1111 1000 10
2222 2000 5
3333 3000 7
4444 4000 9
5555 5000 6
6666 6000 12
2222 1000 15
7-rows selected
Insert Values into BOOK_LENDING
Insert Command
INSERT INTO BOOK_LENDING VALUES(&BOOK_ID,&BRANCH_ID,&CARD_NO,'&DATE_OUT','&DUE_DATE');
Enter the input values
Enter value for book_id: 1111 Enter value for branch_id: 1000 Enter value for card_no: 10 Enter value for date_out: 15-FEB-17 Enter value for due_date: 15-JUN-17 old 1: INSERT INTO BOOK_LENDING VALUES(&BOOK_ID,&BRANCH_ID,&CARD_NO,'&DATE_OUT','&DUE_DATE') new 1: INSERT INTO BOOK_LENDING VALUES(1111,1000,10,'15-FEB-17','15-JUN-17')
row-1 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 2222 Enter value for branch_id: 2000 Enter value for card_no: 10 Enter value for date_out: 10-MAR-17 Enter value for due_date: 15-AUG-17 old 1: INSERT INTO BOOK_LENDING VALUES(&BOOK_ID,&BRANCH_ID,&CARD_NO,'&DATE_OUT','&DUE_DATE') new 1: INSERT INTO BOOK_LENDING VALUES(2222,2000,10,'10-MAR-17','15-AUG-17')
row-2 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 3333 Enter value for branch_id: 3000 Enter value for card_no: 10 Enter value for date_out: 15-APR-17 Enter value for due_date: 15-SEP-17 old 1: INSERT INTO BOOK_LENDING VALUES(&BOOK_ID,&BRANCH_ID,&CARD_NO,'&DATE_OUT','&DUE_DATE') new 1: INSERT INTO BOOK_LENDING VALUES(3333,3000,10,'15-APR-17','15-SEP-17')
row-3 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 4444 Enter value for branch_id: 4000 Enter value for card_no: 10 Enter value for date_out: 10-JUN-17 Enter value for due_date: 15-NOV-17 old 1: INSERT INTO BOOK_LENDING VALUES(&BOOK_ID,&BRANCH_ID,&CARD_NO,'&DATE_OUT','&DUE_DATE') new 1: INSERT INTO BOOK_LENDING VALUES(4444,4000,10,'10-JUN-17','15-NOV-17')
row-4 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 5555 Enter value for branch_id: 5000 Enter value for card_no: 20 Enter value for date_out: 15-FEB-17 Enter value for due_date: 15-JUN-17 old 1: INSERT INTO BOOK_LENDING VALUES(&BOOK_ID,&BRANCH_ID,&CARD_NO,'&DATE_OUT','&DUE_DATE') new 1: INSERT INTO BOOK_LENDING VALUES(5555,5000,20,'15-FEB-17','15-JUN-17')
row-5 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 6666 Enter value for branch_id: 6000 Enter value for card_no: 30 Enter value for date_out: 10-MAR-17 Enter value for due_date: 15-AUG-17 old 1: INSERT INTO BOOK_LENDING VALUES(&BOOK_ID,&BRANCH_ID,&CARD_NO,'&DATE_OUT','&DUE_DATE') new 1: INSERT INTO BOOK_LENDING VALUES(6666,6000,30,'10-MAR-17','15-AUG-17')
row-6 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for book_id: 5555 Enter value for branch_id: 5000 Enter value for card_no: 10 Enter value for date_out: 15-JAN-16 Enter value for due_date: 15-JUN-16 old 1: INSERT INTO BOOK_LENDING VALUES(&BOOK_ID,&BRANCH_ID,&CARD_NO,'&DATE_OUT','&DUE_DATE') new 1: INSERT INTO BOOK_LENDING VALUES(5555,5000,10,'15-JAN-16','15-JUN-16')
row-7 created
Command to select all 6 rows of BOOK_LENDING
SELECT * FROM BOOK_LENDING;
BOOK_ID BRANCH_ID CARD_NO DATE_OUT DUE_DATE
-------- ---------- ---------- --------- ---------
1111 1000 10 15-FEB-17 15-JUN-17
2222 2000 10 10-MAR-17 15-AUG-17
3333 3000 10 15-APR-17 15-SEP-17
4444 4000 10 10-JUN-17 15-NOV-17
5555 5000 20 15-FEB-17 15-JUN-17
6666 6000 30 10-MAR-17 15-AUG-17
5555 5000 10 15-JAN-16 15-JUN-16
7-rows selected
VTU DBMS LAB -SQL Queries for LIBRARY
1] Retrieve details of all books in the library i.e ID, Tile, name of publisher,authors,no. of copies etc in each branch
SELECTC.BRANCH_ID,L.BRANCH_NAME,B.BOOK_ID,B.TITLE,B.PUBLISHER_NAME,B.PUB_YEAR,A.AUTHOR_NAME,C.NO_OF_COPIES
FROM BOOK B,BOOK_AUTHORS A,LIBRARY_BRANCH L,BOOK_COPIES C
WHERE B.BOOK_ID=A.BOOK_ID AND
B.BOOK_ID=C.BOOK_ID AND
L.BRANCH_ID=C.BRANCH_ID AND
(C.BRANCH_ID,C.BOOK_ID) IN
(SELECT BRANCH_ID,BOOK_ID
FROM BOOK_COPIES
GROUP BY BRANCH_ID,BOOK_ID);
Output
BRANCH_ID BRANCH_NAME BOOK_ID TITLE PUBLISHER_NAME --------- -------- ---------- ----------------- -------------- 5000 MSCE 5555 PROGRAMMING SKILLS SSI 1000 SMVIT 1111 FUNNDAMENTALS OF DATABASE PHI 1000 SMVIT 2222 BASICS OF LOGIC DESIGN PEARSON 2000 SVIT 2222 BASICS OF LOGIC DESIGN PEARSON 3000 BMSIT 3333 DATASTRUCTURES MCGRAWHILL 4000 SVCE 4444 ARTIFICIAL INTELLIGENCE WILEY 6000 NMIT 6666 DESIGN OF ALGORITHMS SP
PUB_YEAR AUTHOR_NAME NO_OF_COPIES ---------- ---------------------- --- ------------ 2014 BALAGURUSWAMY 6 2009 NAVATHE 10 2009 GODSE 15 2009 GODSE 5 2015 SAHANI 7 2017 RITCHIE KNIGHT 9 2013 COREMEN 12
7 rows selected
2] Get the particulars of borrowers who have borrowed more than 3 books but from Jan 2017 to Jun 2017
SQL> SELECT * FROM BOOK_LENDING
WHERE DATE_OUT BETWEEN '01-JAN-17' AND '30-JUN-17' AND CARD_NO
IN
(SELECT CARD_NO
FROM BOOK_LENDING
GROUP BY CARD_NO
HAVING COUNT(CARD_NO)>3);
Output
BOOK_ID BRANCH_ID CARD_NO DATE_OUT DUE_DATE
------- ---------- -------- --------- ---------
4444 4000 10 10-JUN-17 15-NOV-17
3333 3000 10 15-APR-17 15-SEP-17
2222 2000 10 10-MAR-17 15-AUG-17
1111 1000 10 15-FEB-17 15-JUN-17
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=&BOOK_ID; Enter value for book_id: 5555 old 1: DELETE FROM BOOK WHERE BOOK_ID=&BOOK_ID new 1: DELETE FROM BOOK WHERE BOOK_ID=5555
1-row deleted
SELECT * FROM BOOK;
Output
BOOK_ID TITLE PUBLISHER_NAME PUB_YEAR ------- ------------------------- -------------- ---------- 1111 FUNNDAMENTALS OF DATABASE PHI 2009 2222 BASICS OF LOGIC DESIGN PEARSON 2009 3333 DATASTRUCTURES MCGRAWHILL 2015 4444 ARTIFICIAL INTELLIGENCE WILEY 2017 6666 DESIGN OF ALGORITHMS SP 2013
SELECT * FROM BOOK_COPIES;
Output
BOOK_ID BRANCH_ID NO_OF_COPIES ------ --------- ------------ 1111 1000 10 2222 2000 5 3333 3000 7 4444 4000 9 6666 6000 12 2222 1000 15
SELECT * FROM BOOK_AUTHORS;
Output
BOOK_ID AUTHOR_NAME
---------- -------------------------
1111 NAVATHE
2222 GODSE
3333 SAHANI
4444 RITCHIE KNIGHT
6666 COREMEN
SELECT * FROM BOOK_LENDING;
Output
BOOK_ID BRANCH_ID CARD_NO DATE_OUT DUE_DATE ------ ---------- -------- --------- --------- 1111 1000 10 15-FEB-17 15-JUN-17 2222 2000 10 10-MAR-17 15-AUG-17 3333 3000 10 15-APR-17 15-SEP-17 4444 4000 10 10-JUN-17 15-NOV-17 6666 6000 30 10-MAR-17 15-AUG-17
4] Partition the book table based on year of publication. Demonstrate its working with a simple query
CONNECT SYSTEM/username;
Connected
GRANT CREATE VIEW TO B2;
Grant succeeded
CONNECT B2/B2;
Connected
CREATE VIEW YEAR AS SELECT PUB_YEAR FROM BOOK;
View created
SELECT * FROM YEAR;
Output
PUB_YEAR
----------
2009
2009
2015
2017
2013
5] Create a view all books and its no. of copies that are currently available in the library
CREATE VIEW ALL_BOOK AS
SELECT B.BOOK_ID,B.TITLE,C.NO_OF_COPIES,L.BRANCH_NAME
FROM BOOK B,BOOK_COPIES C,LIBRARY_BRANCH L
WHERE B.BOOK_ID=C.BOOK_ID
AND L.BRANCH_ID=C.BRANCH_ID;
View created
SELECT * FROM ALL_BOOK;
Output
BOOK_ID TITLE NO_OF_COPIES BRANCH_NAME ---------- ------------------------- ------------ ----- 1111 FUNNDAMENTALS OF DATABASE 10 SMVIT 2222 BASICS OF LOGIC DESIGN 5 SVIT 2222 BASICS OF LOGIC DESIGN 15 SMVIT 3333 DATASTRUCTURES 7 BMSIT 4444 ARTIFICIAL INTELLIGENCE 9 SVCE 6666 DESIGN OF ALGORITHMS 12 NMIT
6 rows selected