In each of the following queries, use SQL to obtain the desired results using the data shown in the Colonial Adventure Tour Database listing found on the homepage under the databases heading. Also, found there is a file containing the SQL commands needed to create the database in a DBMS such as MySQL. You might want to create the database to use when answering the questions. If so, just copy the commands and paste them into MySQL.

You will have 3 attempts to get the activity correct and the computer will automatically grade your answers after each attempt. When an answer consists of more than one word be careful about spacing. Leave only one space between words and ooperators.

Give the SQL statement to list the trip name and state for each trip that occurs during one of the Fall seasons that is Early Fall, Spring and Late Fall. Sort by State. Use the wildcard for the season.

SELECT TripName, State FROM Trip WHERE Season State;

Answer 1:

='Spring'

Answer 2:

ORDER BY TripName,

Give the SQL statement to display the number of trips that originate in Vermont (VT) or Connecticut (CT)??

SELECT FROM Trip WHERE ;

Answer 1:

COUNT(*)

Answer 2:

State='VT' or State='CT'

Give the SQL statement to display the number of reservations that include a trip with a price that is greater than or equal to $20 but less than or equal to $50.

SELECT FROM WHERE TripPrice ;

Answer 1:

COUNT(*)

Answer 2:

Reservation

Answer 3:

>= 20 AND TripPrice

