IT360 Lab 2: SQL SELECT

 

DUE: January 24, 2013, 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 are part of the database:

 

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

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

SHIPPED_ITEM(ShipmentID, ShipmentItemNb, ItemID, Value)

 

(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 and Quantity for items with quantity between 5 and 50

 

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

 

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

 

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

 

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

 

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

 

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

 

10. 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.

                                                                       

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

 

12.  List the ShipmentID, ItemID, Description, Store and City for shipped items with value less than 3000

 

13.  List the ShipmentID, ShipperName, InsuredValue, ItemID and Value for all items shipped in shipments with InsuredValue less than or equal to 15000

 

14. 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.

 

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

 

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

 

17. Find the average InsuredValue for all shipments.

 

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 purchased Quantity for an item shipped after '2010-01-01' (DepartureDate after ‘2010-01-01’).

                                                         

20. (Extra Credit) List the total shipped Value of items purchased in Singapore.

 

 

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

 

3

Large Bureau

8

 

4

Brass Lamps

40

4

 

2

ABC Trans-Oceanic

2009012

 

1

ABC Trans-Oceanic

2008651

 

6

International

488955

 

4

International

399400

 

5

Worldwide

84899440

 

3

Worldwide

49100300

5

 

1

ABC Trans-Oceanic

2008651

15000

 

2

ABC Trans-Oceanic

2009012

12000

 

3

Worldwide

49100300

27500

 

5

Worldwide

84899440

25000

 

6

International

488955

18000

6

 

1

ABC Trans-Oceanic

2008651

 

2

ABC Trans-Oceanic

2009012

7

 

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

8

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

9

 

 

4

International

399400

4

Brass Lamps

2011-08-20

1200

 

4

International

399400

3

Large Bureau

2011-07-09

9500

 

5

Worldwide

84899440

2

Willow Serving Dishes

2011-07-09

4500

 

5

Worldwide

84899440

3

Large Bureau

2011-07-09

3500

 

5

Worldwide

84899440

1

QE Dining Set

2009-04-07

200

10

 

International

2011-08-20

 

International

2011-08-20

 

Worldwide

2011-09-01

 

Worldwide

2011-09-01

11

 

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

12

 

 

5

1

QE Dining Set

Eastern Treasures

Manila

 

4

4

Brass Lamps

Jade Antiques

Singapore

13

 

4

International

7500

4

1200

 

4

International

7500

3

9500

14

 

4

International

 

5

Worldwide

15

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

16

5

17

 

17500

18 (Extra credit)

 

5

19 (Extra credit)

75

20 (Extra credit)

 

18700

 

 

Turn in BOTH electronic (due January 24, 2013 before 2359) and paper copies (due before start of lab on January 25, 2013):

 

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.