P G Department of Computer Science & Technology DBMS-PS01CINT54

Practical Manual

Tables Without Constraints

Create Following tables
1. Customer Master Table:

Attribute Type
Cust_id Char(5)
Lname Char(15)
Fname Char(15)
Area Char(15)
Phone_no Number(8)

Insert following data

(a01, patel, vijacy, sa, 381334)

(a01, satiwal, vandana, mu, 556037)

(a03, jaguste, pramada, da, 372631)

(a04, navindgi, basu, ba, 66661)

(a05, sreedhran, ravi, va, -)

(a06, -, rukmini, ga, 512527)

2. Movie Master Table:

attribute Type
mv_no number(5)
Title Char(25)
Type Char(10)
Star Char(25)
Price Number(8,2)

Insert following data:

(1, bloody vengeance, action, jackie chan, 180.95)
(2, the film, thriller, tom cruise, 200.00)
(3, pretty woman, romantic, recharge gere, 150.00)
(4, home alone, comedy, macaulay culkin, 150.55)
(5, the fugitive, thriller, Harrison ford, 200.00)
(6, coma, suspense, Michael douglas, 100.00)
(7, Dracula, horror, gray oldman, 150.00)
(8, quick change, comedy, bill murray, 100.00)
(9, gone with the wind, drama, Clarke gable, 200.00)
(10, carry on doctor, comedy, Leslie Phillips, 100.00)

3. Invoicce transaction table:

Attribute Type
P G Department of Computer Science & Technology DBMS-PS01CINT54
Practical Manual

Inv_no Char(3)
Mv_no Number(5)
Cust_id char(5)
Issue_date Date
Return_date Date

Insert following data:

(i01, 4, a01, 13-Jan-96, 25-Jan-96)
(i02, 3, a02, 12-feb-96, 15-feb-96)
(i03, 1, a02, 15-feb-96, 18-feb-96)
(i04, 6, a03, 10-mar-96, 13-mar-96)
(i05, 7, a04, 05-feb-96, 08-feb-96)
(i06, 2, a06, 18-mar-96, 21-mar-96)
(i07, 9, a05, 07-Jan-96, 10-Jan-96)
(i08, 9, a01, 11-feb-96, 14-feb-96)
(i09, 1, a05, 15-feb-96, 28-feb-96)

Table Creation With Constraints

Create following tables

4. Create Dept table:

Name Type Constraints
Dept no Number(2) PK
dname Varchar2(14)
Loc varchar2(13)

Insert following data:

(10, Accounting, New York)
(20, RESEARCH, Dallas)
(30, SALES, CHICAGO)
(40, OPERATIONS, BOSTON)

5. Create EMP table:

Name Type Constraints

Empno Number(4) PK
ename Varchar2(10)
Job Varchar2(9)
MGR Number(4)
HIREDATE Date
SAL Number(7,2)
Comm Number(7,2)
Deptno Number(2) FK
P G Department of Computer Science & Technology DBMS-PS01CINT54
Practical Manual

Insert following data:

(7369, Smith, Clerk, 7902, 17-DEC-80, 800, , 20)

(7499, Allen, Salesman, 7698, 20-FEB-81, 1600, 300, 30)

(7521, Ward, Salesman, 7698, 22-feb-81, 1250,500,30)

(7566, Jones, Manager, 7839, 02-APR-81, 2975, , 20)

(7654, Martin, Salesman, 7698, 28-SEP-81, 1250, 1400, 30)

(7698, Blake, Manager, 7839, 01-MAY-81, 2850, , 30)

(7782, Clark, Manager, 7839, 09-JUN-81, 2450, , 10)

(7788, Scott, Analyst, 7566, 09-DEC-82, 3000, , 20)

(7839, King, President, , 17-NOV-81, 5000, , 10)

(7844, Turner, Salesman, 7698, 08-SEP-81, 1500, 0, 30)

(7876, ADAMS, Clerk, 7788, 12-JAN-83, 1100, , 20)

(7900, James, Clerk, 7698, 03-DEC-81, 950, , 30)

(7902, Ford, Analyst, 7506, 03-DEC-81, 3000, , 20)

(7934, Miller, Clerk, 7782, 23-JAN-82, 1300, , 10)

6. Create table SALGRADE

Name Type Constraints

grade number(1) NOT NULL
losal number(4) NOT NULL
hisal number(4) NOT NULL

Insert following data:

(1, 700, 1200)

(2, 1201, 1400)

(3, 1401, 2000)

(4, 2001, 3000)

(5, 3001, 9999)

7. Create table Client_master

P G Department of Computer Science & Technology DBMS-PS01CINT54
Practical Manual

Name Type Constraints

clinent_no Varchar2(6) PK, First letter must start
with ‘C’
Name Varchar2(20) NOT NULL
Address1 Varchar2(30)
Address2 Varchar2(30)
City Varchar2(15)
State Varchar2(15)
Pincode Number(6)
BAL_DUE Number(10,2)

