StudyDaddy Computer Science SQL. I have 35 written questions and 5 SQL assignments.I will provide all three Query languages for the database and the ER Diagram. For the SQL assignments, you must provide both the SQL statement

SQL. I have 35 written questions and 5 SQL assignments.I will provide all three Query languages for the database and the ER Diagram. For the SQL assignments, you must provide both the SQL statement

CIS240 – Assignment 3 Student Key Multiple-Table Queries For each question below, write and execute the appropriate SQL statement in the Database Management System of your choice using the Colonial Adventure Tours database. Please provide both the SQL statement and the output from your statement. You may copy and paste or provide screen shots of your work. If you provide your work in a separate document, please be certain to number your responses. Submit your completed assignment to the appropriate Assignment Submission Folder in the course shell by the due date indicated on the Course Schedule.

15 points 1. For each reservation, list the trip ID, trip name, state, trip date, and number of persons. Order the results by trip date. (1pt) 2. For each customer who lives in the state of New York, list the reservation id, trip date, and total cost (trip price + other fees) of any trips they have reserved. (1pt)TripID TripName State TripDate NumPersons 40 Wachusett Mountain MA 2018-03-26 2 2 Mt Ascutney - North Peak VT 2018-05-14 3 21 Long Pond MA 2018-06-08 2 21 Long Pond MA 2018-06-08 1 32 Northern Forest Canoe Trail NH 2018-06-11 3 1 Arethusa Falls NH 2018-06-12 4 32 Northern Forest Canoe Trail NH 2018-06-18 1 39 Welch and Dickey Mountains Hike NH 2018-06-18 3 39 Welch and Dickey Mountains Hike NH 2018-06-25 5 22 Long Pond Tour ME 2018-07-09 8 8 Black Pond NH 2018-07-09 1 11 Chocorua Lake Tour NH 2018-07-23 6 10 Mt. Cardigan - Firescrew NH 2018-07-23 1 15 Crawford Path Presidentials Hike NH 2018-07-25 6 28 Mount Garfield Hike NH 2018-08-27 2 25 Mount Battie Ride ME 2018-08-29 2 38 Sawyer River Ride NH 2018-09-11 2 38 Sawyer River Ride NH 2018-09-11 1 38 Sawyer River Ride NH 2018-09-11 2 28 Mount Garfield Hike NH 2018-09-12 2 28 Mount Garfield Hike NH 2018-09-12 1 3 Mt Ascutney - West Peak VT 2018-09-15 3 38 Sawyer River Ride NH 2018-09-18 4 4 Bradbury Mountain Ride ME 2018-09-19 4 12 Cadillac Mountain Ride ME 2018-10-01 2 12 Cadillac Mountain Ride ME 2018-10-01 2 26 Mount Cardigan Hike NH 2018-10-16 4 ReservationID TripDate TotalCost 1800005 2018-06-25 55 1800025 2018-09-11 115 3. List the trip name, type, and trip price for each reservation that has 2 people. (1pt) 4. List the name of each hiking trip that has a $15 fee on its reservation. (1pt) 5. List the last name and first name of each guide along with the trip name and type for each trip that they are scheduled to lead. Order the results by the guides' last names. (2pts)TripName Type TripPrice Wachusett Mountain Hiking 55 Long Pond Hiking 95 Mount Garfield Hike Hiking 35 Sawyer River Ride Biking 90 Cadillac Mountain Ride Biking 40 Mount Battie Ride Biking 110 Mount Garfield Hike Hiking 35 Sawyer River Ride Biking 70 Cadillac Mountain Ride Biking 40 TripName Mount Cardigan Hike Crawford Path Presidentials Hike LastName FirstName TripName Type Abrams Miles Long Pond Hiking Abrams Miles Mt Ascutney - North Peak Hiking Abrams Miles McLennan Reservation Hike Hiking Boyers Rita Bradbury Mountain Ride Biking Boyers Rita Black Pond Hiking Boyers Rita Welch and Dickey Mountains Hike Hiking Boyers Rita Mt Adams Hiking Boyers Rita Westfield River Loop Biking Boyers Rita Cadillac Mountain Ride Biking Boyers Rita Mount Garfield Hike Hiking Boyers Rita Big Rock Cave Hiking Boyers Rita Mount Battie Ride Biking Devon Harley Metacomet-Monadnock Trail Hike Hiking Devon Harley Masons Farm Paddling Devon Harley Lower Pond Tour Paddling Devon Harley Chocorua Lake Tour Paddling Devon Harley Wachusett Mountain Hiking Gregory Zach Crawford Path Presidentials Hike Hiking Gregory Zach Pisgah State Park Ride Biking Gregory Zach Mt. Cardigan - Firescrew Hiking Gregory Zach Arethusa Falls Hiking Gregory Zach Mount Cardigan Hike Hiking Gregory Zach Bradbury Mountain Ride Biking Gregory Zach Mt. Chocorua Hiking Kelly Sam Cannon Mtn Hiking Kelly Sam Cherry Pond Hiking Kelly Sam Low Bald Spot Hike Hiking Kelly Sam Pondicherry Trail Ride Biking Kelly Sam Sawyer River Ride Biking Kiley Susan Baldpate Mountain Hiking Kiley Susan Chocorua Lake Tour Paddling Kiley Susan Northern Forest Canoe Trail Paddling Kiley Susan Pontook Reservoir Tour Paddling 6. Using a subquery, list the reservation ID and trip date for each reservation made by customers who live in the city of Londonderry. (1pt) 7. List the trip date and number of persons for each trip where Sam Kelly is a guide (use his name as the criteria). (2pts) 8. List the reservation id, customer number, and type for each trip where the trip price is greater than or equal to $90 and the reservation is for June. (2pts) 9. List the trip name, trip price, and other fees for any reserved trip that is longer than the Sawyer River Ride. Make sure you use the trip name for you criteria rather than hard- coding the distance value. (2pts)Rowan Hal Cadillac Mountain Hiking Rowan Hal Huguenot Head Hike Hiking Rowan Hal Blueberry Mountain Hiking Rowan Hal Seal Beach Harbor Hiking Rowan Hal Arethusa Falls Hiking Stevens Lori Mt Ascutney - North Peak Hiking Stevens Lori Missisquoi River - VT Paddling Stevens Lori Mt Ascutney - West Peak Hiking Stevens Lori Lake Mephremagog Tour Paddling Stevens Lori Bloomfield - Maidstone Paddling Stevens Lori Lower Pond Tour Paddling Unser Glory Park Loop Ride Biking Unser Glory Baldpate Mountain Hiking Unser Glory Long Pond Tour Paddling Unser Glory Chocorua Lake Tour Paddling ReservationID TripDate 1800001 2018-03-26 1800002 2018-06-08 1800014 2018-10-01 1800022 2018-06-08 TripDate NumPersons 2018-09-11 2 2018-09-18 4 2018-09-11 1 2018-09-11 2 ReservationID CustomerNum Type 1800002 101 Hiking 1800021 112 Paddling 1800022 119 Hiking TripName TripPrice OtherFees Northern Forest Canoe Trail 80 20 Long Pond Tour 75 10 Chocorua Lake Tour 75 15 Mount Battie Ride 110 25 Northern Forest Canoe Trail 90 20 Bradbury Mountain Ride 105 25 Crawford Path Presidentials Hike 60 15 10. Without using a subquery, list the customer number, name, and phone number of each customer in the database who has NOT placed a reservation. (2pts) CustomerNum FirstName LastName Phone 110 Martha Bers 585-555-0111 123 Larry Barnett 860-555-9876

Have a similar question?

Continue to edit or attach image(s).

  • Fast and convenient

    Fast and convenient

    Simply post your question and get it answered by professional tutor within 30 minutes. It's as simple as that!

  • Any topic, any difficulty

    Any topic, any difficulty

    We've got thousands of tutors in different areas of study who are willing to help you with any kind of academic assignment, be it a math homework or an article.

  • 100% Satisfied Students

    100% Satisfied Students

    Join 3,4 million+ members who are already getting homework help from StudyDaddy!