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.
S1 Smith 20 London
S2 Jones 10 Paris
S3 Blake 30 Paris
S4 Clark 20 London
S5 Adams 30 Athens
P PNO PNAME COLOR WEIGHT 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.
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.
P2 Bolt Green 17 Paris
P3 Screw Blue 17 Rome
P4 Screw Red 14 London
P5 Cam Blue 12 Paris
P6 Cog Red 19 London
SP SNO PNO QTY
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.
S1 P2 200
S1 P3 400
S1 P4 200
S1 P5 100
S1 P6 100
S2 P1 300
S2 P2 400
S3 P2 200
S4 P2 200
S4 P4 300
S4 P5 400
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 S5’s 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 SNO’s 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
SNO SNAME STATUS CITY
S11 ASUS 55 Portland
2. Change the above record to following in one action query
SNO SNAME STATUS CITY
S11 ASUS 60 Monmouth
3. Add the following record to the P table
PNO PNAME COLOR WEIGHT CITY
P20 Monitor Black 15 Monmouth
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.
SNO PNO QTY
S11 P12 3000
S11 P20 200
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.