Insert Following data:

(C00001, Ivan bayross, bombay, 40054, Maharashtra, 15000)

(C00002, Monika Patel, Anand, 388001, Gujarat, 0)

(C00003, Promadu Jaguste, bombay, 40057, Maharashtra, 5000)

(C00004, Basu Navindgi, bombay, 40056, Maharashtra, 0)

(C00005, Ravi Shreedharan, Delhi, 10001, , 2000)

(C00006, Rukmini, bombay, 40050, Maharashtra, 0)

8. Create table product_master

Name Type Constraints

Product_no Varchar2(6) Primary key, Frist letter
must start with ‘P’
Description Varchar2(15) Not Null
Profit_Perecent Number(4,2) Not Null
Unit_Measure Varchar2(10) Not Null
Qty_on_hand Number(8) Not Null
Reorder_lvl Number(8) Not Null
Sell_price Number(8,2) Not Null, cannot be 0
Cost_price Number(8,2) Not Null, cannot be 0

Insert following data:

(P00001, 1.44 Floppies, 5, Piece, 100, 20, 525, 500)

(P03453, Monitors, 6, Piece, 10, 3, 12000, 11280)

(P06754, Mouse, 5, Piece, 20, 5, 1050, 1000)

(P07865, 1.22 Floppies, 5, Piece, 100, 20, 525, 500)

(P07868, Keyboards, 2, Piece, 10, 3, 3150, 3050)

P G Department of Computer Science & Technology DBMS-PS01CINT54
Practical Manual

(P07885, CD Drive, 2.5, Piece, 10, 3, 5250, 5100)

(P07965, S40 SSD, 4, Piece, 10,3, 10000, 9500)

(P07975, 1.44 Drive, 5, Piece, 10, 3, 1050, 1000)

(P08865, 1.22 Drive, 5, Piece, 2, 3, 1050, 1000)

9. Create table salesman_master

Name Type Constraints

Salesman_no Varchar2(6) Primary key, First letter
must start with ‘S’
Salesman_name Varchar2(20) NOT NULL
Address1 Varchar2(30) NOT NULL
Address2 Varchar2(30)
City Varchar(20)
Pincode Varchar2(6)
State Varchar2(20)
Sal_amt Number(8,2) Not Null, Cannot be 0
Tgt_to_get Number(6,2) Not Null, Cannot be 0
Ytd_sales Number(6,2) Not Null
Remarks Varchar2(60)

Insert Following Details:

(S00001, Kiran, A/14, Worli, Bombay, 400002, MAH, 3000, 100, 50, good)

(S00002, Manish, 65, Nariman, Bombay, 400001, MAH, 3000, 200, 100, good)

(S00003, Ravi, P-7, Bandra, Bombay, 400032, MAH, 3000, 200, 100, good)

(S00004, Ashish, A15, Juhu, Bombay, 400044, MAH, 3500, 200, 150, good)

10. Create table sales_order

Name Type Constraint

S_order_no Varchar2(6) Primary Key, First letter
must start with O
S_order_date Date
Client_no Varchar2(6) FK
Dely_addr Varchar2(25)
Salesman_no Varchar2(6) FK
Dely_type Char(1) P OR F , By default F
Billed_yn Char(1) Y OR N
P G Department of Computer Science & Technology DBMS-PS01CINT54
Practical Manual

Dely_date Date Canot be less than

s_order_date
Order_status Varchar2(10) In process, Fulfilled, back
order, cancelled

Insert following data:

(O19001, 12-JAN-96, C00001, address, F, N, S00001, 20-JAN-96, In process)

(O19002, 25-JAN-96, C00002, address, P, N, S00002, 27-JAN-96, Cancelled)

(O46865, 18-FEB-96, C00003, address, F, Y, S00003, 20-FEB-96, Fulfilled)

(O19003, 03-APR-96, C00001, address, F, Y, S00001, 07-APR-96, Fulfilled)

(O46866, 20-MAY-96, C00004, address, P, N, S00002, 22-MAY-96, Cancelled)

(O10008, 24-JAN-96, C00005, address, F, N, S00004, 26-MAY-90, In process)

11. Create table sales_order_details

Name Type Constraints

S_order_no Varchar2(6) PK and FK
Product_no Varchar2(6) PK and FK
Oty_ordered number(8)
Qty_disp number(8)
Product_rate Number(10,2)

Insert Following Data:

(O19001, P00001, 4, 4, 525)

(O19001, P07965, 2, 1, 8400)

(O19001, P07885, 2, 1, 5250)

(O19002, P00001, 10, 0, 525)

(O46865, P07868, 3, 3, 3150)

(O46865, P07885, 3, 1, 5250)

