Structure Query Language (SQL) was created as a result of the development of the relational data model and is now the de facto standard for relational database management systems. The SQL language is supported today by a huge number of different types of DBMS.

The name of the SQL language is usually pronounced by the letters "es-q-el". The mnemonic name "See-Quel" is sometimes used.

The SQL language provides the user (with minimal effort on his part) the following capabilities:

Create databases and tables with a complete description of their structure

Perform basic data manipulation operations: insert, modify, delete data

Perform both simple and complex queries.

SQL is relationally complete.

The structure and syntax of its commands are quite simple, and the language itself is universal, that is, the syntax and structure of its commands does not change when moving from one DBMS to another.

The SQL language has two main components:

Data Definition Language (DDL) for defining database structures and data access control

DML (Data Manipulation Language), designed to fetch and update data.

The SQL language is non-procedural, that is, when using it, it is necessary to indicate what information should be obtained, and not how it can be obtained. SQL commands are ordinary English words (SELECT, INSERT, etc.). Let's look at the SQL DML statements first:

SELECT - fetching data from the database

INSERT - inserting data into a table

UPDATE - updating data in a table

DELETE - deleting data from a table

SELECT statement

The SELECT statement performs operations equivalent to the following relational algebra operations: select, projection, and join.

The simplest SQL query using it looks like this:

SELECT col_name FROM tbl

The select keyword is followed by a comma-separated list of columns that will be returned from the query. The from keyword specifies which table (or view) to retrieve data from.

The result of a select query is always a table, which is called the result table. Moreover, the results of a query executed with the select statement can be used to create a new table. If the results of two queries against different tables have the same format, they can be combined into one table. Also, the table obtained as a result of the query can become the subject of further queries.

To select all columns and all rows of a table, just make the SELECT * FROM tbl;

Consider the Product table, which contains price information for various types of products:

The result of the query

SELECT * FROM Product;

will be the entire Product table.

You can select specific columns of a table using a query

SELECT col1, col2, ..., coln FROM tbl;

So, the query result

SELECT Type, Price FROM Product;

there will be a table

The list of columns in the select statement is also used when it is necessary to change the order of the columns in the resulting table:

In order to select only those table rows that satisfy certain restrictions, a special keyword where is used, followed by a boolean condition. If a record satisfies this condition, it is included in the result. Otherwise, such an entry is discarded.

For example, the selection of those goods from the Product table, the price of which satisfies the Price condition<3200, можно осуществить, используя запрос

SELECT * FROM Product where Price<3200;

Its result:

The condition can be compound and combined using the logical operators NOT, AND, OR, XOR, for example: where id_ Price> 500 AND Price<3500. Допускается также использование выражений в условии: where Price>(1 + 1) and string constants: where name = "autobalance".

Using the BETWEEN var1 AND var2 construct allows you to check if the values ​​of any expression fall within the range var1 to var2 (including these values):

SELECT * FROM Product where Price BETWEEN 3000 AND 3500;

By analogy with the NOT BETWEEN operator, there is the NOT IN operator.

Column names specified in the SELECT clause can be renamed. For this, the AS keyword is used, which, however, can be omitted, since it is implicitly implied. For example, the request

SELECT Type AS model, Type_id AS num FROM Product where Type_id = 3

will return (alias names should be written without quotes):

The LIKE operator is for comparing a string against a pattern:

SELECT * FROM tbl where col_name LIKE "abc"

This query returns only those records that contain the string value abc in the col_name column.

Two wildcards are allowed in the pattern: "_" and "%". The first one replaces one arbitrary character in the template, and the second one replaces a sequence of arbitrary characters. So, "abc%" matches any string starting with abc, "abc_" - a 4-character string starting with abc, "% z" - an arbitrary string ending with z, and, finally, "% z%" - sequences of characters containing z.

You can find all records of the Product table in which the Type value begins with the letter "a" as follows:

SELECT * FROM Product where Type LIKE "a%";

	auto scales

If the search string contains a wildcard character, then you must specify the control character in the ESCAPE clause. This control character should be used in the pattern before the wildcard character, indicating that the latter should be treated like a regular character. For example, if in a certain field all values ​​containing the character "_" are to be found, then the pattern "% _%" will result in all records from the table being returned. In this case, the template should be written as follows:

