Use SQL and the Colonial Adventure Tours database to complete the following exercises. Provide screen snips...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Use SQL and the Colonial Adventure Tours database to complete the following exercises. Provide screen snips of query outputs 5. List the customer number, customer last name, and customer first name of each customer that lives in New Jersey (NJ), New York (NY) or Pennsylvania (PA). Use the IN operator in your command. 6. Repeat Exercise 5 and sort the records by state in descending order and then by customer last name in ascending order. 7. How many trips are in the states of Maine (ME) or Massachusetts (MA)? 8. How many trips originate in each state? 9. How many reservations include a trip price that is greater than $20 but less than $75? 10. How many trips of each type are there? 11. 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, 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. 12. Find the name of each trip containing the word "Pond." 13. List the guide's last name and guide's first name for all guides that were hired before June 10, 2013. 14. What is the average distance and the average maximum group size for each type of trip? 15. Display the different seasons in which trips are offered. List each season only once. 16. List the reservation IDs for reservations that are for a paddling trip. (Hint: Use a subquery.) 17. What is the longest distance for a biking trip? 18. For each trip in the RESERVATION table that has more than one reservation, group by trip ID and sum the trip price. (Hint: Use the COUNT function and a HAVING clause.) 19. How many current reservations does Colonial Adventure Tours have and what is the total number of persons for all reservations? Use SQL and the Colonial Adventure Tours database to complete the following exercises. Provide screen snips of query outputs 5. List the customer number, customer last name, and customer first name of each customer that lives in New Jersey (NJ), New York (NY) or Pennsylvania (PA). Use the IN operator in your command. 6. Repeat Exercise 5 and sort the records by state in descending order and then by customer last name in ascending order. 7. How many trips are in the states of Maine (ME) or Massachusetts (MA)? 8. How many trips originate in each state? 9. How many reservations include a trip price that is greater than $20 but less than $75? 10. How many trips of each type are there? 11. 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, 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. 12. Find the name of each trip containing the word "Pond." 13. List the guide's last name and guide's first name for all guides that were hired before June 10, 2013. 14. What is the average distance and the average maximum group size for each type of trip? 15. Display the different seasons in which trips are offered. List each season only once. 16. List the reservation IDs for reservations that are for a paddling trip. (Hint: Use a subquery.) 17. What is the longest distance for a biking trip? 18. For each trip in the RESERVATION table that has more than one reservation, group by trip ID and sum the trip price. (Hint: Use the COUNT function and a HAVING clause.) 19. How many current reservations does Colonial Adventure Tours have and what is the total number of persons for all reservations?
Expert Answer:
Related Book For
Concepts of Database Management
ISBN: 978-1285427102
8th edition
Authors: Philip J. Pratt, Mary Z. Last
Posted Date:
Students also viewed these physics questions
-
Use SQL and the Colonial Adventure Tours database (Figures 1-4 through 1-8 in Chapter 1) to complete the following exercises. If directed to do so by your instructor, use the information provided...
-
Use SQL and the Colonial Adventure Tours database (Figures 1-4 through 1-8 in Chapter 1) to complete the following exercises. If directed to do so by your instructor, use the information provided...
-
The following exercises are based on the Colonial Adventure Tours database shown in Figures 1-15 through 1-19 in Chapter 1. No computer work is required. 1. Using the types of entities found in the...
-
Calculate investment balance four years after acquisition Ray Corporation owns a 40 percent interest in the outstanding common stock of Ton Corporation, having acquired its interest for $2,400,000 on...
-
How much will you have in 36 months if you invest $ 75 a month at 10% interest?
-
The surface tension of a liquid can be determined by measuring the force F needed to just lift a circular platinum ring of radius r from the surface of the liquid. (a) Find a formula for y in terms...
-
Helen, a Hollywood actress, was required to do a dangerous stunt for an adventure movie. She insisted that a stunt actress perform the dangerous stunt, but the director wanted a close-up shot of...
-
Nature Hill Middle School wants to raise money for a new sound system for its auditorium. The primary fund-raising event is a dance at which the famous disc jockey Jay Dee will play classic and...
-
Other than the wind speed, what factor has the most impact on the amount of power generated by a wind turbine? Swept area of rotor Turbine style Terrain Blade material.
-
Multiple regression is used by accountants in cost analysis to shed light on the factors that cause costs to be incurred and the magnitudes of their effects. Sometimes, it is desirable to use...
-
Below are incomplete financial statements for Benson. Incorporated. Requlred: Calculate the missing amounts. table [ [ BENSON , INCORPORATED ] , [ Income Statement ] , [ Year ended Decem ber 3 1 , 2...
-
A rigid block of mass M is mounted on four elastic supports and the spring constant of each is k, as shown in the figure below. A small mass m drops from a height L and adheres to the rigid block...
-
Lindon Company is the exclusive distributor for an automotive product that sells for $24.00 per unit and has a CM ratio of 30%. The company's fixed expenses are $118,800 per year. The company plans...
-
Assignment: Prerequisite Assessment - Now that you have unwrapped your chosen standard and reviewed the corresponding standard for the grades below and above, you need to create a diagnostic...
-
Co. A is a newly formed business. During its first year of operations, credit sales were $60,000, sales returns were $10,000 and collections on credit sales were $32,000. One customer account for...
-
Objectives and the Timeline: The objective of this project is to conduct an in-depth ratio analysis of a firm's performance in last five years and forecast its performance of next year using the...
-
What net force (in N) is required to bring a 1500 kg car to rest from a speed of 100 km/h within distance of 55 m? Vo = 100 km/h x = 0 V = 0 x (m) x = 55 m
-
6. (Potential Energy and Conservation of Energy) What should be the spring constant k of a spring designed to bring a 1200-kg car to rest from a speed of 95 km/h so that the occupants undergo a...
-
What is an OODBMS?
-
What is a fact table in a data warehouse?
-
How can you get more information from the same amount of data by using a database approach instead of a non-database approach?
-
Powerhouse Ltd purchased machinery on 2 January 2019, at a cost of $800 000. The machinery is depreciated using the straightline method over a useful life of 8 years with a residual value of $80 000....
-
The purchases and sales of Big Flower Pty Ltd of one brand of lawn fertiliser for the year ended 31 December 2019 are contained in the schedule below. The selling price up to 30 June was $12 per unit...
-
In groups of four or five, consider the following information. On 1 July 2019, Stevenson Pty Ltd, a proprietary company with three shareholders, acquired some property by issuing 100 000 shares to...
Study smarter with the SolutionInn App