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';