# Structured Query Language/Data Query Language

← Relational Databases | Data Manipulation Language →
Data Query Language is used to extract data from the database. It doesn't modify any data in the database. It describes only one query: SELECT.

## SQL data types

Each column has a type. Here are the standard SQL data types:
| Data type | Explanation | Allowed values | Example |
| VARCHAR(n) | A string with a maximum length of n | [0-9a-zA-Z]+{n} | "foo" |
| CHAR(n) | A string with a fixed length of n | [0-9a-zA-Z]{n} | "foo" |
| SMALLINT | A 16 bits signed integer | \-?[0-9]+ | 584 |
| INTEGER | A 32 bits signed integer | \-?[0-9]+ | -8748 |
| FLOAT | A decimal floating point | \-?[0-9]+[\.[0-9]+]? | 48.96 |
| NUMBER(n,[d]) | A number with n digits (and d decimal digits if mentioned) | \-?[0-9]+[\.[0-9]+]? | 484.65 |
| DATE | A date (YYYY-MM-DD) | [0-9][0-9][0-9][0-9]\-[0-1][0-9]\-[0-3][0-9] | 2009-03-24 |
| TIME | A time period of sixty minutes; one twenty-fourth of a day | [0-2][0-9]\:[0-5][0-9]\:[0-5][0-9] | 11:24:56 |
| TIMESTAMP | A date and hour | [0-9]+ | 18648689595962 |
| BLOB | Any binary data | Any | |
There is no boolean type. Integers are used instead.

## SELECT query

The exhaustive syntax of the SELECT query is as follows:

### First query

Let's create the table reunion with many columns:
reunion :
| id_reunion | INTEGER |
| name | VARCHAR(20) |
| description | VARCHAR(255) |
| priority | CHAR(1) |
| planned | SMALLINT |
| date | DATE |
| hour | TIME |
| duration | INTEGER |
| # id_office | INTEGER |
| pdf_report | BLOB |
...and let's fill it:
reunion :
| id_reunion | name | description | priority | planned | date | hour | duration | # id_office | pdf_report |
| 1 | Planning | We need to plan the project. | A | 1 | 2008-03-24 | 10:30:00 | 60 | 35 | 48644...846348 |
| 2 | Progress | What we have done. | C | 1 | 2008-05-12 | 14:00:00 | 30 | 13 | 9862...15676 |
| 3 | Change | What we need to change in the project. | B | 1 | 2008-06-03 | 9:30:00 | 90 | 41 | 34876...4846548 |
| 4 | Presentation | Presentation of the project. | D | 0 | 2008-09-11 | 15:30:00 | 120 | 27 | |
| 5 | Reporting | Explanation to the new beginner. | B | 1 | 2009-03-15 | 14:00:00 | 60 | 7 | 19739...37718 |
| 6 | Learning | A new software version has been installed. | B | 1 | 2009-09-21 | 16:00:00 | 120 | 11 | 785278...37528 |
Let's do a simple query. The following query just returns the content of the reunion table:
* Query:
* Result:
The form of the result depends on the client application. It can be returned as a text output (backend), a HTML page (thin client), a program object (middleware) etc... The statements, queries, clauses (SELECT, FROM...), instructions and operators are not case sensitive but they are commonly written in uppercase for readability.
The SELECT and FROM clauses are the two required clauses of a SELECT query:
* FROM : list the tables the query uses to return the data,
* SELECT : list the data to return.

### WHERE clause

The WHERE clause doesn't influence the columns the query returns but the rows. It filters the rows applying predicates on it. A predicate specifies conditions that can be true or false. SQL can handle conditions whose result is unknown. For example, the following query returns the reunions which have a B priority level:
* Query:
* Result:
The table name can be omitted if it is not ambiguous.

#### Predicate

Compared to the second operand, the first operand can be :
* equal : =
* different : <>
* lesser : <
* lesser or equal : <=
* greater : >
* greater or equal : >=
The following query returns the reunions which have another priority level than B:
* Query:
* Result:

