Done on phpMyAdmin + SQL

Booking

Guest

.

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. c. Foreign keys where appropriate: guestNo in the Booking table must refer to guestNo 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 guestNo is NULL. Note: Yes, specify the word NULL instead of a number. 5. 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: i. propertyNo (a unique integer) ii. address iii. city iv. state v. zipcode vi. 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 Property 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. Paste the result of select * from Property. 8. Write one statement to alter the table Booking to add a column, propertyNo, which: a. is an integer representing the propertyNo for each booking b. is a foreign key to the propertyNo in the Property table c. has a default value of 1 (i.e., your previous rental that all guests were subletting). 9. Paste the result of Select * from Booking. Check out the propertyNo attribute. What happened? 10. Guest 104 would like to sublet one of your new properties, property number 2 from September 1, 2022 until September 30, 2022 for a price of 650. Write the insert statement. 11. Paste the results of a select * from Booking. 12. Write another insert statement to insert a DIFFERENT date for 104 to rent a new property. But this time for a property number which does not exist in the Property table. 13. What error did you receive? Paste the error.

. Part 4: Joins again! 1. Write the SQL Statement to display the guestNo, dateArrive , dateDepart, propertyNo, city, and state for all Bookings. 2. Paste the results. 3. Write the SQL statement to display the guestNo, firstName, lastName, propertyNo, price, city, and state for all Bookings. 4. Paste the results.

. Part 5: Views You notice that you are constantly doing the above 2 joins. You decide to create views to easily have the above available, specifically: 1. Create a view called to BookingDetails which will display the guestNo, dateArrive, dateDepart, propertyNo, city, and state for all Bookings. 2. Paste the results of a Select * from BookingDetails. 3. Create a view called BookingComplete which will display the guestNo, firstName, lastName, propertyNo, price, city, and state for all Bookings. 4. Paste the results of a select * from BookingComplete. 5. Create a view called ExpensiveBooking which will display all the details only in the Booking table for those whose rent is greater than 500. 6. Paste the results select * from ExpensiveBooking.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, dateArrive, dateDepart, propertyNo, 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!

\begin{tabular}{|r|r|r|r|} \hline guestNo & dateArrive & dateDepart & price \\ \hline 101 & 20230601 & 20230831 & 500.00 \\ \hline 102 & 20230701 & 20230731 & 500.00 \\ \hline 103 & 20230201 & 20230330 & 500.00 \\ \hline 104 & 20230401 & 20230531 & 500.00 \\ \hline 101 & 20230901 & 20230905 & 1000.00 \\ \hline 101 & 20231001 & 20231011 & 250.00 \\ \hline \end{tabular} \begin{tabular}{|r|l|l|l|l|} \hline guestNo & lastName & firstName & phoneNo & email \\ \hline 101 & Washington & George & 7208009454 & G.Wash@gmail.com \\ \hline 102 & Johnson & Jane & 7033792303 & J.Smith@Hotmail.com \\ \hline 104 & Raynor & Jim & 7739078027 & J.Raynor@gmail.com \\ \hline 105 & Fischer & Samantha & 3035809728 & Sam.Fischer@yahoo.com \\ \hline \end{tabular}