Question

Use the give table below to answer the following questions. sName Smith sCity SI S2 PI P2 P3 P4 PS Nut Bolt Red Blue 12 17 17 10 Blake Paris Screw Red S4. S5 30 Athens Blue 12 SP SNo (PK) (FK S1 S1 S1 S1 S1 S2 S2 S3 S3 S3 S4 P1 P2 P3 P4 PS PI P2 Pl P2 P3 P2 P3 P4 200 100 100 200 200 200 S5 Table: Content of the Supplier-Parts DB Tables

Answer 1

1. SELECT pNo, qty, avg(qty) AS AVE FROM SP GROUP BY pNo HAVING qty > avg(qty) ORDER BY pNo, qty, avg(qty);

2. SELECT SP.pNo, qty, avg(qty), P.pName AS AVE FROM SP JOIN P ON SP.pNo = P.pNo GROUP BY pNo HAVING qty > avg(qty) ORDER BY pNo, qty, avg(qty);

NOTE: As per Chegg policy, I am allowed to answer only 2 questions (including sub-parts) on a single post. Kindly post the remaining questions separately and I will try to answer them. Sorry for the inconvenience caused.