SY306 Web and Databases for Cyber Operations
In this lab you will develop your database implementation skills by creating and modifying tables in a MySQL database, and by inserting, deleting, modifying, and querying data using SQL.
1. Connect to your MySQL database created on the department’s database server mope by using the command line client:
mysql –u mXXXXXX –p –h mope.academy.usna.edu mXXXXXX
You will use the command line client to test all the SQL statements for this lab. Each statement mush end with ;
2. You must create a folder on you Web drive called "Lab09" (without the quotes) and store your work in that directory.
3. Create all of your answers within a text file named yourlastname_lab09.sql
4. Follow exactly the following format for your file:
# Name alpha
# {1}
/* question 1 here */
Answer 1 here
/* other comments if desired */
# {2}
/* question 2 here */
Answer 2 here
etc
5.
Your format must be what is shown above # {1},
do not add any letters (such as 1A), use only the problem number
6. Add comments as needed with a #, -- , or multi line comments can be enclosed in a C style /* ---- */ fashion
7. Hint: You can test your work by running your sql file from the command line!
Important Note: Your
queries should run correctly on any data, not just the sample data currently in
the database.
Write all your
answers in the yourlastname_lab09.sql file, in the format specified above.
Part A: Write the
SQL statements to accomplish the following tasks:
1.
Create the following 3 tables for your online store. The
primary keys are underlined, and the foreign keys are shown in italic. You can
personalize the tables if needed to be fit to store the information you need
for the products you sell on your website.
PRODUCT(ProductID,
PName, PDescription, Price)
– this table records information about each product. The table must have a
numeric primary key
SALE(SaleID, DeliveryAddress, CreditCard) – this table records information about each sale/transaction. SaleID must be a surrogate key – created by MySQL. Hint: Declare
SaleID int
AUTO_INCREMENT in the column declaration for SaleID
SALEITEM(SaleID, ProductID, Quantity) – this
table records information about which products were sold in each
sale/transaction. You don’t have to specify the foreign keys at this point,
only the primary key. Hint: The primary key can be a combination of columns.
2. Insert 3 products in your PRODUCT table.
3. Update the price of one of your products to cost $10 more than original price.
4. Delete all products with price higher than 10,000 from your PRODUCT table. Hint: You can insert such a product first and then use this command to delete it for testing purposes
5. Modify the SALEITEM table (using ALTER) to add the foreign key constraints for SaleID and ProductID.
6.
Write the SQL query to list the SaleID,
ProductID, PName, PDescription, Price, Quantity and DeliveryAddress
for all sales with delivery address containing the string ‘MD’ anywhere in the
address. The results should be displayed in ascending order by SaleID and ProductID.
Part B:
For
this part, 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:
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
Download the createMadisonImports.sql file from the course website, save it
to your Lab09 folder, and copy-paste the contents in the MySQL command line
client to execute the entire file. It should create the tables for Madison
Importing, with some data in them.
Now, write SQL
queries to accomplish the following tasks.
7. 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
8. List all the cities from the ITEMS table. Each city should only appear once in the result (no duplicates)
9. 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.
10. List all Stores where an item with expected selling value (from SHIPPED_ITEM) higher than 0900 was purchased. Each store should appear only once in the result.
11. List the ShipmentID, ShipperName, ShipperInvoiceNumber, and InsuredValue for all shippers whose name starts with 'AB' and the insured value is greater than $13000.
12. 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.
13. List the ShipmentID, ShipperName, ItemID, Description, and City for all shipped items purchased in Singapore or Manila.
14. (Extra credit) For all items, show ItemID, Description, Store and a calculated column named USCurrencyAmount that is equal to the PriceInLocalCurrency multiplied by ExchangeRate. Hint: look at the examples of using SQL Alias at http://www.w3schools.com/sql/sql_alias.asp
15. (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
16. (Extra Credit) List the maximum weight for an item shipped after '2009-01-01' (DepartureDate after ‘2009-01-01’).
To help you with
debugging, below are the results I obtained from running the queries in Part B 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 queries should work
for ANY possible data in the database.
7a
|
|
|
7b
|
|
1 |
|
ABC Trans-Oceanic |
2008651 |
2009-12-09 |
2009-03-15 |
15000 |
|
|
2 |
|
ABC Trans-Oceanic |
2009012 |
2011-01-09 |
2011-02-09 |
12000 |
|
|
3 |
|
Worldwide |
49090300 |
2011-05-05 |
2011-06-09 |
27500 |
|
|
4 |
|
International |
399400 |
2011-08-20 |
2011-08-30 |
7500 |
|
|
5 |
|
Worldwide |
84899440 |
2011-09-01 |
2011-09-01 |
25000 |
|
|
6 |
|
International |
488955 |
2011-09-02 |
2011-09-03 |
18000 |
7c
|
|
|
|
|
|
|
8
|
|
Manila |
|
|
Singapore |
9
|
|
|
|||||||||||||||||
10
|
|||||
|
|
|
|
|
||
|
|
|||||
|
|
|||||
|
|
11
|
|
||||||||||||
12
|
|
International |
2011-08-20 |
|
|
International |
2011-08-20 |
|
|
Worldwide |
2011-09-01 |
|
|
Worldwide |
2011-09-01 |
13
|
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 |
14(Extra credit)
|
|
1 |
QE Dining Set |
Eastern Treasures |
7156.40471586958 |
|
|
2 |
Willow Serving Dishes |
Jade Antiques |
60.2096024026871 |
|
|
3 |
Large Bureau |
Eastern Sales |
1180.60004711151 |
|
|
4 |
Brass Lamps |
Jade Antiques |
29.5090003480911 |
15 (Extra credit)
|
|
5 |
16 (Extra credit)
75
Deliverables:
3. Turn
in (due before lab on Friday after Spring Break):
a)
Paper submission: turn in the following hardcopy
at the beginning of class on the due date, stapled together in the following
order (coversheet on top):
i.
A completed assignment coversheet.
Your comments will help us improve the course.
ii. A printout of the yourlastname_lab09.sql file containing all the questions and the SQL statements
b) Electronic submission: For this lab, we will use the CS Department submit system: submit.cs.usna.edu. Submit your yourlastname_lab09.sql for SY306 assignment Lab09.