1 Description of the data

We consider a database containing property transactions in France in 2025.

We discussed the conceptual schema of this database in the first tutorial.

The physical schema of the database is shown in the figure below.

Figure 1.1: The physical schema of the database

2 Import the data

Download the database from here the follow the instructions below.

1. Create the target database

   • In pgAdmin, right-click Databases → Create → Database.
   • Enter the database name and click Save.

2. Open Restore

   • Right-click the newly created database.
   • Select Restore…

3. Select the backup file

   • Set Format to Custom or tar.
   • Select file downloaded previously

4. Restore

   • Click Restore.
   • Monitor the progress in the Processes tab.

5. Refresh the database

   • Once the restore completes, right-click the database and select Refresh.
   • Expand Schemas → public to see the restored objects.

3 Exploratory queries

The first thing to do when you encounter a database for the first time is to explore its data. This is done using so-called exploratory queries.

First, we may want to know the size of our database.

👉 Exercise. Count the number of rows in all tables.

SELECT COUNT(*) 
FROM sales_transaction

Next, we may want to learn more about sales transactions. Note that there is a column called transaction_type.

👉 Exercise. What are the distinct transaction types in our database?

SELECT DISTINCT(transaction_type)
FROM sales_transaction

Continuing our exploration, we note that there is a column called property_value in the sales_transaction table. Let’s find out more. There may be a big surprise here!

👉 Exercise. What is the maximum, minimum and average property value across all transactions?

SELECT MAX(property_value) AS max_property_value, 
      MIN(property_value) AS min_property_value,
      AVG(property_value) AS avg_property_value
FROM sales_transaction

Let’s find out more about the properties.

👉 Exercise. What are the different property types?

SELECT DISTINCT(property_type)
FROM property

We would like to learn more about the characteristics of apartments.

👉 Exercise. What is the average built area, land area and number of main rooms in an apartment?

SELECT AVG(built_area) AS avg_built_area, 
       AVG(land_area) AS avg_land_area,
         AVG(num_main_rooms) AS avg_main_rooms
FROM property
WHERE property_type='Appartement'

Now we would like to compare houses and apartments using the same characteristics.

👉 Exercise. Can you write a single query that displays the same characteristics as in the previous exercise for both apartments and houses? Hint. GROUP BY is your friend.

SELECT property_type, 
       AVG(built_area) AS avg_built_area, 
       AVG(land_area) AS avg_land_area,
         AVG(num_main_rooms) AS avg_main_rooms
FROM property
WHERE property_type IN ('Appartement', 'Maison')
GROUP BY property_type

SELECT COUNT(*) - COUNT(DISTINCT city_name)
FROM city

SELECT (COUNT(*) - COUNT(DISTINCT city_name) >0 ) as "duplicate_city_names?"
FROM city

SELECT city_name
FROM city
GROUP BY city_name
HAVING COUNT(*) > 1

ALTER TABLE department
ADD CONSTRAINT dept_name_unique UNIQUE(dept_name)

ALTER TABLE region
ADD CONSTRAINT reg_name_unique UNIQUE(reg_name)

4 Set operations

SQL provides operators to work with sets.

• UNION: returns the union of two tables. The result does not include duplicates.

• UNION ALL: returns the union of two tables while preserving duplicates.

• INTERSECT: returns the rows that are common between two tables.

• EXCEPT: returns the rows from the first table that do not occur in the second table.

Important: the tables involved in these operations must have compatible schemas.

👉 Exercise. Find the identifiers of the properties involved in at least one sale transaction (“Vente”) and at least one exchange transaction (“Echange”).

Hint. An SQL query always returns a table, so you can use a set operator between the results of two SQL queries.

SELECT DISTINCT(property_id)
FROM sales_transaction
WHERE transaction_type='Vente' 
INTERSECT
SELECT DISTINCT(property_id)
FROM sales_transaction
WHERE transaction_type='Echange'

👉 Exercise. Find the identifiers of the properties involved in a sale transaction (“Vente”) or an exchange transaction (“Echange”), but not in both.

SELECT DISTINCT(property_id)
FROM sales_transaction
WHERE transaction_type IN ('Vente', 'Echange')
EXCEPT (
SELECT property_id
FROM sales_transaction
WHERE transaction_type='Vente' 
INTERSECT
SELECT property_id
FROM sales_transaction
WHERE transaction_type='Echange'
)

5 Working with dates

Do you want to know how many days remain until the database exam?

SELECT DATE('2026-12-07') - CURRENT_DATE

Or do you want to know which day of the week the exam falls on?

SELECT TO_CHAR(DATE '2026-12-07', 'Day');

