Part 1: Primary Keys / Foreign Keys / Default Bring up your previous 2 assignments with the SQL code: 1. Write the statements to remove (drop) the previous tables: Booking and Guest 2. Write new CREATE TABLE statements to create Booking and Guest, but this time also include the below (Note: modify the 2 statements from the SQL Basics assignment). a A primary key for each of the tables. Hint: One of them is a composite key! b. A default price in table Booking should be set to 500, Foreign keys where appropriate guesto in the Booking table must refer to guest No in the Guest table Note: When running multiple sql statements you need semicolons separating them. Otherwise run them one at a time. 3. Rerun your insert statements (from the previous 2 weeks' assignments) to insert the rows you did before in the tables. Did they work (yes or no)? If not, explain what happened and fix your errors (paste only the updated inserts below). 4. Primary Keys cannot be NULL Write the SQL statement to insert a row into the Guest table where the guest.No is NULL. Note: Yes, specify the word NULL instead of a muomber 3. Describe what happened. Was there an error? If so, paste it. 6. Write the SQL to insert a row into Guest where the guestNo is 101. 7. Describe what happened. What was the error? 8. Write a statement to insert into a new row into Booking for an existing guest and do not specify the price. Note: In the Insert specify the columns that you do want to add 9. Check out what value got entered into the price. What happened? Paste the results of a select from Booking. 10. When you run the same command from #8 again what happens? What error message do you receive? 11. Write a statement to insert a new entry into the Booking table but use a guestNo that is not currently in the Guest table. 12. What happened? What error did you receive? Part 2: Create another Table Since booking guests was so successful you decide to purchase additional properties to sublet. 1. Please write the correct statement to create the following table, choose a primary key a Property with the following fields. propery No a unique integer) address cin 11 11 state zipcode phone 2. Insert into the above table 3 rows. The first is propertyNo 1 which is the address info for your current place that you have been subletting in the previous assignments (you can use any address of your choosing). Insert 2 more addresses with property numbers 2 and 3, for the two new rooms you purchased 3. Paste the results of select * from Property Part 3: Alter 1. First, write a statement to insert into Property a new entry with unique id 4 without specifying a phone number. You will need to specify the columns you are inserting values into 2. What happened? What do you see in the phone attribute for that row in the table? 3. Write the statement to alter the table Propens to specify a default phone number. Remember you will need single quotes around the number 4. Write a statement to insert another row into Property with id 5 without specifying a phone number 5. What happened? What is now in the phone column? Paste the results of a select * from Property 6. Write the statement to drop the attribute phone from the Property table. 7. Write the update statement to modify any one of the entries in Booking from a price of 500 to a price of 2000 8. Paste the results select * from ExpensiveBooking. See what happened! 9. Create a view which will display the guestNo, dateAmve, date Depart, property No, city, and state for each Booking where the rent is greater than 500. You can name it whatever you want. 10. Paste the results of the view. 11. Create your own view that you feel would be helpful. 12. Paste the results. 13. Explain why you feel this view would be convenient! phoneNo guestNo lastName firstName 101 Washington George 102 Johnson Jane 2222222 75842 email gwashy@yahoo.com jsmith@yahoo.com funmeal@yahoo.com jleo@yahoo.com rleo@yahoo.com 103 Meal Happy 47932 104 Leo Jack 450982 105 Leo Rose 9587481 guestNo date Arrive date Depart 101 2021-06-01 2021-06-30 102 2021-07-28 2021-08-07 104 2021-08-07 2021-08-21 105 2021-01-04 2021-01-15 101 2021-01-01 2021-01-31 101 2021-02-03 2021-03-01 price 500.00 500.00 500.00 500.00 250.00 1000.00
