Supplier ( sid , name, status, city) Part ( pid , name, color, weight, city) Job

Question

Supplier (sid, name, status, city)

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

Job (jid, name, city)

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

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

1.What are the id and the name of all parts supplied to at least two different jobs in city ‘London’?

2. What are the id and the name of all suppliers for parts supplied to at least two different jobs?

3.Use subquery, what are the id and the name of all suppliers, whose total quantity is larger than the total quantity of all suppliers in city ‘London’?

4.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)

Explanation / Answer

1.Ans) select pid,name from (
select distinct p.pid as pid,p.name as name ,j.jid from part p
inner join job j on j.name=p.name and j.city=p.city
inner join supplier s on s.name=p.name and s.city=p.city
inner join supplies sp on sp.pid=p.pid and sp.jid=j.jid and s.sid=sp.sid
where p.city='London' group by p.pid ,p.name,j.jid having count(*)>1
)

2.Ans) select sid,name from (
select distinct s.sid as sid, s.name as name, j.jid from suppliers s
inner join part p on p.name=s.name and p.city=s.city
inner join job j on j.name=s.name and j.city=s.city
inner join supplies sp on sp.pid=p.pid and sp.jid=j.jid and s.sid=sp.sid
group by s.sid,s.name,j.jid having count(*)>1
)

3.Ans) select s.sid,s.name from suppliers s
inner join part p on p.name=s.name and p.city=s.city
inner join job j on j.name=s.name and j.city=s.city
inner join supplies sp on sp.pid=p.pid and sp.jid=j.jid and s.sid=sp.sid
where sp.quantity >
(select sum(sp.quantity) from supplies sp
inner join suppliers s on s.sid=sp.sid and s.city='London'
)

4.Ans) select j.jid,j.name from job j
inner join part p on p.name=j.name and p.city=j.city
inner join suppliers s on j.name=s.name and j.city=s.city
inner join supplies sp on sp.pid=p.pid and sp.jid=j.jid and s.sid=sp.sid
where j.city exists (select city from suppliers)

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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