Joins and correlation.

1) find out the product which has been sold to 'ivan bayroos'.

select d.product_no,p.description from sales_order_details d , product_master p , client_master c,sales_order s
where p.product_no=d.product_no and s.s_order_no=d.s_order_no and c.client_no=s.client_no and c.name='Ivan Bayross';

2) find out the product and their quantities that will have to delivered in the current month.

select d.product_no,p.description,sum(d.qty_ordered)

from sales_order_details d,sales_order s,product_master p

where p.product_no=d.product_no and s.s_order_no=d.s_order_no and to_char(dely_date,'mon-yy')=to_char(sysdate,'mon-yy')

group by d.product_no,p.description;

3) find the product_no and description of moving products.

select distinct p.product_no,p.description from product_master p ,sales_order_details d

where p.product_no=d.product_no;

4) find the names of the clients who have purchased 'CD Drive'.

select distinct s.client_no,c.name from sales_order_details d,sales_order s,product_master p,client_master c

where p.product_no=d.product_no and s.s_order_no=d.s_order_no and c.client_no=s.client_no and p.description='CD Drive'

5) List the product_no and s_order_no of customers having qty_ordered less than 5 from the order detail Table for the product '1.44 Floppies'.

select d.product_no,d.s_order_no from sales_order_details d,sales_order s,product_master p

where s.s_order_no=d.s_order_no and p.product_no=d.product_no and d.qty_ordered<5 and p.description='1.44 Floppies';

6) Find the products and their quantities for the orders placed by 'Vandana Saitwal' and 'Ivan Bayross'.

select d.product_no,p.description,sum(qty_ordered)"Qty Ordered"

from sales_order_details d,sales_order s,product_master p,client_master c 

where s.s_order_no=d.s_order_no and p.product_no=d.product_no and c.client_no=s.client_no

and (c.name='Ivan Bayross' or c.name='Vandana Saitwal')

group by d.product_no,p.description;

7) Find the products and their quantities for the orders placed by client_no'C00001' and 'C00002'.

select s.client_no,d.product_no,p.description ,sum(qty_ordered)"Qty_ordered"

from sales_order s,sales_order_details d,product_master p,client_master c

where s.s_order_no=d.s_order_no and d.product_no=p.product_no and s.client_no=c.client_no

group by s.client_no,d.product_no,p.description

having s.client_no='C00001' or s.client_no='C00002';

Having and Group By

1) Print the description and  total qty sold for each product.

select s.product_no,p.description, sum(s.qty_ordered) from sales_order_details s,product_master p
where p.product_no=s.product_no
group by s.product_no,p.description;

2) Find the value of each product sold.

select  s.product_no,p.description,sum(s.qty_disp*s.product_rate) "Sales Per Product" from

sales_order_details s,product_master p where p.product_no=s.product_no

group by s.product_no,p.description;

3) Calculate the avarage qty sold for each client that has a maximum order value of 15000.00

select c.client_no,c.name,avg(s.qty_disp) "Avg. Sales" from sales_order_details s ,sales_order so,client_master c

where c.client_no=so.client_no and so.s_order_no=s.s_order_no

group by c.client_no,c.name having max(s.qty_ordered*s.product_rate)>15000;

4) 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.

select s.s_order_no,s.s_order_date,sum(so.qty_ordered*so.product_rate)"Order Billed",sum(so.qty_disp*so.product_rate) "Total Amount" from sales_order s, sales_order_details so

 where so.s_order_no=s.s_order_no and s.billed_yn='Y' and to_char(s_order_date,'mon')='jan'

 group by s.s_order_no,s.s_order_date;

5) Print the information of product_master,order_detail table in the following format for all the record:-{description}worth RS{total sales for the products} was sold.

select p.description||' Worth Rs'||sum(d.qty_disp*d.product_rate) from product_master p, sales_order_details d

where p.product_no=d.product_no group by p.description; 

6) 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 ordered in the month of (s_order_date in the month format}.

select p.description||' Worth Rs'||sum(d.qty_disp*d.product_rate)||' was ordered in the month of'||to_char(s_order_date,'month')"Description Total amount Month" from product_master p, sales_order_details d,sales_order s

where p.product_no=d.product_no and s.s_order_no=d.s_order_no group by p.description,s.s_order_date;