Question

1.Write an SQL query that retrieves all pairs of suppliers who supply the same product, along with their product purchase price if applicable.

2.Create a view SUPPLIEROVERVIEW that retrieves, for each supplier, the supplier number, the supplier name, and the total amount of quantities ordered. Once created, query this view to retrieve suppliers for whom the total ordered quantity exceeds 30.

3.Write a nested SQL query to retrieve all purchase order numbers of purchase orders that contain either sparkling or red wine.

Answer 1

1. Write an SQL query that retrieves all pairs of suppliers who supply the same product, along with their product purchase price if applicable.

select sr.supnr as "Supplier No.",sr.supname as "Supplier Name", p.prodnr as "Product No.", p.prodname as "Product Name",s.purchase_price as "Purchase Price" from supplier as sr,supplies as s,product as p where sr.supnr=s.supnr and s.prodnr=p.prodnr and p.prodnr in(select s1.prodnr from supplies as s1 group by s1.prodnr having COUNT(s1.supnr) > 1)

2. Create a view SUPPLIEROVERVIEW that retrieves, for each supplier, the supplier number, the supplier name, and the total amount of quantities ordered. Once created, query this view to retrieve suppliers for whom the total ordered quantity exceeds 30.

create view SUPPLIEROVERVIEW as select sr.supnr as "supplier_no",sr.supname as "supplier_name",sum(pol.quantity) as "total_quantity" from supplier as sr,supplies as s,product as p,po_line as pol where sr.supnr=s.supnr and s.prodnr=p.prodnr and p.prodnr=pol.prodnr group by sr.supnr;

select supplier_no,supplier_name from SUPPLIEROVERVIEW where total_quantity > 30

3. Write a nested SQL query to retrieve all purchase order numbers of purchase orders that contain either sparkling or red wine.

select po.ponr as "Purchase Order No." from purchase_order as po,po_line as pol,product as p where p.prodnr=pol.prodnr and pol.ponr=po.ponr and p.prodnr in(select pr.prodnr from product as pr where pr.prodname in('sparkling','red wine'))