Gestion et analyse des données — Tutorial 2

SQL

The goal of this tutorial is to practice SQL queries on a relational database.

Environment setup

Start the virtual machine with Remote Desktop Connection.
Open pgAdmin4 (icon on the desktop).
On the left-side menu, right-click on Servers and select the PostgreSQL 18 to connect.
When prompted, enter the password postgres to connect to the server.
Select the database travel.
Open a new query tool (right-click on the database travel, select Query tool).

Database schema

The figure 1 shows the physical schema of the travel database. You can also obtain it within pgAdmin4 by right-clicking on the database travel, and selecting ERD for database.

Figure 1: The physical schema of the travel database

Foreign key contraints

Look at the physical schema of the travel database. Tables are linked through foreign key constraints.

For instance, the table flight_booking has a column trip_id that references the column trip_id in the table trip.

Exercise

Exercise 1 Try to delete a row from the table trip that is referenced by a row in the table flight_booking. What happens?

Solution

First, we need to identify a row in the table trip that is referenced by a row in the table flight_booking.

SELECT * 
FROM flight_booking JOIN trip USING (trip_id)
LIMIT 1;

The trip_id 0 is obtained from the output of the previous query.

Trying to delete the row from the table trip:

DELETE FROM trip
WHERE trip_id = 0;

Causes the following error:

ERROR:  update or delete on table trip violates foreign key constraint flight_booking_trip_id_fkey on table flight_booking
Key (trip_id)=(0) is still referenced from table flight_booking.

By default, the foreign key constraints prevent the deletion of a row that is referenced by another row in a different table.

Let’s loook at how the foreign key constraint is defined in table flight_booking.

Right-click on the table flight_booking in the left-side menu.
Select Properties.
Select the Constraints tab.
Select the Foreign Key tab.
Select the foreign key constraint named flight_booking_trip_id_fkey.
Click on the pencil icon to edit the constraint.
Select the Actions tab.

Here you can see that the action on delete is set to No Action.

Exercise

Exercise 2 How is the update action defined for the foreign key constraint flight_booking_trip_id_fkey in the table flight_booking?

Try to update a row in the table trip that is referenced by a row in the table flight_booking. What happens?

Solution

The update action is set to Cascade. That means that if we update the value of the primary key in the table trip, the corresponding foreign key values in the table flight_booking will be automatically updated.

Let’s update the trip_id 0 to 5432 in the table trip:

UPDATE trip
SET trip_id = 5432
WHERE trip_id = 0;

Now the corresponding foreign key values in the table flight_booking have been automatically updated.

Exploratory SQL queries

When working with a new database, it is important to explore the data to get familiar with its structure and content. We can use SQL queries to perform this exploration.

Exercise

Exercise 3

How many customers are men and how many are women?
What is the number of customers in each country?
What is the average age of customers in each country?
What is the total number of hotel reservations made for each hotel? Sort the results in descending order.
What is the total amount spent on hotel reservations by each customer? Sort the results in descending order and show only the top 10 customers.
How many airports are there in each country? List only the countries with more than 10 airports.

Solution

How many customers are men and how many are women?

SELECT gender, COUNT(*) AS num_customers
FROM customer
GROUP BY gender

What is the number of customers in each country?

SELECT country, COUNT(*) AS num_customers
FROM customer
GROUP BY country

What is the average age of customers in each country?

SELECT country, AVG(EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date))) AS avg_age
FROM customer
GROUP BY country

What is the total number of hotel reservations made for each hotel? Sort the results in descending order.

SELECT hotel_id, COUNT(*) AS num_reservations
FROM hotel_booking
GROUP BY hotel_id
ORDER BY num_reservations DESC

What is the total amount spent on hotel reservations by each customer? Sort the results in descending order and show only the top 10 customers.

SELECT customer_id, SUM(price) AS total_spent
FROM hotel_booking JOIN trip USING (trip_id)
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 10

How many airports are there in each country? List only the countries with more than 10 airports.

SELECT country, COUNT(*) AS num_airports
FROM airport
GROUP BY country
HAVING COUNT(*) > 10

Business queries

Suppose you are working for the travel agency that owns the database. You have been asked to provide some insights based on the data stored in the database.

Exercise

Exercise 4

Identify the top 5 most popular destinations based on the number of flights booked.
Find the average duration of hotel stays for each hotel.
Determine the customers who have made the highest number of trip bookings.
Analyze the monthly trends in flight bookings and hotel reservations.
List the details of the trip number 1. In particular, list the first and last names of the customer, and, for each flight, the departure and arrival airports, the departure date, and the departure and arrival city of the airports.

Solution

Identify the top 5 most popular destinations based on the number of flights booked.

SELECT airport_dst, city, country,  COUNT(*) AS num_bookings
FROM flight_booking JOIN flight USING (flight_id)
        JOIN airport ON airport_id=airport_dst
GROUP BY airport_dst, city, country
ORDER BY num_bookings DESC
LIMIT 5

Find the average duration of hotel stays for each hotel.

SELECT hotel_id, AVG(CHECKOUT_DATE - CHECKIN_DATE) AS avg_stay_duration
FROM hotel_booking
GROUP BY hotel_id

Determine the 10 customers who have made the highest number of (hotel and flight) bookings.

SELECT customer_id, first_name, last_name, COUNT(*) AS num_bookings
FROM (
    SELECT customer_id, first_name, last_name
    FROM flight_booking JOIN trip USING (trip_id) JOIN customer USING(customer_id)
    UNION ALL
    SELECT customer_id, first_name, last_name
    FROM hotel_booking JOIN trip USING (trip_id) JOIN customer USING(customer_id)
) AS all_bookings
GROUP BY customer_id , first_name, last_name
ORDER BY num_bookings DESC
LIMIT 10

Analyze the monthly trends in flight bookings and hotel reservations.

-- Flight bookings by month
SELECT EXTRACT(MONTH FROM departure_date) AS month, COUNT(*) AS num_flights
FROM flight_booking JOIN flight USING (flight_id)
GROUP BY month
ORDER BY month;

-- Hotel reservations by month
SELECT EXTRACT(MONTH FROM check_in_date) AS month, COUNT(*) AS num_reservations
FROM hotel_booking
GROUP BY month
ORDER BY month;

List the details of the trip number 1. In particular, list the first and last names of the customer, and, for each flight, the departure and arrival airports, the departure date, and the departure and arrival city of the airports.

SELECT c.first_name,
        c.last_name, 
        s.airport_name as departure_airport, 
        concat(s.city, ', ', s.country) as departure_loc, 
        departure_date,
        d.airport_name as arrival_airport,
        concat(d.city, ', ', d.country) as arrival_loc
FROM customer c
    JOIN trip t USING (customer_id)
    JOIN flight_booking USING (trip_id)
    JOIN flight f USING(flight_id)
    JOIN airport s ON s.airport_id = f.airport_src 
    JOIN airport d ON d.airport_id = f.airport_dst
WHERE t.trip_id=1