Scribd
Upload
4 views2 pages

Database Table Creation and Queries

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.

Uploaded by

Adi
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
4 views2 pages

Database Table Creation and Queries

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.

Uploaded by

Adi
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

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
[Link] all the tables structures
[Link] total number of books in each category with category id and book
count
[Link] all books name by category and sort them by book_name
[Link] a new column name discounted price with book name, original price
and 50% discount
[Link] all the books that are in category from 1 to 3
[Link] all the books which prices are ranged from 100 to 500 taka
[Link] all the author names whose name starts with H and ends with D
[Link] all the books which price is greater than 100 taka and they are in
category 2 or all the books from category 3
[Link] 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