1. LIBRARY DATABASE VTU DBMS LAB | DIRECT SQL CODE

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)


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

Leave a Reply

Your email address will not be published. Required fields are marked *

WhatsApp Icon Join For Job Alerts