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 2 Student Key Single-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.12 points 1. List the trip ID and trip name of each trip. (1pt) 2. List the complete guide table. (1pt) TripID TripName 1 Arethusa Falls 2 Mt Ascutney - North Peak 3 Mt Ascutney - West Peak 4 Bradbury Mountain Ride 5 Baldpate Mountain 6 Blueberry Mountain 7 Bloomfield - Maidstone 8 Black Pond 9 Big Rock Cave 10 Mt. Cardigan - Firescrew 11 Chocorua Lake Tour 12 Cadillac Mountain Ride 13 Cadillac Mountain 14 Cannon Mtn 15 Crawford Path Presidentials Hike 16 Cherry Pond 17 Huguenot Head Hike 18 Low Bald Spot Hike 19 Masons Farm 20 Lake Mephremagog Tour 21 Long Pond 22 Long Pond Tour 23 Lower Pond Tour 24 Mt Adams 25 Mount Battie Ride 26 Mount Cardigan Hike 27 Mt. Chocorua 28 Mount Garfield Hike 29 Metacomet-Monadnock Trail Hike 30 McLennan Reservation Hike 31 Missisquoi River - VT 32 Northern Forest Canoe Trail 33 Park Loop Ride 34 Pontook Reservoir Tour 35 Pisgah State Park Ride 36 Pondicherry Trail Ride 37 Seal Beach Harbor 38 Sawyer River Ride 39 Welch and Dickey Mountains Hike 40 Wachusett Mountain 41 Westfield River Loop GuideNum LastName FirstName Address City State PostalCode PhoneNum HireDate AM01 Abrams Miles 54 Quest Ave. Williamsburg MA 1096 617-555-6032 2012-06-03 BR01 Boyers Rita 140 Oakton Rd. Jaffrey NH 3452 603-555-2134 2012-03-04 DH01 Devon Harley 25 Old Ranch Rd. Sunderland MA 1375 781-555-7767 2017-01-08 GZ01 Gregory Zach 7 Moose Head Rd. Dummer NH 3588 603-555-8765 2013-11-04 KS01 Kiley Susan 943 Oakton Rd. Jaffrey NH 3452 603-555-1230 2016-04-08 KS02 Kelly Sam 9 Congaree Ave. Fraconia NH 3580 603-555-0003 2016-06-10 MR01 Marston Ray 24 Shenandoah Rd. Springfield MA 1101 781-555-2323 2015-09-14 RH01 Rowan Hal 12 Heather Rd. Mount Desert ME 4660 207-555-9009 2014-06-02 SL01 Stevens Lori 15 Riverton Rd. Coventry VT 5825 802-555-3339 2014-09-05 UG01 Unser Glory 342 Pineview St. Danbury CT 6810 203-555-8534 2017-02-02 3. List the name of each customer from Connecticut. (1pt) 4. List the name of each customer not from Massachusetts. (1pt) 5. List the trip ID and trip name of each trip that has the type "paddling" or has the season "summer". (1pt)FirstName LastName Sujata Kasuma Larry Barnett Karen Busa Brianne Brown FirstName LastName Liam Northfold Sujata Kasuma Kyle McLean Joseph Morontoia Quinn Marchand Uschi Rulf Jean Luc Caron Martha Bers Adam Vaccari Clement Chau Sadie Gernowski Siam Bretton-Borak Orlagh Hefferson Larry Barnett Karen Busa Becca Peterson Brianne Brown TripID TripName 1 Arethusa Falls 7 Bloomfield - Maidstone 8 Black Pond 9 Big Rock Cave 10 Mt. Cardigan - Firescrew 11 Chocorua Lake Tour 15 Crawford Path Presidentials Hike 19 Masons Farm 20 Lake Mephremagog Tour 21 Long Pond 22 Long Pond Tour 23 Lower Pond Tour 24 Mt Adams 30 McLennan Reservation Hike 31 Missisquoi River - VT 32 Northern Forest Canoe Trail 34 Pontook Reservoir Tour 35 Pisgah State Park Ride 39 Welch and Dickey Mountains Hike 6. List the trip ID and trip name of each trip that has the season "late spring" and is in Maine.
(1pt) 7. Colonial tours is considering offering a discount on the base price of their trips (not including other fees). List the reservation ID, trip ID, number of persons, and discounted price of each reservation. The discounted price should be 90% of the regular trip price. Use Discounted_Price as the name of the new column. Order the results by Discounted_Price in descending order. (2pts) 8. Find the name of each trip that has the word "pond" in it. Note: be sure that your query lists instances of the work pond, but not the letters "pond" inside of another word. Thus, your query should NOT return the "Pondicherry Trail Ride" record. (1pt)TripID TripName 5 Baldpate Mountain 13 Cadillac Mountain 23 Lower Pond Tour 33 Park Loop Ride 41 Westfield River Loop ReservationID TripID NumPersons Discounted_Price 1800019 25 2 99 1800029 4 4 94.5 1800002 21 2 85.5 1800022 21 1 85.5 1800009 38 2 81 1800021 32 3 81 1800018 38 4 76.5 1800006 32 1 72 1800007 22 8 67.5 1800016 11 6 67.5 1800025 38 2 63 1800024 38 1 63 1800030 15 6 54 1800005 39 5 49.5 1800001 40 2 49.5 1800004 26 4 40.5 1800014 12 2 36 1800026 12 2 36 1800020 28 2 31.5 1800008 28 2 31.5 1800003 28 1 31.5 1800011 3 3 22.5 1800010 2 3 22.5 1800015 10 1 18 1800013 8 1 18 1800017 39 3 18 1800012 1 4 13.5 TripName Black Pond Cherry Pond Long Pond Long Pond Tour Lower Pond Tour 9. For each type of trip, list the type and the average distance. (2pts) 10. For each type of trip that occurs in summer, list the type and the average distance. Note: this is very similar to question 9, but only looking at those record where the season is "summer". (1pt)Type avg_dist Biking 17.125 Hiking 6.875 Paddling 11.5556 Type avg_dist Biking 12 Hiking 7.7778 Paddling 12.75
