IT360 Lab 2: SQL SELECT

 

DUE: January 27, 2014, 2359 (paper copy BEFORE start of lab next day)

 

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

 

Follow the tutorial on the website to connect to the MySQL server.

 

To write SQL queries in MySQL:

  1. Make sure your database (mXXXXXX) is selected as "Default" in the top-left side of the window (not the "master" database)
  2. Write the SQL statements in the top-right pane, each SQL statement ended with ";". You can write one-line comments by starting the line with --, or multi-line comments by using C-style comments /*  multi-line comment here */.
  3. Go to Query -> Execute Current Statement (Ctrl+Enter) to execute the current SQL query (on the cursor line).
  4. Go to Query ->Execute (All or selection) (Ctrl+Shift+Enter) to execute all SQL statements in your script, or only the highlighted statements.

To save SQL queries: From the menu ŕ Fileŕ Save Script 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, with primary key underlined and foreign keys in italic are part of the database:

 

ITEM(ItemID, Description, PurchaseDate, Store, City, Weight, PriceInLocalCurrency, ExchangeRate) - this table stores information about the items purchased, including the weight and purchase price

SHIPMENT(ShipmentID, ShipperName, ShipperInvoiceNumber, DepartureDate, ArrivalDate, InsuredValue) - this table stores information about shipments, including the total value for which the shipment was insured

SHIPPED_ITEM(ShipmentID, ShipmentItemNb, ItemID, Value) - this table stores information about the items included in each shipment, including the expected selling value of each item shipped

 

(Prepare the database)

Download the createMadisonImports.sql file from the course website, and open it in MySQL Workbench (File->Open SQL Script ...). Change the first line of the file to be use mXXXXXX;

where XXXXXX is your alpha, and execute the entire file (Ctrl+Shift+Enter). 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_yourfirstname_lab2.sql.  Write the English question as a comment before each SQL query (In MySQL Workbench, go to File-> New Query/Script tab. Copy-paste the queries below into the new script tab. Comment each question using /* */ and then write the SQL query below it. Save your script as yourlastname_yourfirstname_lab2.sql).

 

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

 

1. a) List all data from the ITEM table.

b) List all data from the SHIPMENT table

c) List all data from the SHIPPED_ITEM table

 

2.  List all the cities from the ITEMS table. Each city should only appear once in the result (no duplicates)

 

3.  List the ItemID, Description, Store, City and Weight for items with weight between 2 and 50, in ascending order by City, and descending by Weight.

 

4. List all Stores where an item with expected selling value higher than 1000 was purchased. Each store should appear only once in the result.

 

5. (Extra credit) List the ShipmentID, ShipperName, ShipperInvoiceNumber, and InsuredValue for all shippers whose name starts with 'AB' and the insured value is greater than $13000.

 

6. (Extra credit) For all items, show ItemID, Description, Store and a calculated column named  USCurrencyAmount that is equal to the PriceInLocalCurrency  multiplied by ExchangeRate.

 

7. (Extra credit) List the ShipmentID, ItemID, Value, Description, and PurchaseDate, for all items shipped.

 

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

                                                                       

9. (Extra credit) List the ItemID, Description, Store, and City for all items purchased in Singapore or Manila.

 

10.(Extra credit) List the ShipmentID and ShipperName for all shipments containing at item with Value higher than 3000. Each (ShipmentID, ShipperName) pair should appear only once in the result.

 

11. List the ShipmentID, ShipperName, ItemID, Description, and City for all shipped items purchased in Singapore or Manila.

 

12. Find the number of shipments with insured value greater than 10000.

 

13. Find the average InsuredValue for all shipments.

 

14. List each city and the total weight of items purchased from that city.

 

15. For each shipment, list the shipment id, shipper name, and the maximum Value of items in that shipment.

 

16. List all stores and the average selling value of the shipped items bought from that store. Display the results sorted, from highest average value to lowest average value.

17. For each shipment that contains at least 3 items, list the shipment id, shipper name, and the maximum Value of items in that shipment. Present results sorted by shipper name.

 

