Main information about SQL language

The SQL language is oriented onto operations with the DB tables and also onto execution of some auxiliary operations. The program written on the SQL language is named as SQL-query. With the help of SQL-query it is possible:

- To form fields of a data set at the appendix execution;

- To include fields and records from several tables into data set;

- To select records by complicated criteria;

- To sort a data set by any field including not indexed;

- To execute data search.

SQL functions

SQL language give a number of functions used in expressions. The most often applied functions are following:

- AVERAGE () - average value

- COUNT () - quantity of values

- MAXIMUM () - maximum value

- MINIMUM () - minimum value

- SUM () - sum of values.

Characteristic of SELECT operator  

The data selection of the tables is consisted in selection from the tables those fields and records that are satisfying to given conditions. Result of query execution on base of which one the records are taken is named as selection. The data can be selected from one or several tables by usage of SELECT operator.

  SELECT operator is the most important operator of SQL language. It will be used for selection of records satisfying to the complicated search criteria. This operator has the following format:  list

SELECT [DISTINCT]

<List of fields> or *

               FROM <List of tables>

               [WHERE < Conditions of selection>]

[ORDER BY <List of fields for sorting>]

[GROUP BY <List of fields for grouping >]

[HAVING <Conditions of grouping >]

The note: at the description of language operators we shall pull down unessential operands and elements, for the denotation of separate elements we shall use characters <and> (these characters are not shown at programming), the optional elements of language constructions are enclosed in square brackets. For visualization we shall write reserved words of SQL language by line letters, and names by capital letters. The elements in lists, for example, names of fields  and tables should be separated by commas.

 The results of SELECT operator execution is the data set which may have the allowed or forbidden recurring records (having identical values of all fields). It is controlled by DISTINCT descriptor. If descriptor misses data set can include records having identical values of all fields.

It is required to include into description of SELECT operator the list of fields and operand FROM. The remaining operands are not mandatory. Names of the tables from which you select the records are enumerated in FROM operand. The list should contain one table as minimum.

 The list of fields defines the fields structure of a resulting data set, these fields can belong to the different tables. Even one field should be preset in the list. If it is required to include all fields of the table (tables) into a set instead of names enumeration it is possible to indicate the character "*". If the list contains fields of several tables then to indicate belonging of a field to this or that table the composite name will be used which one includes a name of the table and name of a field separated by point: <Table name>. <Field name>.

 WHERE operand set the criteria to which the records of a resulting data set should satisfy. The expression describing condition of selection is a logical one. Its elements can be fields names, operations of comparing, arithmetic and logic operations, bracket, function LIKE, NULL, IN etc.

 GROUP BY operand allows to mark records groups in a resulting data set. Group are the records with identical values in fields listed after GROUP BY operand. Marking of groups is necessary for execution of group operations with records.

HAVING operand is used with GROUP BY operand for selection of records inside the groups. The writing rules of grouping condition are similar to the forming rules of selection condition in WHERE operand.

ORDER BY operand contains the fields list that define sorting order of records in a resulting data set. By default sorting according to each field run in the order of values increase. If it is necessary to set descending sort for a field, after a name of this field the descriptor DESC must be indicated.

Examples of queries creation.

Example 1. Selection of all fields.

SELECT * FROM Air_Map.dbf

As an execution result of this query all fields and all records from Air_Map.dbf table are included into a data set. The fields sequence of a data set corresponds to the arrangement order of physical fields of the table definite at its creation.

Example 2. Definition of data set fields.

SELECT Name, Description FROM Air_Map.dbf

Name and Description fields of all records from the Air_Map.dbf table are included into data set formed as a result of SQL- query. The fields order in a data set will correspond to the fields order in this list.

Example 3. Selection of records with unique values of a field.

SELECT DISTINCT Name FROM Air_Map.dbf

The records are selected from the Air_Map.dbf table, thus each airport  name is included in a data set only once (Name field).

Example 4. Selection of records from two tables.

SELECT * FROM Air_Map, Air_Town

The resulting data set contains all fields of all records from Air_Map.dbf and Air_Town.dbf tables.

Example 5. Selection of fields from different tables.

SELECT Air_Map.Name, Air_Town. Airport FROM Air_Map, Air_Town

The resulting data set contains Name field from the Air_Map.dbf table and Airport field from the Air_Town.dbf table.

Simple criterion of records selection.

In the previous example the resulting data set contained all records from the indicated tables, thus the user could control a structure of fields of these records. In practice into a data set normally the records satisfying to any definite criteria assigned with the help of WHERE operand are included.

