Language of interrogation of data

A language of interrogation of data is a data-processing Langage, intended for research, extraction, sorting and formatted, data in a Database.

SQL or the language of interrogation of data ( LID )

A base of data can be questioned in a formal way by language SQL or in an abstract way by the algebraic language. The language SQL (Structured Query Language) is an evolution of SEQUEL developed in 1976 by IBM like a language of research. This evolution with amongst other things be to remove the multivaluées functions of SEQUEL which was the object until 1993 of patent of Pick System thus the cost was prohibitory. SQL became a standard of the relational databases (in 1987, standardization of this language by ANSI). SQL is used in two forms, either in an interactive way, or inside a language host (C, FORTRAN, COBOL,…). SQL comprises only one score of instructions, it is known as procedural (the access to the data is done by their contents and not by their way). SQL is a language of definition of the data (LDD), of handling of data (LMD), of control of the data (LCD) and of interrogation of data (LID). In spite of the success of the language SQL which followed, Edgar F. Codd will denounce this tool which he regards as an incorrect interpretation of his theories.

The algebraic Langage (IT) corresponds to a pseudo algorithm of the SQL. it is composed of few operators (selection, projection and joint being core operators).

Terminology

SQL like allow IT the management and the interrogation of the databases. A database can be regarded as a table has two dimensions, whose columns are the Champs and the lines are the Tuple S. Several tables can contain of the same columns names, to differentiate them it will be necessary to prefix the headings by the name of the Table.

Note:: the base of data provides in example is made up of 2 tables. The table EMPLOYED made up of a code employed, a name, a first name, a birth date, an address and a code employment. The table EMPLOYMENT consisted of a code employment (to which the table refers EMPLOYED), of the wording of use of the city concerned with this employment.

Projection

Projection is an operation which consists in selecting only certain data for posting. Syntax is the following one: R1 < - PROJ (nomdelatable; list properties)

SELECT lists properties FROM nomdelatable;

Examples:

: PROJ (Employed; name, first name)

SQL: SELECT name, first name FROM Employed;

This request returns only the name and first name of the table employed

: PROJ (Employed; *)

SQL: SELECT * FROM employed;

This request returns all the headings of the table employed thanks to the character joker *.

Sorting

To obtain a sorted posting it is necessary to specify it. The operator of sorting is to be used in algebraic language. The clause order by is to be added at the end of request SQL. The sorting by defect is carried out by ascending order; to obtain a sorting by decreasing order, it is necessary to add the parameter DESC . The sorting contains to the maximum 16 criteria.

R2 < - TRI ( R1 ; attribute of ascending sort)

SELECT Lists properties FROM nomdelatable ORDER BY property ASC;

Or:

R2 < - TRI ( R1 ; attribute of the descending sort)

SELECT Lists properties FROM nomdelatable ORDER BY property DESC;

Attention! : the sorting is carried out only with posting, to in no case bases it of data is not modified.

Example:

Selection of the employees (name and first name) sorted alphabetically name and by increasing age.

R1 < - PROJ (Employed; name, first name, datenais) R2 < - TRI (R1; name growing, datenais decreasing )

SELECT name, first name, datenais FROM Employed ORDER BY name ASC, datenais DESC;

Attention! : For a multicriterion sorting, the order of sorting is that of the heading of front.

From the sorting above, we add a sorting by increasing first name

R1 < - PROJ (Employed; name, first name, datenais) R2 < - TRI (R1; name growing, datenais decreasing, increasing first name )

SELECT name, first name, datenais FROM Employed ORDER BY name ASC, datenais DESC, first name ASC;

Selection

The selection consists has to select lines answering a predicate. Syntax is the following one:

R1 < - SALT (R; property operator value AND property operator value)

Attention! : The algebraic language is written with the back of the SQL.

SELECT lists properties FROM nomdelatable WHERE property COp value AND property COp value;

Q' selection criteria (also called predicate)?

It is the result of the comparison of two expressions by means of an operator of comparison.

Q' an operator?

An operator describes an operation of comparison.

Equal = different! = or <> according to the SGBDR Superior > Superior or equal >= Inferior < Inferior or equal <=

Attention! : The tiny ones are differentiated from the capital letters

The joint

It is an operation making it possible to bring back on the same line of the operations coming from several tables. A joint is carried out thanks to a Cartesian product several tables (256 maximum) and the application of the selections.

R3 = JOIN (R 1, R 2; R1.propriété R2.propriété operator)

Under-interrogations

An under-interrogation is the expression of a Prédicat using the result of a select. There can be up to 16 levels of under-interrogations. An error is generated if an under-interrogation does not bring anything or if it brings back more values than cannot accept the fields of them.

Random links: