The data from bm9avan

👋 Hi, I’m @bm9avan (PAVAN)
👀 I’m interested in building web
🌱 I’m currently learning NEXT.js
💞️ I’m looking to collaborate on creating projects
📫 How to reach me https://bm9avan.bio.link/

data's People

Watchers

data's Issues

dbms

Uploading DBMS MANUAL 2018 SCHEME_ISE_221014_144329 (2).pdf…

akhil anna

Uploading DBMS MANUAL 2018 SCHEME_ISE_221014_144329.pdf…

BOOK(Book_id, Title, Publisher_Name, Pub_Year)
BOOK_AUTHORS(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)
CREATE TABLE PUBLISHER
(NAME VARCHAR2 (20),
PHONE INTEGER,
ADDRESS VARCHAR2 (20),
CONSTRAINT PKP PRIMARY KEY(NAME));
CREATE TABLE BOOK
(BOOK_ID VARCHAR(8),
TITLE VARCHAR2 (20),
PUBLISHER_NAME VARCHAR(20),
PUB_YEAR INTEGER,
CONSTRAINT PKB PRIMARY KEY(BOOK_ID),
CONSTRAINT FKB FOREIGN KEY(PUBLISHER_NAME) REFERENCES
PUBLISHER(NAME));
CREATE TABLE BOOK_AUTHORS
(BOOK_ID VARCHAR(8),
AUTHOR_NAME VARCHAR2 (20),
CONSTRAINT PKBA PRIMARY KEY (BOOK_ID,AUTHOR_NAME),
CONSTRAINT FKBA FOREIGN KEY(BOOK_ID) REFERENCES BOOK(BOOK_ID)ON
DELETE CASCADE);
CREATE TABLE LIBRARY_BRANCH
(BRANCH_ID VARCHAR(6),
BRANCH_NAME VARCHAR2 (20),
ADDRESS VARCHAR2 (20),
CONSTRAINT PKLB PRIMARY KEY(BRANCH_ID));
CREATE TABLE BOOK_COPIES
(BOOK_ID VARCHAR(8),
BRANCH_ID VARCHAR2(6),
NO_OF_COPIES INTEGER,
CONSTRAINT PKBC PRIMARY KEY(BOOK_ID, BRANCH_ID),
CONSTRAINT FKBC FOREIGN KEY(BOOK_ID) REFERENCES BOOK(BOOK_ID)ON
DELETE CASCADE,
CONSTRAINT FKBB FOREIGN KEY(BRANCH_ID) REFERENCES
LIBRARY_BRANCH(BRANCH_ID));
CREATE TABLE BOOK_LENDING
(BOOK_ID VARCHAR(8),
BRANCH_ID VARCHAR2(6),
CARD_NO INTEGER,
DATE_OUT DATE,
DUE_DATE DATE,
CONSTRAINT PKBL PRIMARY KEY(BOOK_ID, BRANCH_ID,CARD_NO),
CONSTRAINT FKBL FOREIGN KEY(BOOK_ID) REFERENCES BOOK(BOOK_ID)ON
DELETE CASCADE);
INSERT INTO PUBLISHER VALUES ('MCGRAW-HILL', 9989076587, 'BANGALORE');
INSERT INTO PUBLISHER VALUES ('PEARSON', 9889076565, 'NEWDELHI');
INSERT INTO PUBLISHER VALUES ('RANDOM HOUSE', 7455679345,
'HYDRABAD');
INSERT INTO PUBLISHER VALUES ('HACHETTE LIVRE', 8970862340, 'CHENAI');
INSERT INTO PUBLISHER VALUES ('GRUPO PLANETA', 7756120238,
'BANGALORE');
SQL> SELECT * FROM PUBLISHER;
NAME PHONE ADDRESS

