Library Management Database creation Insert data Select Form the Library Database

Student Library Management Database creation it will create table student, books, subscription,
It has variable book name, author, publication, and no of copies it is declare data type as varchar. Just like it will declare and create other table books and subscription.

SQL> CREATE TABLE LIB(BOOKNAME VARCHAR2(30) NOT NULL,AUTHOR VARCHAR2(30),PUBLICATION VARCHAR2(30),NO
OFCOPIES NUMBER(2));
TABLE CREATED.
SQL> CREATE TABLE STUDENT(ROLLNO NUMBER(10),NAME VARCHAR2(30),NO_CARD NUMBER(1));
TABLE CREATED.
SQL> CREATE TABLE BOOKS(BOOKNO NUMBER(10),BOOKNAME VARCHAR2(30), AVAILABLE VARCHAR2(3),SUBSCRIBED_TO NUMBER(10));
TABLE CREATED
SQL>CREATE TABLE SUBSCRIPTION(BOOKN0 NUMBER(10),ROLLNO NUMBER(10),DO_SUB DATE,DO_RETURN DATE,FINE_AMOUNT NUMBER(2),STATUS VARCHAR2(30));
TABLE CREATED
Output DBMS Lab CS1307 Database Management system
DECRIPTION DBMS Lab CS1307 Database Management system


SQL> DESC LIB;
NAME NULL? TYPE
-------------------- --------------- -----------------
BOOKNAME NOT NULL VARCHAR2(30)
AUTHOR VARCHAR2(30)
PUBLICATION VARCHAR2(30)
NOOFCOPIES NUMBER(2)


SQL> DESC STUDENT;
NAME NULL? TYPE
---------------------------- --------- --------------
ROLLNO NUMBER(10)
NAME VARCHAR2(30)
NO_CARD NUMBER(1)



SQL> DESC BOOKS;
NAME NULL? TYPE
---------------------------- ----------- --------------
BOOKNO NUMBER(10)
BOOKNAME VARCHAR2(30)
AVAILABLE VARCHAR2(3)
SUBSCRIBED_TO NUMBER(10)


SQL> DESC SUBSCRIPTION;
NAME NULL? TYPE
----------------------- --------- ------ ----
BOOKN0 NUMBER(10)
ROLLNO NUMBER(10)
DO_SUB DATE
DO_RETURN DATE
FINE_AMOUNT NUMBER(2)
STATUS VARCHAR2(30)

INSERTION DBMS Lab CS1307 Database Management system
SQL> BEGIN
2 INSERT INTO STUDENT VALUES (&ROLLNO,'&NAME',2);
3 ENDS;
4 /
ENTER VALUE FOR ROLLNO: 1
ENTER VALUE FOR NAME: AISH
OLD 2: INSERT INTO STUDENT VALUES(&ROLLNO,'&NAME',2);
NEW 2: INSERT INTO STUDENT VALUES(1,'AISH',2);
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
Output DBMS Lab CS1307 Database Management system