The previous queries show that:

• You can create a date from a string using the function DATE.

• You can get today’s date using CURRENT_DATE.

• You can apply mathematical operators to dates.

• You can extract the name of the day and month from a date.

You can refer to the PostgreSQL documentation to learn about other functions and operators.

Let’s apply some of these operators to the transaction_date column in the sales_transaction table.

👉 Exercise. Find the number of sales (transaction_type = “Vente”) and the average property value per month for the year 2025.

HINT. The EXTRACT function is your friend.

Good to know. You can also plot the result using the graph visualizer integrated into pgAdmin.

SELECT EXTRACT(MONTH FROM transaction_date) as transaction_month, 
       AVG(property_value) AS avg_property_value,
       COUNT(*) AS nb_sales
FROM sales_transaction
WHERE transaction_type='Vente' AND EXTRACT(YEAR FROM transaction_date) = '2025'
GROUP BY transaction_month

Does the number of transactions depend on the day of the week? Is Saturday the day with the most transactions? Let’s find out.

👉 Exercise. Find the number of sales (transaction_type = “Vente”) per day of the week for the year 2025.

SELECT TO_CHAR(transaction_date, 'Day') as transaction_day, 
       COUNT(*) AS nb_sales
FROM sales_transaction
WHERE transaction_type='Vente' AND EXTRACT(YEAR FROM transaction_date) = '2025'
GROUP BY transaction_day
ORDER BY nb_sales DESC

In the previous section, we found that some properties are involved in both a sale transaction and an exchange transaction. Let’s analyze whether different transactions involving the same property occur on the same day or, if not, how many days separate them.

6 Querying multiple tables

You already know that a relational database is a collection of tables, with each table containing data about a specific entity. Most queries need data from multiple tables to produce a result. This is where join operations come into play.

👉 Exercise. Write a query to retrieve the name of each department together with the name of its corresponding region.

SELECT dept_name, reg_name
FROM department JOIN region USING (reg_code)

Let’s find out more.

👉 Exercise. How many departments does each region have? Sort the result so that the regions with the most departments appear first.

SELECT reg_name, COUNT(*) as nb_depts
FROM department JOIN region USING (reg_code)
GROUP BY reg_name
ORDER BY nb_depts DESC

👉 Exercise. How many cities does each region have? Sort the result so that the regions with the most cities appear first.

SELECT reg_name, COUNT(*) as nb_cities
FROM city JOIN department USING(dept_code)
          JOIN region USING (reg_code)
GROUP BY reg_name
ORDER BY nb_cities DESC

Are there any cities with no property registered in a transaction?

Let’s find out.

👉 Exercise. Write a query that returns the cities that have no properties registered in a transaction.

HINT. This is a useful case for testing an OUTER JOIN.

SELECT city_id, COUNT(property_id) AS nb_properties
FROM property RIGHT OUTER JOIN street USING (street_id) 
              RIGHT OUTER JOIN city USING (city_id)
GROUP BY city_id
HAVING COUNT(property_id) = 0

7 Conditional logic

Every major DBMS provides functions that reproduce the behavior of an if-then-else conditional statement. These functions are generally not part of the SQL standard, with the exception of the CASE expression. A CASE expression can be used in SELECT, INSERT, UPDATE, and DELETE statements.

A CASE expression has the following syntax:

CASE
  WHEN C1 THEN E1
  WHEN C2 THEN E2
  ...
  WHEN CN THEN EN
  [ELSE ED]
END

• C1, C2, …, CN represent Boolean predicates (conditions).

• E1, E2, …, EN represent expressions returned by CASE if the corresponding WHEN clause evaluates to TRUE. An expression can return any type (string, integer), including a whole subquery.

• The ELSE clause is optional (hence, it is shown in brackets). The expression ED associated with ELSE is returned if none of the previous conditions evaluates to TRUE.

Example. Run the following example to understand how a CASE expression works.

SELECT property_id,
      CASE
        WHEN land_area IS NOT NULL THEN 'yes'
        ELSE 'no'
      END AS property_with_garden
FROM property;

Let’s use CASE expressions now.

👉 Exercise. Write a query that returns the category of each property based on its property value. The categories are given in the following table.

SELECT property_id,
    CASE
        WHEN property_value < 100000 THEN 'Very cheap'
        WHEN property_value >= 100000 AND property_value < 250000   THEN 'Affordable'
        WHEN property_value >= 250000 AND property_value < 500000 THEN 'Mid-range'
        WHEN property_value >= 500000 AND property_value < 1000000 THEN 'High-end'
        WHEN property_value >= 1000000 AND property_value < 2000000 THEN 'Luxury'
        ELSE 'Luxury'
    END AS category
