Use information in screenshot below. MySQL DBMS. For each of the following Engli
ID: 3703516 • Letter: U
Question
Use information in screenshot below. MySQL DBMS. For each of the following English descriptions, provide a valid SQL statement and show the evaluated result (command line or screen capture). Views are not allowed and the SQL should be a single statement.
(a) Get project numbers and names for projects supplied by supplier S2.
(b) Get the full details for parts supplied by a supplier in London.
(c) Get all shipments where the quantity is in the range 300 to 750 inclusive.
(d) Get part numbers for parts supplied by a supplier in London to a project in London.
(e) Get all supplier-number/part-number/project number triples such that the indicated supplier, part and project are collocated.
(f) Get the triples in (e) that are not collocated.
(g) For each part being supplied to a project, get the part number, project number and the total quantity.
(h) Get part numbers for those parts supplied to all projects in London.
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 DISTINCT P.JNO, JNAME FROM project AS P ,supplier AS S,spj AS sp WHERE S.SNO=sp.SNO AND sp.JNO = P.JNO;
2.SELECT DISTINCT part.PNO,part.PNAME,part.COLOR, part.WEIGHT FROM supplier, part, spj WHERE supplier.SNO =spj.SNO AND part.PNO=spj.PNO AND supplier.CITY = "London"
3.SELECT * FROM SPJ WHERE QTy BETWEEN 300 AND 750;
4.SELECT PNO FROM spj WHERE EXISTS (SELECT * FROM supplier AS s WHERE CITY = 'London' AND s.SNO = spj.SNO) AND EXISTS (SELECT * FROM project AS j WHERE CITY = 'London' AND j.JNO = spj.JNO);
5.SELECT SNO,PNO,JNO FROM supplier AS S, part AS P, project AS J WHERE S.city = P.city AND P.city = J.city;
6.SELECT SNO,PNO,JNO FROM supplier AS S, part AS P, project AS J WHERE NOT (S.city = P.city AND P.city =J.city);
7.SELECT DISTINCT spj.PNO, spj.JNO, QTy FROM spj, part,project WHERE part.PNO = spj.PNO AND project.JNO = spj.JNO;
8.SELECT DISTINCT sp1.PNO FROM spj AS sp1 , spj as sp2 WHERE NOT EXISTS (SELECT * FROM project as p WHERE p.CITY="London" AND NOT EXISTS (SELECT * FROM spj as sp3, spj as sp4 WHERE sp4.PNO = sp2.PNO AND sp4.JNO = p.JNO));
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.