Question

EXERCISE 1 (SQL Queries) Consider the following schema: SUPPLIERS (SID : integer, SNAME : string, CITY : string) PARTS (PID : integer, PNAME : string, COLOR : string) CATALOG (SID : integer, PID : integer, COST : real) The key fields are underlined, and the domain of each field is listed after the field name. Thus, SID is the key for SUPPLIERS, PID is the key for PARTS, and SID and PID together form the key for CATALOG. The CATALOG relation lists the prices charged for parts by suppliers. CATALOG.SID is a foreign key referring to SUPPLIERS.SID and CATALOG.PID is a foreign key referring to PARTS.PID. Write the following queries in SQL.

7. For every part supplied by a supplier who is at the city of Newark, print the PID and the SID and the name of the suppliers who sell it at the highest price.

8. For every part which has at least two suppliers, find its PID, its PNAME and the total number of suppliers who sell it.

9. Find the PIDs of parts supplied by every supplier who is at the city of Newark or by every supplier who is at the city of Trenton.

10. Find the PIDs of parts supplied by every supplier who is at the city of Newark and by every supplier who is at the city of Trenton. 11. Find the SIDs of suppliers who supply a red part but do not supply a blue part.

Answer 1

7.

The SQL query for the given question would be as follows :

Select CATALOG.PID,CATALOG.SID,SUPPLIERS.SNAME from CATALOG

left join SUPPLIERS on

CATALOG.SID=SUPPLIERS.SID

group by CATALOG.PID

having CATALOG.COST = max(CATALOG.COST)

where SUPPLIERS.CITY = 'Newark';

• The select statement will be used to select the PID,SID, SUPPLIERS.SNAME from CATALOG and SUPPLIERS table
• The left join from CATALOG on SUPPLIERS ensures that we can get sname from SUPPLIERS and we will be having both tables joined
• The group by statement ensures that every product is selected with the having condition that will guarantee the seller with maximum cost
• The where clause is applied to see that we get the sellers who are in the city of Newark

8.

The SQL query for the given question would be as follows :

select CATALOG.PID,PARTS.PNAME,count(CATALOG.SID) from CATALOG
left join PARTS on

PARTS.PID=CATALOG.PID

group by CATALOG.PID

having count(CATALOG.SID)>=2;

• the basic idea for this query is to join the two tables CATALOG and PARTS and to get the product which is having at least 2 suppliers.
• the SELECT statement is used to get pid, pname and the total number of suppliers from the catalog table joined with the table parts in order to get part name, pid and total number of suppliers.
• the group by statement is used to group all the rows with identical pid
• the having clause with help us get only those products where number of sellers is at least 2
• the aggregate function count will help get number of sellers for each product.

9.

The SQL query for the given question would be as follows :

select CATALOG.PID from CATALOG

left join SUPPLIERS on

CATALOG.SID=SUPPLIERS.SID

where SUPPLIERS.CITY in ('Newark','Trenton');

• The select statement will get pid of parts supplied from the tables CATALOG and SUPPLIERS
• left join is used to get the list of suppliers so that we can connect those two tables.
• The where clause is used to get the suppliers which will be in the city of Newark or Trenton with the help of In operator.
• the in operator will make sure that only those suppliers will get selected who are living in Newark or Trenton and not any other city

10.

The SQL query for the given question would be as follows :

select CATALOG.PID from CATALOG

left join SUPPLIERS on

CATALOG.SID=SUPPLIERS.SID

where SUPPLIERS.CITY = 'Newark' and SUPPLIERS.CITY='Trenton';

• The select statement will get pid of parts supplied from the tables CATALOG and SUPPLIERS
• left join is used to get the list of suppliers so that we can connect those two tables.
• The where clause is used to get the suppliers which will be in the city of Newark or Trenton with the help of In operator.
• The AND operator will get us the PIDs of parts supplied by every supplier who is at the city of Newark and by every supplier who is at the city of Trenton

Note :

I am able to answer only the first 4 questions as per the Chegg guidelines.

Please comment and let me know if you have any doubts.

Thank you.

Have a good day.