p8

(1) Display all the employee name and the city where they work.

 select Emp_name , City from Employee

                                                                                                                                            PAGE NO.47

(2) Display the employee name and company’s name having salary more than 15000.

 select Emp_name,Cmp_name from Work where New_salary>15000;

(3) Find the average rating and age of all sailors.
 select avg(Rating),avg(Age) from


Sailors

(4) List various products available.
 select dscr from PAGE NO.48




Product

(5) Display the names of salesman who have salary more than 2850.
 select Name from Salesman where


Amt>2850
(6) Change the cost price of Trousers to 950
 update Product set Cost_price=950 where Dscr='Trousers'
 select Dscr,Cost_price from Product whereDSCR = ‘Trousers’

(7) List all the clients having “a” as a second character in their names.

 select Name from Client where Name like'_a%'
 PAGE NO.49

(8) List all the products whose QtyonHand is less than Reorderlvl.

select Dscr from Product where Qty<Reorder_lvl

(9) Print the description and total qty sold for each product.
 SELECT P.DSCR FROM Client C, Product P, Salesorder S, Salesorder_Details O WHERE C.CL_NO=S.CL_NO and S.OD_NO=O.OD_NO and O.PR_NO=P.PR_No and C.Name='Ivan
Bayross';

Q-10.) Find the names of all clients who have purchased Trousers.
 SELECT C.name FROM Client C, Product P, Salesorder S, Salesorder_Details O WHERE C.CL_NO=S.CL_NO and S.OD_NO=O.OD_NO and P.PR_NO=O.PR_No and P.D0SCR='Trousers' ;
 PAGE NO.50

Q-11.) Find the products and their quantities for the orders placed by client C00001 and C00002.
 SELECT distinct S.Cl_no,P.DSCR,sum(O.Qty_Order) Total_Qty_Order FROM Client C, Product P, Salesorder S, Salesorder_Details O WHERE P.Pr_no=O.Pr_no and
O.Od_no=S.Od_no and S.Cl_no='C00001' or S.Cl_no='C00002' group by P.DSCR,P.Pr_no,S.Cl_no;

Q-12.) List the client details who place order no. O19001.
 SELECT C.* FROM Client C, Salesorder S where C.Cl_no=S.Cl_no and S.Od_no='O19001';

Q-13.) List the name of clients who have placed orders worth Rs. 10000 or more.
 SELECT distinct C.name FROM Client C, Salesorder S, Salesorder_Details O WHERE C.Cl_no=S.Cl_no and S.Od_no=O.Od_no and C.Cl_no=S.Cl_no and (O.Qty_Order*O.Rate)>10000; PAGE NO.51

Q-14.) Find the total of Qty ordered for each Order.
 SELECT Od_no,sum(Qty_Order) FROM Salesorder_Details group by Od_no;

 (15) Find the total of Qty ordered for each Order.
• select Od_no, sum(Qty_order) from Salesorder_Details group by Od_no;