SQL DML Write SQL statements to answer the following questio

***SQL DML***

Write SQL statements to answer the following question. Note that you do not need data tuples to answer. 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)

Use EXISTS subquery, what are the id and the name of all jobs, which job and supplier are in the same city?

Supplier (sid, name, status, city) Part (pid, name, color, weight, city) Job (jid, name, city) Supplies (/sid/, /pid/, /jid/, quantity)

Solution

The id and the name of all jobs : SELECT j.id,j.name FROM Job AS p WHERE EXISTS ( SELECT * FROM Job AS c WHERE c.jid = p.jid, c.jname=p.jname)