Colonial adventure tours sql

MSAccess - Assignment 2 – Chapter 3 Worksheet (34 pts)

Instructions : Add your SQL statements (using copy & paste) and results (using screen captures) to each question. Do NOT remove any existing content or images from this file.

1. (5 pts) Create a table named ADVENTURE_TRIP. The table has the same structure as the TRIP table.Mod1218

Your SQL CREATE statement to create the ADVENTURE_TRIP table here is shown below:

CREATE TABLE ADVENTURE_TRIP

(TRIP_ID NUMBER PRIMARY KEY,

TRIP_NAME CHAR(75),

START_LOCATION CHAR(50),

STATE CHAR(2),

DISTANCE NUMBER,

MAX_GRP_SIZE NUMBER,

TYPE CHAR(20),

SEASON CHAR(20));

As shown in the following image right click the newly created ADVENTURE_TRIP table, select “Design View”.

Right-click the ADVENTURE_TRIP table and select Design View

Your results should match the following:

Place your results here:

2. (5 pts) Add the following row to the ADVENTURE_TRIP table: trip ID: 45; trip name: Jay Peak; start location: Jay; state: VT; distance: 8; maximum group size: 8; type: Hiking and season: Summer. Note: Strings are case sensitive. For example, enter Jay Peak (not JAY PEAK) as the TRIP_NAME.

Hint: Your results should match these results.

Place your SQL INSERT statement here:

Screen capture the MS Access window that shows the results of running the following SQL command:

SELECT * FROM ADVENTURE_TRIP;

3. (4 pts) Delete the ADVENTURE_TRIP table.

Screen capture the MSAccess window that shows the SQL command and the results of running the command that deletes the ADVENTURE_TRIP table.

Place your SQL command here:

Capture a screen image of all your tables (note: the ADVENTURE_TRIP table will no longer be listed).

Hint: Your results should match these results.

4. Accomplish a screen capture of the description of EACH of the five Colonial Adventure Tours tables (CUSTOMER, RESERVATION, TRIP, TRIP_GUIDES, GUIDE). As you did in the last part of Question 1, right-click each table name, select “Design View” and screen capture the results.

a. (2 pts) CUSTOMER table

Place your results here:

b. (2 pts) RESERVATION table

Place your results here:

c. (2 pts) TRIP table

Place your results here:

d. (2 pts) TRIP_GUIDES table

Place your and results here:

e. (2 pts) GUIDE table

Place your results here:

5. Accomplish a screen capture of the contents of EACH of the five Colonial Adventure Tours tables (CUSTOMER, RESERVATION, TRIP, TRIP_GUIDES, GUIDE). You only need to show up to the first 10 rows of each table. Use the “SELECT” SQL command.

Hint: Check out the bottom of page 74 and Figure 3-21 in your eBook.

a. (2 pts) CUSTOMER table

Place your SQL command and results here:

b. (2 pts) RESERVATION table

Place your SQL command and results here:

c. (2 pts) TRIP table

Place your SQL command and results here:

d. (2 pts) TRIP_GUIDES table

Place your SQL command and results here:

e. (2 pts) GUIDE table

Place your SQL command and results here: