Using the SP database, give the names of the suppliers who supplies the most parts and least parts Can the following SQL code generate the correct result Select subq1.sname, subq1.s#, subq1.Most_parts, subq2.sname, subq2.s#, subq2.Least_parts From (Select sname, s.s#, a.most_parts, a.rank rank from sp.s s. (select s#,count(p#) Most_parts,sum(qty) qty, rank()over (order by count(p#) desc) rank From sp.sp Group by s#)a Where s.s#=a.s# and a.rank=1) subq1, (Select sname, s.s#, b.Least_parts, b.rank rank from sp.s s, (select s#,count(p#) Least_parts,sum(qty) qty, rank()over (order by count(p#)) rank From sp.sp True False Group by s#) b Where s.s#=b.s# and b.rank=1) subq2 Where subq1.rank=subq2.rank;
Using the SP database, give the names of the suppliers who supplies the most parts and least parts Can the following SQL code generate the correct result Select subq1.sname, subq1.s#, subq1.Most_parts, subq2.sname, subq2.s#, subq2.Least_parts From (Select sname, s.s#, a.most_parts, a.rank rank from sp.s s. (select s#,count(p#) Most_parts,sum(qty) qty, rank()over (order by count(p#) desc) rank From sp.sp Group by s#)a Where s.s#=a.s# and a.rank=1) subq1, (Select sname, s.s#, b.Least_parts, b.rank rank from sp.s s, (select s#,count(p#) Least_parts,sum(qty) qty, rank()over (order by count(p#)) rank From sp.sp True False Group by s#) b Where s.s#=b.s# and b.rank=1) subq2 Where subq1.rank=subq2.rank;
Chapter3: Creating Tables
Section: Chapter Questions
Problem 7CAT: Review the data for the TRIP table in Figure 1-5 in Chapter 1 and then review the data types used to...
Related questions
Question
Transcribed Image Text:Using the SP database, give the names of the suppliers who supplies the most parts and least parts
Can the following SQL code generate the correct result
Select subq1.sname,
subq1.s#,
subq1.Most_parts,
subq2.sname,
subq2.s#,
subq2.Least_parts
From (Select sname, s.s#, a.most_parts, a.rank rank
from sp.s s.
(select s#,count(p#) Most_parts,sum(qty) qty,
rank()over (order by count(p#) desc) rank
From sp.sp
Group by s#)a
Where s.s#=a.s# and a.rank=1) subq1,
(Select sname, s.s#, b.Least_parts, b.rank rank
from sp.s s,
(select s#,count(p#) Least_parts,sum(qty) qty,
rank()over (order by count(p#)) rank
From sp.sp
True
False
Group by s#) b
Where s.s#=b.s# and b.rank=1) subq2
Where subq1.rank=subq2.rank;
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Recommended textbooks for you
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781305627482
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning