www.techdos.com - THE SQL A to Z: 2nd party - the simple (?) SELECT and functions SQL

Main Menu

THE SQL A to Z: 2nd party - the simple (?) SELECT and functions SQL

Monday, 02 June 2008

1. The SELECT
You can find additional information about the 53 to 127 pages of the book "SQL" collection "La Reference", Campus Press publisher.
The SELECT command is the basic SQL to extract data from a base or calculate new data from existing ...
Here is the general syntax of a command SELECT:

SELECT [DISTINCT or ALL] * or list of columns
FROM table name or sight
[WHERE predicate]
[GROUP BY order of groups]
[HAVING condition]
[ORDER BY ] list of columns

NOTE: In this syntax, the key words of SQL are bold, parameters and a tiny brackets are the parties optional
In fact the order SQL SELECT is composed of 6 clauses which 4 are optional.
Clauses of the order SELECT:

SELECT	Specifying columns of result
FROM	Specifications tables covered by the order
WHERE	Filter on the data (requirements if the lines are present in the result)
BY GROUP	Definition of a group (subset)
HAVING	Filter on the results (conditions reunification lines)
BY ORDER	Sorting data result

NOTE: Most of the time, the difficulty lies in understanding the difference between the filter and the filter WHERE HAVING. Let more pragmatically that the filter WHERE allows filtering data tables while the HAVING filter helps to filter data on the outcome ...

NOTE: To specify a literal value must be surrounded by single quotes.
A first basic example:
Example 1

SELECT CLI_NOM, CLI_PRENOM
FROM T_CLIENT
WHERE TIT_CODE = 'M.'

CLI_NOM   CLI_PRENOM
-------             ----------
Alain                 DUPONT
MARTIN              Marc
Alain                 BOUVIER
Paul                  DUBOIS
Jean                  DREYFUS
Alain                  FAURE
PAUL                 Marcel
DUVAL              Arsène
PHILIPPE          André
Daniel               CHABAUD
BAILLY             Jean-François
...

Lets find the names of customers whose title is' Mr. '(sir).

NOTE: like all parameters to be in the form of literal cast must be surrounded by quotes (single ribs), should be split such a character if it is present in the channel used.

1.1. The operator * (star)

The character * (star) gets all the columns of the table specified in the FROM clause of the query.
Just after the key word SELECT, it specifies the columns of the table to be presented in the response.
The use of the star brings all columns of the table in the response. Otherwise we must explicitly naming each column and separated by commas.

Example 2

SELECT *
FROM T_CLIENT
WHERE TIT_CODE = 'M.'

CLI_ID

1
2
3
4
5
6
11
12
13
16

TIT_CODE

CLI_NOM

DUPONT

MARTIN

BOUVIER

DUBOIS

DREYFUS

FAURE

PAUL

DUVAL

PHILIPPE

CHABAUD

CLI_PRENOM

Alain

Marc

Alain

Paul

Jean

Alain

Marcel

Arsene

Andre

Daniel

CLI_ENSEIGNE

NULL

Transports MARTIN & fils

NULL

Bakery Market

Co. International Mach ...

NULL

NULL
...

Note immediately the presence on several occasions the key word "NULL" in cologne CLI_ENSEIGNE. No this is not a sign, but simply the absence of information. We will see that no information is the mark "NULL" which differs from the chain of pristine nature ( "") or even zero.

1.2. The operator DISTINCT (or ALL)

When the engine built the answer, he returned all the items, usually in the order or it finds them, even if they are duplicates, meaning it gets all lines (ALL default). That is why it is often necessary to use the key word DISTINCT which helps eliminate duplication in the response.

Examples 3 and 4

SELECT CLI_PRENOM FROM T_CLIENT WHERE TIT_CODE = 'M.'	CLI_PRENOM ---------- Alain Marc Alain Paul Jean Alain Marcel Arsène André Daniel ...
SELECT distinct CLI_PRENOM FROM T_CLIENT WHERE TIT_CODE = 'M.'	CLI_PRENOM ---------- Alain Alexandre André Arnaud Arsène Bernard Christian Christophe Daniel Denis ...

1.3. The operator AS

You can add as many columns as you like using the keyword AS.
In principle the operator AS serves to name new columns created by the application.

Example 5

SELECT CLI_NOM as NAME, 'man' asSEXE
FROM T_CLIENT
WHERE TIT_CODE = 'M.'

NAME SEX
------- -----

DUPONT      man
MARTIN       man
BOUVIER     man
DUBOIS       man
DREYFUS    man
FAURE         man
PAUL           man
DUVAL         man
PHILIPPE     man
CHABAUD    man
...

1.4. Operator concatenation

The operator | | (double vertical bar) can concatenate fields type characters.

Example 6

SELECT TIT_CODE || ' ' || CLI_PRENOM || ' ' || CLI_NOM as NOM
FROM T_CLIENT

NAME
-----------------------
Mr. Alain DUPONT
Mr. Marc MARTIN
Mr. Alain BOUVIER
Mr. Paul DUBOIS
Mr. Jean DREYFUS
Mr. Alain FAURE
Mr. Paul LACOMBE
Melle. Evelyne DUHAMEL
Ms. Martine BOYER
Mr. Martin MARTIN
...

Nevertheless found in some RDBMS + as the concatenation operator, the job CONCAT.

1.5. Operators basic mathematical

It can use basic mathematical operators to combine different columns
(,+,-, *, /).

Example 7

SELECT CHB_ID, TRF_CHB_PRIX * 1.206 AS TARIF_TTC
FROM TJ_TRF_CHB
WHERE TRF_DATE_DEBUT = '2001-01-01'

CHB_ID TARIF_TTC
------      ---------
      1     424.51
      2     482.40
      3     617.47
      4     424.51
      5     463.10
      6     482.40
      7     424.51
      8     540.29
      9     482.40
     10     617.47
...

1.6. Special "FROM"

It is possible nicknamed a table in the FROM clause, in this case, the syntax of the part of the FROM SELECT is as follows:

FROM tbl_name nom_de_la_vue or nickname

