Question

You may use the SQL Handout given out in class. The database is called the suppliers-parts-projects database. S, P, J, and SPJ are the 4 tables. S is the Suppliers table P is the Parts table Jis the Projects table and SPJ is where a specified supplier supplies the specified part to the specified project in the specified quantity. NOTE: The database is on the last page of this test, you may detach this from the rest of the test.

Answer 1

Q 1:

Select * from J;

Q 2:

Select * from J where City = 'London';

Q 3:

Select P# from P where Weight = (Select min(Weight) from P);

Q 4:

Select S# from S , SPJ where S.S#= SPJ.S# and SPJ.J# = 'J1';

Q 5:

Select S# from S ,SPJ ,P where S.S# = SPJ.S# and P.P# = SPJ.P# and SPJ.J# = 'J1' and P.P#= 'P1';

Q 6:

Select JName from J , SPJ where J.J# = SPJ.J# and SPJ.S# = 'S1';

Q 7:

Select Color from P , SPJ ,S where P.P# = SPJ.P# and SPJ.S# = S.S# and S.S# ='S1';

Q 8:

Select S# from S , SPJ where S.S# = SPJ.S# and SPJ.J# = 'J1' and SPJ.J# = 'J2';

Q 9:

Select S# from S , SPJ ,P where S.S# = SPJ.S# and P.P# = SPJ.P# and J.J# ='J1' and P.Color = 'Red';

Q 10:

Select P# from P, SPJ ,J where P.P# = SPJ.P# and SPJ.J# = J.J# and J.City = 'London';

Q 11:

Select P# from P, SPJ ,J ,S where P.P# = SPJ.P# and SPJ.J# = J.J# and SPJ.S# = S.S# and S.City = P.City;

Q 12:

Select J# from J , SPJ,P,S where J.J# = SPJ.J# and SPJ.P# = P.P# and SPJ.S# = S.S# and S.City != J.City;

Q 13:

Select J# from J , SPJ ,P ,S where J.J# = SPJ.J# and SPJ.P# = P.P# and SPJ.S# = S.S# and S.# = 'S1';

Q 14:

Select S.City,P.City from S , SPJ,P where S#.S = SPJ.S# and SPJ.P# = P.P# and S.City = P.City;

Q 15:

Select S.City,P.P#,P.City from S, SPJ ,P where S#.S = SPJ.S# and SPJ.P# = P.P# and S.City = P.City;

Q 16:

Update J set JName = 'VIDEO' where J# = 'J6';

Q 17:

Update P set Color ='Orange' where Color = 'Red';

Q 18:

Select sum(P.P#) from P,SPJ,S where P.P# = SPJ.P# and S.S# = SPJ.S# group by S.S# having S# = 'S3';

Q 19:

Select sum(P.Weight) from P ,SPJ ,S where P.P# = SPJ.P# and S.S# = SPJ.S# group by S.S# having S# = 'S1';

Q 20:

Select P.P#,J.J#,sum(Weight) from P ,SPJ ,S where P.P# = SPJ.P# and S.S# = SPJ.S# group by P.P# ;

Do ask if any doubt. Please upvote.