Assignment p
MSAccess - Assignment 3 – Chapter 6 (36 pts)
Instructions : Add your SQL command and results (using copy/paste and screen capture) to each question. Do NOT remove any existing content or images from this file. You MUST include both your SQL Command and Results from running that command in your response to each question.
Take advantage of the “Hints” that accompany each question.
Background: You have been asked by Colonial Adventure Tours to create a PADDLING table that only contains records from the TRIP table with a TYPE = ‘Paddling’. You have also been requested to make some modifications to the PADDLING table.
1. (5 pts) Create a PADDLING table with the structure shown below: Mod1218
Hint: The PADDLING table is similar to the TRIP table that is part of the Colonial Adventure Tours database. The SQL statement that was used to create the TRIP table is shown below.
Model your “CREATE TABLE PADDLING” SQL command after it. Differences between the TRIP and PADDLING tables are:
· There is no START_LOCATION attribute in the PADDLING table
· There is no TYPE attribute in the PADDLING table.
Put your SQL command to create the PADDLING table here:
As shown in the following image right click the newly created PADDLING table, and select “Design View” option.
Right-click the PADDLING table and select Design View
Your results should match the following:
Place your results here:
2. (5 pts) Insert into the PADDLING table the trip ID, trip name, state, distance, maximum group size, and season from the TRIP table for only those trips having type PADDLING.
Put your SQL command to insert data into the PADDLING table here:
Display the full contents of the PADDLING table here:
3. (3 pts) For all trips located in Connecticut (CT), increase the maximum group size by two. Update the PADDLING table accordingly.
Put your SQL command to update the PADDLING table here:
Display the full contents of the PADDLING table here:
4. (3 pts) Insert a new trip into the PADDLING table. The trip ID is 43, the trip name is Lake Champlain Tour, the state is Vermont (VT), the distance is 16, the maximum group size is 12, and the season is Summer.
Put your SQL command to insert a row into the PADDLING table here:
Display the full contents of the PADDLING table here:
5. (3 pts) Delete the trip in the PADDLING table with the trip ID 23.
Put your SQL command to delete a row from the PADDLING table here:
Display the full contents of the PADDLING table here:
6. (3 pts) The distance for the Pontook Reservoir Tour trip is incorrect. While the correct distance is being determined by Colonial Adventure Tours, it needs to be changed to an unknown value. Change the PADDLING table to reflect this change.
Put your SQL command to update a row from the PADDLING table here:
Display the full contents of the PADDLING table here:
7. (3 pts) Add to the PADDLING table a new character column named DIFFICULTY_LEVEL that is three characters in length. Then set the default difficulty level value for all rows to the value MOD.
Put your SQL command to alter the PADDLING table here:
Put your SQL command to update the PADDLING table here:
Display the full contents of the PADDLING table here:
8. (3 pts) Change the DIFFICULTY_LEVEL column in the PADDLING table to HRD for the Lake Champlain Tour trip.
Put your SQL command to update a row of the PADDLING table here:
Display the full contents of the PADDLING table here:
9. (3 pts) Change the length of the SEASON column in the PADDLING table to 25 characters. The SQL command to accomplish this task is shown below. You must replace the ????.
ALTER TABLE ???????
ALTER COLUMN SEASON TEXT(??);
Put your SQL command to alter the PADDLING table here:
As shown in the following image right click the newly created PADDLING table, and select “Design View” option.
Right-click the PADDLING table and select Design View
Your results should match the following:
Place your results here:
10. (3 pts) Change the DIFFICULTY_LEVEL column in the PADDLING table to reject nulls. The SQL command to accomplish this task is shown below. You must replace the ????.
ALTER TABLE ???????
ALTER COLUMN ??????????_????? TEXT NOT NULL;
Put your SQL command to alter the PADDLING table here:
As shown in the following image right click the newly created PADDLING table, and select “Design View” option.
Right-click the PADDLING table and select Design View
Your results should match the following:
Place your results here:
11. (2 pts) Delete the PADDLING table from the database.
Put your SQL command to drop the PADDLING table here:
Screen capture your tables in MSAccess (the PADDLING table should be missing). Place your results here:
MSAccess
-
Assignment 3
–
Chapter 6
(36 pts)
Instructions
:
Add your SQL
command
and results (
using copy/paste and screen capture
) to each
question.
Do NOT
remove
any existing content or images from this file.
You
MUST
include both your
SQL
C
ommand
and
Results
from running that command in your response to each question
.
Take advantage of the “
Hints
” that accompany each question.
Background
:
You have been asked by Colonial Adventure Tours to c
reate a
PADDLING
table
that only contains records from the
TRIP table with a TYPE = ‘Paddling’.
You have also been
requested to make some modifications to the PADDLING table.
1.
(5 pts)
Create a PADD
L
ING table
with
the
structure shown
below:
Mod1218
Hint:
The
PADDLING
table is similar to the
TRIP
table that i
s part of the
Colonial Adventure
Tours
database.
The SQL statement
that was
used to create the
TRIP
table is shown below.
Model your “CREATE TABLE
PADDLING
”
SQL
command
after it.
Differences between the
TRIP
and
PADDLING
tables are:
·
There is no
START_LOCATION
attribute
in the PADDLING table
·
T
h
ere is no
TYPE
attribute in the
PADDLING
table.
Put your SQL command
to create the PADDLING table here:
MSAccess - Assignment 3 – Chapter 6 (36 pts)
Instructions: Add your SQL command and results (using copy/paste and screen capture) to each
question. Do NOT remove any existing content or images from this file. You MUST include both your SQL
Command and Results from running that command in your response to each question.
Take advantage of the “Hints” that accompany each question.
Background: You have been asked by Colonial Adventure Tours to create a PADDLING table
that only contains records from the TRIP table with a TYPE = ‘Paddling’. You have also been
requested to make some modifications to the PADDLING table.
1. (5 pts) Create a PADDLING table with the structure shown below: Mod1218
Hint: The PADDLING table is similar to the TRIP table that is part of the Colonial Adventure
Tours database. The SQL statement that was used to create the TRIP table is shown below.
Model your “CREATE TABLE PADDLING” SQL command after it. Differences between the TRIP
and PADDLING tables are:
There is no START_LOCATION attribute in the PADDLING table
There is no TYPE attribute in the PADDLING table.
Put your SQL command to create the PADDLING table here: