OBJECTIVES
Use SQL commands and then Access to Create a table, Insert data into that table and Select data from that table.
Read all Instructions before beginning
–
contact the instructor with any questions
GENERAL INFORMATION:
First time SQL users and those who have not previously used the W3Schools Web site should read the attached document labelled SQL_Website_Guide.
Exercise Instructions:
Part A SQL (70%)
Use the following Web site
https://www.w3schools.com/sql/default.asp
to complete the SQL portion of the exercise.
For each Task:
a) Label the Task to match the instructions (Task 1, Task 2, etc.)
b) Construct the appropriate SQL command
c) Paste (or type) the SQL command into the appropriate area of Web site
click the “Run SQL” button to execute the command
d) Capture the entire Website response (either the Window or the entire screen) and paste it below the SQL command text.
e) Ensure the SQL text above the screen capture of the response is the same as the SQL command executed on the Website.
If you have difficultly – send the SQL command text, and a copy of the Website’s response to the instructor for assistance.
Task 1
– Use the SQL Create command to create a table with the below specifications
The table name must be Your Last Name plus a unique number
Use the following to name each field (column); with the matching data type
Specify that the InventoryID field is the primary key.
FIELD NAME DATA TYPE
InventoryID 8 numeric digits (no decimal points)
ProductName character field – up to 27 characters
UnitCost decimal field (10 digits including 2 digits after the decimal point)
Warehouse character field – up to 12 characters
Task 2
– Use the Insert command to insert the records / data below:
It is recommended that you Insert one record at a time
10100, Salmon, 18.50, Warehouse1
10101,Tofu, 20, Warehouse2
10102 , Squash, 16, Warehouse1
10103, Brussel Sprouts, 18, Warehouse1
10104 , Chocolate, 9.50, Warehouse2
Insert one more record – you determine the ProductName, it must have a unique InventoryID, a UnitCost of 12 and Warehouse1 for the Warehouse value.
Task 3
Use the Select * command to list all fields for all records in the table.
Task 4
Use a Select command to list ProductName, UnitCost and Warehouse for all products that have a UnitCost of 16 or 18
Task 5
Use a Select command to list ProductName, UnitCost and Warehouse for all products that are stored in Warehouse1.
Task 6
Use a Select command to list ProductName, UnitCost and Warehouse for all products that are stored in Warehouse1 or have a UnitCost greater than 16.
Task 7
Display the number of products that are stored in Warehouse2. (review the Select Count command on the Website)
Submit this part of the assignment as a single word document. Name your document Last_Name_SQLWeb (i.e. Smith_SQLWeb).
Part B Access Database (30%)
Perform the same tasks using MS Access (use the GUI / QBE interface)
1) Create the table (same table name, field names and field types as in Part A)
2) Enter (type) the same data as in Part A
3) Create and Save a Query to complete Task 3 above with the name Task 3
4) Create and Save a Query to complete Task 4 above with the name Task 4
5) Create and Save a Query to complete Task 5 above with the name Task 5
6) Create and Save a Query to complete Task 6 above with the name Task 6
7) Create and Save a Query to complete Task 7 above with the name Task 7
Name the Access database Last Name_Assignment3 (i.e. Smith_ Assignment3).
Ensure you attach and submit BOTH files
(a word document for Part A and an Access database for Part b)