Introduction to Structured Query Language - Database Processing: Fundamentals, Design, and Implementation - page 115

ShipmentID ShipperName ShipperInvoiceNumber DepartureDate ArrivalDate InsuredValue

1 ABC Trans-Oceanic 2008651 10-Dec-12 15-Mar-13 $15,000.00

2 ABC Trans-Oceanic 2009012 10-Jan-13 20-Mar-13 $12,000.00

3 Worldwide 49100300 05-May-13 17-Jun-13 $20,000.00

4 International 399400 02-Jun-13 17-Jul-13 $17,500.00

5 Worldwide 84899440 10-Jul-13 28-Jul-13 $25,000.00

6 International 488955 05-Aug-13 11-Sep-13 $18,000.00

Figure 2-55
Sample Data for the MI
Database SHIPMENT Table

ShipmentID ShipmentItemID ItemID Value

3 1 1 $15,000.00

4 1 4 $1,200.00

Figure 2-56
Sample Data for the MI
Database SHIPMENT_ITEM
Table

4 2 3 $9,500.00

4 3 2 $4,500.00

and SHIPMENT_ITEM should enforce referential integrity, and although it should cascade up-
dates, it should not cascade deletions. The relationship between SHIPMENT and SHIPMENT_
ITEM should enforce referential integrity and cascade both updates and deletions.
We recommend that you create a Microsoft Access 2013 database named MI-CH02.accdb
using the database schema, column characteristics, and data shown above and then use this
database to test your solutions to the questions in this section. Alternatively, SQL scripts for
creating the MI-CH02 database in Microsoft SQL Server, Oracle Database, and MySQL are
available on our Web site at www.pearsonhighered.com/kroenke .

Write SQL statements and show the results based on the MI data for each of the
following:

a. Show all data in each of the tables.

B. List the ShipmentID, ShipperName, and ShipperInvoiceNumber of all shipments.

C. List the ShipmentID, ShipperName, and ShipperInvoiceNumber for all shipments that
have an insured value greater than $10,000.00.

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

e. Assume DepartureDate and ArrivalDate are in the format MM/DD/YY. List the
ShipmentID, ShipperName, ShipperInvoiceNumber, and ArrivalDate of all shipments
that departed in December.

F. Assume DepartureDate and ArrivalDate are in the format MM/DD/YY. List the
ShipmentID, ShipperName, ShipperInvoiceNumber, and ArrivalDate of all shipments
that departed on the tenth day of any month.