Consider the tables attached. Write SQL to accomplish each of the foloowing task
ID: 3835715 • Letter: C
Question
Consider the tables attached. Write SQL to accomplish each of the foloowing tasks. Be sure to include your analysis as comments to each query.
1- List the part number and description of alal parts that have been ordered by customers with a 5,000 credit limit line. List each party obly once.
/*
Table(s):
Columns:
Condition:
*/
2- Show description and price of the parts with above average prices (i.e. its price is greater than the average price of parts).
/*
Table(s):
Columns:
Condition:
*/
The picture below includes the Tables:
Student IS361 Fall, 2015 Part PartNum Description OnHand Class Warehouse Price $24.95 50 HW 45 SG BV06 Home Gym CD52 Microwave ove 32 AP 21 HW 3 $129.95 8 $399.99 DL71 Cordless Drill DR93 Gas Range Dw11 washer 22 HW Stand Mixer FD21. KTO3 Dishwasher KV29 onderLine QuotedPrice Order Num PartNum Numordered 21608 AT94 21608 10/20/2007 148 610 1610 10/20/2007 356 610 4 $329.95 10/21/2007 408 21613 1614 10/21/2007 282 614 KT03 1617 10/23/2007 608 617 BV06 1619 10/23/2007 148 617 21623 10/23/2007 608 619 $1,290.00 FinstName street City State Zip Commission Rate Valerie 624 Randall Grove FL 33321 $20,542.50 0.05 Richard S32 Jackson Sheldon F 33553 sa9.21600 0.07 RepNum LastName l Perez Juan 1626 Taylor Fillmore FL 33336 $23 A 87.00 0.08 Balance Creditumit RepNum Aadunce and Sport 2837 Greenway more R 33336 $6,550.00 $7.500.00 20 3827 Devon Grove FL 33321 $431.50 $10,000.00 35 Direct Brookings 331A6 $5,785.00 $7,500.00 65 382 Wildwood Northfiel R. 1828 Raven Crystal PL 33503 $5,285.25 $5,000.00 as 282 The Everything Shop 3829 Central Grove FL 33321 $3,412.00 $10,000.00 65 356 Bargains 838 Ridgeland Filmore PL 33336 si2,762.00 sisooooo 20 store 372 oxford Sheldon 33553 $2,106.00 $10,000.00 65 462 sport and Appliance 282 Evergreen Atonvile FL 3 $2.851.00 ss, ooo 00 35 524 687 Deerfield's Four Seasons 282 Columbia Sheldon A 33553 $248.00 $7,500.00 35 A Season Page 10 of 10Explanation / Answer
--As we need to find out part number ordered by customer with credit limit greater than 5000
--So we need to join customer table with Part table but there is no direct foreign key(no two column same in both table)
--so we need to find out the way how we can join them
--PartNum is accociated with OrderLine table so we join then and OrderLine is accociated with Order
-- and Order is accociated with Customer table so we will join them by their foreign key constraint.
-- and finally add condition in Customer tabel with balance greater then 5000.
select p.PartNum,p.Description
from Part p,Customer c,Order O, OrderLine OL
where p.PartNum = OL.PartNum
and OL.OrderNum = O.OrderNum
and O.CustomerNum = c.CustomerNum
and c.Balance >= 5000;
--we will use the math function AVG to calculate average in Part table and add consdition with price is greater then average price
select Description , Price
from Part
where Price > (select AVG(Price) from Parts);
Please let me know in comments if you still not able to understood it.