Q.
1 Create the tables described below
Table Name : Client_Master
Column Name Data Type Size Attributes
Client_no Varchar2 6 Primary key / fist letter must start with ‘C’
Name Varchar2 20 NOT NULL
Address1 Varchar2 30
Address2 Varchar2 30
City Varchar2 15
Pincode Number 8
State Varchar2 15
Bal_due Number 10,2
Table Name : Product_Master
Column Name Data Type Size Attributes
Product_no Varchar2 6 Primary key / fist letter must start with ‘P’
Description Varchar2 15 NOT NULL
Profit_percent 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
Table Name : Salesman_Master
Column Name Data Type Size Attributes
Salesman_no Varchar2 6 Primary key / fist letter must start with ‘S’
Salesman_Name Varchar2 20 NOT NULL
Address1 Varchar2 30 NOT NULL
Address2 Varchar2 30
City Varchar2 15
Pincode Number 8
State Varchar2 15
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
Table Name : Sales_order
Column Name Data Type Size Attributes
Order_no Varchar2 6 Primary key / fist letter must start with ‘O’
Order_date Date
Client_no Varchar2 6 Foreign Key references client_no of client_master
table
Salesman_no Varchar2 6 Foreign Key references salesman_no of
salesman_master table
Dely_type Char 1 Delivery : Part (P) / Full )F) Default ‘F’
Billed_yn Char 1
Dely_date Date Cannot be less than order_date
Order_status Varchar2 10 Values ( ‘In process’, ‘Fulfilled’,
‘BackOrder’,’Cancelled’)
Table Name : Sales_order_details
Column Name Data Type Size Attributes
Order_no Varchar2 6 Primary key / Foreign Key references Order_no of
the sales_order table
Product_no Varchar2 6 Primary key / Foreign Key references product_no of
the Product_master table
Qty_orderd Number 8
Qty_disp Number 8
Product_rate Number 10,2
Q.2. Write SQL queries for the following
1. Find out the clients who stay in a city whose second letter is ‘a’.
2. Find products whose selling price is greater than 2000 and less than or equal to
5000.
3. Find the number of days elapsed between today’s date and the delivery date of the
orders placed by the clients.
4. Find out the value of each product sold.
5. Count the number of products having price greater than or equal to 1500
6. Find the names of clients who have purchased ‘CD Drive’ [Joins]
7. Find the product_no and description of constantly sold i.e. rapidly moving
products [Joins]
8. Find the product_no and description of non-moving products i.e. products not
being sold [Subquery]
9. Find the names of clients who have placed orders worth Rs. 10000 or more.
[Subquery]
10. Find out if the product ‘1.44 Drive has been ordered by any client and print the
client_no, name to whom it was sold. [Subquery]
11. Find all the products whose qty_on_hand is less than reorder level
12. List the names, city and state of clients who are not in the state of ‘Maharashtra’.
13. Create view salesview which includes column order_no, order_date, product_no,
qty_ordered, qty_disp.
14. Retrieve the names of all the clients and saleman in the city of ‘Mumbai from the
tables client_master and salesman_master.
15. Retrieve all the product numbers of non-moving items form the product_master
table.