#### Operators

The WHERE clause can have several conditions using the operators AND (all the conditions must be true) and OR (only one condition needs to be true). The operator OR is inclusive (several conditions can be true). The order of evaluation can be indicated with brackets. NOT inverts a condition. The following query returns the reunions which have a B priority level and last more than an hour or which take place on 2008/05/12:
* Query:
* Result:

#### LIKE

LIKE allows simplified regular expression matching. It can be applied on the text columns (CHAR, VARCHAR,...).
* Alphanumerical characters only match identical text,
* % is a wildcard that matches any text,
* _ is a wildcard that matches any single character,
The following query returns the reunions which end with "ing" and which contain " the " in its description:
* Query:
* Result:

#### BETWEEN and IN

BETWEEN matches a range of values that can be numbers, dates or times. IN matches a list of allowed values. The following query returns the reunions which take place between 2008-04-01 and 2009-04-01 and have an A, B or D priority level:
* Query:
* Result:

#### EXISTS

EXISTS is usually used with a subselect. This predicate is true if the list (i.e. the result set of a subselect) is not empty. This keyword allows to filter the returned rows using data that are not directly associated to the returned rows (i.e. they are not joined, not linked, not related... to the returned rows) so you can not use junction in this case. For instance, we want to retrieve all the reunions for which there is at least one reunion two times longer:
* Query:
* Result:
The duration of another reunion is used in this query whereas there is no join, no link and no relationship between the two rows. This condition can not be done without EXISTS. Note that the subselect uses the alias r1 whereas this alias is defined in the main query.
EXISTS is also used to match a lack of data. Let's remember the employee table and the members table:
| employee id_employeefirstnamelastnamephonemail 1BigBOSS936854270big.boss@company.com 2JohnDOE936854271john.doe@company.com 3LinusTORVALDS936854272linus.torvalds@company.com 4JimmyWALES936854273jimmy.wales@company.com 5LarryPAGE936854274larry.page@company.com 6MaxTHE GOOGLER936854275max.the-googler@company.com 7JennyTHE WIKIPEDIAN936854276jenny.the-wikipedian@company.com | members # id_employee# id_project 32 21 43 51 23 61 73 |
The following query returns the employees who are not linked to any project (i.e. the ones there is no relationship for them in the members table):
* Query:
* Result:

#### IS NULL

IS NULL tests if a column is filled. It is often used for foreign key columns.

### FROM clause

The FROM clause defines the tables that are used for the query but it can also join tables. A JOIN builds a super table with the columns of two tables to be used for the query. To explain what a join is, we consider two archaic tables without primary keys nor foreign keys:
| table_1 common_valuespecific_value_1 red9999 grey6666 white0000 purple7777 purple2222 black8888 | table_2 common_valuespecific_value_2 greenHHHHHH yellowPPPPPP blackFFFFFF redOOOOOO redLLLLLL blueRRRRRR |
We want to associate values from columns of different tables matching values on a given column in each table.

#### FULL OUTER JOIN

A JOIN is made matching a column on a table to a column on the other table. After a FULL OUTER JOIN, for a given value (red), for a given row with this value on one table ([ red | 9999 ]), one row is created for each row that matches on the other table ([ red | OOOOOO ] and [ red | LLLLLL ]). If a value exists in only one table, then a row is created and is completed with NULL columns.
| FROM table_1 FULL OUTER JOIN table_2 ON table_1.common_value = table_2.common_value | common_valuespecific_value_1specific_value_2 red9999OOOOOO red9999LLLLLL grey6666NULL white0000NULL purple7777NULL purple2222NULL black8888FFFFFF greenNULLHHHHHH yellowNULLPPPPPP blueNULLRRRRRR |

#### RIGHT OUTER JOIN

