Chapter 7 - Queries

Data is manipulated through queries that target one or more tables of the database and specify the operations to perform to retrieve the data of interest. The database engine interprets the queries to retrieve the data quickly and return the result.

In this chapter we present how queries are designed. We first present queries using relational algebra, which helps to understand their semantics clearly. Then, for each type of query, we explain how to write it in SQL so that database engines can interpret it.

Single-table queries

A single-table query targets only one table. The goal is to retrieve some of the data contained in the target table, or to check that some data does not exist.

There are two main operations for retrieving data from a table: projection (π) and selection (σ).

Taking the table of authors as an example, the query π_prenom,nom(auteurs) projects the attributes prenom (first name) and nom (last name). Running this query gives Table 7.1.

Table 7.1: Result of the projection query.

In SQL, projection is done with the keyword SELECT, specifying the target table with the keyword FROM. Code 7.1 shows the SQL command that retrieves the attributes prenom and nom from the auteurs table.

SELECT prenom, nom FROM auteurs;

Code 7.1: SQL projection query.

Taking the table of authors as an example, the query σ_nom='Carjat'(auteurs) selects the rows whose last name is Carjat. Running this query gives the tuple (1, 'Etienne', 'Carjat').

In SQL, selection is done by adding a WHERE clause containing the selection criterion. Note that the WHERE clause must be used together with the SELECT projection. You can use the "*" character to retrieve all the columns. Code 7.2 shows the SQL command that retrieves the rows whose last name is Carjat.

SELECT * FROM auteurs WHERE nom='Carjat';

Code 7.2: SQL selection query.

SQL offers various operators to express selection criteria (=, <=, IN, LIKE, etc.). Selection criteria can also be combined using the logical operators AND, OR and NOT.

Multi-table queries

Multi-table queries retrieve data by using the associations between several tables. They are used when the information you are looking for is spread across different tables and must be combined.

The basic operation for multi-table queries is the Cartesian product. This operation combines the rows of two tables into a new table.

In SQL, there are two ways to express the Cartesian product. The first is simply to list the tables in the FROM clause. The second is to use the CROSS JOIN clause. Code 7.3 shows two example queries computing the Cartesian product of authors and images.

SELECT * FROM auteurs, images;
SELECT * FROM auteurs CROSS JOIN images;

Code 7.3: SQL Cartesian product query.

The result of the Cartesian product auteurs X images is shown in Table 7.2. You can see that every row of each table is combined with every row of the other in the result.

Table 7.2: Result of the query auteurs X images.

Note that the Cartesian product can produce a result table containing columns with the same name. Indeed, the Cartesian product auteurs X images gives a table with the attributes id, prenom and nom from the auteurs table, but also id, nom, annee and auteur from the images table. There are therefore two columns named id and two columns named nom. To deal with this problem, you must use the attribute renaming operation (ρ).

So, going back to our example, the query auteurs X ρ_{id -> pid, nom -> pnom, auteur -> phtg_id}(images) computes the Cartesian product of the auteurs and images tables, renaming the attributes id, nom and auteur of the images table to pid, pnom and phtg_id. Running this query gives Table 7.3.

Table 7.3: Result of the Cartesian product query with renaming.

In SQL, attributes are renamed with the AS clause. Code 7.4 expresses the Cartesian product of authors and images with attribute renaming.

SELECT p.id, p.prenom, p.nom, i.id AS pid, i.nom AS pnom, i.annee, i.auteur AS phtg_id FROM auteurs AS p, images AS i;

Code 7.4: SQL query with attribute renaming.

The Cartesian product combines two tables, but it adds rows that make little sense, especially when the two tables are associated by a foreign key. For example, the second row of the Cartesian product auteurs X images associates the author Etienne Carjat with the image Le baiser de l'hôtel de ville, whereas the foreign key says that Robert Doisneau took this picture (see Table 7.3). To avoid this problem, all the wrongly associated entities must be removed from the result of the Cartesian product. This can be done with the single-table selection operation (σ), selecting only the rows in which the foreign key equals the primary key (the author's id in our case). Thus the query σ_{images.auteur=auteurs.id}(auteurs X images) keeps only the entities that are consistent with the values of the primary and foreign keys.