SQL> /
ENTER VALUE FOR ROLLNO: 52
ENTER VALUE FOR NAME: JOHN
OLD 2: INSERT INTO STUDENT VALUES(&ROLLNO,'&NAME',2);
NEW 2: INSERT INTO STUDENT VALUES(21,'JOHN',2);
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SQL> /
ENTER VALUE FOR ROLLNO: 32
ENTER VALUE FOR NAME: NABISHA
OLD 2: INSERT INTO STUDENT VALUES(&ROLLNO,'&NAME',2);
NEW 2: INSERT INTO STUDENT VALUES(32,'NABISHA',2);
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SQL> /
ENTER VALUE FOR ROLLNO: 42
ENTER VALUE FOR NAME: RAM
OLD 2: INSERT INTO STUDENT VALUES(&ROLLNO,'&NAME',2);
NEW 2: INSERT INTO STUDENT VALUES(42,'RAM',2);
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SQL> /
ENTER VALUE FOR ROLLNO: 62
ENTER VALUE FOR NAME: THUTHI
OLD 2: INSERT INTO STUDENT VALUES(&ROLLNO,'&NAME',2);
NEW 2: INSERT INTO STUDENT VALUES(62,'THUTHI',2);
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SQL> DECLARE
2 BOOKNO NUMBER;
3 BNAME VARCHAR2(30);
4 NOC NUMBER;
5 NOC1 NUMBER;
6 AUTHOR VARCHAR2(30);
7 PUBLICATION VARCHAR2(30);
8 NOBOOKS NUMBER(10);
9 LIB_REC LIB%ROWTYPE;
10 BEGIN
11 BOOKNO:=&BOOKNO;
12 BNAME:='&BOOKNAME';
13 AUTHOR:='&AUTHOR';
14 PUBLICATION:='&PUBLICATION';
15 NOC:=&NOOFCOPIES;
16 NOC1:=NOC;
17 SELECT COUNT(*) INTO NOBOOKS FROM LIB WHERE BOOKNAME=BNAME;
18 IF NOBOOKS=0 THEN
19 INSERT INTO LIB VALUES(BNAME,AUTHOR,PUBLICATION,NOC);
20 ELSE
21 UPDATE LIB SET NOOFCOPIES=NOOFCOPIES+NOC WHERE BOOKNAME=BNAME;
22 END IF;
23 WHILE NOC!=0
24 LOOP
25 INSERT INTO BOOKS VALUES(BOOKNO,BNAME,'YES',0);
26 NOC:=NOC-1;
27 BOOKNO:=BOOKNO+1;
28 END LOOP;
29 END;
30 /
ENTER VALUE FOR BOOKNO: 1234
OLD 11: BOOKNO:=&BOOKNO;
NEW 11: BOOKNO:=1234;
ENTER VALUE FOR BOOKNAME: DBMS
OLD 12: BNAME:='&BOOKNAME';
NEW 12: BNAME:='DBMS';
ENTER VALUE FOR AUTHOR: SILBER
OLD 13: AUTHOR:='&AUTHOR';
NEW 13: AUTHOR:='SILBER';
ENTER VALUE FOR PUBLICATION: TATA
OLD 14: PUBLICATION:='&PUBLICATION';
NEW 14: PUBLICATION:='TATA';
ENTER VALUE FOR NOOFCOPIES: 5
OLD 15: NOC:=&NOOFCOPIES;
NEW 15: NOC:=5;
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
Output DBMS Lab CS1307 Database Management system

SQL> /
ENTER VALUE FOR BOOKNO: 1256
OLD 11: BOOKNO:=&BOOKNO;
NEW 11: BOOKNO:=1256;
ENTER VALUE FOR BOOKNAME: OOAD
OLD 12: BNAME:='&BOOKNAME';
NEW 12: BNAME:='OOAD';
ENTER VALUE FOR AUTHOR: ALIBRAHMI
OLD 13: AUTHOR:='&AUTHOR';
NEW 13: AUTHOR:='ALIBRAHMI';
ENTER VALUE FOR PUBLICATION: MEGRA
OLD 14: PUBLICATION:='&PUBLICATION';
NEW 14: PUBLICATION:='MEGRA';
ENTER VALUE FOR NOOFCOPIES: 2
OLD 15: NOC:=&NOOFCOPIES;
NEW 15: NOC:=2;
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SQL> /
ENTER VALUE FOR BOOKNO: 1789
OLD 11: BOOKNO:=&BOOKNO;
NEW 11: BOOKNO:=1789;
ENTER VALUE FOR BOOKNAME: CN
OLD 12: BNAME:='&BOOKNAME';
NEW 12: BNAME:='CN';
ENTER VALUE FOR AUTHOR: FEROZAN
OLD 13: AUTHOR:='&AUTHOR';
NEW 13: AUTHOR:='FEROZAN';
ENTER VALUE FOR PUBLICATION: PRESMAN
OLD 14: PUBLICATION:='&PUBLICATION';
NEW 14: PUBLICATION:='PRESMAN';
ENTER VALUE FOR NOOFCOPIES: 6
OLD 15: NOC:=&NOOFCOPIES;
NEW 15: NOC:=6;
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

DisplayOutput DBMS Lab CS1307 Database Management system

SQL> SELECT * FROM LIB;
BOOKNAME AUTHOR PUBLICATION NOOFCOPIES
----------------- ------------ -------------------- ----------------------
DBMS SILBER TATA 5
OOAD ALIBRAHMI MEGRA 2
CN FEROZAN PRESMAN 6

SQL> SELECT * FROM BOOKS;
BOOKNO BOOKNAME AVA SUBSCRIBED_TO
------------ ----------------- ------ ----------
1234 DBMS YES 0
1235 DBMS YES 0
1236 DBMS YES 0
1237 DBMS YES 0
1238 DBMS YES 0
1256 OOAD YES 0
1257 OOAD YES 0
1789 CN YES 0
1790 CN YES 0
1791 CN YES 0
1792 CN YES 0
1793 CN YES 0
1794 CN YES 0
13 ROWS SELECTED.

SQL> SELECT * FROM STUDENT;
ROLLNO NAME NO_CARD
------------ ---------------- ---------
1 AISH 2
32 NABISHA 2
42 RAM 2
52 JOHN 2
62 THUTHI 2

Related post