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 5 Student Key Database Administration 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.Create a view named Vermont that contains the trip ID, trip name, state, and type where the state equals Vermont.
a. Write and execute the create view command to create the Vermont view. (1pt) * Provide command. No output needed.
b. Write and execute the code to retrieve the trip ID, trip name, and type for every paddling trip in the Vermont view. (.5pt) c. Write and execute the query that the DBMS actually executes. (1pt) Output for b and c:
d. Is it possible to insert, update, or delete records through this view? Why or why not?
(1pt) 2. Create a view called GuideHireDate that contains each guide's name and hire date. Combine the first and last name into a single column called Name. For example the Name column for employee AM01 would be "Miles Abrams".
a. Write and execute the create view command to create the GuideHireDate view. (1pt) * Provide command. No output needed.
b. Is it possible to insert, update, or delete records through this view? Why or why not?
(1pt) 3. Create the following users: Matthews, Garcia, Smith, and McBride. Assign all users the starting password Passw0rd, but ensure that McBride must change their password the next time they log in. (2.5pts) * Provide command. No output needed.
4. Grant the following privileges to the users you created:
a. User Matthews must be able to retrieve data from the Reservation table. (1pt) b. Users Garcia and Smith must be able to create and change (but not remove) reservations. (1pt) c. Users Matthews and McBride must be able to change the structure of the Guide table.
(1pt) * Provide command. No output needed.
5. Create the following indexes.
a. Create an index called trip_index1 on the TripName column of the Trip table. (.5pt) b. Create an index called trip_index2 on the TripID and CustomerNum columns of the Reservation table. (.5pt)TripID TripName Type 20 Lake Mephremagog Tour Paddling 31 Missisquoi River - VT Paddling * Provide command. No output needed.
6. Delete the index named trip_index2. (.5pt) * Provide command. No output needed.
7. Write the commands to obtain the following information from the system catalog.
a. List every column in the Guide table and its associated data type. (1pt) * Data type may vary based on DBMS:
b. List every table that contains a column called TripID. Your results should include tables only (no views), so you will likely need to include two system catalog tables using a join or subquery. (1.5pt)column_name data_type GuideNum char LastName varchar FirstName varchar Address varchar City varchar State char PostalCode char PhoneNum char HireDate date table_name reservation trip tripguides