"% | _%" ESCAPE "|"

To check the value for compliance with the string "20%", you can use the following operator:

LIKE "20 #%" ESCAPE "#"

The IS NULL operator allows you to check the absence (presence) of NULL values ​​in the table fields. Using the normal comparison operators in these cases may lead to incorrect results, as comparison with a NULL value results in UNKNOWN (unknown). Thus, the selection condition should look like this:

where col_name IS NULL, instead of where col_name = NULL.

The default selection result returns records in the same order in which they are stored in the database. If you need to sort records by one of the columns, you must use the ORDER BY clause, followed by the name of this column:

SELECT * FROM tbl ORDER BY col_name;

As a result of this query, records will be returned in ascending order of the value of the col_name attribute.

Records can be sorted by several columns. To do this, their names must be specified after the ORDER BY, separated by commas:

SELECT * FROM tbl ORDER BY col_name1, col_name2.

The records will be sorted by the col_name1 field; if there are several records with the same value in the col_name1 column, they will be sorted by the col_name2 field.

If you want to sort records in reverse order (for example, descending by date), you need to specify ORDER BY col_name DESC.

For direct sorting, there is the ASC keyword, which is the default.

If the result of the selection contains hundreds or thousands of records, their output and processing takes a significant amount of time.

Therefore, information is often paginated and presented to the user in chunks. Page navigation is used with the limit keyword followed by the number of records to display. The following query retrieves the first 10 records, while simultaneously performing a reverse sort on the col_name1 field:

SELECT * FROM tbl ORDER BY col_name1 DESC LIMIT 10

To retrieve the next 10 records, the limit keyword is used with two values: the first indicates the position from which to display the result, and the second indicates the number of records to retrieve:

SELECT * FROM tbl ORDER BY col_name1 DESC LIMIT 10,10

To retrieve the next 10 records, you must use the LIMIT 20, 10 construct.

Each SQL command begins with a keyword - a verb that describes the action to be performed by the command, such as CREATE. A team can have one or more sentences. The sentence describes the data that the team is working with, or contains clarifying information about the action performed by the team. Each clause starts with a keyword, such as WHERE (where). Some clauses in the command are required, others are not. Some sentences may contain additional keywords, expressions. Many suggestions include table or field names. Names must be between 1 and 18 characters long, start with a letter, and not contain spaces or special punctuation characters. Key words cannot be used as names.

52. SQL (Structured Query Language) - Structured Query Language Is the standard query language for working with relational databases.

SQL does not contain traditional statements that control the flow of programs, it only contains a set of standard statements for accessing data stored in a database.

The SQL language can be used to access the database in two modes: when interactive work and in application programs.

With the help of SQL, the user can quickly get answers to any, including rather complex queries, in an interactive mode, whereas to implement these queries in another language, one would have to develop a corresponding program. In application programs written in certain programming languages, SQL is used as built-in language for accessing the database.

Characterizing the SQL language as a whole, one can distinguish the following features:

· High-level structure, reminiscent of English;

· Independence from specific DBMS;

· Availability of developing standards;

· The ability to execute interactive queries to retrieve data and modify their structure;

· Software access to databases;

· Support for client / server architecture;

· Extensibility and support for object-oriented technologies;

· The ability to access data on the Internet.

The main functions of the SQL language:

SQL - interactive query language... Users enter SQL commands interactively to fetch data and display it on the screen, and to make changes to the database;

SQL - database programming language... SQL commands are inserted into applications to access the database;

SQL - database administration language... The database administrator can use SQL to define the structure of the database and control access to data;

SQL - client / server application creation language... In application programs, SQL is used as a means of organizing communication over a local network with a database server that stores shared data, etc.

55. Possibilities of language The SQL language, which complies with the latest standards SQL: 2003, SQL: 1999, is a very rich and complex language, all the possibilities of which are difficult to immediately grasp, much less understand. Therefore, you have to break the language into levels. In one of the classifications provided by the SQL standard, this language is divided into "basic" (entry), "intermediate" (intermediate) and "full" (full) levels. The basic level contains about forty commands that can be grouped into categories according to their functionality.

CREATE TABLE Details (NOMZ INT, NAME CHAR (15), YEAR INT, SEX CHAR (3))

DROP TABLE Details

ALTER TABLE Information (SEMPOL CHAR (10))