(O46865, P00001, 10, 10, 525)

(O46865, P03453, 4, 4, 1050)

(O19003, P03453, 2, 2, 1050)

(O19003, P06734, 1, 1, 12000)

(O46866, P07965, 1, 0, 8400)

P G Department of Computer Science & Technology DBMS-PS01CINT54
Practical Manual

(O46866, P07975, 1, 0, 1050)

(O10008, P00001, 10, 5, 525)

(O10008, P07975, 5, 3, 1050)

12. Create table challan_header

Name Type Constraint

Challan_no Varchar2(6) PK / FK
S_order_no Varchar2(6) FK
Challan_date Date Not null
Billed_YN Char(1) Y, N , Deault ‘N’

Insert Following Data

(CH9001, 019001, 12-DEC-95, Y)

(CH6865, 046865, 12-NOV-95, Y)

(CH3965, 010008, 12-OCT-95, Y)

13. Create table Challan_ddetails

Name Type Constraints

Challan_no Varchar2(6) PK and FK
Product_no Varchar2(6) PK and FK
Qty_disp Number(4,2) Not Null

Insert Following Details

(CH9001, P00001, 4)

(CH9001, P00001, 1)

(CH9001, P00001, 1)

(CH6865, P00001, 3)

(CH6865, P00001, 4)

(CH6865, P00001, 10)

(CH3965, P00001, 5)

(CH3965, P00001, 2)
P G Department of Computer Science & Technology DBMS-PS01CINT54
Practical Manual

Queries
• Single Table Retrieval: -
1. Find out the names of all clients.
2. print the entire client_master table.
3. Retrieve the list of names and the cities of all the clients.
4. List the various products available from the product_master.
5. Find the name of all clients having 'a' as the second letter in their names.
6. Find out the clients who stay in city whose second letter is 'a' .
7. Find the list of all clients who stay in bombay or city delhi or city madras.
8. List all the clients who are located in 'Bombay'.
9. Print the list of clients whose bal_due are greater than value 10000
10. Print the information from sales_order table of order placed in month of january.
11. Display order information for client_no 'c00001' and 'c00002' ;
12. Find the products with description as '1.44 drive' and '1.22 drive' .
13. Find the product whose selling price is more than 1500 and also find the new selling price
as original price * 15
14. Rename the new in the above query as new_price
15. Find the product whose cost price is less than 1500
16. List the product in sorted order of their description
17. Calculate the square root of price of each product.
18. Divide the cost of product '540 HDD' by difference between its price and 100.
19. List the names,city,state of clients not in the state of 'Maharashtra' .
20. List the product_no,description,sell_price of products whose description begin with letter
'M' .
21. List of all orders that were canceled in month of March.

• Table Updating: -
P G Department of Computer Science & Technology DBMS-PS01CINT54
Practical Manual

1. Change the s_order_date of cllient_no'C00001' to 24/07/96

2. Change the selling price of '1.44 Drive' to Rs. 1150.00
3. Delete the record with order no 'O19001' from the order table
4. Delete all the records having delivery date before 10-jul-96
5. Change the city of client_no 'C00005' to 'Bombay'
6. Change the delivery date of order no 'O10008' to 16-08-96
7. Change the bal_due of client_no 'C00001' to 1000
8. Change the cost price of '1.22 Floppy Drive' to Rs.950.00

• SET FUNCTIONS AND CONCATENATION: -

1. Count the total no. of orders.
2. Calculate the average price of all the products.
3. Calculate the minimum price of product.
4. Determine the maximum and minimum product price.Rename the title as max_price and
min_price respectively.
5. Count the number of product having price greater than or equal to 1500.
6. Find all products whose qty_on_hand is less than recorder level.
7. Print the information of client_master,product_master,sales_order table in the following
format for all the record.{cust_name}has placed order {order_no}on {s_order_date}.
• JOINS AND CORRELATION: -
1. Find out the product which has been sold to 'ivanbayroos'.
2. Find out the product and their quantities that will have to delivered in the current month.
3. Find the product_no and description of moving products.
4. Find the names of the clients who have purchased 'CD Drive'.
5. List the product_no and s_order_no of customers having qty_ordered less than 5 from the
order detail Table for the product '1.44 Floppies'.
6. Find the products and their quantities for the orders placed by 'VandanaSaitwal' and 'Ivan
Bayross'.
7. Find the products and their quantities for the orders placed by client_no'C00001' and
'C00002'
• NESTED SUBQUERIES: -
1. Find the product_no and description of non-moving products (eg.products not being
sold).
2. Find the customers name, address1, address2, city and pincode for the client who has
placed order no "O19001'.
3. Find the client name who have placed order before the month of may,96.
4. Find out if product "1.44 Drive" is ordered by any client and print client_no name to whom
it was sold.
5. Find the name of clients who have placed ordered worth RS. 10000 or more.