Examples of SQL Queries

Question: Solve the following query using the below tables.

Tables:

1. Suppliers(Sid: integer, Sname: string, address: string)
2. Parts(Pid: integer, Pname: string, color: string)
3. Catalog(Sid: integer, Pid: integer, cost: real)

(1)               Find the name of suppliers who supply some red part.

Solution(1):      

            Select Sname from Suppliers where Sid in

            (Select Sid from Catalog Where Pid in

(Select Pid from Parts where lower(color)=’red’));

Solution(2):

Select S.Sname from Suppliers S, Catalog C, Parts P Where S.Sid = C.Sid and C.Pid = P.Pid and lower(P.color)=’red’;

(2)        Find the Sids of suppliers who supply some red or some green parts.

Solution(1):

            Select Sname from Suppliers where Sid in

            (Select Sid from Catalog Where Pid in

(Select Pid from Parts where lower(color)=’red’ or lower(color)=’green’));

Solution(2):

Select S.Sname from Suppliers S, Catalog C, Parts P Where S.Sid = C.Sid and C.Pid = P.Pid and (lower(P.color)=’red’ or lower(P.color)=’green’);

(3)        Find the Sids of suppliers who supply some red part or are at 220 Packer street.

Solution(1):

            Select Sname from Suppliers where Sid in

            (Select Sid from Catalog Where Pid in

(Select Pid from Parts where lower(color)=’red’)) or

lower(address)=lower(’220 Packer street’);

Solution(2):

Select S.Sname from Suppliers S, Catalog C, Parts P Where (S.Sid = C.Sid and C.Pid = P.Pid and lower(P.color)=’red’) or (lower(S.address)=lower(’220 Packer street’));

(4)               Find Sids of Suppliers who supply some red and some green parts.

Solution(1):      

            Select Sname from Suppliers where Sid in

            (Select Sid from Catalog Where Pid in

(Select Pid from Parts where lower(color)=’red’)) and Sid in

            (Select Sid from Catalog Where Pid in

(Select Pid from Parts where lower(color)=’green’));