SQL Exercises, Exercises of Database Programming

Download SQL Exercises and more Exercises Database Programming in PDF only on Docsity!

SQL Exercises

This material comes form the

recommended book by T. Connoly, C. Begg, A. Strachan

The Database

Hotel (Hotel_No

, Name, Address)

Room (Room_No, Hotel_No

, Type, Price)

Booking (Hotel_No, Guest_No, Date_From

Date_To, Room_No) Guest (Guest_No

, Name, Address)

CREATE TABLE room( room_no VARCHAR(4) NOT NULL,hotel_no CHAR(4) NOT NULL,type CHAR(1) NOT NULL,price DECIMAL(5,2) NOT NULL);

CREATE TABLE booking(hotel_no CHAR(4) NOT NULL,guest_no CHAR(4) NOT NULL,date_from DATETIME NOT NULL,date_to DATETIME NULL,room_no CHAR(4) NOT NULL); Dates: YYYY-MM-DD

Populating the Tables

INSERT INTO hotelVALUES ('H111', 'Grosvenor Hotel‘, 'London'); INSERT INTO roomVALUES ('1', 'H111', 'S', 72.00); INSERT INTO guestVALUES ('G111', 'John Smith', 'London'); INSERT INTO bookingVALUES ('H111', 'G111', DATE'1999-01-01',

DATE'1999-01-02', '1');

Updating the Tables

UPDATE room SET price = price*1.05;

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 bookingWHERE date_to < DATE‘2000-01-01');DELETE FROM bookingWHERE date_to < DATE‘2000-01-01';

Simple Queries

List full details of all hotels.

List full details of all hotels in London.

List the names and addresses of all guests inLondon, alphabetically ordered by name.

List all double or family rooms with a pricebelow £40.00 per night, in ascending order ofprice.

List the bookings for which no date_to hasbeen specified.

Aggregate Functions

How many hotels are there?

What is the average price of a room?

What is the total revenue per night fromall double rooms?

How many different guests have madebookings for August?

Grouping

List the number of rooms in each hotel.

List the number of rooms in each hotel inLondon.

What is the average number of bookingsfor each hotel in August?

What is the most commonly bookedroom type for each hotel in London?

What is the lost income from unoccupiedrooms at each hotel today?