Use information in screenshot below. MySQL DBMS. For each of the following Engli
ID: 3703574 • 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
a)
select JNO,JNAME
from PROJECT,SPJ
where PROJECT.JNO = SPJ.JNO and SPJ.SNO = 'S2';
Here, JNo represents Project Number and JName represents Project name
b)
select * from PART
where PNO in (Select distinct PNO
from SUPPLIER,SPJ
where SUPPLIER.SNO = SPJ.SNO and SUPPLIER.CITY = 'London');
First inner query will be executed which give us distinct Part number supplied in London then outer query is executed giving details about those parts.
c)
select *
from SPJ
where QTY between 300 and 750;
between include both 300 and 700 also
d)
select PNO from SPJ
where SNO in (Select SNO from SUPPLIER where CITY = 'LONDON')
and
JNO in (Select JNO from PROJECT where CITY = 'LONDON') ;
In this query, we select part numbers from SPJ where both where conditions are satisfied.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.