Consider the following schema: Suppliers(sid: integer, sname: string, address: string)

Parts(pid: integer, pname: string, color: string)

Catalog(sid: integer, pid: integer, cost: real)

The Catalog relation lists the prices charged for parts by Suppliers. Write the following queries in SQL:

e. The following query returns: SELECT DISTINCT C.sid FROM Catalog C WHERE C.cost > ( SELECT AVG (C1.cost) FROM Catalog C1 WHERE C1.pid = C.pid )

f. The following query returns: SELECT P.pid, S.sname FROM Parts P, Suppliers S, Catalog C WHERE C.pid = P.pid AND C.sid = S.sid AND C.cost = (SELECT MAX (C1.cost) FROM Catalog C1 WHERE C1.pid = P.pid)

g. Find the sids of suppliers who supply only red parts.

h. The following query returns: SELECT DISTINCT C.sid FROM Catalog C, Parts P WHERE C.pid = P.pid AND P.color = Red INTERSECT SELECT DISTINCT C1.sid FROM Catalog C1, Parts P1 WHERE C1.pid = P1.pid AND P1.color = Green

i. Find the sids of suppliers who supply a red part or a green part.

j. The following query returns: SELECT S.sname, COUNT(*) as PartCount FROM Suppliers S, Parts P, Catalog C WHERE P.pid = C.pid AND C.sid = S.sid GROUP BY S.sname, S.sid HAVING EVERY (P.color=Green)

k. For every supplier that supplies a green part and a red part, print the name and price of the most expensive part that she supplies.