We will see in what circumstances this reputation is necessary or compulsory.
NOTE: Some authors prefer to use the word aliases that we reject because it often indicates another concept, or mean that we accept so timid ...

1.7. Using the dual character quote (quote)
When a name of an element of a database (table, column, for example) is the same as a keyword SQL, it should be surrounded by quotes (double quote). In principle, reserved words of SQL are not recommended for naming objects of physical model data ...

Imagine a table name JOIN composed of the following fields:

SELECT     NAME    NOT      DATE
-------           ------        -------      ---
Yes        DURAND    F        1999-11-12
No          DUVAL       M       1998-01-17
Example 8: we want to select columns SELECT and date when the column is NOT F. ..

SELECT SELECT, DATE FROM JOIN WHERE NOT = 'F'	ERROR!
SELECT "SELECT", "DATE" FROM "JOIN" WHERE "NOT" = 'F'	Correct: one surrounds the keywords SQL by double coasts

It is also necessary when the name (a column or a table) is composed of characters such as white or other, which is to be avoided.

NOTE: The names of IDs database object must be written in the limited character sets:
[A.. Z] + [a.. Z] + [0 .. 9] + [_].
They should not begin with a number and are insensitive (indifference between uppercase and lowercase).

2. The clause BY ORDER

You can find additional information about the 66 to 70 pages of the book "SQL" collection "La Reference", Campus Press publisher.

