Use information in screenshot below. MySQL DBMS Given the English description: F
ID: 3703515 • Letter: U
Question
Use information in screenshot below. MySQL DBMS Given the English description: For parts that are supplied to some projects in average quantity of more than 350, show part numbers and part names (popular bulk parts).
(a) Write a valid SQL statement
(b) Define a view that completes this description and show the result: For parts that are supplied to some projects in average quantity of less than 400.
(c) Prove that the view has been created in the system catalog (hint: SQL on information_schema again)
os 0274|2 2 5 6 4 8 1 2 5 3 3 2 1 5 1212848 G000000 27 7429 4123456 244444 3 3 T00000 ET N-13333333534662255613456 NBSSCC 123 456 123-4567 11222|22 2 2 2 3 3 4 4 5 5 5 5 5 5 5 5 5 5 JExplanation / Answer
1. Select a.PNO, a.PNAME from PART a inner join SPJ b
on a.PNO=b.PNO
group by a.QTY
Having avg(a.QTY )>350
2. Create view information-schema.PARTView as
Select a.PNO, a.PNAME from PART a inner join SPJ b
on a.PNO=b.PNO
group by a.QTY
Having avg(a.QTY )<400
3. The above view is created iinformation-shema which show view is created in system catlog
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.