SQL DML Write SQL statements to answer the following questio

***SQL DML***

Write SQL statements to answer the following questions. Note that you do not need data tuples to answer these questions. Use the following relational schema.

Supplier (sid, name, status, city)

Part (pid, name, color, weight, city)

Job (jid, name, city)

Supplies (/sid/, /pid/, /jid/, quantity)

What are the id and the name of all suppliers for ‘Blue’ parts supplied to at least two different jobs?

Solution

Answer:

1. 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 = ‘blue’
GROUP BY s.sid, s.sname
HAVING COUNT(*) = (SELECT COUNT(*) FROM Parts AS p1
WHERE p1.color=‘blue’)

2. SELECT s1.sid, s1.sname
FROM Suppliers AS s1, Catalog AS c1, Parts AS p1
WHERE s1.sid = c1.sid
AND p1.pid = c1.pid AND p1.color = ‘blue’
GROUP BY s1.sid, s1.sname
HAVING COUNT(*) = (SELECT COUNT(*) FROM Parts AS p2
WHERE p2.color=‘blue’)