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 4 Student Key Updating Data 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.

Note: The output from describing tables includes the information that I want you to determine, as a result, output from describing tables is omitted from this key.

10 points – 1 point each 1. Create a Hiking table with the following structure:

Column Type Length Decimal Places Comments ReservationID Char 7 Primary Key TripName Varchar 75 TripDate Date Total Decimal 6 2 CustomerNum Char 4 Describe the Hiking table.

* Verify using table above.

2. Insert into the Hiking table the reservation ID, the trip name, trip date, total cost (trip price plus other fees), and customer number for each reserved trip that has a type of Hiking.

Query the Hiking table to show that all records were inserted successfully.

ReservationId TripName TripDate Total CustomerNum 1800001 Wachusett Mountain 2018-03-26 55 101 1800002 Long Pond 2018-06-08 95 101 1800003 Mount Garfield Hike 2018-09-12 35 103 1800004 Mount Cardigan Hike 2018-10-16 60 104 1800005 Welch and Dickey Mountains Hike 2018-06-25 55 105 1800008 Mount Garfield Hike 2018-09-12 35 108 1800010 Mt Ascutney - North Peak 2018-05-14 25 102 1800011 Mt Ascutney - West Peak 2018-09-15 25 102 1800012 Arethusa Falls 2018-06-12 15 115 1800013 Black Pond 2018-07-09 25 116 1800015 Mt. Cardigan - Firescrew 2018-07-23 20 120 1800017 Welch and Dickey Mountains Hike 2018-06-18 25 122 1800020 Mount Garfield Hike 2018-08-27 45 124 1800022 Long Pond 2018-06-08 120 119 1800030 Crawford Path Presidentials Hike 2018-07-25 75 104 3. All July hiking trip reservations are being given a 4% discount off of the total trip price. Update the Hiking table to reflect this change. Query the Hiking table to show the changes.

4. Customer 102 has decided to cancel their hiking trips. Delete those records from the Hiking table. Query the Hiking table to show the changes.ReservationId TripName TripDate Total CustomerNum 1800001 Wachusett Mountain 2018-03-26 55 101 1800002 Long Pond 2018-06-08 95 101 1800003 Mount Garfield Hike 2018-09-12 35 103 1800004 Mount Cardigan Hike 2018-10-16 60 104 1800005 Welch and Dickey Mountains Hike 2018-06-25 55 105 1800008 Mount Garfield Hike 2018-09-12 35 108 1800010 Mt Ascutney - North Peak 2018-05-14 25 102 1800011 Mt Ascutney - West Peak 2018-09-15 25 102 1800012 Arethusa Falls 2018-06-12 15 115 1800013 Black Pond 2018-07-09 24 116 1800015 Mt. Cardigan - Firescrew 2018-07-23 19.2 120 1800017 Welch and Dickey Mountains Hike 2018-06-18 25 122 1800020 Mount Garfield Hike 2018-08-27 45 124 1800022 Long Pond 2018-06-08 120 119 1800030 Crawford Path Presidentials Hike 2018-07-25 72 104 ReservationId TripName TripDate Total CustomerNum 1800001 Wachusett Mountain 2018-03-26 55 101 1800002 Long Pond 2018-06-08 95 101 1800003 Mount Garfield Hike 2018-09-12 35 103 1800004 Mount Cardigan Hike 2018-10-16 60 104 1800005 Welch and Dickey Mountains Hike 2018-06-25 55 105 1800008 Mount Garfield Hike 2018-09-12 35 108 1800012 Arethusa Falls 2018-06-12 15 115 1800013 Black Pond 2018-07-09 24 116 1800015 Mt. Cardigan - Firescrew 2018-07-23 19.2 120 1800017 Welch and Dickey Mountains Hike 2018-06-18 25 122 1800020 Mount Garfield Hike 2018-08-27 45 124 1800022 Long Pond 2018-06-08 120 119 1800030 Crawford Path Presidentials Hike 2018-07-25 72 104 5. Add a new record to the Hiking table with the following information:

Customer 114 has scheduled a trip to Long Pond for June 24th 2018. The reservation ID for this trip is 1800031, and the total price is $105. Query the Hiking table to show the changes.

