1. LIBRARY DATABASE.sql

-- 1
CREATE TABLE PUBLISHER
(NAME VARCHAR(20) PRIMARY KEY,
PHONE INTEGER,
ADDRESS VARCHAR(20));

DESC PUBLISHER;

-- 2
CREATE TABLE BOOK
(BID INTEGER PRIMARY KEY,
TITLE VARCHAR(20),
PUBYEAR VARCHAR(20),
PUBNAME REFERENCES PUBLISHER(NAME) ON DELETE CASCADE);

DESC BOOK;

-- 3
CREATE TABLE BOOK_AUTHORS
(ANAME VARCHAR(20),
BID REFERENCES BOOK(BID) ON DELETE CASCADE,
PRIMARY KEY(BID, ANAME));

DESC BOOK_AUTHORS;

-- 4
CREATE TABLE LIBRARY_BRANCH
(BID INTEGER PRIMARY KEY,
BNAME VARCHAR(50),
ADDRESS VARCHAR(50));

DESC LIBRARY_BRANCH;

-- 5
CREATE TABLE BOOK_COPIES
(NOOFCOPIES INTEGER,
BID REFERENCES BOOK(BID) ON DELETE CASCADE,
BRANCHID REFERENCES LIBRARY_BRANCH(BRANCHID) ON DELETE CASCADE,
PRIMARY KEY (BID,BRANCHID));

DESC BOOK_COPIES;

-- 6
CREATE TABLE CARD
(CARDNO INTEGER PRIMARY KEY);

DESC CARD;

-- 7
CREATE TABLE BOOKLENDING
(DATEOUT DATE,
DUEDATE DATE,
BID REFERENCES BOOK(BID) ON DELETE CASCADE,
BRANCHID REFERENCES LIBRARY_BRANCH(BRANCHID) ON DELETE CASCADE,
CARDNO REFERENCES CARD(CARDNO) ON DELETE CASCADE,
PRIMARY KEY (BID,BRANCHID,CARDNO));

DESC BOOKLENDING;


-- 1
INSERT INTO PUBLISHER VALUES('MCGRAW-HILL',9191919191,'BANGALORE');
INSERT INTO PUBLISHER VALUES('PEARSON',8181818181,'NEWDELHI');
INSERT INTO PUBLISHER VALUES('RANDOM HOUSE',7171717171,'HYDERABAD');
INSERT INTO PUBLISHER VALUES('LIVRE',6161616161,'CHENNAI');
INSERT INTO PUBLISHER VALUES('PLANETA',5151515151,'BANGALORE');

SELECT * FROM PUBLISHER;

-- 2
INSERT INTO BOOK VALUES(1,'DBMS','JAN-2017','MCGRAW-HILL');
INSERT INTO BOOK VALUES(2,'ADBMS','JUN-2016','MCGRAW-HILL');
INSERT INTO BOOK VALUES(3,'CN','SEP-2016','PEARSON');
INSERT INTO BOOK VALUES(4,'CG','SEP-2015','PLANETA');
INSERT INTO BOOK VALUES(5,'OS','MAY-2016','PEARSON');

SELECT * FROM BOOK;

-- 3
INSERT INTO BOOK_AUTHORS VALUES('NAVATHE',1);
INSERT INTO BOOK_AUTHORS VALUES('NAVATHE',2);
INSERT INTO BOOK_AUTHORS VALUES('TANENBAUM',3);
INSERT INTO BOOK_AUTHORS VALUES('EDWARD ANGEL',4);
INSERT INTO BOOK_AUTHORS VALUES('GALVIN',5);

SELECT * FROM BOOK_AUTHORS;

-- 4
INSERT INTO LIBRARY_BRANCH VALUES(10,'RR NAGAR','BANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES(11,'RNSIT','BANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES(12,'RAJAJI NAGAR','BANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES(13,'NITTE','MANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES(14,'MANIPAL','UDUPI');

SELECT * FROM LIBRARY_BRANCH;

-- 5
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);

SELECT * FROM BOOK_COPIES;

-- 6
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);

SELECT * FROM CARD;

-- 7
INSERT INTO BOOKLENDING VALUES('01-JAN-17','01-JUN-17',1,10,101);
INSERT INTO BOOKLENDING VALUES('11-JAN-17','11-MAR-17',3,14,101);
INSERT INTO BOOKLENDING VALUES('21-FEB-17','21-APR-17',2,13,101);
INSERT INTO BOOKLENDING VALUES('15-MAR-17','15-JUL-17',4,11,101);
INSERT INTO BOOKLENDING VALUES('12-APR-17','12-MAY-17',1,11,104);

SELECT * FROM BOOKLENDING;


--QUERIES

-- QUERY 1

SELECT B.BID, B.TITLE, B.PUBNAME, A.ANAME,C.NOOFCOPIES,L.BRANCHID
FROM BOOK B, BOOK_AUTHORS A, BOOK_COPIES C, LIBRARY_BRANCH LIBRARY_BRANCH LIBRARY_BRANCH L
WHERE B.BID=A.BID
AND B.BID=C.BID
AND L.BRANCHID=C.BRANCHID;

-- QUERY 2

SELECT CARDNO
FROM BOOKLENDING
WHERE DATEOUT BETWEEN '01-JAN-17' AND '01-JUN-17'
GROUP BY CARDNO
HAVING COUNT(*)>3;

-- QUERY 3

DELETE FROM BOOK
WHERE BID = 3;

-- QUERY 4

CREATE VIEW VPUBLICATION AS
SELECT PUBYEAR
FROM BOOK;

-- QUERY 5

CREATE VIEW VBOOKS AS
SELECTB.BID, B.TITLE, C.NOOFCOPIES
FROM
BOOK B, BOOK_COPIES C, LIBRARY_BRANCH L
WHEREB.BID=C.BID
AND C.BRANCHID=L.BRANCHID;