IT360 Lab 2: SQL SELECT

 

DUE: January 30, 2011, 2359 (paper copy BEFORE start of lab next day)

 

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

 

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

 

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 Madison Importing database: Madison Importing purchases antiques and home furnishings in Asia and ships those items to a warehouse facility in Los Angeles. A database is used to keep track of items purchased, shipments and items shipped. The following tables are part of the database:

 

ITEM(ItemID, Description, PurchaseDate, Store, City, Quantity, LocalCurrencyAmt, ExchangeRate)

SHIPMENT(ShipmentID, ShipperName, ShipperInvoiceNumber, DepartureDate, ArrivalDate, InsuredValue)

SHIPMENT_ITEM(ShipmentID, ShipmentItemNb, ItemID, Value)

 

(Prepare the database)

Download the createMadisonImports.sql file from the course website, and open it in SQL Server. 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 for Madison Importing, with some data in them.

 

Now, write SQL queries to accomplish the following tasks. Save all queries in a file called yourlastname_lab2.sql.  Write a comment “/* Exercise xx */”, where xx is the exercise number, before each query.

 

Note: Your queries should run correctly on any data, not just the sample data currently in the database.

 

  1. List all data from the ITEM table.

Result on the sample data:

 

  1. List the ShipmentID, ShipperName, and ShipperInvoiceNumber of all shipments, in ascending order by ShipperName, and descending by ShipperInvoiceNumber.

Result on the sample data:

 

  1. List the ShipmentID, ShipperName, ShipperInvoiceNumber and InsuredValue for all shipments that have an insured value greater than $10000.

Result on the sample data:

 

  1.  List the ShipmentID, ShipperName, and ShipperInvoiceNumber for all shippers whose name starts with 'AB'.

Result on the sample data:

 

  1. Determine the maximum, minimum, and average InsuredValue.

Result on the sample data:

 

  1. For all items, show ItemID, Description, Store and a calculated column named  USCurrencyAmount that is equal to the LocalCurrencyAmount  multiplied by ExchangeRate.

Result on the sample data:

 

  1. For each City, Store combination in the ITEM table, display the City, Store, and the number of item types purchased in that City, Store combination.

Result on the sample data:

 

  1. List the City and the number of item types purchased in that city, for all cities with at least 2 different items purchased there.

Result on the sample data:

 

  1. List the ShipmentID, ItemID, Value, Description, and PurchaseDate, for all items shipped.

Result on the sample data:

 

  1. List the ShipmentID, ShipperName, ShipperInvoiceNumber, ItemID, Description, PurchaseDate, and Value for all items shipped.

Result on the sample data:

 

  1. List the ShipperName and DepartureDate  of all shipments that have an item with a Value of 1000 or more. Use a sub-query.

Based on the sample data, the result is given below. Depending on how you wrote the query, you might get some duplicate rows (which is OK as long as the query is correct).

 

  1. List the ShipperName and DepartureDate  of all shipments that have an item that was purchased in Singapore. Use a join. Present results sorted by ShipperName in ascending order and then DepartureDate in descending order.

Result on the sample data:

 

  1. List the ShipmentID, ShipperName, and InsuredValue for shipments with InsuredValue lower than the average InsuredValue.

Result on the sample data:

 

  1. List the ItemID, Description, Store, and City for all items purchased in Singapore or Manila.

Result on the sample data:

 

  1. List the ItemID, ShipperName, DepartureDate of shipment, and Value, for items that were purchased in Singapore. Use a combination of a join and a subquery. Present results sorted by Shipper in ascending order and then DepartureDate in descending order.

Result on the sample data:

 

  1. (EXTRA CREDIT) List the ShipmentID and ShipperName for the shipments that contain items from each city in the ITEMS table.
  2. (EXTRA CREDIT)  Do the “Practice SQL” exercises posted on the calendar for January 19, 2011.

 

Turn in BOTH electronic (due January 30, 2011 at 2359) and paper copies (due before start of lab on January 31, 2011):

 

Electronic:

  1. Upload the yourlastname_lab2.sql file containing all the SQL statements to Lab 2 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_lab2.sql.