ASSIGNMENT-2
Perform the tables created previously generate the SQL statements for the operations mentioned below:
CREATE FOLLOWING TABLES:
1. CLIENT MASTER
COLUMN NAME DATA TYPE SIZE DEFAULT ATTRIBUTES
CLIENTNO. VARCHAR2 6 Primary Key/first 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
BALDUE NUMBER 10,2
2. PRODUCT MASTER
COLUMN NAME DATA TYPE SIZE DEFAULT ATTRIBUTES
PRODUCTNO VARCHAR2 6 Primary Key/first letter must start with ‘P’
DESCRIPTION VARCHAR2 15 Not Null
PROFITPERCENT NUMBER 4,2 Not Null
UNITMASTER VARCHAR2 10 Not Null
QTYONHAND NUMBER 8 Not Null
REORDERLVL NUMBER 8 Not Null
SELLPRICE NUMBER 8,2 Not Null, Cannot be 0
COSTPRICE NUMBER 8,2 Not Null, Cannot be 0
3. SALESMANMASTER
COLUMN NAME DATA TYPE SIZE DEFAULT ATTRIBUTES
SALESMANNO VARCHAR2 6 Primary Key/first letter must start with ‘S’
SALESMANNAME VARCHAR2 20 Not Null
ADDRESS1 VARCHAR2 30 Not Null
ADDRESS2 VARCHAR2 30
CITY VARCHAR2 20
PINCODE NUMBER 8
STATE VARCHAR2 20
SALAMT NUMBER 8,2 Not Null, Cannot be 0
TGTTOGET NUMBER 6,2 Not Null, Cannot be 0
YTDSALES NUMBER 6,2 Not Null, Cannot be 0
REMARKS VARCHAR2 60
4. Sales_Order
COLUMN NAME DATA TYPE SIZE DEFAULT ATTRIBUTES
ORDERNO VARCHAR2 6 Primary key/ first letter must start with ‘O’
CLIENTNO VARCHAR2 6 Foreign key references ClientNo of ClientMaster table
ORDERDATE DATE Not null
DELYADDR VARCHAR2 25
SALESMANNO VARCHAR2 6 Foreign key references SalesmanNo of SalesmanMaster
DELYTYPE CHAR 1 F Delivery: part(P)/ full(F)
BIILYN CHAR 1
DELYDATE DATE Cannot be less than Order_date
ORDERSTATUS VARCHAR2 10 Values(‘In Process’,’Fullfilled’,’BackOrder’,’Cancelled’)
5. Sales_Order_Details
COLUMN NAME DATA TYPE SIZE DEFAULT ATTRIBUTES
ORDERNO VARCHAR2 6 Foreign key references OrderNo of sales_order table
PRODUCTNO VARCHAR2 6 Foreign key references ProductNo of Productmaster tab
QTYORDERED NUMBER 8
QTYDISP NUMBER 8
PRODUCTRATE NUMBER 10,2
INSERTING THE FOLLOWING DATA INTO THEIR RESPECTIVE TABLES:
1. Data for client master table:
ClentnNo Name City Pincode State BalDue
C00001 Rakesh Joshi Mumbai 400054 Maharashtra 15000
C00002 Mayur Patel Madras 780001 Tamilnadu 0
C00003 Ishita Mehta Mumbai 400057 Maharashtra 5000
C00004 Amit Solanki Bangalore 560001 Karnataka 0
C00005 Hiren Pandya Mumbai 400060 Maharashtra 2000
C00006 Dipak Sharma Mangalore 560050 Karnataka 0
2. Data for product master table
ProductNo Description Profit Unit QtyOn RecordLvl SellPrice CostPrice
Percent Master Hand
P00001 T-Shirts 5 Piece 200 50 350 250
P0345 Shirts 6 Piece 150 50 500 350
P06734 Cotton Jeans 5 Piece 100 20 600 450
P07865 Jeans 5 Piece 100 20 750 500
P07868 Trousers 2 Piece 150 50 850 550
P07885 Pull Overs 2.5 Piece 80 30 700 450
P07965 Denim Shirts 4 Piece 100 40 350 250
P07975 Lycra Tops 5 Piece 70 30 300 175
P08865 Skirts 5 Piece 75 30 450 300
3. Data for SALESMAN MASTER table:
SalesmanNo Name Address1 Address2 City Pincode State SalAmt TgtToGet YtdSales Remarks
S00001 Aman A/14 Worli Mumbai 400002 Maharashtra 3000 100 50 Good
S00002 Omkar 65 Nariman Mumbai 400001 Maharashtra 3000 200 100 Good
S00003 Raj P-7 Bandra Mumbai 400032 Maharashtra 3000 200 100 Good
S00004 Ashish A/5 Juhu Mumbai 400044 Maharashtra 3000 200 100 Good
4. Data for Sales_Order table:
OrderNo ClientNo OrderDate SalesmanNo DelyType BillYN DelyDate OrderStatus
O19001 C00001 12-June-04 S00001 F N 20-July-04 In Process
O19002 C00002 25-June-04 S00002 P N 27-June-04 Cancelled
O46865 C00003 18-Feb-04 S00003 F Y 20-Feb-04 Fulfilled
O19003 C00004 03-Apr-04 S00001 F Y 07-Apr-02 Fulfilled
O46866 C00005 20-May-04 S00002 P N 22-May-04 Cancelled
O19008 C00006 24-May-04 S00004 F N 26-July-04 Fulfilled
5. Data for Sales_order_details.
OrderNo ProductNo QtyOrdered QtyDisp Productrate
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 P0345 4 4 1050
O19003 P03453 2 2 1050
O19003 P06734 1 1 12000
O46866 P07965 1 0 8400
O46866 P07975 1 0 1050
O19008 P00001 10 5 525
O19008 P07975 5 3 1050
[A] Perform the following computations on table data:
1. List the names of all clients having ‘a’ as the second letter in their names.
2. List the clients who stay in a city whose First letter is ‘M’.
3. List all the clients who stay in ‘Bangalore’ or ‘Mangalore’.
4. List all clients whose BalDue is greater then value 10000.
5. List all information from the Sales_Order table for orders placed in the month of June.
6. List the order information for ClientNo. ‘C00001’ and ‘C00002’.
7. List products whose selling price is greater than 500 and less than or equal to 750.
8. List product whose selling price is more than 500.
9. Calculate a new selling price as, original selling price *0.15.
10. Rename the new column in the output of the above query as new_price.
11. List the names, city and state of clients who are not in the state of ‘Maharashtra’.
12. Calculate the average price of all the products.
13. Determine the maximum and minimum product prices. Rename the output as max_price and min_price respectively.
14. Count the number of products having price less than or equal to 500.
15. List all the products whose QtyOnHand is less than reorder level.
[B] Exercise on Date Manipulation:
1. List the order number and day on which clients placed their order.
2. List the month (in alphabets) and date when the orders must be delivered.
3. List the Order Date in the format ‘DD-Month-YY’. E.g. 20-November-02.
4. List the date, 15 days after today’s date.
[C] Exercise on using Having and Group By Clauses:
1. Print the description and total qty sold for each product.
2. Find the values of each product sold.
3. Calculate the average qty sold for each client that has maximum order value of 15000.00.
4. Find out the total of all the billed orders for the month of June.
[D] Exercise on Joins and Correlation:
1. Find out the products, which have been sold to ‘Rakesh Joshi’.
2. Find out the products and their quantities that will have to be delivered in the current month.
3. List the ProductNo and description of constantly sold (i.e. rapidly moving) products.
4. Find the names of clients who have purchased ‘Trousers’.
5. List the products and orders from customers who have ordered less than 5 units of ‘Pull Overs’.
6. Find the products and their quantities for the orders placed by ‘Rakesh Joshi’ and ‘Mayur Patel’.
7. Find the products and their quantities for the orders placed by ClientNo ‘C00001’ and ‘C00002’.
[E] Exercise on Sub-queries:
1. Find the ProductNo and description of non-moving products i.e. products not being sold.
2. List the CustomerName, Address1, Address2, City and Pin Code for the client who has placed order no ‘O19001’.
3. List the client names that have placed orders before the month of May-04
4. List if the product ‘Lycra Top’ has been ordered by any client and print the Client No., Name to whom it was sold.
5. List the names of clients who have placed orders worth Rs. 10000 or more.
