Question

I need the SQL statements for these questions:

6. List the reservation IDs for reservations that are for a paddling trip. (Hint: Use a subquery.)

7. What is the longest distance for a biking trip?

8. 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.)

9. How many current reservations does Colonial Adventure Tours have and what is the total number of persons for all reservations?

10. List the reservation ID and trip ID for all trips that occur in the month of July, 2016. There are multiple ways to create this query.

here are the tables:

CUSTOMER FIRST NAME POSTAL CODE CUSTOMER NUM LAST NAME ADDRESS Northfold Liam 9 Old Mill Rd Londonderry 10 NH 03053 103 Kasuma Sujata 132 Main St. #1 East Hartford CT 06108 MA 01854 Goff 164A South Bend Rd. Lowell Ryan 105 McLean Kyle 345 Lower Ave. Wolcott NY 14590 106 Morontoia Joseph 156 Scholar St Johnston 02919 107 Marchand Quinn 76 Cross Rd Bath NH 03740 108 32 Sheep stop St Edinboro PA Rulf Usch 16412 109 Caron Jean Luc 10 Greenfield St Rome ME 04963 110 Martha York Bers 65 Granite St NY 14592 112 Jones Laura 373 Highland Ave. Somerville MA 02143 115 Nu Adam 1282 Ocean Walk Ocean City Vaccari 08226 116 Murakami Iris 7 Cherry Blossom St. Weymouth MA 02188 119 Londonderry vT Chau 18 Ark Ledge Ln Clement 05148 120 Gernowski Sadie 24 stump Rd. Athens ME 04912 121 Cambridge VT Bretton-Borak Siam 10 Old Main St 122 Hefferson orlagh 132 South St. Apt 27 Manchester NH 03101 123 25 Stag Rd Fairfield Barnett Larry 06824 124 Busa Karen 12 Foster St. South Windsor CT 06074 125 51 Fredrick St Albion Peterson Becca NY 14411 126 Brown Brianne 154 Central St Vernon CT 06066 PHONE Click to Add 603-555-7563 413-555-3212 860-555-0703 781-555-8423 585-555-5321 401-555-4848 603-555-0456 814-555-5521 207-555-9643 585-555-0111 857-555-6258 609-555-5231 617-555-6665 802-555-3096 207-555-4507 802-555-3443 603-555-3476 860-555-9876 857-555-5532. 585-555-0900 860-555-3234

Answer 1

Below are the required Queries:

6)
Select R.Reservation_ID
From Reservation R
Where R.Trip_ID IN (Select distinct Trip_ID From Trip Where TYPE = 'Paddling')

7)
Select Max(Distance) From Trip
Where Type = 'Biking'

8)
Select Trip_Id, SUM(Trip_Price)
From Trip Group By Trip_Id HAVING Count(Trip_Id) > 1

9)
How many current reservations does Colonial Adventure Tours
have and what is the total number of persons for all reservations?
--- What is Colonial Adventure Tours column name in whihc table.

--- Total number of person in all reservation
SUM(Num_Persons) As TotalNumberOfPerson
From Reservation

10)
Select Reservation_Id, Trip_id From Reservation
Where Trip_Date >= CONVERT(DATETIME, '07/01/2016',101) AND Trip_Date <= CONVERT(DATETIME, '07/31/2016',101)