CREATE VIEW Grade M1 AS SELECT * FROM Grade WHERE GROUP = "M-1"

INSERT INTO Information VALUES (980101, "IVANOV I. I.", 1980, "HUSBAND")

DELETE FROM Details WHERE NOMZ = 980201

UPDATE Details SET FULL NAME = "I. I. KRAVTSOVA" WHERE NOMZ = 980201

SELECT * FROM Information WHERE FULL NAME = "SIDOROV S. S." OR Full name = "PETROV P. P."

54. Data types and expressions To access a relational table in SQL, you need to write (set) a command. SELECT – keyword tells the DBMS what action this command will take. Query commands begin with a keyword. In addition to SELECT, these can be words CREATE- create, INSERT-insert, DELETE- delete, COMMIT- to complete and etc.

FROM - a keyword like SELECT that appears in every command. It is followed by a space followed by the name of the tables used as sources of information. The names of tables and fields must contain from 1 to 18 characters, start with a letter and not contain spaces or special characters.

WHERE – a keyword followed by a predicate is a condition imposed on a record in the table, which it must satisfy in order to disappear into the selection.

ORDER BY - sorting of the displayed records (Asc - in ascending order, Desc - in descending order. If the sorting type is not specified, the sorting occurs in ascending order).

CHAR (length) CHARACTER (length)Constant length character strings

INTEGER INTWhole numbers

SMALLINTSmall integer

