How many trips originate in each state

MSAccess - Assignment 2 – Chapter 4 Worksheet (86 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.

The Entity Relationship Diagram (ERD) for the Colonial Adventure Tours database is shown below. You need this diagram to know the table and attribute names.

1. (4 pts) List the guide number, last name, first name and hire date of each guide. Mod1218

Put your SQL command and your results here:

2. (4 pts) List the trip name of each trip that has the type Biking.

Put your SQL command and your results here:

3. (4 pts) List the last name and state of each guide that lives in Massachusetts (MA).

Put your SQL command and your results here:

4. (4 pts) List the last name and state of each guide that does NOT live in Massachusetts (MA).

Put your SQL command and your results here:

5. (4 pts) List the trip name and distance of each trip that has the type Hiking and that has a distance longer than 10 miles.

Put your SQL command and your results here:

6. (4 pts) You are interested in a biking trip in the early fall for more than 10 individuals. List all the columns for those trips that meet your requirements.

Put your SQL command and your results here:

7. (4 pts) List the reservation ID and trip price of those reservations that have a trip price that is greater than or equal to $40 but less than or equal to $60.

Put your SQL command and your results here:

8. (4 pts) List the customer number, customer last name, customer first name , city and state of each customer that lives in New Jersey (NJ), New York (NY) or Pennsylvania (PA). Use the IN operator in your command.

.

Put your SQL command and your results here:

9. (4 pts) Repeat the previous question and sort the records by state in ascending order and then by customer last name in descending order.

Put your SQL command and your results here:

10. (4 pts) How many trips are in the state of Massachusetts (MA)?

Put your SQL command and your results here:

11. (4 pts) How many trips originate in each state?

Put your SQL command and your results here:

12. (4 pts) Find the name of each trip containing the word “ Pond ”.

Put your SQL command and your results here:

13. (4 pts) Colonial Adventure Tours calculates the total price of a trip by adding the trip price plus other fees and multiplying the result by the number of persons included in the reservation. List the reservation ID, trip ID, customer number, number of persons and total price for all reservations where the number of persons is greater than four. Use the column name TOTAL_PRICE for the calculated field.

Put your SQL command and your results here:

14. (4 pts) List the guide’s last name, first name and hire date for all guides that were hired before June 10, 2013. Order your results by the hire date.

Put your SQL command and your results here:

15. (4 pts) What is the minimum distance and maximum distance for each type of trip?

Put your SQL command and your results here:

16. (4 pts) Display the different seasons in which trips are offered. List each season only once.

Put your SQL command and your results here:

17. (4 pts) List the trip IDs for paddling trips.

Put your SQL command and your results here:

18. (4 pts) List the reservation IDs for reservations that are for a paddling trip. You must use a subquery.

Put your SQL command and your results here:

19. (4 pts) What is the total number of persons for all reservations.

Put your SQL command and your results here:

20. (10 pts) On this last question you will be writing your own question and generating the SQL command to answer that question. In addition to the typical SELECT clause and FROM clause you must also include a WHERE clause with an AND clause. Have fun with this one.

Put your question here:

Put your SQL command and your results here:

MSAccess

-

Assignment 2

–

Chapter 4

Worksheet

(86 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 runnin

g that command in your response to

each question.

Take advantage of the “

Hints

” that accompany each question.

The Entity Relationship Diagram (ERD) for the

Colonial Adventure Tours database is shown below.

You

need this

diagram to know the table and attribute names.

MSAccess - Assignment 2 – Chapter 4 Worksheet (86 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.

The Entity Relationship Diagram (ERD) for the Colonial Adventure Tours database is shown below. You

need this diagram to know the table and attribute names.

Applied Sciences

Architecture and Design

Biology

Business & Finance

Chemistry

Computer Science

Geography

Geology

Education

Engineering

English

Environmental science

Spanish

Government

History

Human Resource Management

Information Systems

Law

Literature

Mathematics

Nursing

Physics

Political Science

Psychology

Reading

Science

Social Science

Home

Blog

Archive

Contact

google+twitterfacebook

Copyright © 2019 HomeworkMarket.com