Question : Consider the following relations: Supplier(S#,sname,status,city) Parts(P#,pname,color,weight,city) -SP(S#,P#,quantity)

Answer the following simple queries in SQL.

                        a) Find name of supplier for city = “MUMBAI”.

                        Ans. select * from supplier where city=”MUMBAI”;

                        b) Find suppliers whose name start with “AD”

                        Ans. select * from suppliers where sname like AD%;

                        c) Find all suppliers whose status is 10, 20 or 30.

                        Ans. select * from suppliers where status in(10,20,30);

                        d) Find total number of city of all suppliers.

                        Ans. select count(city) from suppliers;

                        e) Find s# of supplier who supplies ‘BLUE’ part.

                        Ans.  select s# from sp where p# in (select p# from parts where color =”BLUE”);

                        f) Count number of supplier who supplies ‘BLUE’ part.

                        Ans. select count(*) from sp where p# in (select p# from parts where color =”blue”);

                        g) Sort the supplier table by sname.

                        Ans. select * from suppliers order by sname desc;

                        h) Delete records in supplier table whose status is 40.

                        Ans delete from suppliers where status =40;

i) Find name of parts whose color is ‘red’

Ans. select pname from parts where color=’red’;

j) Find parts name whose weight less than 10 kg.

Ans. select pname from  where weight<10;

k) Find all parts whose weight from 10 to 20 kg.

Ans. select pname from parts where weight between 10 and 20

l) Find average weight of all parts.

ans. select avg(weight) from suppliers;

m) Find S# of supplier who supply part ‘p2’

Ans.  select s# from sp where p#=(select p# from parts where pname=”p2”);

n) Find name of supplier who supply maximum parts.

Ans. select name from supplier where s#=(select s# from sp having max(quantity));

o) Sort the parts table by pname.

Ans. select * from parts order by pname desc;