MCGRAW-HILL 9989076587 BANGALORE
PEARSON 9889076565 NEWDELHI
RANDOM HOUSE 7455679345 HYDRABAD
HACHETTE LIVRE 8970862340 CHENAI
GRUPO PLANETA 7756120238 BANGALORE
INSERT INTO BOOK VALUES ('1','DBMS', 'MCGRAW-HILL',2017);
INSERT INTO BOOK VALUES ('2','ADBMS', 'MCGRAW-HILL',2016);
INSERT INTO BOOK VALUES ('3','CN', 'PEARSON',2016);
INSERT INTO BOOK VALUES ('4','CG', 'GRUPO PLANETA',2015);
INSERT INTO BOOK VALUES ('5','OS', 'PEARSON',2016);
SQL> SELECT * FROM BOOK;

INSERT INTO BOOK_AUTHORS VALUES ('1','NAVATHE');
INSERT INTO BOOK_AUTHORS VALUES ('2','NAVATHE');
INSERT INTO BOOK_AUTHORS VALUES ('3','TANENBAUM');
INSERT INTO BOOK_AUTHORS VALUES ('4','EDWARD ANGEL');
INSERT INTO BOOK_AUTHORS VALUES ('5','GALVIN');
SQL> SELECT * FROM BOOK_AUTHORS ;
BOOK_ID AUTHOR_NAME

1 NAVATHE
2 NAVATHE
3 TANENBAUM
4 EDWARD ANGEL
5 GALVIN
INSERT INTO LIBRARY_BRANCH VALUES ('10','VV PURAM','BANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES ('11','BIT','BANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES ('12','RAJAJI NAGAR', 'BANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES ('13','JP NAGAR','BANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES ('14','JAYANAGAR','BANGALORE');
SQL> SELECT * FROM LIBRARY_BRANCH;
BRANCH BRANCH_NAME ADDRESS

10 VV PURAM BANGALORE
11 BIT BANGALORE
12 RAJAJI NAGAR BANGALORE
13 JP NAGAR BANGALORE
14 JAYANAGAR BANGALORE
INSERT INTO BOOK_COPIES VALUES ( '1','10', 10);
INSERT INTO BOOK_COPIES VALUES ( '1','11', 5);
INSERT INTO BOOK_COPIES VALUES ( '2','12', 2);
INSERT INTO BOOK_COPIES VALUES ( '2','13', 5);
INSERT INTO BOOK_COPIES VALUES ( '3','14', 7);
INSERT INTO BOOK_COPIES VALUES ( '5','10', 1);
INSERT INTO BOOK_COPIES VALUES ( '4','11', 3);
SQL> SELECT * FROM BOOK_COPIES;

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

SQL> SELECT * FROM BOOK_LENDING;
SELECT B.BOOK_ID, B.TITLE, B.PUBLISHER_NAME, A.AUTHOR_NAME,
C.NO_OF_COPIES, L.BRANCH_ID
FROM BOOK B, BOOK_AUTHORS A, BOOK_COPIES C, LIBRARY_BRANCH L
WHERE B.BOOK_ID=A.BOOK_ID
AND B.BOOK_ID=C.BOOK_ID
AND L.BRANCH_ID=C.BRANCH_ID;
SELECT CARD_NO
FROM BOOK_LENDING
WHERE DATE_OUT BETWEEN '01-JAN-2017' AND '01-JUL-2017'
GROUP BY CARD_NO
HAVING COUNT (*)>3;
SQL> SELECT * FROM BOOK;
SQL> SELECT * FROM BOOK_COPIES;

CREATE TABLE BOOKPART
PARTITION BY RANGE (PUB_YEAR)
(PARTITION P1 VALUES LESS THAN(2016),
PARTITION P2 VALUES LESS THAN (MAXVALUE))
AS SELECT * FROM BOOK;

CREATE VIEW BC AS SELECT B.BOOK_ID,C.TITLE,B.BRANCH_ID,
(B.NO_OF_COPIES-(SELECT COUNT(*) FROM BOOK_LENDING WHERE
B.BOOK_ID=BOOK_ID AND B.BRANCH_ID=BRANCH_ID)) AS NO_COPY
FROM BOOK_COPIES B,BOOK C
WHERE B.BOOK_ID=C.BOOK_ID;