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 1 Student Key Creating Tables For each question below, write and execute the appropriate SQL statement in the Database Management System of your choice using the indicated 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.8 points Use the BITS database for steps 1-4.
1. Create a table named Location. The Location table should have the same structure as the Trip table from the Colonial Adventure Tours database (see the ER Diagram), but with the following changes (2pts):
a. Change TripID to LocationID and use the number/numeric data type (use number for Oracle and numeric for all other DBMSs).
b. Change TripName to LocationName.
c. Change the data type for the Distance column to decimal(4,1).
* Table should be created successfully.
2. Insert the following records into the Location table (1pt):
42,Poudre River Trail,Island Grove Park,CO,21.4,10,Biking,Late Spring 43,Pikes Peak,Barr Trail,CO,26,12,Hiking,Early Fall * Insertion should succeed.
3. Display the contents of the Location table to ensure the records were added correctly. (1pt) 4. Remove the Location table. Ensure that it was dropped successfully using a query or describe statement. (1pt) * Query should fail.
5. Run the appropriate script file to create the Colonial Adventure Tours database. Make sure that you run the correct file for the Database Management System you are using. (1pt) * Commands should complete successfully.
6. Double check that you are switched to the Colonial database (in Oracle SQL Developer, this will require creating a connection for the Colonial user). Verify that the database was created correctly by using a DESCRIBE (or equivalent) command for each table and comparing it to the ER diagram. (1pt) 7. Verify that the data was loaded correctly by querying each table and comparing the results to pages 16-20 in the Concepts book. (1pt)LocationID LocationName StartLocation State Distance MaxGrpSize Type Season 42 Poudre River Trail Island Grove Park CO 21.4 10 Biking Late Spring 43 Pikes Peak Barr Trail CO 26 12 Hiking Early Fall
