MySQL SELECT PRACTICE
Some Review Questions
1. What are the two major components of SQL and what function do they serve?
2. What are the advantages and disadvantages of SQL?
3. Explain the function of each of the clauses in the SELECT statement. What restrictions are imposed on these clauses?
4. What restrictions apply to the use of the aggregate functions within the SELECT statement? How do nulls affect the aggregate functions?
5. Explain how the GROUP BY clause works. What is the difference between the WHERE and HAVING clauses?
6. What is the difference between a subquery and a join? Under what circumstances would you not be able to use a subquery?
SQL Queries Exercise Using NetBeans 6.x.x – Create and Insert
Executing SQL Queries Exercises
1. List full details of all hotels.
2. List full details of all hotels in London.
3. List the names and addresses of all guests in London, alphabetically ordered by name.
4. List all double or family rooms with a price below £40.00 per night, in ascending order of price.
5. List the bookings for which no date_to has been specified.
Aggregate Functions
How many hotels are there?
What is the average price of a room?
What is the total revenue per night from all double rooms?
How many different guests have made bookings for August?
Subqueries and Joins
1. List the price and type of all rooms at the Grosvenor Hotel.
2. List all guests currently staying at the Grosvenor Hotel.
3. 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.
4. What is the total income from bookings for the Grosvenor Hotel today?
5. List the rooms that are currently unoccupied at the Grosvenor Hotel.
6. What is the lost income from unoccupied rooms at the Grosvenor Hotel?
Grouping
1. List the number of rooms in each hotel.
2. List the number of rooms in each hotel in London.
3. What is the average number of bookings for each hotel in April?
4. What is the most commonly booked room type for each hotel in London?
5. What is the lost income from unoccupied rooms at each hotel today?
Creating and Populating Tables With Data
1. Using the CREATE TABLE statement, create the Hotel, Room, Booking and Guest tables.
2. Insert records into each of these tables.
3. Update the price of all rooms by 5%.
4. Create a separate table with the same structure as the Booking table to hold archive records. Using the INSERT statement, copy the records from the Booking table to the archive table relating to bookings before 1st January 2008. Delete all bookings before 1st January 2008 from the Booking table.
In this exercise we will be introduced to some theory on SQL and then proceed to creating database, tables and inserting sample data. Then we will start executing the SQL queries against the database that we have created, exploring the SQL DML language. The pre requirements for this tutorial are MySQL 5.x.x and NetBeans 6.x.x (or other SQL editor such as MySQL Query Browser etc.)
MySQL SELECT PRACTICE
Note: MySQL is not a case sensitive. If you want to retain the case, use double quotes ("") for the string identifier. You will find that your uppercase in MySQL script will be converted to a lowercase when executed.
Some Review Questions
1. What are the two major components of SQL and what function do they serve?
A data definition language (DDL) for defining the database structure. A data manipulation language (DML) for retrieving and updating data.
2. What are the advantages and disadvantages of SQL?
Advantages:
Disadvantages:
|
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.
WHERE - Filters the rows subject to some condition.
GROUP BY - Forms groups of rows with the same column value.
HAVING - Filters the groups subject to some condition.
SELECT - Specifies which columns are to appear in the output.
ORDER BY - Specifies the order of the output.
If the SELECT list includes an aggregate function and no GROUP BY clause is being used to group data together, then no item in the SELECT list can include any reference to a column unless that column is the argument to an aggregate function.
When GROUP BY is used, each item in the SELECT list must be single-valued per group.
Further, the SELECT clause may only contain:
Column names.
Aggregate functions.
Constants.
An expression involving combinations of the above.
All column names in the SELECT list must appear in the GROUP BY clause unless the name is used only in an aggregate function.
4. What restrictions apply to the use of the aggregate functions within the SELECT statement? How do nulls affect the aggregate functions?
An aggregate function can be used only in the SELECT list and in the HAVING clause. Apart from COUNT(*), each function eliminates nulls first and operates only on the remaining non-null values. COUNT(*) counts all the rows of a table, regardless of whether nulls or duplicate values occur.
5. Explain how the GROUP BY clause works. What is the difference between the WHERE and HAVING clauses?
SQL first applies the WHERE clause. Then it conceptually arranges the table based on the grouping column(s). Next, applies the HAVING clause and finally orders the result according to the ORDER BY clause. WHERE filters rows subject to some condition; HAVING filters groups subject to some condition.
6. What is the difference between a subquery and a join? Under what circumstances would you not be able to use a subquery?
With a subquery, the columns specified in the SELECT list are restricted to one table. Thus, cannot use a subquery if the SELECT list contains columns from more than one table.
SQL Queries Exercise Using NetBeans 6.x.x
The following tables form part of a database held in a relational DBMS:
Let create a database and tables. Then populate those tables with some sample data. Firstly, we use MySQL Command Line Client.
1. Create a database namedhotel_db. Then switch to NetBeans.
CREATE DATABASE hotel_db;
2. If you want to see the database files, it is under the data folder of MySQL path.
3. You can continue using the MySQL Command Line Client Tool for the following database manipulation exercise, however we we will use NetBeans. Launch NetBeans and connect to the just created database as shown in the following Figures. Select Database > expand Driver > select MySQL (Connector / J driver) > right click mouse > select Connect Using.
4. Fill in the root’s password and click OK.
5. The following Figure shows that the connection was established.
6. The following Figure shows that the connection to the hotel_db database has been established.
7. Let execute SQL queries. Select the hotel_db connection > right click mouse > select Execute Command. This will launch query editor on the right window.
8. Next, create tables and populate them with sample data. Copy and paste the following SQL script into the query editor and click the Run button ( ).
-- Script for MySQL 5.x.x exercises
-- Revised 3/24/2008 by yummy
-- create a table named hotel
create table hotel(
hotelno varchar(10),
hotelname varchar(20),
city varchar(20),
primary key (hotelno)
)type=innodb;
-- insert some sample data
insert into hotel values('fb01', 'Grosvenor', 'London');
insert into hotel values('fb02', 'Watergate', 'Paris');
insert into hotel values('ch01', 'Omni Shoreham', 'London');
insert into hotel values('ch02', 'Phoenix Park', 'London');
insert into hotel values('dc01', 'Latham', 'Berlin');
9. If there is no error, view the data. Select hotel table > right click mouse > Select View Data.
10.The hotel table and its data shown in the following Figure.
11.Next, create more tables and populate sample data. Follow the same steps as previously done.
create table room(
roomno numeric(5),
hotelno varchar(10),
type varchar(10),
price decimal(5,2),
primary key (roomno, hotelno),
foreign key (hotelno) REFERENCES hotel(hotelno)
)type=innodb;
insert into room values(501, 'fb01', 'single', 19);
insert into room values(601, 'fb01', 'double', 29);
insert into room values(701, 'fb01', 'family', 39);
insert into room values(1001, 'fb02', 'single', 58);
insert into room values(1101, 'fb02', 'double', 86);
insert into room values(1001, 'ch01', 'single', 29.99);
insert into room values(1101, 'ch01', 'family', 59.99);
insert into room values(701, 'ch02', 'single', 10);
insert into room values(801, 'ch02', 'double', 15);
insert into room values(901, 'dc01', 'single', 18);
insert into room values(1001, 'dc01', 'double', 30);
insert into room values(1101, 'dc01', 'family', 35);
12.Then verify the process.
create table guest(
guestno numeric(5),
guestname varchar(20),
guestaddress varchar(50),
primary key (guestno)
)type=innodb;
insert into guest values(10001, 'John Kay', '56 High St, London');
insert into guest values(10002, 'Mike Ritchie', '18 Tain St, London');
insert into guest values(10003, 'Mary Tregear', '5 Tarbot Rd, Aberdeen');
insert into guest values(10004, 'Joe Keogh', '2 Fergus Dr, Aberdeen');
insert into guest values(10005, 'Carol Farrel', '6 Achray St, Glasgow');
insert into guest values(10006, 'Tina Murphy', '63 Well St, Glasgow');
insert into guest values(10007, 'Tony Shaw', '12 Park Pl, Glasgow');
create table booking(
hotelno varchar(10),
guestno numeric(5),
datefrom date,
dateto date,
roomno numeric(5),
primary key (hotelno, guestno, datefrom),
foreign key (roomno, hotelno) REFERENCES room(roomno, hotelno),
foreign key (guestno) REFERENCES guest(guestno)
)type=innodb;
insert into booking values('fb01', 10001, '04-04-01', '04-04-08', 501);
insert into booking values('fb01', 10004, '04-04-15', '04-05-15', 601);
insert into booking values('fb01', 10005, '04-05-02', '04-05-07', 501);
insert into booking values('fb01', 10002, '16-05-04', '04-05-29', 601);
insert into booking values('fb01', 10001, '04-05-01', null, 701);
insert into booking values('fb02', 10003, '04-04-05', '10-04-04', 1001);
insert into booking values('fb02', 10005, '04-05-12', '30-05-04', 1101);
insert into booking values('ch01', 10006, '04-04-21', null, 1101);
insert into booking values('ch02', 10002, '04-04-25', '04-05-06', 801);
insert into booking values('dc01', 10007, '04-05-13', '04-05-15', 1001);
insert into booking values('dc01', 10003, '04-05-20', null, 1001);
Executing SQL Queries Exercises
Next, let execute SQL queries.
1. List full details of all hotels.
SELECT * FROM hotel;
2. List full details of all hotels in London.
SELECT * FROM hotel WHERE city LIKE '%London%';
Strictly speaking, this would also find rows with an address like: '10 London Avenue, New York'.
3. List the names and addresses of all guests in London, alphabetically ordered by name.
SELECT guestname, guestaddress
FROM guest
WHERE guestaddress LIKE '%London%'
ORDER BY guestname;
4. List all double or family rooms with a price below £40.00 per night, in ascending order of price.
SELECT * FROM room
WHERE price < 40 AND type IN ('Double', 'Family')
ORDER BY price;
(Note, ASC is the default setting).
5. List the bookings for which no date_to has been specified.
SELECT * FROM booking WHERE dateto IS NULL;
Aggregate Functions
1. How many hotels are there?
SELECT COUNT(*) FROM hotel;
2. What is the average price of a room?
SELECT AVG(price) FROM room;
3. What is the total revenue per night from all double rooms?
SELECT SUM(price) FROM room WHERE type = 'Double';
4. How many different guests have made bookings for August?
SELECT COUNT(DISTINCT guestno)
FROM booking
WHERE (datefrom >= '2004-08-01' AND datefrom <= '2004-08-31');
What about for May?
SELECT COUNT(DISTINCT guestno)
FROM booking
WHERE (datefrom >= '2004-05-01' AND datefrom <= '2004-05-31');
Subqueries and Joins
1. List the price and type of all rooms at the Grosvenor Hotel.
SELECT price, type
FROM room
WHERE hotelno = (SELECT hotelno FROM hotel
WHERE hotelname = 'Grosvenor');
2. List all guests currently staying at the Grosvenor Hotel.
SELECT * FROM guest
WHERE guestno =
(SELECT guestno FROM booking
WHERE datefrom <= CURRENT_DATE AND dateto >= CURRENT_DATE AND
hotelno = (SELECT hotelno FROM hotel
WHERE hotelname = 'Grosvenor'));
3. 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
(SELECT g.guestname, h.hotelno, b.roomno FROM Guest g, Booking b, Hotel h
WHERE g.guestno = b.guestno AND b.hotelno = h.hotelno AND
h.hotelname= 'Grosvenor' AND
b.datefrom <= CURRENT_DATE AND b.dateto >= CURRENT_DATE) AS XXX
ON r.hotelno = XXX.hotelno AND r.roomno = XXX.roomno;
-------------------------------------------------------------------------------------------------------------------
4. What is the total income from bookings for the Grosvenor Hotel today?
SELECT SUM(price) FROM booking b, room r, hotel h
WHERE (b.datefrom <= CURRENT_DATE AND
b.dateto >= CURRENT_DATE) AND
r.hotelno = h.hotelno AND r.roomno = b.roomno;
5. List the rooms that are currently unoccupied at the Grosvenor Hotel.
SELECT * FROM room r
WHERE roomno NOT IN
(SELECT roomno FROM booking b, hotel h
WHERE (datefrom <= CURRENT_DATE AND
dateto >= CURRENT_DATE) AND
b.hotelno = h.hotelno AND hotelname = 'Grosvenor');
6. What is the lost income from unoccupied rooms at the Grosvenor Hotel?
SELECT SUM(price) FROM room r
WHERE roomno NOT IN
(SELECT roomno FROM booking b, hotel h
WHERE (datefrom <= CURRENT_DATE AND
dateto >= CURRENT_DATE) AND
b.hotelno = h.hotelno AND hotelname = 'Grosvenor');
Grouping
1. List the number of rooms in each hotel.
SELECT hotelno, COUNT(roomno) AS count FROM room
GROUP BY hotelno;
2. List the number of rooms in each hotel in London.
SELECT hotel.hotelno, COUNT(roomno)
AS count FROM hotel, room
WHERE room.hotelno = hotel.hotelno
AND city LIKE '%London%'
GROUP BY hotelno;
3. What is the average number of bookings for each hotel in April?
SELECT AVG(X) AS AveNumBook FROM
(SELECT hotelno, COUNT(hotelno) AS X
FROM booking b
WHERE (b.datefrom >= DATE'2004-04-01' AND b.datefrom <= DATE'2004-04-31')
GROUP BY hotelno) AS AnotherThing;
/*SELECT AVG(sum_column1) AS AvgSumCol1
FROM (SELECT SUM(column1) AS sum_column1
FROM t1 GROUP BY column1) AS t1;*/
Syntax: SELECT ... FROM (subquery) [AS] name ...
The [AS] name clause is mandatory, because every table in a FROM clause must have a name.
4. What is the most commonly booked room type for each hotel in London?
SELECT MAX(X) AS MostlyBook
FROM (SELECT type, COUNT(type) AS X
FROM booking b, hotel h, room r
WHERE r.roomno = b.roomno AND b.hotelno = h.hotelno AND
h.city LIKE '%London%'
GROUP BY type) AS Dummy;
Syntax: SELECT ... FROM (subquery) [AS] name ...
The [AS] name clause is mandatory, because every table in a FROM clause must have a name.
5. What is the lost income from unoccupied rooms at each hotel today?
SELECT hotelno, SUM(price) FROM room r
WHERE roomno NOT IN
(SELECT roomno FROM booking b, hotel h
WHERE (datefrom <= CURRENT_DATE AND
dateto >= CURRENT_DATE) AND
b.hotelno = h.hotelno)
GROUP BY hotelno;
Creating and Populating Tables
1. Using the CREATE TABLE statement, create the Hotel, Room, Booking and Guest tables.
CREATE TABLE hotel(
hotelno CHAR(4) NOT NULL,
hotelname VARCHAR(20) NOT NULL,
city VARCHAR(50) NOT NULL
);
CREATE TABLE room(
roomno VARCHAR(4) NOT NULL,
hotelno CHAR(4) NOT NULL,
type CHAR(1) NOT NULL,
price DECIMAL(5,2) NOT NULL
);
CREATE TABLE booking(
hotelno CHAR(4) NOT NULL,
guestno CHAR(4) NOT NULL,
datefrom DATETIME NOT NULL,
dateto DATETIME NULL,
roomno CHAR(4) NOT NULL
);
CREATE TABLE guest(
guestno CHAR(4) NOT NULL,
guestname VARCHAR(20) NOT NULL,
guestaddress VARCHAR(50) NOT NULL
);
2. Insert records into each of these tables.
INSERT INTO hotel VALUES ('H111', 'Grosvenor', 'London');
INSERT INTO room VALUES ('1', 'H111', 'Single', 72.00);
INSERT INTO guest VALUES ('G111', 'John Smith', 'London');
INSERT INTO booking VALUES ('H111', 'G111', '2008-01-01', '2008-01-02', '1');
3. Update the price of all rooms by 5%.
UPDATE room SET price = price*1.05;
Note: 5/100 = 0.05 + 1 (original price) = 1.05
Can also be:
UPDATE room SET price = price + price*0.05;
4. Create a separate table with the same structure as the Booking table to hold archive records. Using the INSERT statement, copy the records from the Booking table to the archive table relating to bookings before 1st January 2008. Delete all bookings before 1st January 2008 from the Booking table.
CREATE TABLE booking_old(
hotel_no CHAR(4) NOT NULL,
guest_no CHAR(4) NOT NULL,
date_from DATETIME NOT NULL,
date_to DATETIME NULL,
room_no VARCHAR(4) NOT NULL
);
INSERT INTO booking_old(
SELECT * FROM booking
WHERE date_to < DATE'2008-01-01');
DELETE FROM booking
WHERE date_to < DATE'2008-01-01';
More practice on SQL queries using MySQL and NetBeans in next tutorial.