TABLE 1
Code for creating table:
create table client_master ( Client_No varchar(10) not null primary key,
Name varchar(25),
City varchar(20),
Pin_code int,
State varchar(20),
Bal_Due int );
insert into client_master ( Client_No , Name , City , Pin_code , State ,Bal_Due )
values ("C00001", "Ivan Bayross", "Bombay", 400054, "Maharashtra", 15000);
insert into client_master ( Client_No , Name , City , Pin_code , State ,Bal_Due )
values ("C00002", "Vandana Saitwal", "Madras", 780001, "Tamil Nadu", 0);
insert into client_master ( Client_No , Name , City , Pin_code , State ,Bal_Due )
values ("C00003", "Pramada Jaguste", "Bombay", 400057, "Maharashtra", 5000);
insert into client_master ( Client_No , Name , City , Pin_code , State ,Bal_Due )
values ("C00004", "Basu Navindgi", "Bombay", 400056, "Maharashtra", 0);
insert into client_master ( Client_No , Name , City , Pin_code , Bal_Due )
values ("C00005", "Ravi Sreedharan", "Delhi", 100001, 2000);
insert into client_master ( Client_No , Name , City , Pin_code , State ,Bal_Due )
values ("C00006", "Rukmini", "Bombay", 400050, "Maharashtra", 0);
Output:
TABLE 2
Code for creating table 2:
create table product_master ( Product_No varchar(10) not null primary key,
Description varchar(25),
Profit_Percent float,
UOM varchar(25),
Qty_on_hand int,
Reorder_Level int,
Sell_Price int,
Cost_Price int );
insert into Product_master (Product_No, Description, Profit_Percent, UOM, Qty_on_hand,
Reorder_Level, Sell_Price, Cost_price)
values ("P00001", "1.44 Floppies", 5, "Piece", 100, 20, 525, 500);
insert into Product_master (Product_No, Description, Profit_Percent, UOM, Qty_on_hand,
Reorder_Level, Sell_Price, Cost_price)
values ("P03453", "Monitors", 6, "Piece", 10, 3, 12000, 11280);
insert into Product_master (Product_No, Description, Profit_Percent, UOM, Qty_on_hand,
Reorder_Level, Sell_Price, Cost_price)
values ("P06734", "Mouse", 5, "Piece", 20, 5, 1050, 1000);
insert into Product_master (Product_No, Description, Profit_Percent, UOM, Qty_on_hand,
Reorder_Level, Sell_Price, Cost_price)
values ("P07865", "1.22 Floppies", 5, "Piece", 100, 20, 525, 500);
insert into Product_master (Product_No, Description, Profit_Percent, UOM, Qty_on_hand,
Reorder_Level, Sell_Price, Cost_price)
values ("P07868", "Keyboards", 2, "Piece", 10, 3, 3150, 3050);
insert into Product_master (Product_No, Description, Profit_Percent, UOM, Qty_on_hand,
Reorder_Level, Sell_Price, Cost_price)
values ("P07885", "CD Drive", 2.5, "Piece", 10, 3, 5250, 5100);
insert into Product_master (Product_No, Description, Profit_Percent, UOM, Qty_on_hand,
Reorder_Level, Sell_Price, Cost_price)
values ("P07965", "540 HDD", 4, "Piece", 10, 3, 8400, 8000);
insert into Product_master (Product_No, Description, Profit_Percent, UOM, Qty_on_hand,
Reorder_Level, Sell_Price, Cost_price)
values ("P07975", "1.44 Drive", 5, "Piece", 10, 3, 1050, 1000);
insert into Product_master (Product_No, Description, Profit_Percent, UOM, Qty_on_hand,
Reorder_Level, Sell_Price, Cost_price)
values ("P08865", "1.22 Drive", 5, "Piece", 2, 3, 1050, 1000);
Output:
TABLE 3
Code for creating table 3:
Create table salesman_master (Salesman_No varchar(10) not null primary key,Salesman_Name
varchar(10),Address_1 varchar(10),Address_2 varchar(10),City varchar(10),Pincode
number(10),State varchar(10),Salamt number(5),Tgt_To_Get number(5),Ytd_sales number(5),Remarks
varchar(5));
Insert into
salesman_master(Salesman_No,Salesman_Name,Address_1,Address_2,City,Pincode,State,Salamt,Tgt_T
o_Get,Ytd_sales,Remarks)
Values ("S00001","Kiran","A/14","Worli","Bombay",400002,"MAH",3000,100,50,"Good");
Insert into
salesman_master(Salesman_No,Salesman_Name,Address_1,Address_2,City,Pincode,State,Salamt,Tgt_T
o_Get,Ytd_sales,Remarks)
Values ("S00002","Manish","65","Nariman","Bombay",400001,"MAH",3000,200,100,"Good");
Insert into
salesman_master(Salesman_No,Salesman_Name,Address_1,Address_2,City,Pincode,State,Salamt,Tgt_T
o_Get,Ytd_sales,Remarks)
Values ("S00003","Ravi","P-7","Bandra","Bombay",400032,"MAH",3000,200,100,"Good");
Insert into
salesman_master(Salesman_No,Salesman_Name,Address_1,Address_2,City,Pincode,State,Salamt,Tgt_T
o_Get,Ytd_sales,Remarks)
Values ("S00004","Ashish","A/5","Juhu","Bombay",400044,"MAH",3500,200,150,"Good");
Output:
TABLE 4
Code for creating table 4:
create table sales_order( S_Order_No varchar(10) not null primary key, S_Order_Date varchar(30),
Client_No varchar(20), Dely_Type varchar(10), Bill_Yn varchar(10), Salesman_No varchar(20), Dely_Date
varchar(25), Order_Status varchar(10), foreign key (Client_No) references client_master(Client_No),
foreign key (Salesman_No) references salesman_master(Salesman_No));
insert into Sales_order( S_Order_No , S_Order_Date , Client_No , Dely_Type , Bill_Yn , Salesman_No ,
Dely_Date , Order_Status) values ( "O19001", "12-jan-96", "C00001", "F", "N", "S00001", "20-jan-96",
"IP");
insert into Sales_order( S_Order_No , S_Order_Date , Client_No , Dely_Type , Bill_Yn , Salesman_No ,
Dely_Date , Order_Status) values ( "O19002", "25-jan-96", "C00002", "P", "N", "S00002", "27-jan-96",
"C");
insert into Sales_order( S_Order_No , S_Order_Date , Client_No , Dely_Type , Bill_Yn , Salesman_No ,
Dely_Date , Order_Status) values ( "O46865", "18-feb-96", "C00003", "F", "Y", "S00003", "20-feb-96",
"F");
insert into Sales_order( S_Order_No , S_Order_Date , Client_No , Dely_Type , Bill_Yn , Salesman_No ,
Dely_Date , Order_Status) values ( "O19003", "03-apr-96", "C00001", "F", "Y", "S00001", "07-apr-96",
"F");
insert into Sales_order( S_Order_No , S_Order_Date , Client_No , Dely_Type , Bill_Yn , Salesman_No ,
Dely_Date , Order_Status) values ( "O46866", "20-may-96", "C00004", "P", "N", "S00002", "22-may-96",
"C");
insert into Sales_order( S_Order_No , S_Order_Date , Client_No , Dely_Type , Bill_Yn , Salesman_No ,
Dely_Date , Order_Status) values ( "O10008", "24-may-96", "C00005", "F", "N", "S00004", "26-may-96",
"IP");
Output:
TABLE 5
Code for creating table 5:
create table sales_order_details(S_Order_No varchar(10), Product_No varchar(10), Qty_Ordered int,
Qty_Disp int, Product_Rate int, foreign key (S_Order_No) references sales_order(S_Order_No),
foreign key (Product_No) references product_master(product_No));
insert into sales_order_details(S_Order_No , Product_No , Qty_Ordered , Qty_Disp , Product_Rate )
values ("O19001", "P00001", 4 , 4, 525 );
insert into sales_order_details(S_Order_No , Product_No , Qty_Ordered , Qty_Disp , Product_Rate )
values ("O19001", "P07965", 2 , 1 , 8400 );
insert into sales_order_details(S_Order_No , Product_No , Qty_Ordered , Qty_Disp , Product_Rate )
values ("O19001", "P07885", 2 ,1 , 5250 );
insert into sales_order_details(S_Order_No , Product_No , Qty_Ordered , Qty_Disp , Product_Rate )
values ("O19002", "P00001", 10 , 0 , 525 );
insert into sales_order_details(S_Order_No , Product_No , Qty_Ordered , Qty_Disp , Product_Rate )
values ("O46865", "P07868", 3 , 3 , 3150 );
insert into sales_order_details(S_Order_No , Product_No , Qty_Ordered , Qty_Disp , Product_Rate )
values ("O46865", "P07885", 3 ,1 , 5250 );
insert into sales_order_details(S_Order_No , Product_No , Qty_Ordered , Qty_Disp , Product_Rate )
values ("O46865", "P00001",10 ,10 , 525 );
insert into sales_order_details(S_Order_No , Product_No , Qty_Ordered , Qty_Disp , Product_Rate )
values ("O46865", "P03453", 4 ,4 , 1050 );
insert into sales_order_details(S_Order_No , Product_No , Qty_Ordered , Qty_Disp , Product_Rate )
values ("O19003", "P03453", 2, 2, 1050 );
insert into sales_order_details(S_Order_No , Product_No , Qty_Ordered , Qty_Disp , Product_Rate )
values ("O19003", "P06734", 1, 1 , 12000 );
insert into sales_order_details(S_Order_No , Product_No , Qty_Ordered , Qty_Disp , Product_Rate )
values ("O46866", "P07965", 1, 0 , 8400 );
insert into sales_order_details(S_Order_No , Product_No , Qty_Ordered , Qty_Disp , Product_Rate )
values ("O46866", "P07975", 1, 0 , 1050 );
insert into sales_order_details(S_Order_No , Product_No , Qty_Ordered , Qty_Disp , Product_Rate )
values ("O10008", "P00001", 10 , 5 , 525 );
insert into sales_order_details(S_Order_No , Product_No , Qty_Ordered , Qty_Disp , Product_Rate )
values ("O10008", "P07975", 5, 3 , 1050 );
Output:
TABLE 6
Code for creating table 6:
create table challan_header(Challan_No varchar(6) not null primary key, S_Order_No varchar(6),
Challan_Date varchar(10), Billed varchar(1), foreign key (S_Order_No) references sales_order
(S_Order_No));
insert into challan_header(Challan_No , S_Order_No , Challan_Date , Billed )
values ("CH9001", "O19001", "12-dec-95", "Y");
insert into challan_header(Challan_No , S_Order_No , Challan_Date , Billed )
values ("CH6865", "O46865", "12-nov-95", "Y");
insert into challan_header(Challan_No , S_Order_No , Challan_Date , Billed )
values ("CH3965", "O10008", "12-oct-95", "Y");
Output:
TABLE 7
Code for creating table 7:
create table challan_details(Challan_No varchar(6), Product_No varchar(6), Qty_Disp int,
foreign key (Challan_No) references challan_header(Challan_No),
foreign key (Product_No) references product_master(Product_No));
insert into challan_details(Challan_No,Product_No,Qty_Disp ) values("CH9001", "P00001", 4);
insert into challan_details(Challan_No,Product_No,Qty_Disp ) values("CH9001", "P07965", 1);
insert into challan_details(Challan_No,Product_No,Qty_Disp ) values("CH9001", "P07885", 1);
insert into challan_details(Challan_No,Product_No,Qty_Disp ) values("CH6865", "P07868", 3);
insert into challan_details(Challan_No,Product_No,Qty_Disp ) values("CH6865", "P03453", 4);
insert into challan_details(Challan_No,Product_No,Qty_Disp ) values("CH6865", "P00001", 10);
insert into challan_details(Challan_No,Product_No,Qty_Disp ) values("CH3965", "P00001", 5);
insert into challan_details(Challan_No,Product_No,Qty_Disp ) values("CH3965", "P07975", 2);
Output:
Single Table Retrieval:
Question 1: Find out names of all the clients.
Code:
select Name from Client_master;
Question 2: Print the entire client_master table.
Code:
select * from Client_master;
Question 3: Retrieve the list of names and the cities of all the clients.
Code:
select Name, City from Client_master;
Question 4: List the various products available from the product_master table.
Code:
select Description from Product_master;
Question 5: Find the names of all clients having ‘a’ as the second letter in their names.
Code:
select Name from Client_master where Name like '_a%';
Question 6: Find out the clients who stay in a city whose second letter is ’a’.
Code:
select Name from Client_master where City like '_a%';
Question 7: Find the list of all clients who stay in city ‘Bombay’ or city ‘Delhi’ or city ‘Madras’.
Code:
select Name from Client_master where City = "Bombay" or City = "Delhi" or City = "Madras";
Question 8: List all the clients who are located in Bombay.
Code:
select Name from Client_master where City = "Bombay";
Question 9: Print the list of clients whose bal_due are greater than value 10000.
Code:
select Name from Client_master where Bal_Due >= 10000;
Question 10: Print the information from sales_order table of orders placed in the month of January.
Code:
select * from sales_order where month(S_Order_Date) = 01;
Question 11: Display the order information for client_no 'C00001' and 'C00002.
Code:
select * from Sales_order where Client_No in ('C00001', 'C00002');
Question 12: Find the products with description as 1.44 Drive' and'1.22 Drive.
Code:
Select Product_No, Description from Product_master where Description in('1.44 Drive', '1.22 Drive');
Question 13: Find the products whose selling price is greater than 2000 and less than or equal to 5000
Code:
select Product_No , Description from Product_master where Sell_Price > 2000 and Sell_Price <= 5000;
Question 14: Find the products whose price is more than 1500 and also find the new selling price as
original selling price * 15.
Code:
select Product_No , Description, (Sell_Price * 15) New_sell_price from Product_master where Sell_Price
> 1500;
Question 15: Rename the new column in the above query as new_price.
Code:
select Product_No , Description, (Sell_Price * 15) New_price from Product_master where Sell_Price >
1500;
Question 16: Find the products whose cost price is less than 1500.
Code
select Product_No, Description from Product_master where Cost_price < 1500;
Question 17: List the products in sorted order of their description.
Code:
select Product_No, Description from Product_master order by Description;
Question 18: Calculate the square root of the price of each product.
Code
select Product_No, sqrt(Sell_Price) square_root_price from Product_master;
Question 19: Divide the cost of product ‘540 HDD’ by difference between its price and 100.
Code
select (sell_price - cost_price)/100 from product_master where description = '540 HDD';
Question 20: List the name, city and state of clients not in the state of ‘Maharashtra’.
Code
select name, city, state from client_master where state not in ('Maharashtra');
Question 21: List the product_no, description, sell_price of products whose description begin with
letter ‘M’.
Code
select product_no, description, sell_price from product_master where description like 'M%';
Question 22: List all the orders that were canceled in the month of March.
Code
select * from sales_order where order_status = 'C';
Set Functions and Concatenation:
Question 23: Count the total number of orders.
Code:
select count(S_Order_No ) from sales_order;
Questino 24: Calculate the average price of all the products
Code:
select avg(Product_rate) from sales_order_details;
Question 25: Calculate the minimum price of products.
Code:
select min(Product_rate) from sales_order_details;
Question 26: Determine the maximum and minimum product prices. Rename the title as max_price
and min_price respectively.
Code:
select max(Product_Rate) max_price, min(Product_Rate) min_price from sales_order_details;
Question 27: Count the number of products having price greater than or equal to 1500
Code:
select count(Sell_Price) from product_master where Sell_Price >= 1500;
Question 28: Find all the products whose qty_on_hand is less than reorder level.
Code:
select Product_No, Description from Product_master where Qty_on_hand < Reorder_Level;
Question 29: Print the information of client_master, product_master, sales_order table in the
following format for all the records {cust_name} has placed order {order_no} on {s_order_date}.
Code:
select concat(cm.Name, ' has placed order ', so.S_Order_NO, ' on ', so.S_Order_Date) information
from sales_order so, client_master cm
where cm.Client_No = so.Client_No;
Having and Group By:
Question 30: Print the description and total qty sold for each product.
Code:
select Description, sum(Qty_Ordered) from product_master as pm, sales_order_details as sod where
pm.Product_No = sod.Product_No group by pm.Product_No;
Queston 31: Find the value of each product sold.
Code:
select Distinct Product_No, Product_Rate from sales_order_details;
Question 32: Calculate the average qty sold for each client that has a maximum order value of
15000.00.
Code:
select so.Client_No, avg(sod.Qty_Disp)
from sales_order as so, sales_order_details as sod
where so.S_Order_No = sod.S_Order_No
group by Client_No having max(sod.Product_Rate * sod.Qty_Disp);
Question 33: Find out the total sales amount receivable for the month of jan. It will be the sum total
of all the billed orders for the month
Code:
select sum(sod.Product_Rate * sod.Qty_Ordered) as total_amount
from sales_order as so, sales_order_details as sod
where so.S_Order_Date like '%jan%' and so.S_Order_No = sod.S_Order_No;
Question 34: Print the information of product_master, order_detail table in the following format for
all the records:- {description} worth Rs. {total sales for the product} was sold.
Code:
select concat(pm.Description, ' worth Rs. ', sum(sod.Qty_Disp * sod.Product_Rate), ' was sold. ' ) info
from product_master as pm, sales_order_details as sod
where pm.Product_No = sod.Product_No
group by pm.Product_No;
Joins and Correlations:
Question 36: Find out the products which has been sold to ‘Ivan Bayross’.
Code:
select cm.name name, so.S_Order_No, sod.Product_No
from client_master as cm, sales_order as so, sales_order_details as sod
where cm.name = "Ivan Bayross" and cm.Client_No = so.Client_No and so.S_Order_No =
sod.S_Order_No;
Question 37: Find out the products and their quantities that will have to delivered in the current
month.
Code:
select sod.Product_No, sum(sod.Qty_Ordered)
from sales_order so, sales_order_details sod
where so.S_Order_No = sod.S_Order_No and month(so.Dely_Date) = 01
group by sod.Product_No;
Question 38: Find the productno and description of moving products
Code:
select sod.Product_No, pm.Description
from product_master pm, sales_order_details sod
where sod.Product_No = pm.Product_No
group by sod.Product_No having sum(sod.Qty_Disp)>0;
Question 39: Find the names of clients who have purchased 'CD Drive.
Code:
select cm.Name, pm.Description
from client_master cm, product_master pm, sales_order so, sales_order_details sod
where pm.Description = 'CD Drive' and pm.Product_No = sod.Product_No and so.S_Order_No =
sod.S_Order_No and cm.Client_No = so.Client_No;
Question 40: List the product no and s Order no of customers having qty ordered less than 5 from the
order details Table lor the product '1.44 Floppies’.
Code:
select so.Client_No, pm.Product_No, sod.S_Order_No
from product_master pm, sales_order_details sod, sales_order so
where sod.Qty_Ordered < 5 and pm.Product_No = sod.Product_No and so.S_Order_No =
sod.S_Order_No;
Question 41: . Find the products and their quantities for the orders placed by 'Vandana Saitwal’ and
‘Ivan Bayross'.
Code:
select cm.Name, sod.Product_No, sod.Qty_Disp
from sales_order_details sod, client_master cm, sales_order so
where cm.Name in ('Ivan Bayross','Vandana Saitwal') and cm.Client_No = so.Client_No and
so.S_Order_No = sod.S_Order_No;
Question 42: Find the products and their quantities for the orders placed by client_no 'C00001' and
‘CO0002'
Code:
select cm.Client_No, sod.Product_No, sod.Qty_Disp
from sales_order_details sod, client_master cm, sales_order so
where cm.Client_No in ('C00001','C00002') and cm.Client_No = so.Client_No and so.S_Order_No =
sod.S_Order_No;
Nested Queries:
Question 43: Find the product_no and description of non-moving products.
Code
select Product_No, Description from product_master
where Product_No in (select distinct Product_No from sales_order_details where (Qty_Ordered -
Qty_Disp) = 0);
Question 45: Find the client names who have placed orders before the month of May, 96.
Code:
select Name from client_master
where Client_No in (select distinct Client_No from sales_order where month(S_Order_Date) < 5);
Question 46: Find out if product '1.44 Drive' is ordered by any client and print the client_no, name to
whom it is was sold.
Code
select Client_No, Name from client_master
where Client_No in (select distinct Client_No from sales_order where S_Order_No in
(select S_Order_No from sales_order_details where Product_No = 'P00001'));
Question 47: Find the names of clients who have placed orders worth Rs. 10000 or more.
Code
select Client_No, Name from client_master
where Client_No in (select distinct Client_No from sales_order where S_Order_No in
(select S_Order_No from sales_order_details group by S_Order_No having sum(Qty_Ordered *
Product_Rate)>=10000));
Queries using Date:
Question 48: Display the order number and day on which clients placed their order.
Code
select S_Order_No, day(S_Order_Date) from sales_order;
Question 49: Display the month (in alphabets) and date when the order must be delivered.
Code
select monthname(Dely_Date), Dely_Date from sales_order;
Question 52: Find the number of days elapsed between today's date and the delivery date of the
orders placed by the clients.
Code
select datediff(curdate(), Dely_Date) as days_diff from sales_order;
Table Updations:
Question 53: Change the s_order_date of client_no ‘C00001’ to 24/07/96.
Code
update sales_order set S_Order_Date = '1996-07-24' where Client_No = 'C00001';
select * from sales_order where Client_No = 'C00001';
Question 54: Change the selling price of"1.44 Floppy Drive' to Rs. 1150.00.
Code
update product_master set Sell_Price = '1150.00' where Product_No = 'P07965';
select * from product_master where Product_No = 'P07965';
Queston 55: Delete the record with order number 'O19001' from the order table.
Code
delete from sales_order where S_Order_No = 'O19001';
Question 57: Change the city of client_no 'C00005' to 'Bombay'.
Code:
update client_master set City = 'Bombay' where Client_No='C00005';
Questin 58: Change the delivery date of order number 'O10008' to 16-08-96.
Code:
update sales_order set Dely_Date = '1996-08-16' where S_Order_No = 'O10008';
SELECT * FROM SALES_ORDER WHERE S_Order_No = 'O10008';
QUESTON 59: Change the bal_due of client_no 'CO000l' to 1000.
Code:
update client_master set Bal_Due = 1000 where Client_No='C00001';
select * from client_master where Client_No='C00001';
Question 60: Change the cost price of'1.22 Floppy Drive' to Rs. 950.00.
Code:
update product_master set Cost_Price = 950 where Product_No = 'P07865';
select * from product_master where Product_No = 'P07865';