This combination of a Cartesian product followed by a selection is used very often to manipulate the data of tables linked by foreign keys. It is called a join operation. A join combines data from several tables based on an equality relation between primary and foreign keys. The inner join operation is written with the symbol ⨝ in relational algebra. It computes the Cartesian product of the tables and keeps the rows that satisfy the specified equality condition.

In SQL, an inner join is obtained with the JOIN clause and the ON clause, which specifies the condition on the keys. Code 7.5 shows the SQL query that performs the inner join of auteurs and images. The result of this query is shown in Table 7.4. We have shown the effect of the selection operator by striking through the removed rows. You can clearly see that only the rows in which the foreign key (column auteur) matches the primary key (first id column) remain.

SELECT * FROM auteurs JOIN images ON auteurs.id = images.auteur;

Code 7.5: SQL inner join query.

Table 7.4: Result of the query auteurs ⨝_{auteurs.id = images.auteur} images.

Code 7.6 shows how to perform a join and rename the attributes of the tables to avoid name conflicts. The attribute nom of the images table is renamed titre (title), and only the author's nom and prenom and the image's annee (year) are kept in the result. The result of this query is shown in Table 7.5.

SELECT auteurs.prenom, auteurs.nom, images.nom as titre, images.annee FROM auteurs JOIN images ON auteurs.id = images.auteur;

Code 7.6: SQL join query with attribute renaming and projection to simplify the result.

Table 7.5: Result of a join with renaming and projection of attributes.

Query results

SQL queries return tables that often need further processing (sorting, computing an average, etc.) to obtain the desired result. Fortunately, SQL offers several operations to refine results. We present some of them here.

The first operation sorts the results of a query by one or more attributes. This is done directly in the query with the ORDER BY clause. By default, the order is ascending (from smallest to largest). The order can be specified with ASC or DESC. For example, Code 7.7 shows the SQL query that sorts the results by year.

SELECT * FROM auteurs JOIN images ON auteurs.id = images.auteur ORDER BY annee;

Code 7.7: SQL join query with sorting.

Another operation limits the size of the result table. This is done directly in the query with the LIMIT clause. For example, Code 7.8 shows the SQL query that sorts the results by year and returns only the first element.

SELECT * FROM auteurs JOIN images ON auteurs.id = images.auteur ORDER BY annee LIMIT 1;

Code 7.8: SQL join query with sorting and a limit.

Finally, SQL aggregate functions are powerful operations that compute aggregated values from several entities. For example, the COUNT() function counts the number of rows matching a given criterion. To illustrate its use, consider the case where we want to know the total number of authors in our database. We would then use COUNT(*) in our SQL query (see Code 7.9).

SELECT COUNT(*) FROM auteurs;

Code 7.9: SQL counting query.

The AVG() function computes the average of a given numeric column. Suppose the auteurs table had an age column and we wanted to know the average age of the authors. We would then use AVG(age) (see Code 7.10).

SELECT AVG(age) FROM auteurs;

Code 7.10: SQL query computing an average.

SQL offers many other functions, such as SUM(), which adds up all the values of a numeric column, MIN(), which finds the smallest value of a given column, or MAX(), which finds the largest value of a given column.

Nested queries

The results of an SQL query can be used in other queries; these are called nested queries (or subqueries).

A classic example is when a first query finds the maximum value with MAX() and a second query looks for the row containing this maximum value. Code 7.11 shows the SQL query that finds the author who created the image with the most likes (assuming the table of images has a column storing the number of likes).

SELECT * FROM auteurs JOIN images ON auteurs.id = images.auteur WHERE likes = (SELECT MAX(likes) FROM images);

Code 7.11: SQL join query with a nested query using the MAX() function.

Another possibility is to use the ALL and ANY quantifiers. The ALL quantifier checks that a condition holds for every row returned by a subquery, while the ANY quantifier checks that a condition holds for at least one of those rows. For example, the SQL query in Code 7.12 uses the ANY quantifier to return the authors who created at least one image, and the ALL quantifier to return the images with at least as many likes as every other image.

SELECT * FROM auteurs WHERE id = ANY (SELECT auteur FROM images);
SELECT * FROM images WHERE likes >= ALL (SELECT likes FROM images);

Code 7.12: SQL queries with the ANY and ALL quantifiers.

Practice

Review questions

Exercise 1 - Sports competitions

Project - Image wall