NUMERIC (precision, degree) DECIMAL (precision, degree DEC (precision, degree)Fixed point number

FLOAT (precision)Floating point number

Double precisionnumbers with float zap high precision

Expressions in SQL, they are used to set criteria for selecting data or performing operations on values ​​that are read from a database. Expressions are a specific sequence of database fields, constants, functions, connected by operators.

Constants are used to indicate specific data values. Fixed point constants, for example: 21 -375.18 62.3

Floating point constants, for example: 1.5Е7 -3.14Е9 2.5Е-6 0.783Е24

String constants must be enclosed in single quotes. Examples of such constants: "Minsk" "New York" "Ivanov I. I."

Missing value(NULL). SQL supports handling missing data with the concept of missing value.

Most SQL-oriented DBMS support the so-called aggregate (summary) functions... Commonly used aggregate functions include the following:

· COUNT- the number of values ​​in the table column;

· SUM- the sum of the values ​​in the column;

· AVG- the arithmetic mean of the values ​​in the column;

· MAX- the maximum value in the column;

· MIN Is the minimum value in the column.

You can use the following in expressions operator types:

· arithmetic: + (addition), - (subtraction), * (multiplication), / (division);

· relationship: = (equal),> (greater than),< (меньше), >= (greater than or equal),<= (меньше или равно), <>(not equal);

· brain teaser: AND(logical "AND"), OR(logical "OR"), NOT(logical negation);

56. Commands for managing transactions allow you to ensure the integrity of the database.

SQL transaction Are several sequential SQL commands that must be executed as a single unit.

In SQL language, transaction processing is implemented using two commands - COMMIT and ROLLBACK... They manage the changes made by a group of teams. Command COMMIT reports the successful completion of the transaction. It informs the DBMS that the transaction is completed, all its commands have been executed successfully and no inconsistencies have arisen in the database. Command ROLLBACK reports unsuccessful completion of the transaction. It informs the DBMS that the user does not want to complete the transaction, and the DBMS must discard all changes made to the database as a result of the transaction. In this case, the DBMS returns the database to the state it was in before the transaction was executed.

Commands COMMIT and ROLLBACK are used mainly in program mode, although they can also be used in interactive mode.

57. To access control commands refers to commands for performing administrative functions that assign or revoke the right (privilege) to use the database tables in a certain way. Each user of the database has certain rights in relation to the objects of the database.

Rights Are those actions with the object that the user can perform. Rights can change over time: old ones can be canceled, new ones added. The following rights are provided:

INSERT - the right to add data to the table;

UPDATE - the right to change the data of the table;

DELETE - the right to delete data from the table;

· REFERENCES - the right to define the primary key.

58 Embedding language in application programs ...To built-in refers to commands designed to implement access to the database from application programs written in a particular programming language.

Today we will turn to the computer topic, so this article will be of particular interest, primarily for programmers. We, dear reader, will talk about the structured query language, which in the English version is encrypted as - SQL (Structured Query Language). So, let's get to the point. Right now, let's talk about what SQL is and what it is for.

Structured Query Language is a general purpose language for creating, modifying, and manipulating information that is part of relational databases. SQL was originally the primary way of working with data. With it, the user could perform the following actions:

• creating a new table in the database (DB);
• adding new records to existing tables;
• editing records;
• complete deletion of records;
• selection of records from different tables, in accordance with the specified conditions;
• changing the type and structure of one or more tables.

As it developed, SQL has been greatly transformed and enriched with new useful functions, as a result of which it has become more and more like a real programming language. Today, SQL is the only mechanism capable of linking application software and a database. That's what SQL is.

SQL has several kinds of queries. It is worth noting that any SQL query implies either a request for data from the required database, or an appeal to the database with the obligatory change of data in it. In this regard, it is customary to distinguish the following types of requests:

• creation or modification of new or existing objects in the database;
• receiving data;
• adding new data to the table;
• deletion of data;
• access to the database management system (DBMS).

A little about the advantages and disadvantages of this system for working with data.

SQL Benefits

• Independence from the existing DBMS in the given system. SQL texts are universal for many DBMS. However, this rule applies to simple tasks associated with processing data in tables.
• The presence of SQL standards helps to "stabilize" the language.
• Declarativeness. This advantage lies in the fact that when working with data, the programmer selects only the information that needs to be changed or modified. How this will be done is automatically decided at the program level of the DBMS itself.

Disadvantages of SQL

• SQL does not conform to the relational data model. In this regard, SQL replaces Tutorial D, which is truly relational.
• The complexity of SQL determines its purpose. The language is so complex that only a programmer can use it. Although initially it was conceived as a control tool that an ordinary user would work with.
• Some discrepancy in standards. Many DBMS companies add their own features to the SQL dialect, which significantly affects the versatility of the language.

One last thing: what is SQL Server? This is a database management system that was developed within the walls of the famous Microsoft company. This system successfully works with databases of both home personal computers and large databases of huge enterprises. In this market segment, SQL Server is more than competitive.

Well, literally in a nutshell, let's remember MySQL. This application is typically used as a server that receives calls from local or remote clients. MySQL can also be included in standalone programs. It should be noted that this application is one of the most flexible data management systems, as it includes many different types of tables.

SQL LANGUAGE: DATA MANIPULATION

IN THIS LECTURE ...

· Purpose of Structure Query Language (SQL) and its special role in working with databases.

· History of the emergence and development of the SQL language.

· Writing SQL statements.

· Fetching information from databases using the SELECT statement.

· Construction of SQL statements, characterized by the following features:

· Using the WHERE clause to select rows that satisfy various conditions;

· Sorting the results of query execution using the ORDER BY construction;

· Use of aggregating functions of the SQL language;

· Grouping the selected data using the GROUP BY clause;

· Application of subqueries;

· The use of table joins;

· Application of operations with sets (UNION, INTERSECT, EXCEPT).

· Making changes to the database using INSERT, UPDATE and DELETE statements.

One of the languages ​​that emerged as a result of the development of the relational data model is SQL, which is now very widespread and has actually become the standard language for relational databases. The SQL standard was released by the US National Standards Institute (ANSI) in 1986, and in 1987 the International Organization for Standardization (ISO) adopted this standard as an international standard. SQL is currently supported by hundreds of different types of database management systems designed for a wide variety of computing platforms, from personal computers to mainframes.

This chapter uses the ISO definition of SQL.

Introduction to the SQL language

In this part, we will look at the purpose of the SQL language, explore its history, and analyze the reasons why it is now so important for database applications.

The purpose of the SQL language

Any language designed to work with databases should provide the user with the following capabilities:

· Create databases and tables with a complete description of their structure;

· Perform basic data manipulation operations, such as inserting, modifying and deleting data from tables;

· Execute simple and complex queries.

In addition, the language for working with databases should solve all of the above tasks with minimal effort on the part of the user, and the structure and syntax of its commands should be simple enough and accessible for learning.

And finally, the language must be universal, i.e. meet some recognized standard, which will allow you to use the same syntax and command structure when moving from one DBMS to another. The modern SQL language standard satisfies almost all of these requirements.

SQL is an example of a data transformation language, or a language designed to work with tables in order to transform input data into the desired output form. The SQL language, which is defined by the ISO standard, has two main components:

· Data Definition Language (DDL), designed to define database structures and control access to data;

· Data Manipulation Language (DML), designed to fetch and update data.

Before the SQL3 standard, SQL only included commands for defining and manipulating data; it lacked any commands to control the course of calculations. In other words, in this language there were no IF ... THEN ... ELSE, GO TO, DO ... WHILE, or any other instructions designed to control the flow of the computational process. Such tasks had to be solved programmatically, using programming languages ​​or task control, or interactively, as a result of actions performed by the user himself. Due to such incompleteness, from the point of view of the organization of the computational process, the SQL language could be used in two ways. The first was for interactive work, where the user entered individual SQL statements from the terminal. The second was to inject SQL statements into procedural language programs.

Advantages of the SQL3 language, the formal definition of which was adopted in 1999:

· The SQL language is relatively easy to learn.

· It is a non-procedural language, so it needs to indicate what information is to be obtained, not how it can be obtained. In other words, SQL does not require specifying data access methods.

· Like most modern languages, SQL supports a fluent operator format. This means that when you enter, the individual operator elements are not linked to the fixed items on the screen.

· The structure of commands is set by a set of keywords that are ordinary English words, such as CREATE TABLE -Create a table, INSERT - Insert, SELECT -Select.

For example:

CREATE TABLE [Sales] ((S), [Object name] VARCHAR (15), [Cost] DECIMAL (7,2));

INSERT INTO [Object] VALUES ("SG16", "Brown", 8300);

SELECT, [Object name], [Cost];

FROM [Sales]

WHERE [Cost]> 10000;

· The SQL language can be used by a wide range of users, including database administrators (DBAs), company management, application programmers, and many other end users of various categories.

Currently, there are international standards for the SQL language that formally define it as the standard language for creating and manipulating relational databases, which it actually is.

History of the SQL language

The history of the relational data model, and indirectly of the SQL language, began in 1970 with the publication of a seminal paper by EF Codd, who was at the time at the IBM San Jose Research Laboratory. In 1974, D. Chamberlain, who worked in the same laboratory, published a definition of a language called "Structured English Query Language", or SEQUEL. In 1976, a revised version of this language, SEQUEL / 2, was released; later its name had to be changed to SQL for legal reasons - the abbreviation SEQUEL was already used by philologists. But to this day, many people still pronounce the SQL acronym as "sequel", although officially it is recommended to read it as "es-kyu-el".

In 1976, based on the SEQUEL / 2 language, IBM Corporation released a prototype DBMS called "System R". The purpose of this trial was to test the feasibility of implementing the relational model. Among other positive aspects, the most important of the results of this project can be considered the development of the SQL language itself.However, the roots of this language go back to the SQUARE language (Specifying Queries as Rational Expressions), which was the predecessor of the System R project. The SQUARE language was developed as a research tool for implementation relational algebra through phrases written in English.

In the late 1970s, Oracle was released by what is now Oracle Corporation. Perhaps this is the very first of the commercial implementations of a relational DBMS built on the use of the SQL language.

A little later, the INGRES DBMS appeared, which used the QUEL query language.

This language was more structured than SQL, but its semantics were less similar to that of regular English. Later, when SQL was adopted as the standard relational database language, INGRES was completely translated into its use. In 1981, IBM released its first commercial relational database management system called SQL / DS (for DOS / VSE). In 1982, a version of this system was released for the VM / CMS environment, and in 1983 - for the MVS environment, but under the name DB2.

In 1982, the United States National Standards Institute (ANSI) began work on the Relation Database Language (RDL) based on concept papers from IBM. In 1983, the International Organization for Standardization (ISO) joined this work. The joint efforts of both organizations culminated in the release of the SQL language standard. The RDL name was dropped in 1984, and the draft language was redesigned to approximate existing SQL implementations.

The original version of the standard, which was issued by ISO in 1987, has received a lot of criticism. In particular, Date, a renowned researcher in the field, pointed out that the standard omitted critical functions, including referential integrity and some relational operators.

In addition, he noted the excessive redundancy of the language - one and the same request could be written in several different versions. Most of the criticisms were found to be valid and the necessary adjustments were made to the standard even before it was published. However, it was decided that it was more important to release the standard as soon as possible so that it could act as a common basis on which both the language itself and its implementations could develop further than waiting until all the functions that different experts consider necessary are defined and agreed. for a similar language.

In 1989, ISO published a supplement to the standard defining data integrity functions. In 1992, the first major revision of the ISO standard was released, sometimes referred to as SQL2 or SQL-92. Although some of the functions were first defined in this standard, many of them have already been fully or partially implemented in one or more commercial implementations of the SQL language.

And the next version of the standard, which is commonly called SQL3, was released only in 1999. This release contains additional support for object-oriented data management functions.

Functions that are added to the language standard by developers of commercial implementations are called extensions. For example, the SQL language standard defines six different types of data that can be stored in databases. In many implementations, this list is supplemented by various extensions. Each of the language implementations is called a dialect. There are no two completely identical dialects, as there is currently no dialect that fully complies with the ISO standard.

Moreover, as database developers introduce new functionality into systems, they continually expand their SQL dialects, with the result that individual dialects become more and more different from each other. However, the core of the SQL language remains more or less standardized across all implementations.

Although the original concepts of SQL were developed by IBM, its importance soon pushed other developers to create their own implementations. Currently, there are literally hundreds of products available on the market based on the use of the SQL language, and we constantly hear about the release of more and more new versions,

Functionality of the SQL language

The basic functionality of the SQL language is shown below.

Data definition. This SQL function is a description of the structure of the supported data and the organization of relational relationships (tables). To implement it, the operators for creating a database, creating tables and accessing data are intended.

Database creation... The CREATE DATABASE statement is used to create a new database. The name of the database to be created is indicated in the operator structure.

Creation of tables. The base table is created using the CREATE TABLE statement. This operator specifies field names, data types for them, length (for some data types). The following data types are used in SQL:

INTEGER - integer;

CHAR - character value;

VARCHAR - character value, only non-empty characters are stored;

DECIMAL - decimal number;

FLOAT - floating point number;

DOUBLE PRECISION - double floating point precision;

DATETIME - date and time;

BOOL is a boolean value.

The create table statement specifies constraints on column values ​​and on a table. Possible limitations are shown in table. 4.8

Table 4.8 Restrictions on Defined Data

For the relational data model, specifying a foreign key (FOREIGNKEY) is essential. When declaring foreign keys, you must impose appropriate constraints on the column, such as NOT NULL.

In an SQL statement, CHECK denotes semantic constraints that enforce data integrity, such as constraining the set of valid values ​​for a specific column.

You cannot use the create table statement multiple times for the same table. If, after its creation, inaccuracies in its definition were found, then you can make changes using the ALTER TABLE statement. This operator is intended to change the structure of an existing table: you can delete or add a field to an existing table.

Data manipulation. SQL allows a user or application to modify the contents of a database by inserting new data, deleting or modifying existing data.

Inserting new data is the procedure for adding rows to the database and is performed using the INSERT statement.

Data modification assumes changes to values ​​in one or more columns of a table and is performed using an UPDATE statement. Example:

SET amount = amount + 1000.00

WHERE sum> 0

Deleting Rows from the table is done using the DELETE statement. The syntax of the operator is:

FROM table

The WHERE clause is optional; however, if you do not include it, all records in the table will be deleted. It is useful to use the SELECT statement with the same syntax as the DELETE statement to check beforehand which records will be deleted.

Ensuring data integrity. The SQL language allows you to define fairly complex integrity constraints, the satisfaction of which will be checked for all database modifications. Control over the results of transactions, processing of emerging errors and coordination of parallel work with the database of several applications or users is provided by the COMMIT statements (fixes the successful end of the current transaction and the beginning of a new one) and ROLLBACK (the need to rollback - automatic restoration of the database state at the beginning of the transaction)

Fetching data Is one of the most important functions of the database, to which the SELECT statement corresponds. An example of using the operator was discussed in the previous section.

In SQL, you can create nested sequences of queries (subqueries). There are certain types of queries that are best implemented using subqueries. These requests include the so-called existence checks. Suppose you want to get data about students who do not have a grade of seven. If an empty set is returned, then this means only one thing - each student has at least one such grade.

Linking tables... SQL statements allow you to retrieve data from more than one table. One way to do this is to link tables on a single common field.

The SELECT statement must contain a constraint on the coincidence of the values ​​of a specific column (field). Then only those rows in which the values ​​of the specified column are the same will be retrieved from the linked tables. The column name is indicated only together with the table name; otherwise, the operator will be ambiguous.

You can use other types of table joins: The INTER JOIN statement ensures that the resultant recordset contains matching values ​​in the related fields. Outer joins (OUTER JOIN) allow to include in the query result all rows from one table and the corresponding rows from another

Access control. SQL provides synchronization of database processing by various applications, data protection from unauthorized access.

Access to data in a multi-user environment is governed by the GRANT and REVOKE statements. In each statement, you must specify the user, the object (table, view), in relation to which the authority is assigned, and the authority itself. For example, the GRANT statement instructs user X to select data from the PRODUCT table:

GRANT SELECT ON PRODUCT TO X

The REVOKE operator revokes all previously granted authorizations.

Embedding SQL in Applications... Real applications are usually written in other languages ​​that generate SQL code and send them to the DBMS as ASCII text.

The IBM standard for SQL products specifies the use of embedded SQL. When writing an application program, its text is a mixture of commands from the main programming language (for example, C, Pascal, Cobol, Fortran, Assembler) and SQL commands with a special prefix, for example. ExecSQL. The structure of the SQL statements has been extended to accommodate the host language variables in the SQL statement.

The SQL processor modifies the view of the program in accordance with the requirements of the compiler of the main programming language. The function of the compiler is to translate (translate) a program from a source programming language into a language close to a machine language. After compilation, the application program (application) is an independent module.

SQL dialects

Modern relational DBMSs use SQL dialects to describe and manipulate data. A subset of the SQL language that allows you to create and describe a database is called DDL (Data Definition Language).

Initially, SQL was called SEQUEL (Structured English Query Language), then SEQUEL / 2, and then simply SQL. Today, SQL is the de facto standard for relational database management systems.

The first language standard appeared in 1989 - SQL-89 and was supported by almost all commercial relational DBMSs. It was of a general nature and was open to wide interpretation. The advantages of SQL-89 include the standardization of the syntax and semantics of the operators for fetching and manipulating data, as well as the fixation of tools for limiting the integrity of the database. However, it was missing such an important section as manipulating the database schema. The incompleteness of the SQL-89 standard led to the appearance in 1992. the next version of the SQL language.

SQL2 (or SQL-92) covers almost all necessary problems: manipulating the database schema, managing transactions and sessions, supporting client-server architectures or application development tools.

A further step in the development of the language is the version of SQL 3. This version of the language is supplemented with a trigger mechanism, the definition of an arbitrary data type, and an object extension.

Currently, there are three levels of the language: beginner, intermediate and complete. Many vendors of their DBMSs use their own SQL implementations based at least at the entry level of the corresponding ANSI standard and containing some DBMS-specific extensions. Table 4.9 shows examples of SQL dialects.

Table 4.9 SQL dialects

DBMS	Query language
System R DBMS	SQL
Db2	SQL
Access	SQL
SYBASE SQL Anywhere	Watcom-SQL
SYBASE SQL Server	Transact_SQL
My SQL	SQL
Oracle	PL / SQL

Object-oriented databases use the Object Query Language (OQL). The OQL language was based on the SQL2 SELECT command and added the ability to direct a query to an object or a collection of objects, as well as the ability to call methods within a single query.

The compatibility of many of the SQL dialects in use determines the compatibility of the DBMS. For example, SYBASE SQL Anywhere is as compatible as possible with SYBASE SQL Server. One of the sides of this compatibility is SYBASE SQL Anywhere's support for such a dialect of the SQL language as Transact-SQL... This dialect is used in SYBASE SQL Server and can be used in SYBASE SQL Anywhere along with its own SQL dialect - Watcom-SQL.

Control questions

1. How can you classify a DBMS?

2. What database models are there?

3. What are the main elements of infological models?

4. What types of relationships between entities exist?

5. What are ER diagrams and what are they used for?

6. What does the table normalization procedure allow to do?

7. What are the language and software tools of the DBMS?

8. What type is the MS Access DBMS?

9. What are the main objects of the MS Access DBMS?

10. What are the basic SQL statements used for?