Question 2 5 points Consider the following schema Suppliers

Question 2 (5 points). 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:

1. Find the pnames of parts for which there is some supplier.

2. Find the snames of suppliers who supply every part.

3. Find the snames of suppliers who supply every red part.

4. Find the pnames of parts supplied by Acme Widget Suppliers and no one else.

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

6. Find the sids of suppliers who supply a red part and a green part.

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

Question 3 (5 points). The following relations keep track of airline flight information:

Write the following queries in SQL:

For each pilot who is certified for more than three aircraft, find the eid and the maximum cruisingrange of the aircraft for which she or he is certified.

Find the names of pilots whose salary is less than the price of the cheapest route from Los Angeles to Honolulu.

Find the aids of all aircraft that can be used on routes from Los Angeles to Chicago.

Print the names of employees who are certified only on aircrafts with cruising range longer than 1000 miles.

Solution

Question 2

1)

SELECT p.pname FROM Parts AS p
WHERE EXISTS (
SELECT * FROM Catalog AS c
WHERE c.pid = p.pid)

2)

SELECT S.sname FROM Suppliers S
WHERE NOT EXISTS (( SELECT P.pid FROM Parts P )
EXCEPT
( SELECT C.pid FROM Catalog C
WHERE C.sid = S.sid ))

3)

SELECT s.sid, s.sname
FROM Suppliers AS s, Catalog AS c, Parts AS p
WHERE s.sid = c.sid
AND p.pid = c.pid AND p.color = ‘red’
GROUP BY s.sid, s.sname
HAVING COUNT(*) = (SELECT COUNT(*) FROM Parts AS p1
WHERE p1.color=‘red’)

4)

SELECT p.pname FROM Parts AS p

WHERE p.pid IN (

(SELECT c1.pid AS pid FROM Catalog AS c1, Suppliers s1

WHERE c1.pid = s1.sid

AND s1.sname = ‘Acme Widget Suppliers’)

EXCEPT

(SELECT DISTINCT c2.pid AS pid

FROM Catalog AS c2, Suppliers s2

WHERE c2.pid = s2.sid

AND s2.sname != ‘Acme Widget Suppliers’))

5)

SELECT DISTINCT C.sid
FROM Catalog C
WHERE NOT EXISTS ( SELECT *
FROM Parts P
WHERE P.pid = C.pid AND P.color <> ‘Red’ )