SQL script: Please write SQL statements for the following questions: 1. Get supp

Question

SQL script:

Please write SQL statements for the following questions:

1. Get supplier names for suppliers who supplied parts for jobs only in Athens. (Double negation; Use a nested query)

2. Which city has the most suppliers? (Use a nested query)

3. Among parts that are used in projects, find out the most used parts in each project.

Thanks!

num jname city Sorter Paris Punch Rome 13 Reader Athens Console Athens 15 Collator London Terminal Oslo Tape London p num pname color weight city pl Nut Red 12 London Bolt Green 17 aris p2 P3 Screw Blue 17 Rome p4 14 London Screw Red ps Cam Blue 12 aris P6 Cog Red 19 London s num s name status city 51 20 London Smith. 10 Paris S2 Jones 30 Paris Blake 54 Clark 20 London 55 Adams T 30 Athens

Explanation / Answer

Answer:

1)/*
Get supplier names for suppliers who supplied parts for jobs only in Athens. (Double negation; Use a nested query)
*/
Select S.s_name
From S INNER JOIN SPJ ON SPJ.s_num = S.s_num
Inner Join J ON J.j_num = SPJ.j_num
Where J.city = 'Athens'

2)
/*
Which city has the most suppliers? (Use a nested query)
*/

Select S.city From S OS
Where EXISTS (select top(1) S.city, Count(S.s_num) From S InS
Where InS.city = OS.city Group By S.city Order by Count(S.s_num) DESC)

3)
/*
Among parts that are used in projects, find out the most used parts in each project.
*/
Select P.p_num
From P
Where EXISTS (Select top(1) SPJ.p_num, Count(SPJ.p_num) From SPJ Where P.p_num = SPJ.p_num Group By SPJ_p_num Order By Count(SPJ.p_num) DESC)

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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