Practical - 01
Q - 1. Create the following tables:i) client_master
Columnname Datatype Size
=====================================
client_no varchar2 6
name varchar2 20
address1 varchar2 30
address2 varchar2 30
city varchar2 15
state varchar2 15
pincode number 6
bal_due number 10,2
ii) Product_master
Columnname Datatype Size
=====================================
Product_no varchar2 6
Description varchar2 30
Profit_percent number 3
Unit_measure varchar2 3
Qty_on_hand number 10,2
Reoder_lvl number 10,2
Sell_price number 10,2
Cost_price number 10,2
Q - 2- Insert the following data into their respective tables:
client_no name address1 address2 city state pincode bal_due
0001 Ivan Bombay Bombay Bombay Maharastra 40054 15000
0002 Vandana Madras Madras Madras Tamilnadu 78001 0
0003 Pramda Bombay Bombay Bombay Maharastra 40056 5000
0004 Basu Delhi Delhi Delhi Delhi 10001 2000
0005 Sarthak Mehsana Mehsana Mehsana Gujarat 384012 0
Product_no Description Profit_percent Unit_measure Qty_on_hand Reoder_lvl Sell_price Cost_price
P001 1.44 floppies 5 piece 100 20 525 500
P002 Monitors 6 piece 10 3 12000 11500
P003 Mouse 5 piece 20 5 1050 1000
P004 Keyboards 2.5 piece 10 3 3150 3000
P005 HDD 4 piece 10 3 8400 8000
Q - 3 On the above table and data, solve the following queries:
1. Find all the names of all the clients.
2. Display all the cities and names of all the clients.
3. List out all various products from the product table.
4. List out all uniques products from the product table.
5. Display all the information of clients whose state is Gujarat.
6. Find all the details for client no 0001 to 0003.
7. Display all the description about "1.44 floppies" and "HDD".
8. List out all the product which has sell price greater than 5000.
9. List out all the product which has profit percent between 3 to 5.
10. Display all the details whose city name is not Mehsana.
Q - 4 On the above table and data, solve the following queries:
1. Change the selling price of Mouse to Rs.1150.00
2. Delete the record with client 0001 from the client master table.
3. Change the city of client_no’0004’ to Bombay.
4. Change the bal_due of client_no 0001 to 1000.
5. Find the products which selling price is more than 1500 and also find the new selling price as original selling price * 15
6. Find out the clients who stay in a city whose second letter is a.
7. List the products in the sorted order of their description.
8. Count the total number of orders.
9. Calculate the average price of all the products.
10. Count the number of products having price greater than or equal to 1500.
Tags:
DBMS