IT462 Lab 1: SQL SELECT Review

 

DUE: September 1, 2011, Before lab

 

In this lab you will rehearse your database implementation skills by querying a MS SQL Server database using SQL.

 

Follow the tutorial on the website to connect to the Microsoft SQL Server 2008.

 

To write SQL queries in SQL Server:

  1. Select the database created for you (mxxxxxx).
  2. From the toolbar, select New Query.
  3. Make sure your database is selected in the top-left side of the window (not the "master" database)
  4. Write the SQL statements in the window opened. You can write one-line comments by starting the line with --, or multi-line comments by using C-style comments /* multi-line comment here */.
  5. Click the checkmark button in the toolbar to check the syntax of your SQL.
  6. Click the “! Execute” in the toolbar to execute all SQL statements in the file. If you want to execute only one statement, highlight it (select it with the mouse) and then click on “! Execute”.

To save SQL queries: From the menu à Fileà Save As...  Make sure you save queries in a file on your X drive, not the local drive!

 

In this lab, you will work on the Catalog database used to keep track of suppliers, parts, and a catalog containing all parts available from different suppliers and their price. The following tables are part of the database:

 

Suppliers(sid, sname, address)

Parts(pid, pname, color)

PartsSupplied(sid, pid, cost)

 

(Prepare the database)

Download the createCatalog.sql file from the course website, and open it in Microsoft SQL Server 2008. Change the first line of the file to be use mXXXXXX;

where XXXXXX is your alpha, and execute the file. It should create the tables above, with some data in them.

 

Now, write SQL queries to accomplish the following tasks. Save all queries in a file called yourlastname_lab1.sql.  Write the query text as a comment “/* Find ... */”, before each query.

 

Note: Your queries should run correctly on any data, not just the sample data currently in the database. The results on the sample data are shown at the end of lab writeup.

 

  1. Find the pnames of parts for which there is some supplier. Each name should appear only once in the result set.
  2. Find the total number of parts (pids) that are red.
  3. Find the pnames of parts supplied by Acme Widget Suppliers.
  4. Find the pnames of parts that Acme Widget Suppliers does not supply.
  5. Find the pnames of parts supplied by Acme Widget Suppliers and no one else.
  6. Find the sids of suppliers that supply a red part or a green part.
  7. Find the sids of suppliers that supply at least a red part and a green part (Hint: The expression color = 'red' AND color = 'green' is always false)
  8. Print the sid and average cost of parts supplied by that supplier, for each supplier.
  9. For each supplier, print the sid, sname and maximum cost for the parts supplied by that supplier.
  10. Find the sid, sname, address and total number of parts supplied, for suppliers that supply at least 2 parts.
  11. For each supplier, print the sid, sname and the number of red parts supplied by that supplier.
  12. Find the snames of suppliers who supply every red part.

 

 

Extra credit:

 

  1. Find the sids of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers that supply that part).
  2. Find the sids of suppliers that supply only red parts.
  3. For every supplier that supplies a green part and a red part (at least), print the name and price of the most expensive part that it supplies.

 

Results on the sample data:

 

/* 1 */

pname

----------------------------------------

7 Segment Display

Acme Widget Washer

Anti-Gravity Turbine Generator

Fire Hydrant Cap

I Brake for Crop Circles Sticker

Left Handed Bacon Stretcher Cover

 

(6 row(s) affected)

 

/* 2*/

 

-----------

3

 

(1 row(s) affected)

 

/* 3 */

pname

----------------------------------------

Acme Widget Washer

Acme Widget Washer

Fire Hydrant Cap

 

(3 row(s) affected)

 

/* 4 */

pname

----------------------------------------

Left Handed Bacon Stretcher Cover

Smoke Shifter End

I Brake for Crop Circles Sticker

Anti-Gravity Turbine Generator

Anti-Gravity Turbine Generator

7 Segment Display

 

(6 row(s) affected)

 

/* 5 */

pname

----------------------------------------

Acme Widget Washer

 

(1 row(s) affected)

 

/* 6 */

sid

---------------------------------------

1

1

2

2

2

3

3

 

(7 row(s) affected)

 

/* 7*/

sid

---------------------------------------

3

 

(1 row(s) affected)

 

/* 8 */

sid                                     AverageCost

--------------------------------------- ---------------------------------------

1                                       4.233333

2                                       8.333333

3                                       6.750000

4                                       831699.553333

 

(4 row(s) affected)

 

/* 9*/

sid                                     sname                                    MaxCost

--------------------------------------- ---------------------------------------- ---------------------------------------

1                                       Acme Widget Suppliers                    11.70

2                                       Big Red Tool and Die                     16.50

3                                       Perfunctory Parts                        12.50

4                                       Alien Aircaft Inc.                       1247548.23

 

(4 row(s) affected)

 

/* 10 */

sid                                     sname                                    address                                                      TotalParts

--------------------------------------- ---------------------------------------- ------------------------------------------------------------ -----------

1                                       Acme Widget Suppliers                    1 Grub St., Potemkin Village, IL 61801                       3

2                                       Big Red Tool and Die                     4 My Way, Bermuda Shorts, OR 90305                           3

3                                       Perfunctory Parts                        99999 Short Pier, Terra Del Fuego, TX 41299                  2

4                                       Alien Aircaft Inc.                       2 Groom Lake, Rachel, NV 51902                               3

 

(4 row(s) affected)

 

/* 11 */

sid                                     sname                                    TotalRedParts

--------------------------------------- ---------------------------------------- -------------

1                                       Acme Widget Suppliers                    2

2                                       Big Red Tool and Die                     3

3                                       Perfunctory Parts                        1

 

(3 row(s) affected)

 

/* 12 */

sname

----------------------------------------

Big Red Tool and Die

 

(1 row(s) affected)

 

 

Turn in BOTH electronic and paper copies (due before start of lab on September 1st, 2011):

 

Electronic:

  1. Upload the yourlastname_lab1.sql file containing all the SQL statements to Lab 1 assignment on Blackboard.

Hard-copies:

  1. The completed assignment coversheet. Your comments will help us improve the course.
  2. A hard copy of the yourlastname_lab1.sql.