SQL

Examples: Extract data: select in SQL

204 Views
Oct 3, 2021
Examples: Extract data: select in SQL

What would be the point of saving data in a database, if there was no tool to extract it? The command selectis probably the most widely used in the language, and perhaps the most complex as well. We propose to begin our study with a few simple examples, before looking at the most complicated cases.

2.1. Extract data from a single table

We have already seen the use of this command on very simple cases in our introductory part. The simplest form of the command selectis as follows.

Example 33. Simple form of select

SELECT [ DISTINCT ] [table_name.] Column_name | * | expression [ AS column_alias], ...
     FROM table_name [ AS table_alias]
   [ WHERE predicate]
   [ ORDER   BY column_name [ ASC |  DESC ], ...

2.1.1. List of columns and expressions

The first argument of one selectis the list of columns or expressions that we want to see displayed. Each item in this list can be prefixed with a table name if it is a column. This prefixing is mandatory if two columns with the same names coexist in this list, in this case it is necessary to resolve the ambiguity.

Each column name or expression can be associated with an alias, so as to make the final display readable.

Let’s see some examples:

Example 34. Columns and expressions in a SELECT query

select last name, first name ...
  select max (birth) ...
  select Sailors.name   as sailor_name,

2.1.2. List of tables: clause from

Then comes the list of tables on which our selection operates. Normally there should only be one, since we refrain from using the old form of expression for joins, as we will see below. But this syntax is still allowed, and used.

Here again, it is possible to define an alias on the tables that are declared, for the purpose of better readability of the written SQL code.

Let’s see some examples:

Example 35. Table specifications in a SELECT query

select ...   from Sailors, Municipality ...
  select max (ddbirth)…
  select m1.name, m2.name   from Sailors   as m1, Sailor   as m2…


We will see in the following that it is also possible to put a query selectin the clause from. This is called a nested query.

2.1.3. Selection conditions: clause where

The selection conditions are optional. If they are not present, then all rows from the specified tables are returned. Otherwise only the rows for which the selection conditions are true are selected.

This clause is quite complex, so we’ll cover it in a later paragraph .

2.1.4. Classification of the result: clause order by

The result of one selectis usually unranked. If you want to classify it in alphabetical or numerical order, you must use a clause order by. This clause can take a list of columns as parameters.

This first query ranks the sailors by nom, in ascending order.

Example 36. ORDER BY ASC clause

select name, age   from Sailors   order   by name   asc ;


This second query classifies them in ascending order of name, then in descending order of age.

Example 37. ORDER BY DESC clause

select name, age   from Sailors   order   by name   asc , age   desc ;

2.1.5. Keyword distinct

Finally, the keyword distinctallows you to remove duplicates from the final result.

2.2. Clause where

This clause consists in evaluating a Boolean expression for all the rows of the table (or tables) as a parameter of the clause fromof this query. The argument of a clause wheremust therefore always be reduced to an expression whose result can only take two values: trueor false.

2.2.1. Comparison operators

Comparison operators are the classic operators of any programming language. The possible difference is that they operate on data of a different nature, and it is therefore necessary to define precisely how they work.

Table 8. Comparison operators

Operator Numerical comparison Textual comparison Time comparison
= Equal Equal Same time
< Smaller Before Earlier
> Bigger After Next
<= Smaller or equal Before or equal Not after
> = Greater or equal After or equal Not before
<> Different Different Not at the same time


Note that there is no negation operator in this table.

2.2.2. Comparison composition

It is possible to compose these logical operations using the following operators:

  • and : and classical logic;
  • or : or classical logic;
  • not : classical logical negation.

Everything would happen as in any programming language, if SQL did not introduce the notion of zero value, and allow their inclusion in Boolean operations.

For example, what is the result of andbetween a value trueand a value null? Or, which zero value is less than or greater than 2? The SQL response is specified: it is unknown.

2.2.3. Predicate is, case of values nulland unknown

As soon as a value, numeric, textual, temporal or Boolean, is compared to a zero value, then the result of the comparison takes the value unknown.

There is only one way to know if a value is null or unknown, it is to test it explicitly thanks to the predicate is, which is used as in the following examples.

This first example gives us all the sailors whose first name has been forgotten.

Example 38. IS NULL Predicate

select *   from Sailors   where firstname   is   null ;


This second example gives us all the sailors for which the date of death or the date of birth is zero (it is mainly a toy example to illustrate the use of is unknown).

Example 39. IS UNKNOWN Predicate

select name, (ddmort> birth)   as age_de_mort 
     from Sailors 
     where (dead> birth)   is unknown;


Note the use of the predicate iswith nulland unknown. It is this predicate that must be used if we want to test that a value is worth trueor false.

It is very important to note that nulland unknownare not equal to anything, not even to themselves.

Boolean algebra SQL is a bit special, in that it manages a third state in addition to the usual trueand falseunknown. Any Boolean operation with unknownresults unknown.

2.2.4. Predicate like

The predicate likeis used to compare strings using substitution characters. A string is compared to a pattern, the characters of which _and %represent respectively any single character, and any sequence of characters, possibly of zero length. Using the character %can lead to very long searches, so this predicate should be used with caution.

Let’s see some simple examples. The following query selects all sailors whose names begin with M.

Example 40. LIKE predicate

select *  from Marins  where nom  like  'M%' ;


The following query selects all the sailors whose name does not contain 4 letters and ends with “ly”.

Example 41. NOT LIKE predicate

select *  from Marins  where nom  not  like  '__ly' ;


Note that comparing strings using like compares strings character by character, unlike the comparer =. So if A = 'Bart'and B = 'Bart '(note the spaces at the end of the string), then will (A = B)return true, while that will (A like B)return false.

If a string comparison is attempted when one of the two strings is valid null, then the result will be unknown.

2.2.5. Predicate between

The predicate betweenallows you to test whether a value is between two other values. Here is an example of use.

Example 42. BETWEEN Predicate

select *  from Marins  where ddnaissance  between 1750  and 1950 ;

2.2.6. Predicate in

This predicate returns trueif the compared element is in the list as a parameter of in. This list can be explicit (as in our example), or can be expressed by a sub-query. The negation of indoes not necessarily return the complement, this is especially the case if there are null values.

Example 43. IN predicate

select *  from Marins  where id_commune  in (1, 3, 6) ;

2.2.7. Predicate exists

This predicate is used to test whether or not a query returns a result. This query is usually a subquery. Again, using the negation of existsdoes not necessarily return the complement, due to the null values.

We can check the validity of a join using this predicate. For example, let’s write a query that tests that all the fields in our column id_communehave a match in the table Communes:

Example 44. EXISTS predicate

select last name, first name, commune_id
     from Marin
     where   not   exists (
        select * 
        from Commune, Marin 
        where Commune.id = Marin.id_commune);

2.3. Nested queries

Nested queries (or subqueries) can be used in several places of a query:

  • as a parameter of a clause where, in this case the selection takes place on the result of this nested query;
  • as a parameter of a predicate existsor in.

Let’s see a simple example of a nested query in a clause where.

Example 45. Query nested in a clause where

select *   from (
      select Marins.name   as surname, Marins.prenom as firstname   , Communes.lastname   as common_name
      from Marins
          join Communes   on Marins.id_commune = Communes.id
)   as marin_commune 
  order   by name, first name;


Writing a nested query actually creates a table local to the query, which cannot be referenced elsewhere, with its own name, defined here defined by as marin_commune, and its own list of fields, also defined by aliases. This local table is also called inline view in the English documentation.

Once this local table is created, it can be used just like a normal table.

2.4. Aggregation functions, grouping

As we saw in the introductory chapter, it is possible to group several rows together on specific criteria, and to calculate expressions on certain particular columns of these rows.

The syntax of selectthen becomes a little more complex, and becomes the following.

Example 46. Simple form of select with aggregate function

SELECT [ DISTINCT ] [table_name.] Column_name | * | expression [ AS column_alias], ...
     FROM table_name [ AS table_alias]
   [ WHERE predicate]
   [ GROUP   BY list_of_column_to_group]
   [ HAVING condition_on_the_groups]
   [ ORDER   BY column_name [ ASC |  DESC ], ...

2.4.1. Aggregation functions

Here are the main aggregation functions of SQL.

Table 9. Aggregation functions

name Operation performed
AVG() Calculates the average of its arguments.
COUNT() Returns the number of arguments passed.
MAX() Returns the largest value of its arguments.
MIN() Returns the smallest value of its arguments.
SUM() Returns the sum of the arguments.

2.4.2. Examples of aggregation

Let us immediately see the simplest example, which allows counting the number of rows in any table.

Example 47. Using COUNT ()

select count(*)  from Marins ;


Let’s see a first simple example, which returns the average age calculated over the whole table Marins.

Example 48. Using AVG ()

select avg (age)   from Sailors;


This second example calculates the average age of sailors by municipality.

Example 49. Using AVG () … GROUP BY

select common_name, avg (age)   as average_age   from Sailors 
     group   by common_name;


The clause group bytells the SQL server that it must group the sailors by municipality before calculating the average age of these sailors, group by group. The result will therefore contain as many rows as there are different municipalities in the Sailors table.

The use of the clause havingmakes it possible to filter this type of result. In the following example, we are only interested in municipalities for which the average age is over 20 years.

Example 50. Using AVG () … GROUP BY … HAVING

select common_name, avg (age)   as average_age   from Sailors 
     group   by common_name
     having average_age> 20;


It is important to understand the difference between the clause whereand the clause having. In this type of query, the clause whereacts as a filter on the data before applying the data grouping. The clause havingacts once the groupings have been carried out and the aggregation functions have been evaluated.

Leave a Reply

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