Databas – Part III
22
Chapter 6 SQL: Data Manipulation
Review Questions
6.1 What are the two major components of SQL and what function do they serve?
6.2 What are the advantages and disadvantages of SQL?
Advantages
Satisfies ideals for database language
Disadvantages
Impedance mismatch – mixing programming paradigms with embedded access
6.3 Explain the function of each of the clauses in the SELECT statement. What restrictions are
imposed on these clauses?
FROM Specifies the table or tables to be used.
Databas – Part III
23
6.4 What restrictions apply to the use of the aggregate functions within the SELECT statement? How
do nulls affect the aggregate functions?
6.5 Explain how the GROUP BY clause works. What is the difference between the WHERE and
HAVING clauses?
6.6 What is the difference between a subquery and a join? Under what circumstances would you not
be able to use a subquery?
Exercises
For the Exercises 6.7 6.28, use the Hotel schema defined at the start of the Exercises at the end of
Chapter 3.
Simple Queries
6.7 List full details of all hotels.
6.8 List full details of all hotels in London.
24
6.9 List the names and addresses of all guests in London, alphabetically ordered by name.
6.10 List all double or family rooms with a price below £40.00 per night, in ascending order of price.
6.11 List the bookings for which no dateTo has been specified.
Aggregate Functions
6.12 How many hotels are there?
6.13 What is the average price of a room?
6.14 What is the total revenue per night from all double rooms?
6.15 How many different guests have made bookings for August?
Subqueries and Joins
6.16 List the price and type of all rooms at the Grosvenor Hotel.
25
6.17 List all guests currently staying at the Grosvenor Hotel.
6.18 List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the
room, if the room is occupied.
SELECT r.* FROM Room r LEFT JOIN
6.19 What is the total income from bookings for the Grosvenor Hotel today?
6.20 List the rooms that are currently unoccupied at the Grosvenor Hotel.
SELECT * FROM Room r
6.21 What is the lost income from unoccupied rooms at the Grosvenor Hotel?
Databas – Part III
Grouping
6.22 List the number of rooms in each hotel.
6.23 List the number of rooms in each hotel in London.
6.24 What is the average number of bookings for each hotel in August?
6.25 What is the most commonly booked room type for each hotel in London?
6.26 What is the lost income from unoccupied rooms at each hotel today?
Databas – Part III
Populating Tables
6.27 Insert records into each of these tables.
INSERT INTO Hotel
6.28 Update the price of all rooms by 5%.
General
6.29 Investigate the SQL dialect on any DBMS that you are currently using. Determine the compliance
of the DBMS with the ISO standard. Investigate the functionality of any extensions the DBMS
supports. Are there any functions not supported?
6.30 Show that a query using the HAVING clause has an equivalent formulation without a HAVING
clause.
6.31 Show that SQL is relationally complete.
Case Study 2
For Exercises 6.32 6.40, use the Projects schema defined in the Exercises at the end of Chapter 5.
6.32 List all employees in alphabetical order of surname, and then first name.
6.33 List all the details of employees who are female.
Databas – Part III
28
6.34 List the names and addresses of all employees who are Managers.
6.35 Produce a list of the names and addresses of all employees who work for the IT department.
6.37
6.38 Produce a report of the total hours worked by each employee, arranged in order of
department number and within department, alphabetically by employee surname.
6.39 For each project on which more than two employees worked, list the project number, project
name, and the number of employees who work on that project.
6.40 List the total number of employees in each department for those departments with more than
10 employees. Create an appropriate heading for the columns of the results table.
Databas – Part III
29
6.41 List all book titles.
6.43 List all book titles published in the year 2012.
6.44 List all copies of book titles that are available for borrowing.
6.45
6.46 List the names of borrowers who currently
30
6.47 List the names of borrowers with overdue books.
6.48 -321-52306-
6.49 -321-52306-
6.50 -321-52306- een borrowed?
6.51
31
6.52 For each book title with more than 3 copies, list the names of library members who have
borrowed them.
SELECT title, borrowerName
6.53 Produce a report with the details of borrowers who currently have books overdue.
6.54 Produce a report detailing how many times each book title has been borrowed.