Selection criterion represents a logical expression in which it is possible to use following operations:

- comparing operations (=,>, <, < >)

- LIKE - comparing by template

- IS NULL - check of zero value

- IN - check of entrance

- BETWEEN - check of entrance into a range.

In simple selection criterion one operation is used. For operations of comparing and comparing by template the selection criterion has the following format:

< Expression 1 > < the comparing operation > < Expression 2 >

Example 1. Selection of records by values of a numeric field.

SELECT Name FROM Air_Map WHERE Linkobject > 1000

This operator sets the obtaining of  airports names list having object number of  more than 1000.

Example 2. Selection of records by values of a character field.

            SELECT Name  FROM Air_Map WHERE Description = 'Airport'

In the given example the airports list is forming.

Example 3. Check of partial coincidence.

SELECT Description FROM Air_Map WHERE Name LIKE " Hil % ".

So you can get the Descriptions list of airports which names are starting from characters " Hil".

The special characters are used in terms of LIKE operation:

- % - substitution of any amount of characters, including zero character

- _ - substitution of one character.

Complicated criteria of records selection.

It is possible to use some operations at definition of records selection criterion. The complicated criterion (logical expression) consists of following elements:

- Simple criteria

- Logic operations:

- AND - logical AND

- OR - logical OR

- NOT - logical NOT

- Parentheseses.

Example. Complicated criterion of selection

SELECT * FROM Air_Map WHERE (Description = 'Airport') AND

                                                                (Name LIKE " Mar% ")

The list of airports which names are starting from characters Mar is made in this example.

Records sorting.

The sorting is an ordering of records by increase or decrease of fields values. The fields by which the sorting is executed is indicated in ORDER BY operand. By default sorting happens in increase order of fields values.

Example. Sorting of records.

SELECT * FROM Air_Map ORDER BY Name

Sorting of records is given by Name field.

Records editing.

The editing of records it is a change of field values in group of records. It is executed by UPDATE operator.

UPDATE <Table name>

               SET < Field name> = (Expression),

                               …

               < Field name > = (Expression)

               [WHERE < Condition of selection >];

Example. Change of field values.

UPDATE Air_Map SET Description = 'Central Airport '

Central Airport value is written in "Description" field .

Records insert.

Records insert into a table is executed with the help of INSERT operator which allows to add one or several records to tables.

INSERT INTO < Table name>

                          (< List of fields >)

                          VALUES (< List of values >);

Example. Addition of a record.

INSERT INTO Air_Map (Name, Description)

                           VALUES ("Hilton", "Airport");

New record having the name and airport description is included into Air_Map.dbf table.

Records deleting.

To delete records use DELETE operator having the following format:

DELETE FROM < Table name >

                [WHERE < Condition of selection >];                    

Example. Deleting of a record.

DELETE FROM Air_Map WHERE Linkobject =0;                    

All records which have zero values in Linkobject field are deleted from the Air_Map.dbf table.

Link of tables.

It is possible to include fields from the different tables into a data set, similar inclusion is named connection (link). Link of tables can be internal or external.

Internal connection represents the elementary case when after SELECT word the fields of different tables are enumerated.

Example. Internal link of tables at use of selection criterion:

SELECT AIR_TOWN.АЭРОПОРТ, AIR_MAP.ОПИСАНИЕ

FROM AIR_MAP, AIR_TOWN

WHERE AIR_MAP.НАЗВАНИЕ = AIR_TOWN. АЭРОПОРТ

Link between Air_Map .dbf and Air_Town .dbf tables is established. The data set includes АЭРОПОРТ field from Air_ Town .dbf table and ОПИСАНИЕ field from Air_Map.dbf table according to names of airports.

At internal link of the tables, which fields are specified in SQL - query, are equal in rights.

At external link of tables it is possible to specify, what table will be the main one, and what - the subordinate one. In this case FROM operand has following format:

FROM <Table1> [<Link type>] JOIN <Table 2> ON <Condition of selection>

Selection criterion after ON word sets condition of records inclusion into a data set; connected (linked) tables are specified at the left and on the right of JOIN word. What from two tables will be the main one, defines the link type:

LEFT - at the left

RIGHT - on the right (by default).

Example. External link

SELECT AIR_MAP.ОПИСАНИЕ, AIR_TOWN.АЭРОПОРТ FROM

AIR_TOWN LEFT JOIN AIR_MAP ON AIR_TOWN.АЭРОПОРТ =

AIR_MAP.НАЗВАНИЕ

As well as in the previous example, tables Air_Map .dbf and Air_ Town .dbf are linking. The main table is Air_Map .dbf table.