6. Add a variable length character field named Difficulty to the Hiking table. The maximum length for this column is 12 characters. Ensure that the value for all records (and any future additions) is 'Beginner'. Query the Hiking table to show the changes.

7. None of the trip names in the Hiking table are long enough to need all 75 characters that are allowed in the trip name column. Leave TripName as a variable length character field, but shorten the maximum length to 35 characters. Describe the Hiking table to show the changes.

* Verify using information above.

8. Change the CustomerNum column so that it does not allow NULLs. Describe the Hiking table to show the changes.

* Verify using information above.ReservationId TripName TripDate Total CustomerNum 1800001 Wachusett Mountain 2018-03-26 55 101 1800002 Long Pond 2018-06-08 95 101 1800003 Mount Garfield Hike 2018-09-12 35 103 1800004 Mount Cardigan Hike 2018-10-16 60 104 1800005 Welch and Dickey Mountains Hike 2018-06-25 55 105 1800008 Mount Garfield Hike 2018-09-12 35 108 1800012 Arethusa Falls 2018-06-12 15 115 1800013 Black Pond 2018-07-09 24 116 1800015 Mt. Cardigan - Firescrew 2018-07-23 19.2 120 1800017 Welch and Dickey Mountains Hike 2018-06-18 25 122 1800020 Mount Garfield Hike 2018-08-27 45 124 1800022 Long Pond 2018-06-08 120 119 1800030 Crawford Path Presidentials Hike 2018-07-25 72 104 1800031 Long Pond 2016-06-24 105 114 ReservationId TripName TripDate Total CustomerNum Difficulty 1800001 Wachusett Mountain 2018-03-26 55 101 Beginner 1800002 Long Pond 2018-06-08 95 101 Beginner 1800003 Mount Garfield Hike 2018-09-12 35 103 Beginner 1800004 Mount Cardigan Hike 2018-10-16 60 104 Beginner 1800005 Welch and Dickey Mountains Hike 2018-06-25 55 105 Beginner 1800008 Mount Garfield Hike 2018-09-12 35 108 Beginner 1800012 Arethusa Falls 2018-06-12 15 115 Beginner 1800013 Black Pond 2018-07-09 24 116 Beginner 1800015 Mt. Cardigan - Firescrew 2018-07-23 19.2 120 Beginner 1800017 Welch and Dickey Mountains Hike 2018-06-18 25 122 Beginner 1800020 Mount Garfield Hike 2018-08-27 45 124 Beginner 1800022 Long Pond 2018-06-08 120 119 Beginner 1800030 Crawford Path Presidentials Hike 2018-07-25 72 104 Beginner 1800031 Long Pond 2016-06-24 105 114 Beginner 9. Customer 105 has decided to postpone their trip. Remove the trip date from their reservation in the Hiking table. Query the Hiking table to show the changes.

10. Delete the Hiking table. Attempt to query the Hiking table to verify deletion.

* Query should failReservationId TripName TripDate Total CustomerNum Difficulty 1800001 Wachusett Mountain 2018-03-26 55 101 Beginner 1800002 Long Pond 2018-06-08 95 101 Beginner 1800003 Mount Garfield Hike 2018-09-12 35 103 Beginner 1800004 Mount Cardigan Hike 2018-10-16 60 104 Beginner 1800005 Welch and Dickey Mountains Hike 55 105 Beginner 1800008 Mount Garfield Hike 2018-09-12 35 108 Beginner 1800012 Arethusa Falls 2018-06-12 15 115 Beginner 1800013 Black Pond 2018-07-09 24 116 Beginner 1800015 Mt. Cardigan - Firescrew 2018-07-23 19.2 120 Beginner 1800017 Welch and Dickey Mountains Hike 2018-06-18 25 122 Beginner 1800020 Mount Garfield Hike 2018-08-27 45 124 Beginner 1800022 Long Pond 2018-06-08 120 119 Beginner 1800030 Crawford Path Presidentials Hike 2018-07-25 72 104 Beginner 1800031 Long Pond 2016-06-24 105 114 Beginner