SQL Technical Data Management and Applications
ECT301 – Technical Data Management and Applications
Assignment 4
Total Points: 50
Due: 12/1/2014
1. Explain the function of each of the clauses in the SELECT statement. What restrictions are imposed on these clauses? (2 points each)
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
2. List the 5 aggregate functions and explain their use. (10 points)
3. SQL exercises (5 points each)
Hotels Schema
Hotel
|
hotelNo |
hotelName |
city |
Room
|
roomNo |
hotelNo |
type |
price |
Booking
|
hotelNo |
guestNo |
dateFrom |
dateTo |
roomNo |
Guest
|
guestNo |
guestName |
guestAddress |
Use the hotel schema above to write SQL statements for the following queries.
a. List full details of all hotels.
b. List full details of all hotels in London.
c. List the names and addresses of all guests in London, alphabetically ordered by name.
d. List all double or family rooms with a price below £40.00 per night, in ascending order of price.
e. List the bookings for which no dateTo has been specified.
4. What are the two major components of SQL and what function do they serve?(3 points)