FROM property JOIN sales_transaction USING(property_id)

👉 Exercise. Write a query that returns the price per square meter for each property that is a house. Order the result by price in descending order. Use the ROUND function to round the price to the nearest whole number. HINT. Remember that, for some properties, the built area may be 0 or NULL. We need to handle these cases to avoid an error.

SELECT property_id,
    CASE 
        WHEN built_area IS NULL OR built_area = 0 THEN 0
        ELSE ROUND(property_value/built_area)
    END AS price_per_sqm
FROM property JOIN sales_transaction USING (property_id)
WHERE property_type='Maison'
ORDER BY price_per_sqm DESC

In the result of the previous query, you may have noticed some very high prices per square meter. These correspond to particular cases. For an analysis of the housing market, we may want to ignore them. For example, we may want to consider only houses with a price per square meter less than or equal to 15,000.

👉 Exercise. Try to apply this filter. Can you do it? If not, that is normal: we need a subquery.

8 Subqueries

A subquery is a query contained within another SQL statement; the latter is referred to as the containing statement. The containing statement can be another query or an INSERT, DELETE, or UPDATE statement.

A subquery is always enclosed in parentheses, and it is usually executed before the containing statement. Like any other query, a subquery always returns a table. The number of rows and columns in the returned table affects how the result of the subquery can be used.

SELECT dept_code, dept_name
FROM department
WHERE reg_code <> 
    (SELECT reg_code
     FROM region
     WHERE reg_name = 'Île-de-France' )

In the previous example, the subquery returns only one row and one column. This is known as a scalar subquery and can appear on either side of a condition using the operators =, <>, <, >, <=, >=.

👉 Exercise. Write a query that returns the city name, department name, and region name for all properties that have the maximum built area.

SELECT property_id, built_area
FROM property
WHERE built_area = (
    SELECT MAX(built_area) AS min_built_area
    FROM property
)

If you use these operators with subqueries that return more than one row or column, you get an error, as in the following example:

SELECT dept_code, dept_name
FROM department
WHERE reg_code =
    (SELECT reg_code
     FROM region
     WHERE reg_name <> 'Île-de-France' )

SELECT dept_code, dept_name
FROM department
WHERE reg_code IN
    (SELECT reg_code
     FROM region
     WHERE reg_name <> 'Île-de-France' )

SELECT reg_name, COUNT(dept_code) AS nb_depts
FROM region JOIN department USING (reg_code)
GROUP BY reg_name
HAVING COUNT(dept_code) <= ALL (
    SELECT COUNT(dept_code)
    FROM region JOIN department USING (reg_code)
    GROUP BY reg_name
)

SELECT DISTINCT property_type, num_main_rooms 
FROM property
WHERE built_area < 1000 
    AND (property_type, num_main_rooms) IN (
        SELECT property_type, num_main_rooms
        FROM property
        WHERE built_area >= 1000
);

SELECT DISTINCT city_name, dept_name
FROM sales_transaction 
    JOIN property USING(property_id)
    JOIN street USING (street_id)
    JOIN city USING (city_id)
    JOIN department USING (dept_code)
WHERE dept_code <> '75' 
    AND (property_value, transaction_date) IN (
        SELECT property_value, transaction_date
        FROM sales_transaction 
        JOIN property USING(property_id)
        JOIN street USING (street_id)
        JOIN city USING (city_id)
        JOIN department USING (dept_code)
        WHERE dept_code = '75'
    )

SELECT city_name, avg_built_area
FROM (
    SELECT c.city_name,
           AVG(p.built_area) AS avg_built_area
    FROM property p
    JOIN street s USING (street_id)
    JOIN city c USING (city_id)
    GROUP BY c.city_name
) AS city_averages
WHERE avg_built_area > 100;

SELECT property_id, price_per_sqm
FROM (
    SELECT property_id,
        CASE 
            WHEN built_area IS NULL OR built_area = 0 THEN 0
            ELSE ROUND(property_value/built_area)
        END AS price_per_sqm
    FROM property JOIN sales_transaction USING (property_id)
    WHERE property_type='Maison'
) prices_per_sqm
WHERE price_per_sqm <= 15000    
ORDER BY price_per_sqm DESC

SELECT property_id, property_type, property_value
FROM property p
    JOIN sales_transaction USING (property_id)
    JOIN (
            SELECT p2.property_type, AVG(st2.property_value) AS avg_value
            FROM property p2
                JOIN sales_transaction st2 USING (property_id)
            GROUP BY p2.property_type
        ) avg_by_type
    USING(property_type)
WHERE property_value > avg_value;