[SOLVED] SQL database Lab # 2. IS520

Lab # 2.IS520

Do the problems yourself and ask questions in class. Using techniques not discussed in class is mostly disallowed and must have references clearly stated.

What to do
I.Create the following tables using MySQL, SQL Server, or Oracle. Access is allowed, but not recommended.

Table for suppliers. SNO is the key and
SNAME is not necessarily unique.
Table for suppliers. SNO is the key and
SNAME is not necessarily unique.
S1Smith20London
S2Jones10Paris
S3Blake30Paris
S4Clark20London
S5Adams30Athens

PPNOPNAMECOLORWEIGHTCITY
Table for parts. PNO is the key. CITY is the city where the part is stored. Assume that a part has only one color and stored at one city.
Table for parts. PNO is the key. CITY is the city where the part is stored. Assume that a part has only one color and stored at one city.
P2BoltGreen17Paris
P3ScrewBlue17Rome
P4ScrewRed14London
P5CamBlue12Paris
P6CogRed19London

SPSNOPNOQTY
Table for shipments. SNO + PNO are the key. This is the only information about who is supplying what.
Table for shipments. SNO + PNO are the key. This is the only information about who is supplying what.
S1P2200
S1P3400
S1P4200
S1P5100
S1P6100
S2P1300
S2P2400
S3P2200
S4P2200
S4P4300
S4P5400
II.Simple Queries (3 points each).
1. List all cities in P without duplication.
2. Get SNames for suppliers in Paris with status being the same as S5s status.
3. Get SNames and STATUS for suppliers in Paris, in descending order of total shipment of the supplier.
4. Get SNames for suppliers who do not supply part P3 [Hint using NOT IN].
5. Count the number of shipment records, not the QTY, for P2 (each record in SP where P2 is referred is considered as one shipment).
III. More complex Queries (5 points each, ask questions, if any, in class)
1. For two parts that have the same city, list the city and two part numbers. Sort the list by city and make sure the first PNO is smaller than the second PNo
2. For each PName, list the PName and the total number of parts with that name.
3. For each part shipped, get its PNO, number of suppliers, and the total shipment quantity of the PNO.
4. Get part numbers and suppliers names for all parts shipped by more than one supplier.
5. List suppliers that ships ONLY red and blue parts. [Hint: Find the list of red parts L1, find the list blue parts L2, find the list of parts that are neither red nor blue L3. You need to find the SNOs that supply a part in L1 and a part in L2, but not a part in L3. You have to use subquery, several ones.]

IV. Action Queries (3 points each)
1.Add the following record to the S table
SNOSNAMESTATUSCITY
S11ASUS55Portland

2.Change the above record to following in one action query
SNOSNAMESTATUSCITY
S11ASUS60Monmouth

3.Add the following record to the P table
PNOPNAMECOLORWEIGHTCITY
P20MonitorBlack15Monmouth

4.Add two shipment records. If your database is locked down by referential integrity constraint, add an additional record as problems 3, but change the PNO to P12.

SNOPNOQTY
S11P123000
S11P20200

Show all the part info, supplier info, and shipment info for shipment records involve S11 after complete problem #4.

5.Delete any reference of S11 and P20 [Hint: three delete statements]

What to turn in (Turn in your answer in PDF format, No Email please)
For I. Turn in the screen dumps of the tables (3 points for the S and P tables, 4 points for the SP table).
For II, III, and IV. Turn in, for each problem, the query itself (in SQL statement format) and the result of the query as a screen dump ON THE SAME PAGE side by side or up and down. Please label the questions and their answers clearly. Please provide answers in the order the problems are given. Please make sure the fonts are big enough (minimally size 14, prefer size 14 or 16) for me to read. Please do not come up with your own table and columns names for the table.