The RIGHT OUTER JOIN is like the FULL OUTER JOIN but it doesn't create row for values that don't exist on the left table.
| FROM table_1 RIGHT OUTER JOIN table_2 ON table_1.common_value = table_2.common_value | common_valuespecific_value_1specific_value_2 red9999OOOOOO red9999LLLLLL black8888FFFFFF greenNULLHHHHHH yellowNULLPPPPPP blueNULLRRRRRR |

#### LEFT OUTER JOIN

The LEFT OUTER JOIN is like the FULL OUTER JOIN but it doesn't create row for values that don't exist on the right table.
| FROM table_1 LEFT OUTER JOIN table_2 ON table_1.common_value = table_2.common_value | common_valuespecific_value_1specific_value_2 red9999OOOOOO red9999LLLLLL grey6666NULL white0000NULL purple7777NULL purple2222NULL black8888FFFFFF |

#### INNER JOIN

The INNER JOIN is like the FULL OUTER JOIN but it creates row only for values that exist on both the left table and the right table.
| FROM table_1 INNER JOIN table_2 ON table_1.common_value = table_2.common_value | common_valuespecific_value_1specific_value_2 red9999OOOOOO red9999LLLLLL black8888FFFFFF |

#### Alias

The FROM clause can declare several tables, separated by , and aliases can be defined for table name with the keyword AS, which allows the user to make several joins with the same tables. The following query is equivalent to the INNER JOIN above:
* Query:
The keyword AS can be omitted.

### SELECT clause

The SELECT clause doesn't influence the data processed by the query but the data returned to the user. * return all the data processed after joining and filtering. Otherwise, the SELECT clause lists expressions separated by ,.
The expressions can be a table name, a table name and a column name separated by a dot or simply a column name if it is not ambiguous. The SELECT clause also allows evaluated expressions like addition, subtraction, concatenation, ... An expression can be followed by an alias with the keyword AS. The keyword AS can be omitted.
Here is an example:
* Query:
* Result:
The expressions can be also the following aggregation functions:
* count(*): the count of rows returned,
* max(<column_name>): the greatest value of the column,
* min(<column_name>): the lowest value of the column.
Here is a new example:
* Query:
* Result:

### ORDER BY clause

The ORDER BY clause sorts the rows returned by the query by one or several columns. The sort is done with the first column mentioned. The second column is used to sort the rows which have the same value in the first column and so on. The keywords ASC or DESC can be added after each column. ASC indicates an ascending sort. DESC indicates a descending sort. Default is a descending sort. Let's do two simple requests, the first sorting by only one column and the second sorting by two columns:
* Query:
* Result:
* Query:
* Result:

### GROUP BY clause

The GROUP BY clause is used for aggregation operations. It gathers the rows into groups, for instance, all the rows that have the same value in a given column. After gathering rows into groups, any aggregation operation is applied on each group instead of a unique big group of rows. As a consequence, an aggregation operation will return as many result as the number of groups. Groups can be formed with all the rows that have the same value for a given column or the same combination of values for several given columns. For instance, we want to know the number of reunions for each type of priority:
* Query:
* Result:
Due to the GROUP BY clause, the aggregation function count(*) doesn't return a global count but a count for each priority level (A, B, C and D).
* Query:
* Result:
Note that there are four groups with 1 for the column planned and there are two groups with 120 for the column duration. However, you can see that there is no group with the same combination of values from the two columns.

### HAVING clause

The HAVING clause is used with the GROUP BY clause. The HAVING clause contains a predicate and removes from the returned rows the groups for which the predicate is false. For example, we want to retrieve only the priorities for which there are at least two reunions with the same priority level:
* Query:
* Result:

### FETCH FIRST clause

The FETCH FIRST clause is used to limit the number of returned rows. Only the first rows are returned. The number of returned rows is the number indicated in the clause.
* Query:
* Result:
This clause is often used not to return useless rows for test or to improve the performance.
Now you can explore all the data of an already existing database.

## SQL Functions

* COUNT
* AVG
* MIN
* MAX
* SUM
Eg:
returns the number of rows in the table reunion.
* See also: