Scribd Logo
Upload

Welcome to Scribd!

  • 3 views

    EP111

    Uploaded by

    panafo4689
    DBMS Queries

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd

    EP111

    Uploaded by

    panafo4689
    3 views2 pages
    DBMS Queries

    Copyright

    © © All Rights Reserved

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    DBMS Queries

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    3 views2 pages

    EP111

    Uploaded by

    panafo4689
    DBMS Queries

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    of 2

    CREATE TABLE PUBLISHER

    (NAME VARCHAR(20) PRIMARY KEY,


    PHONE INTEGER,
    ADDRESS VARCHAR(20));

    CREATE TABLE BOOK


    (BOOK_ID INTEGER PRIMARY KEY,
    TITLE VARCHAR(20),
    PUB_YEAR VARCHAR(20),
    PUB_NAME VARCHAR(20),
    FOREIGN KEY (PUB_NAME) REFERENCES PUBLISHER(NAME) ON DELETE CASCADE);

    CREATE TABLE BOOK_AUTHORS


    (AUTHOR_NAME VARCHAR(20),
    BOOK_ID INTEGER,
    FOREIGN KEY (BOOK_ID) REFERENCES BOOK(BOOK_ID) ON DELETE CASCADE,
    PRIMARY KEY(BOOK_ID, AUTHOR_NAME));

    CREATE TABLE LIBRARY_PROGRAMME


    (PROGRAMME_ID INTEGER PRIMARY KEY,
    PROGRAMME_NAME VARCHAR(50),
    ADDRESS VARCHAR(50));

    CREATE TABLE BOOK_COPIES


    (NO_OF_COPIES INTEGER,
    BOOK_ID INTEGER,
    PROGRAMME_ID INTEGER,
    FOREIGN KEY (BOOK_ID) REFERENCES BOOK(BOOK_ID) ON DELETE CASCADE,
    FOREIGN KEY(PROGRAMME_ID) REFERENCES LIBRARY_PROGRAMME(PROGRAMME_ID) ON DELETE
    CASCADE,
    PRIMARY KEY (BOOK_ID,PROGRAMME_ID));

    CREATE TABLE CARD


    (CARD_NO INTEGER PRIMARY KEY);

    CREATE TABLE BOOK_LENDING


    (BOOK_ID INTEGER,
    PROGRAMME_ID INTEGER,
    CARD_NO INTEGER,
    DATE_OUT DATE,
    DUE_DATE DATE,
    FOREIGN KEY (BOOK_ID) REFERENCES BOOK(BOOK_ID) ON DELETE CASCADE,
    FOREIGN KEY (PROGRAMME_ID) REFERENCES LIBRARY_PROGRAMME(PROGRAMME_ID) ON DELETE
    CASCADE,
    FOREIGN KEY (CARD_NO) REFERENCES CARD(CARD_NO) ON DELETE CASCADE,
    PRIMARY KEY (BOOK_ID,PROGRAMME_ID,CARD_NO));

    INSERT INTO PUBLISHER VALUES('SAPNA',912121212,'BANGALORE');


    INSERT INTO PUBLISHER VALUES('PENGUIN',921212121,'NEW YORK');
    INSERT INTO PUBLISHER VALUES('PEARSON',913131313,'HYDERABAD');
    INSERT INTO PUBLISHER VALUES('OZONE',931313131,'CHENNAI');
    INSERT INTO PUBLISHER VALUES('PLANETZ',914141414,'BANGALORE');

    INSERT INTO BOOK VALUES(1,'BASICS OF EXCEL','JAN-2017','SAPNA');


    INSERT INTO BOOK VALUES(2,'PROGRAMMING MINDSET','JUN-2018','PLANETZ');
    INSERT INTO BOOK VALUES(3,'BASICS OF SQL','SEP-2016','PEARSON');
    INSERT INTO BOOK VALUES(4,'DBMS FOR BEGINNERS','SEP-2015','PLANETZ');
    INSERT INTO BOOK VALUES(5,'WEB SERVICES','MAY-2017','OZONE');

    INSERT INTO BOOK_AUTHORS VALUES('SRI DEVI',1);


    INSERT INTO BOOK_AUTHORS VALUES('DEEPAK',2);
    INSERT INTO BOOK_AUTHORS VALUES('PRAMOD',3);
    INSERT INTO BOOK_AUTHORS VALUES('SWATHI',4);
    INSERT INTO BOOK_AUTHORS VALUES('PRATHIMA',5);

    INSERT INTO LIBRARY_PROGRAMME VALUES(100,'HSR LAYOUT','BANGALORE');


    INSERT INTO LIBRARY_PROGRAMME VALUES(101,'KENGERI','BANGALORE');
    INSERT INTO LIBRARY_PROGRAMME VALUES(102,'BANASHANKARI','BANGALORE');
    INSERT INTO LIBRARY_PROGRAMME VALUES(103,'SHANKARA NAGAR','MANGALORE');
    INSERT INTO LIBRARY_PROGRAMME VALUES(104,'MANIPAL','UDUPI');

    INSERT INTO BOOK_COPIES VALUES(10,1,100);


    INSERT INTO BOOK_COPIES VALUES(16,1,101);
    INSERT INTO BOOK_COPIES VALUES(20,2,102);
    INSERT INTO BOOK_COPIES VALUES(6,2,103);
    INSERT INTO BOOK_COPIES VALUES(4,3,104);
    INSERT INTO BOOK_COPIES VALUES(7,5,100);
    INSERT INTO BOOK_COPIES VALUES(3,4,101);

    INSERT INTO CARD VALUES(500);


    INSERT INTO CARD VALUES(501);
    INSERT INTO CARD VALUES(502);
    INSERT INTO CARD VALUES(503);
    INSERT INTO CARD VALUES(504);

    INSERT INTO BOOK_LENDING VALUES(1, 100, 501, '2017-01-01','2017-01-31');


    INSERT INTO BOOK_LENDING VALUES(3, 104, 501, '2017-01-11','2017-03-01');
    INSERT INTO BOOK_LENDING VALUES(2, 103, 501, '2017-02-21','2017-04-21');
    INSERT INTO BOOK_LENDING VALUES(4, 101, 501, '2017-03-11','2017-06-11');
    INSERT INTO BOOK_LENDING VALUES(1, 101, 504, '2017-04-09','2017-07-08');

    You might also like