1- Compare and contrast the three phases of database design.
2- Write an SQL select statement to list all double or family rooms with a price less than $255 per night in ascending order of price. If you have not successfully constructed the full tables Hotel, Room,Guest and Booking and fill the relevant date as required in the previous practical, you could replace your table construction and data via this SQL script.
Students may try out these two simplified queries first if they wish:
o List all rooms with a price less than $255 per night.
o List all double or family rooms with a price less than $255 per night.
3- What is the possible maximum total revenue per night from all double rooms?
NOTE: We recall that aggregate functions such as COUNT(), SUM() are applied to each group if theGROUP BY clause is present, or applied to the whole set of records if GROUP BY clause is absent.
4- List the price and type of all rooms at the Marriott hotel in London.
HINT: Use JOIN for tables Hotel and Room.
5- List the number of rooms in each hotel.
HINT: Use aggregate function COUNT(), and also make use of the GROUP BY clause.
6- Write an SQL query to update the price of all rooms by 5%.
NOTE: We recall from last week's practical that the syntax for using UPDATE can be found
a. https://www.w3schools.com/sql/sql_update.asp
b. https://www.techonthenet.com/sql/update.php
Tutorial 7: Q1, Q6, Q8-Q9 (with screenshots for the output results of the SQL).
7- Describe the purpose of normalizing data.
8- Read prac7script1.sql and draw a logical data model to illustrate the design. This file can be found under the prac link on vUWS. It may be helpful to refer to the sample data used in prac7script2.sql.
9- Write an SQL query to show the average customer balance for each area code.