List the trip ids and trip names for each pair of trips that have the same start location

MSAccess - Assignment 3 – Chapter 5 Worksheet (84 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.

1. (4 pts) For each reservation, list the reservation ID, trip ID, customer number, and customer last name. Order the results by the customer’s last name. Mod1218

Put your SQL command and your results here:

2. (4 pts) For each reservation for customer Ryan Goff, list the reservation ID, trip ID, and number of persons.

Put your SQL command and your results here:

3. (4 pts) List the trip name and type of each trip that has Rita Boyers as a guide.

Put your SQL command and your results here:

4. (4 pts) List the trip name of each biking trip that has Rita Boyers as a guide.

Put your SQL command and your results here:

5. (4 pts) For each reservation that has a trip date of July 23, 2016, list the customer’s last name,

the trip name, start location and trip date.

Put your SQL command and your results here:

6. (4 pts) List the trip id, trip name, and the guide’s first and last names for those guides who can lead a paddling trip.

Put your SQL command and your results here:

7. (4 pts) List the first and last name for those guides that can lead a Biking trip. List each guide name only once and sort the results by the guide’s last name.

Put your SQL command and your results here:

8. (4 pts) List the trip name, trip id and customer number of all reservations for hiking trips. Sort the results in ascending order by trip id as the major sort key and customer number as the minor sort key.

Put your SQL command and your results here:

9. (4 pts) Repeat Exercise 8 but use aliases for the table names.

Put your SQL command and your results here:

10. (4 pts) Background: Last week you learned it is possible to place one query inside another. The inner query is called a subquery. The subquery is evaluated first. After the subquery has been evaluated, the outer query can use the results of the subquery to find its results. In this question you will be using a subquery to find all the trips from Maine. These results will be used by the main query to list reservation information for all of these Maine trips.

List the reservation ID, trip ID, and trip date for reservations for a trip in Maine (ME).

In this question you must use the IN operator in your main query to link to your subquery.

Put your SQL command and your results here:

11. (4pts) During the first week of our course we had not begun using MSAccess as our Database Management System. Question #6 of the Week 1 Assignment asked you the following:

Your task to is answer the exact same question,

“List the trip name of each trip that has Hal Rowan as a guide”

but use MSAccess and SQL this time.

Put your SQL command and your results here:

12. (4pts) During the first week of our course we had not begun using MSAccess as our Database Management System. Question #10 of the Week 1 Assignment asked you the following:

Your task to is answer the exact same question,

“List the last name of each customer that has a reservation for a trip in Masschusetts (MA)”

but use MSAccess and SQL this time.

NOTE: Your results may list ‘Northfold’ twice. If you precede the LAST_NAME attribute in your SELECT clause with the key word DISTINCT, duplicate records will be removed

Put your SQL command and your results here:

13. (4 pts) List the trip IDs for each pair of trips that have the same start location. (For example, one such pair would be trip ID 2 and trip ID 3, because the start location of both trips is Weathersfield.) The first trip ID listed should be the major sort key, and the second trip ID should be the minor sort key.

Background: Although it may seem strange, it is actually possible to “join” a single table to itself. This is called a “self join”. Reference section “Joining a Table to Itself” on page 143 in your eBook for supporting information. A “self-join” is necessary when the query is comparing values within the same table. In this question, you are looking for pairs of trips that have the same start location. In order to build this type of query it is necessary to distinguish both instances of the same table. This is accomplished by using a table “alias”. Check out “FIGURE 5-12: Using aliases for a self-join” in your eBook for a related example. In the following hint “F” is the alias for the first instance of the table TRIP and “S” is the alias for the second instance of the TRIP table. By the way, there is nothing “magical” about “F” and “S”. They were simply chosen because it makes it easier to keep track of the two instances of TRIP.

Put your SQL command and your results here:

NOTE: The next question consists of two parts: 14a and 14b.

14a. (4pts) Background: Generate the single table query that lists the MAX_GRP_SIZE of all paddling trips.

Put your SQL command and your results here:

14b. (4pts) List the trip ID, trip name and maximum group size for each trip whose maximum group size is greater than the maximum group size of every paddling trip. You MUST use the ALL operator.

Put your SQL command and your results here:

NOTE: The next question consists of three parts: 15a, 15b and 15c.

15a. (2 pts) List the customer number and last name of each customer that lives in the state of Connecticut (CT).

Put your SQL command and your results here:

15b. (4 pts) List the customer number and last name of each customer that has a reservation involving four people.

Put your SQL command and your results here:

15c. (2 pts) List the number and name of each customer that either lives in the state of Connecticut (CT), or that currently has a reservation for 4 people. NOTE: This question simply requires that you combine the results of the first query (15a) with the results of the second query (15b).

Your first query was based on this table

Your second query was based on these two tables

Put your SQL command and your results here:

In addition, explain, in English, why your results only contain 7 records (in view of the 4 records returned from the top half of the UNION and 4 records returned from the bottom half of the UNION). In other words, why did the results not contain a total of 8 results.

Put your explanation here:

16. (4 pts) List the trip ID, trip name, and reservation ID for all trips. All trips should be included in the result. For those trips that currently do not have reservations, the reservation ID should be left blank. Order the results by trip ID.

Put your SQL command and your results here:

17. (4 pts) Laura Jones wants confirmation on her upcoming trip that she believes is on June 15, 2016. Your records indicate that Laura Jones’ customer number is '112'. To prepare the information you will pass along to Ms Jones, generate the query that lists the customer’s last and first name, the trip name, the trip date and the guide’s last and first name for customer number '112'.

Put your SQL command and your results here:

Did Ms Jones remember the right date for her trip?

18. (10 pts) On this last question you will be writing your own question and generating the SQL command to answer that question. Your query must involve two tables. Have fun with this one.

Put your question here:

Put your SQL command and your results here:

MSAccess

-

Assignment

3

–

Chapter

5

Worksheet

(84 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.

1.

(4 pts)

For each reservation, list the reservation ID, trip ID, customer number, and customer

last

name. Order

the results by the customer’s last name.

Mod1218

MSAccess - Assignment 3 – Chapter 5 Worksheet (84 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.

1. (4 pts) For each reservation, list the reservation ID, trip ID, customer number, and customer

last name. Order the results by the customer’s last name. Mod1218