Scribd Logo
Upload

Welcome to Scribd!

  • 21 views

    Idb Lab Performance

    Uploaded by

    Adi
    This document contains the details of an assignment to create tables, insert data, perform queries and updates on tables related to books, authors, categories and employees in a database. It includes 18 tasks such as creating tables, inserting data, adding/removing columns, finding maximum/minimum/average values, grouping and sorting data, applying conditions and discounts. The goal is to demonstrate proficiency in SQL commands for data definition, manipulation and queries.

    Copyright:

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

    Idb Lab Performance

    Uploaded by

    Adi
    21 views2 pages
    This document contains the details of an assignment to create tables, insert data, perform queries and updates on tables related to books, authors, categories and employees in a database. It includes 18 tasks such as creating tables, inserting data, adding/removing columns, finding maximum/minimum/average values, grouping and sorting data, applying conditions and discounts. The goal is to demonstrate proficiency in SQL commands for data definition, manipulation and queries.

    Original Description:

    database

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document contains the details of an assignment to create tables, insert data, perform queries and updates on tables related to books, authors, categories and employees in a database. It includes 18 tasks such as creating tables, inserting data, adding/removing columns, finding maximum/minimum/average values, grouping and sorting data, applying conditions and discounts. The goal is to demonstrate proficiency in SQL commands for data definition, manipulation and queries.

    Copyright:

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

    Idb Lab Performance

    Uploaded by

    Adi
    This document contains the details of an assignment to create tables, insert data, perform queries and updates on tables related to books, authors, categories and employees in a database. It includes 18 tasks such as creating tables, inserting data, adding/removing columns, finding maximum/minimum/average values, grouping and sorting data, applying conditions and discounts. The goal is to demonstrate proficiency in SQL commands for data definition, manipulation and queries.

    Copyright:

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

    LAB Performance

    Introduction to database
    Name:

    Imteaz Md. Nafis

    ID:

    15-29623-2

    BOOK (Book_id, book_name, author_id,cat_id,isbn, price,inserted_by,insert_date)


    AUTHOR (author_id,author_name,address,phone,email)
    CATEGORY (cat_id, cat_name)
    EMPLOYEE (emp_id, emp_name, address, emp_salary, emp_mgr_id)
    1. Create the above mentioned tables
    Ans:
    create table Book(
    book_id number(10)constraint PK_of_table_book primary key,
    book_name varchar2(20),
    author_id number(10),
    cat_id varchar2(20),
    isbn number(10),
    price number(5),
    inserted_by varchar2(30),
    insert_date date default sysdate
    )
    desc Book
    create table author(
    author_id number(10)constraint PK_of_table_author primary key,
    author_name varchar2(20),
    address varchar(20),
    phone number(11),
    email varchar2(20)
    )
    desc author
    create table category(
    cat_id number(10)constraint PK_of_table_category primary key,
    cat_name varchar2(20)
    )
    desc category
    create table employee(
    emp_id number(10)constraint PK_of_table_employee primary key,
    emp_name varchar2(20),
    address varchar2(20),
    emp_salary number(6),
    emp_mgr_id number(11)
    )

    desc employee
    2. Insert 5 rows of data into each table
    Ans:
    3.
    4.
    5.
    6.
    7.
    8.
    9.

    Add three new columns to employee table (phone, email, dob)


    Rename address column of employee to emp_address
    Delete isbn column from book table
    Find maximum and minimum sale price from all the books
    Find the average price of all the books of category no 4
    Find all the books name and price inserted 7 days back from today
    Show all the books name in the following format This book_name has been
    inserted by inserted_by on insert_date
    10.Show all the tables structures
    11.Show total number of books in each category with category id and book
    count
    12.Group all books name by category and sort them by book_name
    13.Show a new column name discounted price with book name, original price
    and 50% discount
    14.Show all the books that are in category from 1 to 3
    15.Show all the books which prices are ranged from 100 to 500 taka
    16.Find all the author names whose name starts with H and ends with D
    17.Find all the books which price is greater than 100 taka and they are in
    category 2 or all the books from category 3
    18.Show the discounted price in the following manner with case and decode
    a. For category 1 book discount is 20%
    b. For category 2 book discount is 30%
    c. For category 3 book discount is 40%
    d. For all other books discount is 10%

    You might also like