Scribd Logo
Upload

Welcome to Scribd!

  • 110 views

    SQL3

    Uploaded by

    chandumenswear3
    The document discusses creating tables and inserting data into a library database. It provides examples of SQL queries to retrieve specific book details and listings based on criteria like publisher, price, author name etc.

    Copyright:

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

    SQL3

    Uploaded by

    chandumenswear3
    110 views6 pages
    The document discusses creating tables and inserting data into a library database. It provides examples of SQL queries to retrieve specific book details and listings based on criteria like publisher, price, author name etc.

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses creating tables and inserting data into a library database. It provides examples of SQL queries to retrieve specific book details and listings based on criteria like publisher, price, author name etc.

    Copyright:

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

    SQL3

    Uploaded by

    chandumenswear3
    The document discusses creating tables and inserting data into a library database. It provides examples of SQL queries to retrieve specific book details and listings based on criteria like publisher, price, author name etc.

    Copyright:

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

    DBMS Lab Manual

    Activity 3: (Logical, Relational Operators)

    Database: Library
    Create Following tables and insert tuples with suitable constraints

    Table: Books

    Book_Id Book_name Author_Name Publishers Price Type Quantity


    C0001 The Klone and I Lata Kappor EPP 355 Novel 5
    F0001 The Tears William Hopkins First Publ 650 Fiction 20
    T0001 My First C++ Brain & Brooke ERP 350 Text 10
    T0002 C++ Brainworks A.W.Rossaine TDH 350 Text 15
    F0002 Thunderbolts Ana Roberts First Publ. 750 Fiction 50

    Table : Issued

    Book_Id Quantity_Issued
    T0001 4
    C0001 5
    F0001 2
    T0002 5
    F0002 8

    Write queries for the following


    1. To show Book name, Author name and price of books of First Publ. publisher
    2. Display Book id, Book name and publisher of books having quantity more than 8 and price less
    than 500
    3. Select Book id, book name, author name of books which is published by other than ERP
    publishers and price between 300 to 700
    4. Generate a Bill with Book_id, Book_name, Publisher, Price, Quantity, 4% of VAT “Total”
    5. Display book details with book id‟s C0001, F0001, T0002, F0002 (Hint: use IN operator)
    6. Display Book list other than, type Novel and Fiction
    7. Display book details with author name starts with letter „A‟
    8. Display book details with author name starts with letter „T‟ and ends with „S‟
    9. Select BookId, BookName, Author Name , Quantity Issued where Books.BooksId =
    Issued.BookId
    10. List the book_name, Author_name, Price. In ascending order of Book_name and then on
    descending order of price

    Create table :-

    Table 1:-

    SQL> create table books (book_id varchar(10), book_name varchar(15), author_name varchar(15),
    publishers varchar(10),price number, type varchar(10), quantity number, primary key(book_id));

    Table created.

    Priya M R 17
    DBMS Lab Manual

    Table 2:-

    SQL> create table issued(book_id varchar(10), Qty_issued number, foreign key(book_id) references
    books(book_id));

    Table created.

    Insert values into tables:-

    Table 1:-

    SQL> insert into books values('&book_id','&book_name', '&author_name','&publishers', &price,


    '&type',&quantity);

    Enter value for book_id: C0001


    Enter value for book_name: The Klone and I
    Enter value for author_name: Lata Kappor
    Enter value for publishers: EPP
    Enter value for price: 355
    Enter value for type: Novel
    Enter value for quantity: 5

    1 row created.

    SQL> /
    Enter value for book_id: F0001
    Enter value for book_name: The Tears
    Enter value for author_name: William Hopkins
    Enter value for publishers: First Publ
    Enter value for price: 650
    Enter value for type: Fiction
    Enter value for quantity: 20

    1 row created.

    SQL> /
    Enter value for book_id: T0001
    Enter value for book_name: My First C++
    Enter value for author_name: Brain & Brooke
    Enter value for publishers: ERP
    Enter value for price: 350
    Enter value for type: Text
    Enter value for quantity: 10

    1 row created.

    SQL> /
    Enter value for book_id: T0002
    Enter value for book_name: C++ Brainworks
    Enter value for author_name: A.W.Rossaine
    Enter value for publishers: TDH
    Enter value for price: 350
    Enter value for type: Text
    Enter value for quantity: 15

    1 row created.

    Priya M R 18
    DBMS Lab Manual

    SQL> /
    Enter value for book_id: F0002
    Enter value for book_name: Thunderbolts
    Enter value for author_name: Ana Roberts
    Enter value for publishers: First Publ
    Enter value for price: 750
    Enter value for type: Fiction
    Enter value for quantity: 50

    1 row created.

    Table 2:-

    SQL> insert into issued values('&book_id',&qty_issued);


    Enter value for book_id: T0001
    Enter value for qty_issued: 4

    1 row created.

    SQL> /
    Enter value for book_id: C0001
    Enter value for qty_issued: 5

    1 row created.

    SQL> /
    Enter value for book_id: F0001
    Enter value for qty_issued: 2
    1 row created.

    SQL> /
    Enter value for book_id: T0002
    Enter value for qty_issued: 5

    1 row created.

    SQL> /
    Enter value for book_id: F0002
    Enter value for qty_issued: 8

    1 row created.

    SQL> select * from books;

    BOOK_ID BOOK_NAME AUTHOR_NAME PUBLISHERS PRICE TYPE QUANTITY


    ---------- --------------- --------------- ---------- ------- ---------- --------------
    C0001 The Klone and I Lata Kappor EPP 355 Novel 5
    F0001 The Tears William Hopkins First Publ 650 Fiction 20
    T0001 My First C++ Brain & Brooke ERP 350 Text 10
    T0002 C++ Brainworks A.W.Rossaine TDH 350 Text 15
    F0002 Thunderbolts Ana Roberts First Publ 750 Fiction 50

    Priya M R 19
    DBMS Lab Manual

    SQL> select * from issued;

    BOOK_ID QTY_ISSUED
    ---------- ----------
    T0001 4
    C0001 5
    F0001 2
    T0002 5
    F0002 8

    Queries:-

    1. To show Book name, Author name and price of books of First Publ. publisher

    SQL> select book_name,author_name,price from books where publishers='First Publ';

    BOOK_NAME AUTHOR_NAME PRICE


    --------------- --------------- --------- -----------------------------
    The Tears William Hopkins 650
    Thunderbolts Ana Roberts 750

    2. Display Book id, Book name and publisher of books having quantity more than 8 and price
    less than 500.

    SQL> select book_id,book_name,publishers from books where quantity>8 and price<500;

    BOOK_ID BOOK_NAME PUBLISHERS


    ---------- ---- --------- ---------- ---------------------------
    T0001 My First C++ ERP
    T0002 C++ Brainworks TDH

    3. Select Book id, book name, author name of books which is published by other than ERP
    publishers and price between 300 to 700.

    SQL> select book_id,book_name,author_name from books where publishers<>'ERP' and (price


    between 300 and 700);

    BOOK_ID BOOK_NAME AUTHOR_NAME


    ---------- ----- -------- ----------------- -----------------------
    C0001 The Klone and I Lata Kappor
    F0001 The Tears William Hopkins
    T0002 C++ Brainworks A.W.Rossaine

    Priya M R 20
    DBMS Lab Manual

    4. Generate a Bill with Book_id, Book_name, Publisher, Price, Quantity, 4% of VAT “Total”.

    SQL> select books.book_id,book_name,publishers,price,quantity,price+price*0.04 as total from


    books,issued where books.book_id=issued.book_id;

    BOOK_ID BOOK_NAME PUBLISHERS PRICE QUANTITY TOTAL


    ---------- --------------- ---------- --------- --------- -------------------------------------------------------------
    T0001 My First C++ ERP 350 10 364
    C0001 The Klone and I EPP 355 5 369.2
    F0001 The Tears First Publ 650 20 676
    T0002 C++ Brainworks TDH 350 15 364
    F0002 Thunderbolts First Publ 750 50 780

    5. Display book details with book id‟s C0001, F0001, T0002, F0002 (Hint: use IN operator)

    SQL> select * from books where book_id in ('C0001','F0001','T0002','F0002');

    BOOK_ID BOOK_NAME AUTHOR_NAME PUBLISHERS PRICE TYPE QUANTITY


    ---------- --------------- --------------- ---------- --------- ---------- ------------------------------------------
    F0002 Thunderbolts Ana Roberts First Publ 750 Fiction 50
    T0002 C++ Brainworks A.W.Rossaine TDH 350 Text 15
    F0001 The Tears William Hopkins First Publ 650 Fiction 20
    C0001 The Klone and I Lata Kappor EPP 355 Novel 5

    6. Display Book list other than, type Novel and Fiction.

    SQL> select book_name from books where type<>'novel' and type<>'fiction';

    BOOK_NAME
    ---------------
    My First C++
    C++ Brainworks

    7. Display book details with author name starts with letter „A‟.

    SQL> select * from books where author_name like 'A%';

    BOOK_ID BOOK_NAME AUTHOR_NAME PUBLISHERS PRICE TYPE QUANTITY


    ---------- --------------- ------- ----- ---------- ------ ---------- ------------------------ --------------
    T0002 C++ Brainworks A.W.Rossaine TDH 350 Text 15
    F0002 Thunderbolts Ana Roberts First Publ 750 Fiction 50

    8. Display book details with author name starts with letter „T‟ and ends with „S‟.

    SQL> select * from books where author_name like 'T%S';

    no rows selected

    Priya M R 21
    DBMS Lab Manual

    9. Select BookId, BookName, Author Name , Quantity Issued where Books.BooksId =


    Issued.BookId.

    SQL> select books.book_id,book_name,author_name,qty_issued from books,issued where


    books.book_id=issued.book_id;

    BOOK_ID BOOK_NAME AUTHOR_NAME QTY_ISSUED


    ---------- --------------- ----- ---------- --------------------- --------------------
    T0001 My First C++ Brain & Brooke 4
    C0001 The Klone and I Lata Kappor 5
    F0001 The Tears William Hopkins 2
    T0002 C++ Brainworks A.W.Rossaine 5
    F0002 Thunderbolts Ana Roberts 8

    10. List the book_name, Author_name, Price. In ascending order of Book_name and then on
    descending order of price.

    SQL> select book_name,author_name,price from books order by book_name asc, price desc;

    BOOK_NAME AUTHOR_NAME PRICE


    --------------- --------------- --------- ------------
    C++ Brainworks A.W.Rossaine 350
    My First C++ Brain & Brooke 350
    The Klone and I Lata Kappor 355
    The Tears William Hopkins 650
    Thunderbolts Ana Roberts 750

    Priya M R 22

    You might also like