BY ORDER column1 | 1 [ASC or DESC] [, column2 | 2 [ASC or DESC] ...

This clause allows us to define the sorting columns of the answer, either by specifying the literal name of the column, indicating its order No in the list that follows the key word SELECT.
ASC specifies the ascending and descending order DESC sorting. ASC DESC or may be omitted, in this case is ascending order, which is used by default.

Although the ORDER BY clause is not necessary, it is often useful to sort the response in terms of columns. However, the response time often suffers.
To specify the sort order, we must place the names of columns separated just after the key word "ORDER BY", in order wanted .. You can also use the rank of each column in the order specified in the SELECT clause.

Warning: sorting is an internal sorting, it must therefore put in the clause that the names of columns presented in the SELECT clause.

Often, the placement of DISTINCT enough, in general, to establish a tri since the engine must engage in a comparison of lines but this mechanism is not guaranteed because the sorting is done in a non-controllable which may vary One server to another.

Example 9

SELECT CLI_NOM, CLI_PRENOM
FROM T_CLIENT
ORDER BY CLI_NOM, CLI_PRENOM
or
SELECT CLI_NOM, CLI_PRENOM
FROM T_CLIENT
ORDER BY 1, 2

CLI_NOM    CLI_PRENOM
--------            ----------
AIACH          Alexandre
Christian       ALBERT
AUZENAT      Michel
BACQUE       Michel
Jean-François BAILLY
Frédéric         BAVEREL
BEAUNEE      Pierre
BENATTAR     Bernard
BENATTAR     Pierre
Joël               BENZAQUI
...

NOTE: NULL markers are located primarily in the order established.

NOTE: A problem, which is not resolved, is to choose the order of columns of the answer. On some servers that can be obtained by placing the names of columns to get in the order in which one wants to see them appear in the SELECT clause, but this possibility is never guaranteed ...

ATTENTION: ORDER BY clause is the last clause of any kind and SQL should appear only once in the SELECT even s'i the requests are intertwined or a set of queries sets.

3. The WHERE clause
You can find additional information about the 62 to 165 pages of the book "SQL" collection "La Reference", Campus Press publisher.

WHERE predicate
The predicate must contain any logical expression returning a true value.
For example, a query as stupid as the next, is supposed to work:

Example 10

SELECT CLI_NOM
FROM T_CLIENT
WHERE 1=1

CLI_NOM
-------
DUPONT
MARTIN
BOUVIER
DUBOIS
DREYFUS
FAURE
LACOMBE
DUHAMEL
BOYER
MARTIN
...

Warning: Most RDBMS do not include a column boolean. A complaint as the first may fail.

Example 11

SELECT * FROM TJ_CHB_PLN_CLI WHERE CHB_PLN_CLI_OCCUPE	ERROR! although CHB_PLN_CLI_OCCUPE can be boolean, most compilers SQL does not direct this test.
SELECT * FROM TJ_CHB_PLN_CLI WHERE CHB_PLN_CLI_OCCUPE = True	CORRECT ... But on some SQL compiler must be: CHB_PLN_CLI_OCCUPE = 'True' (literally). If the boolean does not exist, then it must be CHB_PLN_CLI_OCCUPE = 1 if we chose to define booleans as INTEGER (1) with 0 and 1

To level the lack of boolean, using either a literal (True / False, True / False, Yes / No) or a digital with the values 0 (False) and 1 (True). The advantage of numerical values is that the calculation logic is comparable to the divisions and additions ...

AND operator	FALSE	True
FALSE	FALSE	FALSE
True	FALSE	True

similarity between the AND and multiplication

multiplication	0	1
0	0	0
1	0	1,<>0

OR	FALSE	True
FALSE	FALSE	True
True	True	True

similarity between OU and the addition

addition	0	1
0	0	1,<>0
1	1,<>0	2,<>0

3.1. Operators comparison

In the WHERE clause, you have different operators logical comparisons:

WHERE value1 [NOT and] = or <or <= or> or> or = <> value2 [OR and AND ...]

Example 12

SELECT CLI_NOM, CLI_PRENOM
FROM T_CLIENT
WHERE CLI_NOM >= 'A' AND CLI_NOM <'E'

or
SELECT CLI_NOM, CLI_PRENOM
FROM T_CLIENT
WHERE (CLI_NOM >= 'A')
AND
(CLI_NOM <'E')
more readable!

CLI_NOM       CLI_PRENOM
-------                ----------
DUPONT           Alain
BOUVIER          Alain
DUBOIS            Paul
DREYFUS         Jean
Evelyne         DUHAMEL
BOYER           Martine
Arsene              DUVAL
Amelie            DAUMIER
CHABAUD       Daniel
Jean-François   BAILLY
...

Here we get all the names of customers whose name begins with the letters A, B, C or D.

Warning: in some engines SQL operator "different" (<>) is written! =

3.2. IN Operator
IN allows the operator to determine whether a value lies in a set, whatever the type of reference values specified (alpha, digital date…). Of course, it is possible to reverse the operation of the operator IN by adding the NOT operator.

Example 13

SELECT TIT_CODE, CLI_NOM, CLI_PRENOM
FROM T_CLIENT
WHERE TIT_CODE IN ('Mme.', 'Melle.')

TIT_CODE   CLI_NOM         CLI_PRENOM
--------          ----------             ----------
Ms.             BOYER              Martine
Ms.             Noëlle           GALLACIER
Ms.             Lucette                HESS
Ms.             LETERRIER       Monique
Ms.             MARTINET         Carmen
Ms.             Jacqueline          DAVID
Ms.             MOURGUES      Jacqueline
Ms.             ZAMPIERO         Annick
Ms.             Marie-Louise        ROURE
Ms.             Patricia            DE CONINCK
...

We are looking for customers female, based on the code title.
The content of the bracket may be replaced by the result of a query with a single column. In this case one speaks of nested queries, what we will see further.

3.3.Operator BETWEEN
The operator BETWEEN allows to determine whether a value lies in a given interval, regardless of the type of reference values specified (alpha numeric, date ...).

Thus, the motion seen in the example 12 can be written:

Example 14

SELECT CLI_NOM, CLI_PRENOM
FROM T_CLIENT
WHERE CLI_NOM BETWEEN 'A' AND 'E'

CLI_NOM        CLI_PRENOM
-------                     ----------
DUPONT               Alain
BOUVIER              Alain
DUBOIS                Paul
DUBOIS               Jean
Evelyne             DUHAMEL
BOYER                Martine
Arsène                  DUVAL
Amélie                 DAUMIER
CHABAUD             Daniel
Jean-François        BAILLY
...

NOTE: operators IN BETWEEN and are very useful in cases where one wishes to make applications where the user can enter a list of multiple choice (IN) or a range of value (BETWEEN).

3.4. Operator LIKE

The LIKE operator can make a partial comparison. It is used mainly with columns containing data type alpha. It uses wildcards% and _ ( 'percent' and 'white stressed'). The joker% replaces any string, including the empty string. White stressed replaces a single character.

Example 15

SELECT CLI_NOM, CLI_PRENOM
FROM T_CLIENT
WHERE CLI_NOM LIKE 'B%'

CLI_NOM     CLI_PRENOM
-------                  ----------
BOUVIER             Alain
BOYER              Martine
Jean-François      BAILLY
BOUCHET            Michel
BEAUNEE            Pierre
BERGER          Jean-Pierre
Andre                 BOURA
Joel                  BENZAQUI
Frederic              BAVEREL
BERTRAND         Christophe
...

We are looking for customers whose name begins with B.
But if your data are likely to contain one of two wildcards, then we must use an escape sequence, using the keyword ESCAPE ...

Let customers with the sign contains at least one white character stressed:

Example 16

SELECT *
FROM T_CLIENT
WHERE CLI_ENSEIGNE
LIKE '%_%'

CLI_ID   TIT_CODE   CLI_NOM CLI_PRENOM    CLI_ENSEIGNE
------          --------           ----------        -------------    ------------- -----------------
      2       M.               MARTIN       Marc       MARTIN Transport & son
      6       M.               FAURE        Alain        Bakery Market
     10      M.                MARTIN     Martin     HERMAREX IMPORT_EXPORT
     11      M.               PAUL          Marcel       Co. International Mach ...
     17      M.            BAILLY     Jean-François Company DUPONT HEATING
     24       M.   *** CHTCHEPINE    Dominica     HOTEL OF THE STATION
     26      M.               IBM Corp.    GARREAU            Paul.
     34      Ms.           GALLACIER     Noëlle         Transport GALLACIER
     42      Ms.           LETERRIER     Monique      SA AROMAX ENTREVONT
     49       M.      COULOMB    Renaud Cabinet COULOMN and CALEMANT

SELECT *
FROM T_CLIENT
WHERE CLI_ENSEIGNE
LIKE '%#_%' ESCAPE '#'

CLI_ID TIT_CODE CLI_NOM CLI_PRENOM CLI_ENSEIGNE
------ -------- ---------- ------------- ------------- -----------------
10 M. MARTIN Martin HERMAREX IMPORT_EXPORT

To deal with this case, one defined "#" character as exhaust. The character that follows the escape character is thus interpreted as a character and not as a wildcard.

NOTE: LIKE operator conducts a search taking into account the difference between upper and lower case letters. If you want to do a search by not taking no account of the difference between upper and lower case should be used and operators LOWER UPPER (see below). But most of the time, the use of RDBMS like in a given ignore the case.

3.5. Summary of operators for the predicates of the WHERE clause

Here is a table summarizing the main operators used in the construction of predicates:

Operators comparisons	= <> <<=>> =
logical connectors	OR (\| AND)
operator of negation	NOT
parenthesis	(...)
mathematical operators	+ - * /
comparison logic	IS [NOT] (TRUE \| FALSE \| UNKNOWN)
Compared with value	IS [NOT] NULL
interval	BETWEEN value borne_basse AND borne_haute
Compared partial string	value LIKE motif [ESCAPE Exhaust]
Compared to a list of value	value [NOT] IN (list)

4. Various
You can find additional information about the 71 to 107 pages of the book "SQL" collection "La Reference", Campus Press publisher.

4.1. Trantypage to using the CAST
It can change the type of data from one column to make a comparison of heterogeneous data type for example between a box containing digital data and a field containing data type string ...
His syntax is CAST (AS new type column).

Example 17

SELECT CHB_ID, CHB_NUMERO, CHB_POSTE_TEL
FROM T_CHAMBRE
WHERE CAST(CHB_POSTE_TEL AS INTEGER) / 10 > CHB_NUMERO

CHB_ID   CHB_NUMERO   CHB_POSTE_TEL
------           ----------                   -------------
      1           1                        101
      2         2                          102
      3           3                         103
      4         4                          104
      5           5                          105
      6         6                          106
      7       7                        107
      8          8                         108
      9            9                         109
     10          10                         110

The operator of CAST allows transtyper values contained in a column.
Of course we need a type of data can be converted into another type (type compatibility), so that the answer is not marred by errors or omissions.

Example 18

SELECT ADR_VILLE, CAST(ADR_CP AS INTEGER) + 1
FROM T_ADRESSE

ADR_VILLE                     ADR_CP   ADR_CP + 1
------------                             ------          ----------
VERSAILLES                    78000         78001
MONTMAIZIN                    11254        11255
PARIS                               75015        75016
VERGNOLLES CEDEX452 84524        84525
MARSEILLE                       13002        13003
PARIS                                75012        75013
BONNEUIL CEDEX              94152        94153
PARIS                                75012        75013
PARIS                                75014        75015
PARIS                                 75017       75018
...

4.2. Last capitalized / Tiny

Operators LOWER UPPER and allow to capital or tiny strings in queries.

Example 19

SELECT upper(CLI_PRENOM), lower(CLI_NOM)
FROM T_CLIENT

CLI_NOM                 CLI_PRENOM
-------                              ----------
ALAIN                           Dupont
MARC                           Martin
ALAIN                           Bouvier
PAUL                            Dubois
JEAN                            Dreyfus
ALAIN                           Faure
PAUL                           Lacombe
EVELYNE                    Duhamel
MARTINE                      Boyer
MARTIN                        Martin
...

NOTE: To make a search by not taking no account of the difference between upper and lower case, you must use the operator UPPER (or lower, but attention to the transformation of accents!):

Example 20

SELECT *
FROM T_CLIENT
where upper(CLI_PRENOM) = upper(CLI_NOM)

CLI_ID   TIT_CODE    CLI_NOM    CLI_PRENOM        CLI_ENSEIGNE
------         --------           -------            ----------               ------------------- ---
10           M.             MARTIN        Martin            HERMAREX IMPORT_EXPORT

NOTE: Some RDBMS can set the activation of the systematic search strings without taking into account the break. On others, the setting can be confused accented letters or not ...

4.3. Remove the white (or any other characters)

The function TRIM can remove a head or tail (or two) white or any other character specified.
TRIM ([LEADING or TRAILING or BOTH] [character] FROM column name)
LEADING: suppression in mind
TRAILING: suppression queued
BOTH: removal head and tail
In our telephone table, we want to abolish the zero lead No. order to communicate with foreigners who do not need to call this number (they must simply call 00 33 followed by the number to 9 digits).

Example 21

SELECT TEL_NUMERO,
'00~33 ' || TRIM(LEADING '0' FROM TEL_NUMERO)
AS TEL_INTERNATIONAL
FROM T_TELEPHONE

TEL_NUMERO         TEL_INTERNATIONAL
--------------                    -----------------
01-45-42-56-63        00 ~ 33 1-45-42-56-63
01-44-28-52-52        00 ~ 33 1-44-28-52-52
01-44-28-52-50        00 ~ 33 1-44-28-52-50
06-11-86-78-89        00 ~ 33 6-11-86-78-89
02-41-58-89-52        00 ~ 33 2-41-58-89-52
01-51-58-52-50        00 ~ 33 1-51-58-52-50
01-54-11-43-21        00 ~ 33 1-54-11-43-21
06-55-41-42-95        00 ~ 33 6-55-41-42-95
01-48-98-92-21        00 ~ 33 1-48-98-92-21
01-44-22-56-21        00 ~ 33 1-44-22-56-21
...

NOTE: Some SQL servers offer different functions as LTRIM and RTRIM for a removal of white head or tail.

4.4. Extract substring

The function SUBSTRING can extract a substring from a string. It needs the first order of nature and number of characters on which it must operate.

SUBSTRING (column name FROM TO m n)

Extract the substring column name starting on m n characters.

Example 22

SELECT CLI_NOM, CLI_PRENOM,
SUBSTRING(CLI_PRENOM FROM 1 FOR 1) ||
SUBSTRING(CLI_NOM FROM 1 FOR 1)
AS INITIALES
FROM T_CLIENT

CLI_NOM       CLI_PRENOM         INITIAL
-------                  ----------               ---------
DUPONT             Alain                  AD
MARTIN              Marc                  MM
BOUVIER            Alain                  AB
DUBOIS               Paul                  PD
DREYFUS           Jean                  JD
FAURE               Alain                  AF
LACOMBE           Paul                  PL
DUHAMEL         Evelyne               ED
BOYER             Martine                MB
MARTIN              Martin               MM
...

This example built the original customers from columns CLI_NOM and CLI_PRENOM_CLI.
Attention, some RDBMS using SUBSTR function.

4.5. Operator treatment dates
4.5.1. Extract a time setting a date
The operator EXTRACT can extract from a date, the day the month or year…

EXTRACT (or MONTH YEAR DAY FROM or column name)

In the table reservation on research identifying the rooms have been booked during the month of May of any year and 3 people.

Example 23

SELECT distinct CHB_ID
FROM TJ_CHB_PLN_CLI
WHERE EXTRACT(MONTH FROM PLN_JOUR) = 5
AND CHB_PLN_CLI_RESERVE = 1
AND CHB_PLN_CLI_NB_PERS = 3

CHB_ID
------
      1
      5
      6
      8
     11
     12
     16
     17
     18
     20

NOTE: it is unfortunate to note that the function EXTRACT standard SQL, often very useful, is rarely present in engines databases. Neither Access or Oracle or Sybase, and SQL Server are staffed. Only the middleware BDE Borland Inprise allows Corel to fully exploit this function with RDBMS Paradox, dBase, FoxPro, InterBase, MSSQL, Sybase, Informix, DB2, Oracle.
However it is common to find them approaching functions: Example DATEPART in SQL Server.

4.5.2. Time and current date
The current time, current date and the combined time course can be obtained using the functions CURDATE, CURRENT_TIME and CURRENT_TIMESTAMP.

Example 24

SELECT distinct CHB_ID
FROM TJ_CHB_PLN_CLI
WHERE (CHB_PLN_CLI_RESERVE = 1)
AND PLN_JOUR BETWEEN CURRENT_DATE and CURRENT_DATE + 14
AND CHB_PLN_CLI_NB_PERS = 3

attention, the result of this application varies depending on when you run!

CHB_ID
------
      1
      5
      6
      8
     11
     12
     16
     17
     18
     20

This query returns the rooms reserved for 3 persons between the date and for the next two weeks.

Warning: Most RDBMS still do not accept this version of the standard search functions current time. Below are the functions specific to different SQL servers:

Oracle	SYSDATE ()
Sybase	GETDATE ()
SQL Server	GETDATE ()
Access	NOW ()
MySQL	NOW ()
Paradox (QBE)	TODAY

4.6. Operators statistics
It is possible to make counts statistics on the columns, using the operators AVG (average), MAX (maximum), MIN (minimum), SUM (total), COUNT (number). They also call functions aggregate.

Example 25

SELECT AVG(TRF_CHB_PRIX) as MOYENNE,
MAX(TRF_CHB_PRIX) as MAXI,
MIN(TRF_CHB_PRIX) as MINI,
SUM(TRF_CHB_PRIX) as TOTAL,
COUNT(TRF_CHB_PRIX) as NOMBRE
FROM TJ_TRF_CHB
WHERE TRF_DATE_DEBUT = '2001-01-01'

MINI          MAX        AVERAGE        TOTAL        NUMBER
--------         --------          --------               ----------            ------
406.74 F 512.00 F      352.00 F          7 728.00 F         19

This application calculates the average, maximum, minimum, aggregation and the number of room rates for the date of first debut in January 2001.
It is surprising that operators statistics VARIANCE or STDDEV (standard deviation) are rarely present in the RDBMS because it is statistical functions that have great usefulness. But the standard SQL 92 has not accepted. However, they exist in Oracle. This is all the more surprising that modern techniques of DataWareHouse and in particular the DataMining use these functions in the framework of statistical calculations of hypercubes (OLAP techniques and ROLAP).

ATTENTION: we will see that the use of statistical functions requires mostly the introduction of a clause grouping, to be determined what is the target subset of aggregation for the calculations.

4.7. Other standard functions

BIT_LENGTH	Size of a column of type or ILO ILO VARYING (number of bits)
CHAR_LENGTH	Size of a column of type character (number of characters)
OCTET_LENGTH	Size of a column of type character (number of bytes)
CURDATE	Date underway
CURRENT_TIME	Time underway
CURRENT_TIMESTAMP	Date and time underway
CONVERT	Conversion paramétrèe a string
POSITION	Position a string in a substring
TRANSLATE	Translation of a string in a format specified

4.8. Other mathematical operators (non-standard)
Operators below can be implemented in different engines.

ABS	absolute value
MOD	Modulo
SIGN	sign
SQRT	square root
CEIL	smallest
FLOOR	largest integer
ROUND	rounding
TRUNC	truncated
EXP	exponential
LN	natural logarithm
LOG	logarithm decimal
POWER	power
COS	cosine
COSH	hyperbolic cosine
SIN	sinus
SINH	sinus hyperbolic
TAN	tangent
TANH	hyperbolic tangent
PI	constant Pi

Some are rarely implemented because RDBMS focus on information management, collection and processing of information rather than mathematical calculation.

Warning: the name of some of these operators may differ from RDBMS to another.

4.9. Other processors strings (non-standard)

CONCAT	concatenation: equivalent of \| \| - Note: Best used \| \| more standard. + The column between Alpha can also often be used as a concatenation operator, prefer anyway \| \|
INITCAP	initial capital letters
LPAD	Completion or truncation n position left
LTRIM / RTRIM	removal head / tail of a chain
REPLACE	Replacement
RPAD	Completion or truncation n position on the right
SOUNDEX	code consonnance - Attention: phonetic often french
INSTR	Position a chain in a substring
LENGTH	length of the chain
TO_CHAR	digital form literal - Attention: often in English
ASCII	ASCII code of a character
CHR	character whose code ASCII is given
REVERSE	Reverse the order of characters at chaine
FLIP	Spins parties right and left of a chain compared to No. character serving as a pivot.

Warning: the name of some of these operators may differ from RDBMS to another.

4.10. Other operators on temporal values (not standard)

ADD_MONTH, ADD_DAY, ADD_YEAR ...	adds months, days of years at a time
LAST_DAY	No returns the last day of a month on a date
MONTH_BETWEEN	number of months between two dates
NEXT_DAY	date of the next day of a given name
TO_CHAR	date in the form literal - Attention: often in English
DATE_DIFF	difference between two dates

4.11. Operators of all (non-standard)

GREATEST	the highest values of a set
LEAST	the smallest of a set of values
DECODE	reference value located in Nth position

5. Treatment of "values" zero
You can find additional information about the pages 115 to 120 of the book "SQL" collection "La Reference", Campus Press publisher.
NOTE: NULL is not strictly speaking a value, but the lack of value, which is why we talk marker NULL and not NULL.
The marker NULL poses a number of problems and we will raise this paragraph in a corner of the veil, that we will deal a little later in the general case of searching for occurrences of non-existence.

5.1. The null is not the empty string, or zero
NULL is not a value. It is a marker. Hence the marker NULL can never be compared has a value.
Wanted customers who did not sign.

Example 26
SELECT CLI_ID, CLI_NOM

FROM T_CLIENT

WHERE CLI_ENSEIGNE =''

CLI_ID CLI_NOM
------- --------
...
The answer must produce an empty table!
For controurner this problem requires:
is thought to record a string empty when inserting data into the table
is the WHERE clause with an operator specializing in the treatment of zero values

The problem of "null" has been well described in the book by Joe Celko "SQL advanced" published by International Thomson Publishing. Joe Celko raises the question in these terms: lack of information is it due to his ignorance or his impertinence? So why not make difference between the color of the roof of a car which is not known, and the color of the roof of a motorcycle which does not apply… Some logicians of relational algebra have gone even far in proposing different values to handle different cases, distinguishing cases very different: "null", the "unknown" and "unworkable" ...

There is therefore a real dilemma to use applications based on criteria of no value and should always be very careful to use terms that references to zero values, depending on what one wants to obtain. Especially since the NULL spread in the calculations.

Here is an excerpt from the table T_LIGNE_FACTURE

LIF_ID       FAC_ID       LIF_QTE      LIF_REMISE_POURCENT       LIF_REMISE_MONTANT      LIF_MONTANT       LIF_TAUX_TVA
--------          --------          ---------              -------------------                          ------ -----------                      ------------                ------------
   1                1            1.00                     15.00                                      NULL                            320.00F                   18.60
   2                3            1.00 F                   50.00                                     NULL                            250.00 F                  18.60
   3                3            1.00 F                   50.00                                     NULL                            320.00 F                  18.60
4                 3            1.00 F                   50.00                                     NULL                            240.00 F                  18.60
  5                 5            1.00                      NULL                                     NULL                            320.00 F                  18.60
6                 5            1.00                      NULL                                     NULL                            220.00 F                  18.60
  7                 7            1.00                      NULL                                     NULL                            220.00 F                  18.60
  8                 7            1.00                      NULL                                     NULL                            250.00 F                  18.60
  9                 7            1.00                      NULL                                     NULL                            320.00 F                  18.60
10                 7            1.00                      NULL                                     NULL                            270.00 F                  18.60
...
We want to calculate the total cost of each line of this table, for an invoice.
The request for FAC_ID = 3 is as follows:
Example 27

SELECT FAC_ID, sum

(LIF_QTE * (LIF_MONTANT - LIF_REMISE_MONTANT) * (1 - LIF_REMISE_POURCENT / 100))

TOTAL_FAC, sum

((LIF_QTE * (LIF_MONTANT - LIF_REMISE_MONTANT) *

(1 - LIF_REMISE_POURCENT / 100)) * (LIF_TAUX_TVA / (100 + LIF_TAUX_TVA)))

TOTAL_TAXES

FROM T_LIGNE_FACTURE

WHERE FAC_ID = 3 GROUP BY FAC_ID

FAC_ID     TOTAL_FAC      TOTAL_TAXES
------           ---------                   -----------
  3                NULL                   NULL
It is observed that for lines that do not have values filled in columns LIF_REMISE_POURCENT, LIF_REMISE_MONTANT, the calculation results in the value "null", which translates to display ... nothing!

NOTE: In general, to get out of this bad no one can, when creating the database, require all fields of digital type (real or whole) not to accept the zero value and take the default zero ...

Warning: the arithmetic of zero is pretty special ... Remember always that NULL spread. This is true for digital dates but also for strings. Thus SQL draws a distinction between a string and an empty field not available. In the case of the concatenation of a NULL column and a column properly informed, the return value is NULL!

5.2. Operators treatment markers NULL
The standard SQL 2 (1992) specifies a comparison and various operators on markers NULL:
IS NULL / IS NOT NULL: tests if the column is empty or not empty.
COALESCE () which seeks the first non-empty value in a set
NULLIF NULLifie a column based on a given value

COALESCE (value1, value2 [, value3] ...)NULLIF (nom_de_colonne, value) expression IS [NOT] NULL

NOTE: ISNULL (one word) is another function of connection that we sometimes encounter (reference value if the value is zero). In the same vein, or NVL VALUE are expressions COALESCE equivalent to that found on some RDBMS.
The previous query is expressed, using the operator ISNULL:

Example 28

SELECT FAC_ID,sum

(LIF_QTE * (LIF_MONTANT - ISNULL (LIF_REMISE_MONTANT, 0)) *

(1 - ISNULL (LIF_REMISE_POURCENT, 0) / 100))

TOTAL_FAC, sum

((LIF_QTE * (LIF_MONTANT - ISNULL (LIF_REMISE_MONTANT, 0)) *

(1 - ISNULL (LIF_REMISE_POURCENT, 0) / 100)) * (LIF_TAUX_TVA / (100 + LIF_TAUX_TVA)))

TOTAL_TAXES

FROM T_LIGNE_FACTURE

WHERE FAC_ID = 3 GROUP BY FAC_ID

FAC_ID      TOTAL_FAC        TOTAL_TAXES
------            ---------                  -----------
   3               810.0                    127.03
NOTE: As a general rule, when it deals with columns containing monetary values or digital, it is good to ensure that the column is mandatory and that by default it is filled to zero.
Otherwise, it will make a systematic use of functions NULLIF or COALESCE in all calculations and it will put the performance of execution!

6. Denial of values
It is NOT operator that carries out the negation of values and reverses the logical value of a predicate.
The operator may NOT be combined with most operators comparison. But it becomes very interesting lorqu'il is combined with the operators IN BETWEEN, and LIKE NULL
Wanted by example all rooms to receive at least 3 people and does not figure 4 (figure of death in Japan) or rooms bearing the No. 7 and 13 for a client particularly superstitious ...

Example 29

SELECT CHB_ID, CHB_NUMERO,

CHB_COUCHAGE FROM T_CHAMBRE

WHERE

NOT (CAST (CHB_NUMERO AS VARCHAR (10)) LIKE '% 4%')

CHB_NUMERO AND NOT IN ('7 ','13')

AND

CHB_COUCHAGE> = 3

CHB_ID        CHB_NUMERO        CHB_COUCHAGE
-----------            ----------                        ------------
    1                     1                                 3
    5                     5                                 3
    6                     6                                 5
    8                     8                                 3
   11                   11                                3
   12                   12                                3
   15                   16                                3
   16                   17                                5
   17                   18                                3
   19                   20                                3
We will see that NOT IN is particularly valuable in nested queries, ie requees multitables.
We now want the names of customers who do not start with 'OF':

Example 30

SELECT CLI_NOM

FROM T_CLIENT

WHERE CLI_NOM NOT LIKE '% OF'

--------------------------------
MARTIN
BOUVIER
DREYFUS
FAURE
LACOMBE
BOYER
MARTIN
PAUL
PHILIPPE
PIERRELAYE
...

7. The turnout in the SQL
You can find additional information about the 120 to 123 pages of the book "SQL" collection "La Reference", Campus Press publisher.
SQL has a connection to how FIs and other structures test procedural languages.
But it should not use that wisely, that is to say as little time as possible, many cases ouvant be treated either by the COALESCE either by queries with operations ensemblistes type UNION. Indeed performance deteriorate very quickly when using CASE because of the impossibility of effcetuer treatment "packages".
The structure of SQL CASE includes two different syntaxes. The CASE for connection to the values of expression and CASE widespread.

7.1. CASE on expression
In this case, the syntax is as follows:
CASE expression value1

WHEN THEN expression1

[value2 WHEN THEN expression2] ...

[ELSE expression_defaut]

END

Example 31
SELECT CHB_NUMERO,

CASE

WHEN CHB_ETAGE 'DRC' 0

THEN WHEN'1 st 'THEN WHEN 1 e'2' THEN 2 END

AS FLOOR, CHB_COUCHAGE BY ORDER

FROM T_CHAMBRE FLOOR, CHB_COUCHAGE

CHB_NUMERO   FLOOR     CHB_COUCHAGE
----------                -----------        ------------
2                          0                 2
3                          0                 2
4                          0                 2
1                          0                 3
7                          1                 2
9                          1                 2
10                          1                 2
5                          1                 3
8                          1                 3
11                         1                 3
12                         1                 3
6                          1                 5
14                          2                 2
15                          2                 2
19                          2                 2
21                         2                 2
16                          2                 3
18                          2                 3
20                          2                  3
17                          2                  5

7.2. CASE widespread
The expression disappears in favour of different predicates.
Condition1

CASE

WHEN THEN expression1

[condition2 WHEN THEN expression2] ...

[ELSE expression_defaut]

END

Example 32

SELECT CHB_NUMERO,

CASE

WHEN CHB_ETAGE = 'DRC' 0

THEN WHEN CHB_ETAGE ='1 st '

THEN WHEN 1 = CHB_ETAGE e'2' THEN 2 END

FLOOR, CHB_COUCHAGE BY ORDER

FROM T_CHAMBRE FLOOR, CHB_COUCHAGE

CHB_NUMERO          FLOOR           CHB_COUCHAGE
   ----------                    -----------                 ------------
      2                             0                           2
      3                             0                           2
      4                             0                           2
      1                             0                           3
      7                             1                           2
      9                             1                           2
     10                            1                           2
      5                             1                           3
      8                             1                           3
     11                            1                           3
     12                            1                           3
      6                             1                           5
    14                             2                           2
    15                             2                           2
    19                             2                           2
    21                             2                           2
   16                              2                           3
   18                              2                           3
   20                              2                           3
   17                              2                           5

Who gives the same result!
ATTENTION: all RDBMS do not support these two syntaxes.
NOTE: in both cases it is possible to replace the IF a procedural language:

Condition1 CASE WHEN THEN

expression1 [ELSE expression_defaut]

END

CASE expression value1 WHEN THEN

expression1 [ELSE expression_défaut]

END

8. The manufacturer lines valued (ROW VALUE CONSTRUCTOR)
We are in front of a little-known element of SQL because rarely implemented. Pay tribute to Oracle because it is one of the few RDBMS to implement. This construction can take advantage of embarrassment for many cases drastically simplifying your queries.

The idea is that different columns to match different criteria compared with the same comparison operator can be evaluated simultaneously.
It is easier to understand this concept with an example.
One user created a table T_EVENEMENT in which he has four columns indicating the hour, minute, the second occurrence of the event and the nature of the event:

CREATE table T_EVENEMENT

(EVT_HEURE int NOT NULL, EVT_MINUTE smallint NULL,

NULL EVT_SECONDE smallint, EVT_EVENEMENT varchar (64) NOT

NULL CONSTRAINT CKC_EVT_HEURE CHECK (EVT_HEURE> = 0),

CONSTRAINT CKC_EVT_MINUTE CHECK (EVT_MINUTE BETWEEN 59 AND 0),

CONSTRAINT CKC_EVT_SECONDE CHECK ( EVT_SECONDE BETWEEN 59 AND 0))

INSERT INTO T_EVENEMENT VALUES (3, 12, 25, 'Surge')

INSERT INTO T_EVENEMENT VALUES (3, 2, 48, 'Overheating')

INSERT INTO T_EVENEMENT VALUES (3, 15, 0, 'Stop')

INSERT INTO T_EVENEMENT VALUES ( 4, 0, 0, 'Fire')

INSERT INTO T_EVENEMENT VALUES (2, 58, 59, 'Start')

INSERT INTO T_EVENEMENT VALUES (4, 22, 33, 'Explosion')

EVT_HEURE           EVT_MINUTE      EVT_SECONDE          EVT_EVENEMENT
   -----------                  -----------                  ----------                         --------------
      2                           58                         59                                Start
      3                            2                          48                           Overheating
      3                           12                         25                               Surge
      3                           15                           0                            Judgement
      4                            0                            0                                Fire
Explosion                   4                           22                                33
The question is: what are the events after 3h 10?
Example 33: intuitively, we have to write the following query:

SELECT *

FROM T_EVENEMENT

WHERE EVT_HEURE> 3

AND

EVT_MINUTE> 10

EVT_HEURE      EVT_MINUTE       EVT_SECONDE      EVT_EVENEMENT
-----------                -----------                  ----------                     --------------
Explosion                    4                         22                           33
But she forgets knowingly three events that occurred before 3:10! How is this possible?
In fact the event which occurred at 4 am 0 is not taken into account because of the first line of the WHERE clause!
EVT_HEURE> 3

AND

EVT_MINUTE> 10
simply because 0 minute is much less than 10 minutes ...
But 4h 0 is far greater than 3h 10 ... we must remove the part AND EVT_MINUTE> 10 of the WHERE clause
The correct construction is:
Example 34

SELECT *

FROM T_EVENEMENT

WHERE EVT_HEURE> 3 OR EVT_HEURE = 3

ANDEVT_MINUTE> 10

EVT_HEURE    EVT_MINUTE    EVT_SECONDE      EVT_EVENEMENT
-----------               ----------                -----------                  -------------
3                          12                      25                         Surge
3                          15                       0                      Judgement
4                           0                        0                           Fire
Explosion                4                       22                            33
Hence the idea to implement a construction able to comprehensively assess such an operation. This construction is the ROW VALUE CONSTRUCTOR I translated by manufacturer lines valued ...
With this tool, the previous query expressed more simply:

Example 35

SELECT *

FROM

WHERE T_EVENEMENT

(EVT_HEURE, EVT_MINUTE)> (3, 10)

EVT_HEURE     EVT_MINUTE      EVT_SECONDE     EVT_EVENEMENT
   -----------            ----------                 -----------                    -------------
     3                     12                           25                         Surge
     3                     15                            0                       Judgement
     4                      0                             0                           Fire
Explosion              4                             22                          33
The syntax of such an expression using a ROW VALUE CONSTRUCTOR is:
(<constructeur Lines valuees>) <comparateur> (<constructeur lines valuees>)

The manufacturer lines can be valued: a list of columns, a list of values, a list that combines columns and values or even a sub Text:
(column1, column2, column3)
(column1, value1, value2, column2)
(value1, value2)
SELECT *

FROM Matable provided they do return a single line
etc. ...

The equivalent between this building and that not using the ROW VALUE CONSTRUCTOR, can be developed as follows.
Suppose that the first lines valued includes columns of tables from 1 to n and that the second line manufacturer valued contains values from 1 to n, ie:

(column1, column2 ... colonneN) <comparateur> (value1, value2 ... valeurN)

then this comparison is equivalent to:

column1 <comparateur> value1

column1 = value1

AND column2 <comparateur> value2

column1 = value1

AND column2 = value2

AND column3 <comparateur> value3 ...

column1 = value1

AND column2 = value2

AND

...

AND colonneN <comparateur> valeurN

Also based on our game tests, here is an example more complete: all seek the events after 3h12'30 ":

Example 36

SELECT *

FROM

WHERE T_EVENEMENT

(EVT_HEURE, EVT_MINUTE, EVT_SECONDE)> (3, 12, 30)

EVT_HEURE     EVT_MINUTE      EVT_SECONDE     EVT_EVENEMENT
   -----------             ----------                 -----------                    -------------
      3                     15                          0                     Judgement
      4                      0                           0                       Fire
Explosion                4                          22                       33
Example 37: and its construction logically equivalent:
SELECT *

FROM T_EVENEMENT

WHERE EVT_HEURE> 3

EVT_HEURE = 3

AND

EVT_MINUTE> 12

EVT_HEURE = 3

AND

EVT_MINUTE = 12

AND

EVT_SECONDE> 30

EVT_HEURE     EVT_MINUTE    EVT_SECONDE     EVT_EVENEMENT
   -----------              ----------               -----------                   -------------
         3                   15                       0                        Judgement
        4                      0                       0                             Fire
Explosion                 4                      22                               33
In fact, the ROW VALUE CONSTRUCTOR acts, in research, just as if we viewed the data so fractal
The treaty is more coarse, then s'interrese a few more fine once the coarse fixed, and so forth depending on the level fractal that it was fixed.

NOTE: The use of ROW VALUE CONSTRUCTOR with a standard of comparison equality sometimes surprising results that can defy logic. We must be wary of a systematic recourse to such a construction.

Example 38: What are the events that occurred in 3 hours 12 (using the ROW VALUE CONSTRUCTOR)

SELECT *

FROM

WHERE T_EVENEMENT

(EVT_HEURE, EVT_MINUTE, EVT_SECONDE) = (3, 12)

EVT_HEURE        EVT_MINUTE            EVT_SECONDE          EVT_EVENEMENT
   -----------                   ----------                    -----------                        ---------------
        3                        12                          25                                 Surge
        3                         2                          48                                Overheating
        3                        15                           0                                 Judgement
This offer may interest it must be said!
The construction equivalent logic being:

Example 39

SELECT *

FROM T_EVENEMENT

WHERE EVT_HEURE = 3

EVT_HEURE = 3

AND EVT_MINUTE = 12

EVT_HEURE       EVT_MINUTE        EVT_SECONDE          EVT_EVENEMENT
   -----------               ----------                  -----------                           ---------------
       3                       12                         25                                 Surge
       3                         2                         48                             Overheating
       3                       15                           0                              Judgement
Finally, note that the ROW VALUE CONSTRUCTOR has a huge interest, that allow the insertion multiple (several lines of data) in the same order INSERT!

Example 40: integration with multiple ROW VALUE CONSTRUCTOR

INSERT INTO T_EVENEMENT

(EVT_HEURE, EVT_MINUTE, EVT_SECONDE, EVT_EVENEMENT

VALUES ((4, 37, 21, 'Catastrophe'), (4, 44, 18, 'Cataclisme'), (5, 21, 1, 'Destruction final planet earth' ))

ATTENTION: only ORACLE RDBMS and a few more experimental than professionals (OCELOT for example) use such a technique!

09. Conclusion
Curiously Paradox has not implemented operators recovery time common values as they exist in QBE! It is easy to move from the date or the current time as a parameter of the request, but still ...

Regarding MS Access, one can not but be struck by the fact that most basic functions of the complaints are incompatible with the standard. For example, the LIKE wild different uses: * replaces%? replaces _. This forces to use a syntax owner who makes the portability of applications very difficult one RDBMS to another. But would this not an appropriate tactic? Another drawback it can not treat BETWEEN NOT!

More curious most RDBMS does not concatenation operator | |!

In Sybase SQL Server as the function modulo expressed as a '%' or huge possibility of confusion between wildcards, like, as the calculation of percentage ... When a release on this subject?

The RDBMS nearest the standard is that of Sybase, followed by SQL Server. The most complete game by its function is probably Oracle.