Assignment-6

Q1: Create the tables described below:

Table Name: CLIENT_MASTER
Description: Used to store client information.
Column Name Data Type Size Default Attributes
CLIENTNO Varchar 6 Primary Key / first letter must start with ‘C’
NAME Varchar 20 Not Null
ADDRESS1 Varchar 30
ADDRESS2 Varchar 30
CITY Varchar 15
PINCODE Int 8
STATE Varchar 15
BALDUE Float 10,2

Table Name: PRODUCT_MASTER

Description: Used to store product information.
Column Name Data Type Size Default Attributes
PRODUCTNO Varchar 6 Primary Key / first letter must start with ‘P’
DESCRIPTION Varchar 15 Not Null
PROFITPERCENT Decimal 4.2 Not Null
UNITMEASURE Varchar 10 Not Null
QTYONHAND Int 8 Not Null
REORDERLVL Int 8 Not Null
SELLPRICE Decimal 8.2 Not Null, Cannot be 0
COSTPRICE Decimal 8.2 Not Null, Cannot be 0

Table Name: SALESMAN_MASTER

Description: Used to store salesman information working for the company.
Column Name Data Type Size Default Attributes
SALESMANNO Varchar 6 Primary Key / first letter must start with ‘S’
SALESMANNAME Varchar 20 Not Null
ADDRESS1 Varchar 30 Not Null
ADDRESS2 Varchar 30
CITY Varchar 20
PINCODE Int 8
STATE Varchar 20
SALAMT Float 8,2 Not Null, Cannot be 0
TOTTOGET Float 6,2 Not Null, Cannot be 0
YTDSALES Float 6,2 Not Null
REMARKS Varchar 60

Table Name: SALES_ORDER

Description: Used to store client’s orders.
Column Name Data Type Size Default Attributes
ORDERNO Varchar 6 Primary Key / first letter must start with ‘O’
CLIENTNO Varchar 6 Foreign Key references ClientNo of Client_Master table
ORDERDATE Date Not Null
DELYADDR Varchar 25
SALESMANNO Varchar 6 Foreign Key references SalesmanNo of Salesman_Master table
DELYTYPE Char 1 F Delivery: part (P) / full (F)
BILLYN Char 1
DELYDATE Date Cannot be less than Order_Date
ORDERSTATUS Varchar 10 Values (‘In Process’, ‘Fulfilled’, ‘BackOrder’, ‘Cancelled’)
Table Name: SALES_ORDER_DETAILS
Description: Used to store client’s orders with details of each product ordered.
Column Name Date Type Size Default Attributes
ORDERNO Varchar 6 Foreign Key reference OrderNo of Sales_Order table
PRODUCTNO Varchar 6 Foreign Key reference ProductNo of Product_Master
table
QTYORDERED Int 8
QTYDISP Int 8
PRODUCTRATE Float 10,2

2. Insert the following data into their respective tables:

a. Re-insert the data generated for tables CLIENT_MASTER, PRODUCT_MASTER and

SALESMAN_MASTER.

b. Data for Sales_Order table:

OrderNo ClientNo OrderDate SalesmanNo DelyType BillYN DelyDate OrderStatus
O19001 C00001 12-June-04 S00001 F N 20-July-02 In Process
O19002 C00002 25-June-04 S00002 P N 27-June-02 Cancelled
O46865 C00003 18-Feb-04 S00003 F Y 20-Feb-02 Fulfilled
019003 C00001 03-Apr-04 S00001 F Y 07-Apr-02 Fulfilled
O46866 C00004 20-May-04 S00002 P N 22-May-02 Cancelled
O19008 C00005 24-May-04 S00004 F N 26-July-02 In Process

c. Data for Sales_Order_Details table:

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
3. Using the tables created previously generate the SQL statements for the operations
mentioned below. The tables in user are as follows:
a. Client_Master
b. Product_Master
c. Salesman_Master
d. Sales_Order
e. Sales_Order_Details

i) Perform the following computations on table data:

a. List the names of all clients having ‘a’ as the second letter in their names.
b. List the clients who stay in a city whose First letter is ‘M’.
c. List all clients who stay in ‘Bangalore’ or ‘Mangalore’
d. List all clients whose BalDue is greater than value 10000.
e. List all information from the Sales_Order table for orders placed in the month of June.
f. List the order information for ClientNo ‘C00001’ and ‘C00002’.
g. List products whose selling price is greater than 500 and less than or equal to 750.
h. List products whose selling price is more than 500. Calculate a new selling price as, original
selling price*.15. Rename the new column in the output of the above query as new_price.
i. List the names, city and state of clients who are not in the state of ‘Maharashtra’.
j. Count the total Int of orders.
k. Calculate the average price of all the products.
l. Determine the maximum and minimum product prices. Rename the output as max_price and
mi9n_price respectively.
m. Count the Int of products having price less than or equal to 500.
n. List all the products whose QtyOnHand is less than recorder level.

ii) Exercise on Date Manipulation:

a. List the order Int and day on which clients placed their order.
b. List the month (in alphabets) and date when the orders must be delivered.
c. List the OrderDate in the format ‘DD-Month-YY’. E.g. 12-February-02.
d. List the date, 15 days after today’s date.
4. Exercises on using Having and Group By Clauses:
a. Print the description and total qty sold for each product.
b. Find the value of each product sold.
c. Calculate the average qty sold for each client that has a maximum order value of 15000.00.
d. Find out the total of all the billed orders for the month of June.

i) Exercise on Joins and Correction:

a. Find out the products, which have been sold to ‘Ivan Bayross’.
b. Find out the products and their quantities that will have to be delivered in the current month.
c. List the ProductNo. and description of constantly sold (i.e. rapidly moving) products.
d. Find the names of clients who have purchased ‘Trousers’.
e. List the products and orders from customers who have ordered less than 5 units of ‘Pull
Overs’.
f. Find the products and their quantities for the orders placed by ‘Ivan Bayross’ and ‘Mamta
Muzumdar’
g. Find the products and their quantities for the orders placed by ClientNo ‘C00001’ and
C00002’.

ii) Exercise on Sub-queries:

a. Find the ProductNo and description of non-moving products i.e. products not being sold.
b. List the customer Name, Address1, Address2, City and PinCode for the client who has
placed order no ‘O19001’.
c. List the client names that have placed orders before the month of May, 02.
d. List if the product ‘Lycra Top’ has been ordered by any client and print the Client_no, Name
to whom it was sold.
e. List the names of clients who have placed orders worth Rs. 10000 or more.