Course 9: Sort, classify, calculate in SQL

Oct 2, 2021
Course 9: Sort, classify, calculate in SQL

Classify data

Classifying data consists, in SQL, of imposing the order in which they must be displayed. In the first example we wrote, the sailors left in a random order, which is not the order in which they were entered.

Imposing an order on this display is done using the directive order by, as in the following example.

Example 7. Selection in alphabetical order

select name, first name   from Sailors   order   by name, first name   asc ;

This command is used to classify the sailors by last name then by first name in case of equality of name, in ascending order (keyword asc). The result is as follows.

------------------------------ -------------------- ----------
Auguin Christophe
Bart Jean
Cartier Jacques
Dumont d'Urville Jules
Dupleix                        Joseph
Moitessier Bernard
Montcalm Louis
Surcouf Robert
Tabarly Eric

2.2. Sort data

Sorting data consists of extracting rows from a table according to certain criteria. So far we have extracted all the data from our table Marins. Here we just want to extract the lines that interest us. For example, extract the sailors born 18 th century, between 1701 and 1800:

Example 8. Sailors born 18 th century

select name, first name,   from Sailors 
     where birth <= 1701   and ddmort> 1801 
     order   by name;

The result is of course the following:

------------------------------ -------------------- ----------
Bart Jean
Dupleix                        Joseph
Montcalm Louis

2.3. Perform calculations

The SQL language also makes it possible to calculate expressions. These expressions can take as parameters a single cell, several cells of the same row, or several cells of the same column. These types of calculations do not have the same status, as we will see below. In this first example, we will limit ourselves to calculating expressions between cells on the same row.

Take, for example, the list of sailors, by calculating the age they were on the day of their death. This age can be obtained simply by subtracting their date of death and their date of birth.

Example 9. Calculation of the age of seafarers

select name, ddmort - birth   as age 
     from Sailors   order   by age   asc ;

Notice that we have created a column in this calculation, called age, and which takes the value ddmort - ddnaissance. Once this column has been created in the context of this calculation, we can sort it. The result of this calculation is as follows:

------------------------------ ----------
Auguin <null>
Montcalm 47
Bart                                   52
Dumont d'Urville 52
Surcouf 54
Cartier 66
Dupleix                                66
Tabarly 67
Moitessier                             69

Let us notice immediately that Auguin is present in this table, with the <null>value for his age. This is expected, and comes from the fact that Auguin is still alive, that his year of death is <null>. We will see the processing of values <null>in detail below.

Here we have created an additional column in a query, result of the calculation of other columns. You can also group rows together, and perform numerical calculations on these groupings, which are called aggregations .

Example 10. Counting sailors from our table

select count (name)   as number_of_   sailors from Sailors;

The function count()counts the number of values ​​present in the field nomof the entire table Marins. There are many other functions of this type, to calculate means, standard deviations, etc … The result is of course:


You can select part of the table, by adding criteria:

Example 11. Counting the number of seafarers born since 1700

select count (name) number_of_ 
     sailors from Sailors 
     where ddbirth <= 1700;

Here is the result :


With all these elements, it becomes possible to calculate the average age at which sailors have died since 1700:

Here is the result :

Example 12. Life expectancy of seafarers born since 1700

select avg (ddmort - ddnaissance)   as esperance_de_de_live
     from Sailors
     where ddnaissance> 1700;

Here is the result of that query.


2.4. Updating a value

Updating one or more values ​​of one or more columns is done using the command update. If, for example, an error has slipped into the date of birth of one of the sailors, it is possible to correct it using the command:

Example 13. Correction of the date of birth of Jean Bart

update Sailors   set ddnaissance = 1650
     where name =   'Bart' ;

An order Updatewhich is not followed by any clause Wherewould act on all the rows of the table concerned. We must therefore be careful when handling this command.

Leave a Reply

Your email address will not be published. Required fields are marked *