Trip Trip Guide Colonial Adventure Tours MasGasco Type Season | TiD Guide + Guide Guidelium AMO1 G201 Last Name FirstName State MA RH01 AM01 SL01 BR01 09452 Abrams Boyers Devon Gregory ME 01375 ME SL01 City Williamsburg Jaffrey Sunderland Dummer Jaffrey Fracani Mount Desert Soringfeld DH01 G201 KS01 KSOZ MR01 RH01 Harley Zach Susan Sam NH Address 54 Quest Ave. 140 Oukton Rd. 25 Old Ranch Rd. 7 Moose Head Rd. 943 Oakton Rd. 9 Congaree Ave. 24 Shenandoah Rd. 12 Heather Rd. 15 Riverton Rd. 342 Pineview St. Late Saring Early Fall Early Fall Late Spring Early Fall Late Spring Summer 03452 PostalCareebanalum HireDate 01096 617-555-60326 /3/2012 603-555-2134 3/4/2012 781-555-77671 /8/2012 25 603-555-8765 11/4/2012 603-555-1230 4/8/2013 03590 603-555-00036 /10/2013 781-555-2322 9/14/2015 04660 207-555-9009 6/2/2014 05825 902-555-3339 9/5/2014 06810 203-555-85 34 2/2/2015 BRO1 G201 KS01 NH Ray MA Marston Rowan NH UGO1 Hal ME RH01 NH SL01 Stevens SLO1 Summer UGO1 Glory Danbury BRO1 BRO1 Customer Customer Num G201 DH01 KS01 FirstName City State Last Name Narthifold UGO1 ME BR01 NH H01 103 Hiking Hiking Biking Hiking Hiking Paddling Hiking Hiking Hiking Paddling Biking Hiking Hiking Hiking Hiking Hiking Hiking Paddling Paddling Hiking Paddling Paddling Hiking Biking Hiking Hiking Hiking Hiking Hiking Paddling Paddling Arnold Sujata CT Kawa Sacingfeld East Hartford Lowell Wolcott Johnston MA Tuollan Arethusa Falls Mt Ascutoes - North Peak Mt Ascuton - West Peak Bradbury Mountain Ride Baldpate Mountain Blueberry Mountain Bironfeld - Maidstone Black Pond Big Rock Cave Mt. Cardigan - Fires Chocorua Lake Tour Cadillac Mountain Ride Cadillac Mountain Cannon Mit Crawford Path Presidentials Hike Cherry Pond Huguenot Head Hike Low Bald Spot Hike Mason's Farm Lake Merhremagne Tour Long Pand Long Pand Tour Lower Pond Tour Mt Adams Mount Battie Ride Mount Cardigan Hike Mt. Chocorua Mount Gadeld Hike Metacomet-Monadnock Trail Hike McLennan Reservation Hike Missisquel River - VT Northern Forest Cance Trail Park Loop Ride Pontook Reservoir Tour Pisgah State Park Ride Pondicherry Trail Ride Seal Beach Harbor Sawyer River Ride Welch and Dickey Mountains Hike Wachusete Mountain Westfold River Loop G201 Stadtlocation Harts Location Weathersfeld Weathersfeld Lewiston-Auburn North NON Batchelders Grant Lincoln Blockfeld Tamworth Orange Tamworth Bar Harbor Bay Harbor Franconia Crawford Notch Bar Harbor Whitebeld Pinkas Notch North Stratford Newport Rutland Greenville Poland Randolph Camden Cardigan Albany Woodstock Pelham Turihan Lowell Stark Mount Desert Island Dura Northborough White Mountains Bar Harbor Mount Cougais Thorton Princeton Fort Casteld NY 105 106 ME KS02 RH01 McLean Megetais Marchand Rulf Caran Kyle Joseph Quinn Uschi Late Spring Early Fall Summer Spring Early Fall Early Fall Late Spring Late Spring Summer Summer Late Spring Summer Early Fall Late Fall Spring Early Fall Late Spring ME 108 Address 9 Old Mill Rd. 2232 South St. Apt 3 132 Main St. 1 1644 South Bend Rd. 345 Lower Ave. 156 Schalar St. 76 Cross Rd. 32 Sheep Stop St. 10 Greenfeld St. 65 Granite St. 373 Highland Ave. 1282 Ocean Walk 7 Cherry Blossom St. 18 Ark Ledge in. 24 Sturg Rd. 10 Old Main St. 132 South St. Apt 27 25 Stag Rd. 12 Forster St. 51 Fredrick St. 154 Central St. 109 Postal ade 03053 01101 06108 01854 14590 02919 03740 16412 04963 14592 02143 OB226 02188 05148 04912 05444 03101 06824 06074 14411 06066 Edinbora Rome bananun 603-555-7563 413-555-3212 860-555-0703 781-555-8423 SBS-555-5321 401-555-4848 603-555-0456 814-555-5521 207-555-9643 585-555-0111 857-555-6258 609-555-5221 617-555-6665 902-555-3096 207-555-4507 802-555-3443 603-555-3476 860-555-9876 857-555-5532 585-555-0900 860-555-3234 110 Martha Laura Adam NH NH SLO1 AM01 UG01 Jones 115 NH NI DH01 116 MA 119 BR01 VT 120 Murakami Chau Genomski Bretton Bacak Heffer 90 Barnett Somerville Ocean City Weymouth Londonderry Athens Cambridge Manchester South Windsor Eviteld Clement Sadie Star Odab R01 NH Biking 122 G201 G201 123 124 DH01 NH NH 125 29 Petersen Paddling Biking Biking Hiking Biking Hiking Late Spring Late Spring Summer Late Spring Early Spring Early Fall Summer Early Spring Late Spring AMO1 Brown Brianne Vernon UGO1 Hiking Biking S01 G201 Trial Numersans Trio Osbertees Customer Num Reservation Reservation 1600001 1600002 1600003 101 $0.00 $0.00 RH01 KSO2 101 103 BRO1 104 105 106 1600005 1600006 1600007 1600008 107 $0.00 $15.00 $0.00 $20.00 $10.00 $0.00 $40.00 $0.00 $0.00 $0.00 $5.00 108 109 102 1600010 1600011 1600012 1600013 TripDate 3/26/2016 6/8/2016 9/12/2016 10/16/2016 6/25/2016 6/18/2016 7/9/2016 9/12/2016 9/11/2016 5/14/2016 9/15/2016 6/12/2016 7/9/2016 10/1/2016 7/22/2016 7/23/2016 6/18/2016 9/18/2016 8/29/2016 8/27/2016 6/11/2016 6/8/2016 9/11/2016 9/11/2016 10/1/2016 9/19/2016 7/25/2016 115 119 $95.00 $35.00 $45.00 $55.00 $80.00 $75.00 $35.00 $90 00 $25.00 $25.00 $15.00 $20.00 $40.00 $20.00 $75.00 $2000 S85.00 $110.00 $35.00 $90.00 $95.00 $70.00 $70.00 $40.00 $105.00 $60.00 120 1600015 1600016 11 121 122 1600018 126 $0.00 $15.00 $5.00 $15.00 $25.00 $10.00 $20.00 $25.00 $3000 $45.00 $0.00 $25.00 $15.00 124 124 112 1600021 119 121 125 1600025 1600026 1600029 126