18. (Extra Credit) List the ShipmentID of shipments that have at least two items: one purchased in Singapore and one purchased in Manila. Display the results sorted by ShipmentID. Hint:

  i) any condition that specifies City = “Singapore” AND City = “Manila” is always false

  ii) You can specify the same table multiple times in the FROM clause (you can join a table with itself), just rename them

 

19. (Extra Credit) List the maximum weight for an item shipped after '2010-01-01' (DepartureDate after ‘2010-01-01’).

                                                         

 

To help you with debugging, below are the results I obtained from running the queries on the sample data. You should get something similar, but obtaining the same results as I did on the sample data does not guarantee that your SQL is correct. Your SQL query should work for ANY data in the database.

 

1a

 

 

1

QE Dining Set

2009-04-07

Eastern Treasures

Manila

2

403405

0.01774

 

2

Willow Serving Dishes

2011-07-09

Jade Antiques

Singapore

75

102

0.5903

 

3

Large Bureau

2011-07-09

Eastern Sales

Singapore

8

2000

0.5903

 

4

Brass Lamps

2011-08-20

Jade Antiques

Singapore

40

50

0.5902

1b

 

1

ABC Trans-Oceanic

2008651

2009-12-10

2010-03-15

15000

 

2

ABC Trans-Oceanic

2009012

2011-01-10

2011-02-10

12000

 

3

Worldwide

49100300

2011-05-05

2011-06-10

27500

 

4

International

399400

2011-08-20

2011-08-30

7500

 

5

Worldwide

84899440

2011-09-01

2011-10-01

25000

 

6

International

488955

2011-09-02

2011-10-03

18000

1c

 

 

4

1

4

 

1200

 

4

2

3

9500

 

5

1

2

4500

 

5

2

3

3500

 

5

3

1

200

 

 

 

 

2

 

Manila

 

Singapore

3

 

1

QE Dining Set

Eastern Treasures

Manila

2

4

Brass Lamps

Jade Antiques

Singapore

40

3

Large Bureau

Eastern Sales

Singapore

8

4

Jade Antiques

Eastern Sales

5

1

ABC Trans-Oceanic

2008651

15000

6

 

1

QE Dining Set

Eastern Treasures

7156.40471586958

 

2

Willow Serving Dishes

Jade Antiques

60.2106024026871

 

3

Large Bureau

Eastern Sales

1180.60004711151

 

4

Brass Lamps

Jade Antiques

29.5100003480911

7

5

1

200

QE Dining Set

2009-04-07

5

2

4500

Willow Serving Dishes

2011-07-09

4

3

9500

Large Bureau

2011-07-09

5

3

3500

Large Bureau

2011-07-09

4

4

1200

Brass Lamps

2011-08-20

8

 

International

2011-08-20

 

International

2011-08-20

 

Worldwide

2011-09-01

 

Worldwide

2011-09-01

9

 

1

QE Dining Set

Eastern Treasures

Manila

 

2

Willow Serving Dishes

Jade Antiques

Singapore

 

3

Large Bureau

Eastern Sales

Singapore

 

4

Brass Lamps

Jade Antiques

Singapore

10

 

4

International

 

5

Worldwide

11

5

Worldwide

1

QE Dining Set

Manila

5

Worldwide

2

Willow Serving Dishes

Singapore

4

International

3

Large Bureau

Singapore

5

Worldwide

3

Large Bureau

Singapore

4

International

4

Brass Lamps

Singapore

12

5

13

 

17500

14

Manila

2

Singapore

123

15

4

International

9500

5

Worldwide

4500

16

Eastern Sales

6500

Jade Antiques

2850

Eastern Treasures

200

17

5

Worldwide

4500

18 (Extra credit)

 

5

19 (Extra credit)

75

 

 

Turn in BOTH electronic (due January 27, 2014 before 2359) and paper copies (due before start of lab on January 28, 2014):

 

Electronic:

  1. Upload the yourlastname_yourfirstname_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_youfirstname_lab2.sql.