***SQL DML*** Write SQL statements to answer the following questions. Note that

Question

***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 for suppliers of the same part to at least two different jobs.

Supplier (sid, name, status, city)

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

Job (jid, name, city)

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

Explanation / Answer

In order to do this, the following steps need to be followed:

1. Join tables - Supplier and Supplies

2. Use a WHERE clause for Supplier.sid = Supplies.sid

Notice that in the final data, the sid, pid will be the same. The varying attribute is jid. There should be atleast 2 jids for the same set of sid, pid in a tuple.

3. Group the resultant by Supplies.sid, Supplier.name

4. Then just check for those tuples where there is at least two different jid. This can be done by using HAVING clause with aggregate function COUNT.

The following is the complete SQL query:

SELECT Supplier.sid, Supplier.name
FROM Supplier, Supplies
WHERE Supplier.sid = Supplies.sid
GROUP BY Supplies.sid, Supplier.name, Supplies.pid HAVING COUNT(Supplies.jid) > 1

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.