What is SQL. Functionality of the SQL language

18.08.2019 Reviews

02/07/07 11.6K

An Introduction to Relational Database Management

sql is often referred to as Esperanto for Database Management Systems (DBMS). Indeed, there is no other language in the world for working with databases (DB), which would be so widely used in programs. The first sol standard appeared in 1986 and has gained universal acceptance by now. It can be used even when working with non-relational DBMSs. Unlike other software tools, such as the C and Cobol languages, which are the prerogative of professional programmers, sql is used by specialists from a wide variety of fields. Programmers, DBMS administrators, business analysts - they all successfully process data using sql. Knowledge of this language is useful to everyone who has to deal with a database.

In this article, we will go over the basic concepts of sql. Let's tell its background (and dispel a few myths along the way). You will get acquainted with the relational model and will be able to acquire the first skills in working with sql, which will help in the further development of the language.

Is sql hard to learn? It depends on how deeply you are going to delve into the essence. There is a lot to learn to become a pro. The sql language appeared in 1974 as the subject of a small research paper of 23 pages and has come a long way since then. The text of the current standard - the official document "the international standard database language sql" (usually called sql-92) - contains over six hundred pages, but it does not say anything about the specific features of the sol versions implemented in the DBMS of microsoft, oracle, sybase and others. The language is so developed and diverse that just a simple listing of its capabilities will require several journal articles, and if you collect everything that is written on the topic of sol, you get a multivolume library.

However, for the average user it is not at all necessary to know sql completely and completely. As a tourist who finds himself in a country where they speak an incomprehensible language, it is enough to learn only a few common expressions and grammar rules, and in sql - knowing a little, you can get a lot of useful results. In this article, we will look at the basic sql commands, the rules for setting criteria for selecting data and show how to get the results. As a result, you will be able to independently create tables and enter information into them, create queries and work with reports. This knowledge can become the basis for further independent mastering of sql.

What is sql?

sql is a specialized non-procedural language that allows you to describe data, fetch and process information from relational DBMS. Specialization means that sol is intended only for working with a database; it is impossible to create a full-fledged application system only by means of this language - for this you will need to use other languages in which you can embed sql commands. Therefore, sql is also called an auxiliary language tool for data processing. An auxiliary language is used only in conjunction with other languages.

A general-purpose application language usually has facilities for creating procedures, but sql does not. With its help, you cannot indicate how a certain task should be performed, but you can only determine what exactly it is. In other words, when working with sql, we are interested in the results, not the procedures for obtaining them.

The most essential property of sql is the ability to access relational databases. Many people even think that the expressions "database processed by means of sql" and "relational database" are synonyms. However, you will soon see that there is a difference between the two. The sql-92 standard doesn't even have the term relation.

What is a relational DBMS?

Without going into details, a relational DBMS is a system based on a relational data management model.

The concept of a relational model was first proposed in the work of Dr. EF Codd, published in 1970. It described the mathematical apparatus for structuring and managing data, and also proposed an abstract model for representing any real information. Before that, when using a database, it was required to take into account the specific features of storing information in it. If the internal structure of the database changed (for example, in order to improve performance), the application programs had to be reworked, even if there were no changes at the logical level. The relational model made it possible to separate the particular features of data storage from the application level. Indeed, the model does not describe in any way how information is stored and accessed. Only how this information is perceived by the user is taken into account. Thanks to the emergence of the relational model, the approach to data management has fundamentally changed: from an art it has turned into a science, which led to a revolutionary development of the industry.

Basic concepts of the relational model

According to the relational model, a relation is some kind of table with data. A relation can have one or more attributes (features) corresponding to the columns of this table, and some set (possibly empty) of data representing sets of these attributes (they are called n-ary tuples, or records) and corresponding to the rows of the table.

For any tuple, the attribute values must belong to so-called domains. In fact, a domain is a set of data that defines the set of all valid values.

Let's take an example. Suppose you have a DomainDaysWeeks that contains values from Monday to Sunday. If the relation has a DayWeek attribute corresponding to this domain, then any tuple of the relation in the DayWeek column must contain one of the listed values. The appearance of the values January or Cat is not allowed.

Please note: the attribute must have one of the valid values. Setting multiple values at once is prohibited. Thus, in addition to the requirement that the attribute values belong to a certain domain, the condition of its atomicity must be met. This means that decomposition is unacceptable for these values, that is, you cannot break them down into smaller parts without losing the main meaning. For example, if the attribute value simultaneously contained Monday and Tuesday, then two parts could be distinguished, retaining the original meaning - Day of the Week; therefore, this attribute value is not atomic. However, if you try to break the meaning of "Monday" into parts, you get a set of individual letters - from "P" to "K"; the original meaning is lost, so the meaning of "Monday" is atomic.

Relationships have other properties as well. The most significant of them is the mathematical property of closed operations. This means that as a result of performing any operation on a relation, a new relation should appear. This property allows you to get predictable results when performing mathematical operations on relationships. In addition, it becomes possible to represent operations in the form of abstract expressions with different levels of nesting.

In his original work, Dr. Codd defined a set of eight operators called relational algebra. The four operators — union, logical multiplication, difference, and Cartesian product — were carried over from traditional set theory; the rest of the operators were created specifically to handle relationships. In subsequent work by Dr. Codd, Chris Date and others, additional operators have been proposed. The rest of this article will discuss three relational operators - production (project), constraints (select, or restrict), and merge (join).

sql and the relational model

Now that you are familiar with the relational model, let's forget about it. Of course, not forever, but only to explain the following: although it was the relational model proposed by Dr. Codd that was used in the development of sql, there is no full or literal correspondence between them (this is one of the reasons why the term relation). For example, the concepts sql table and relation are not equivalent, because tables can contain several identical rows at once, while identical tuples are not allowed in a relationship. In addition, sql does not provide for relational domains, although data types play a role to some extent (some influential proponents of the relational model are now trying to get relational domains included in the future sql standard).

Unfortunately, the inconsistency between sql and the relational model has generated a lot of confusion and controversy over the years. But since the main topic of the article is learning sql, and not the relational model, these issues are not covered here. Just remember that there are differences between the terms used in sql and in the relational model. Further in the article, only terms accepted in sql will be used. Instead of relations, attributes and tuples, we will use their sql counterparts: tables, columns and rows.

Static vs dynamic sql

You may already be familiar with terms like static and dynamic sql. An sql query is static if it is compiled and optimized before the execution of the program. We have already mentioned one of the forms of static sql when we talked about embedding sql commands in C or Cobol programs (there is another name for such expressions - embedded sql). As you can probably guess, a dynamic sql query is compiled and optimized during program execution. As a rule, ordinary users use just dynamic sql, which allows them to create queries in accordance with immediate needs. One of the options for using dynamic sql queries is their interactive or direct call (there is even a special term - directsql), when the queries sent for processing are entered interactively from the terminal. There are certain differences between static and dynamic sql in the syntax of the structures used and in the execution features, but these issues are beyond the scope of the article. We only note that for clarity of understanding, examples are given in the form of direct sql queries, since this allows you to learn how to use sql not only for programmers, but also for most end users.

How to learn sql

You are now ready to write your first sql queries. If you have access to the database through sql and you want to use our examples in practice, then consider the following: you must log in as a user with unlimited permissions and you will need software tools for interactive processing of sql queries (if we are talking about a network database, you should talk with the database administrator about granting you the appropriate rights). If there is no access to the database through sql, do not be upset: all examples are very simple and you can figure them out "dry", without going to the machine.

In order to perform any actions in sql, you must execute an expression in the sql language. There are several types of expressions, but three main groups can be distinguished among them: ddl-commands (data definition language), dml-commands (data manipulation language) and data control tools. Thus, in a sense, sql combines three different languages.

Data Description Language Commands

Let's start with one of the main ddl commands - create table. There are several types of tables in sql, the main ones are two types: base (base) and selective (views). Tables referring to real-life data are basic; selective - these are "virtual" tables that are created based on information obtained from the base tables; but to users, the forms look like regular tables. The create table command is for creating base tables.

In the create table command, you should specify the name of the table, specify the list of columns and the types of data they contain. There may be other optional items as parameters, but first let's just look at the basic parameters. Let's show the simplest syntactic form for this command:

create table TableName (Column DataType);

create and table are sql keywords; TableName, Column, and DataType are formal parameters instead of which the user enters actual values each time. The Column and DataType parameters are enclosed in parentheses. In sql, parentheses are commonly used to group individual items. In this case, they allow you to combine the definitions for the column. The semicolon at the end is the command separator. It must complete any sql expression.

Let's look at an example. Let's say you want to create a table to store data about all appointments. To do this, enter the command in sql:

create table appointments (appointment_date date);

After executing this command, a table named appointments will be created with one column, appointment_date, in which data of type date can be written. Since no data has been entered at this time, the number of rows in the table is zero (with the create table command only the definition of the table is given; the actual values are entered by the insert command, which is discussed later).

The appointments and appointment_date parameters are called identifiers because they set the names for specific database objects, in this case, the names for the table and column, respectively. There are two types of identifiers in sql: regular and delimited. Highlighted identifiers are enclosed in double quotes and are case-sensitive. Regular identifiers are not distinguished by any limited characters and are not case-sensitive. This article applies only to regular identifiers.

Symbols used to construct identifiers must follow certain rules. In ordinary identifiers, only letters (not necessarily Latin, but also other alphabets), numbers and the underscore character can be used. The identifier must not contain punctuation marks, spaces or special characters (#, @,% or!); also, it cannot start with a digit or underscore. Separate sql keywords can be used for identifiers, but this is not recommended. The identifier is intended to designate some object, so it must have a unique (within a certain context) name: you cannot create a table with a name that is already found in the database; you cannot have columns with the same name in the same table. By the way, keep in mind that appointments and appointments are the same names for sql. It is impossible to create a new identifier just by changing the case of letters.

Although a table can have only one column, in practice, tables with multiple columns are usually required. The command for creating such a table in general looks like this:

create table TableName (Column DataType [(, ColumnDataType)]);

Square brackets are used to indicate optional elements, curly ones contain elements that can represent a list of single-path constructions (when entering a real sql command, neither one nor the other brackets are placed). This syntax allows you to specify any number of columns. Note that there is a comma in front of the second item. If there are several parameters in the list, then they are separated from each other by commas.

create table appointments2 (appointment_date date, appointment_time time, description varchar (256));

This command creates the appointments2 table (the new table must have a different name, since the appointments table is already present in the database). Like the first table, it has an appointment_date column to record the date of the meetings; in addition, the appointment_time column was added to record the times of these appointments. The description parameter is a text string that can contain up to 256 characters. The type specified for this parameter is varchar (short for character varying), since it is not known in advance how much space will be required for the recording, but it is clear that the description will take no more than 256 characters. When describing a parameter in the character string type (and some other types), the length of the parameter is indicated. Its value is specified in parentheses to the right of the type name.

You may have noticed that in the two examples we looked at, the command entry is formatted differently. If in the first case the command is completely placed on one line, then in the second after the first open parenthesis the writing is continued from a new line, and the definition of each next column starts from a new line. There are no special requirements for record formatting in sql. Breaking a record into lines makes it easier to read. The sql language allows, when writing commands, not only to split the command line by line, but also to insert indents at the beginning of lines and spaces between record elements.

Now that you know the basic rules, let's look at a more complex example of creating a multi-column table. At the beginning of the article, the employees table was shown. It contains the following columns: last name, first name, date of hiring, department, category, and salary for the year. The following sql command is used to define this table:

create table employees (last_name character (13) not null, first_name character (10) not null, hire_date date, branch_office character (15), grade_level smallint, salary decimal (9, 2));

There are several new elements in the team. First of all, it is the expression not null at the end of the definition of the last_name and first_name columns. With the help of such structures, requirements are set that must be met. In this case, it is indicated that the last_name and first_name fields must be filled in when entering; you cannot leave these columns empty (this is quite logical: how can you identify an employee without knowing his name?).

In addition, there are three new data types in the example: character, smallint, and decimal. Until now, we have hardly talked about types. Although there are no relational domains in sql, it does have a set of basic data types. This information is used when allocating memory and comparing values; to a certain extent narrows the list of possible values as you type, however, type checking in sql is less strict than in other languages.

All data types available in sql can be divided into six groups: character strings, exact numeric values, approximate numeric values, bit strings, datetime and intervals. We have listed all the varieties, but in this article only a few of them will be considered in detail (bit strings, for example, are not of particular interest to ordinary users).

By the way, if you thought that the date time was a typo, you were wrong. This group (datetime) includes most of the time-related data types used in sql (such parameters as time intervals are separated into a separate group). In the previous example, we have already encountered two data types from the datetime group - date and time.

The next datatype you are already familiar with is character varying (or just varchar); it belongs to the group of character strings. If varchar is used to store strings of variable length, then the char type found in the third example is intended for storing strings with a fixed number of characters. For example, the last_name column will contain strings of 13 characters, regardless of the actual surname entered, be it poe or penworth-chickering (in the case of poe, the remaining 10 characters will be filled with spaces).

From a user perspective, varchar and char have the same meaning. Why did you need to introduce two types? The fact is that in practice you usually have to find a compromise between performance and saving disk space. As a rule, using fixed-length strings gives some gain in access speed, however, if the strings are too long, disk space is wasted. Reserving 256 characters for each comment line in appointments2 may not be rational; more often than not, the lines will be significantly shorter. On the other hand, surnames also have different lengths, but they usually require about 13 characters; in this case, the losses will be minimal. There is a good rule of thumb: if you know that the length of the string changes slightly or is relatively small, then use char; otherwise, varchar.

The next two new data types, smallint and decimal, belong to the group of precise numeric values. smallint is short for small integer. Sql also provides an integer data type. The presence of two similar types in this case is explained by considerations of saving space. In our example, the values of the grade_level parameter can be represented using a two-digit number, so the smallint type is used; however, in practice, it is not always known what maximum values the parameters can have. If there is no such information, then use integer. The actual space allocated for storing smallint and integer parameters and the corresponding range of values for these parameters are platform-specific.

The decimal data type, commonly used for financial accounting, allows you to specify a pattern with the required number of decimal places. Since this type is used for accurate numeric notation, it guarantees precision when performing mathematical operations on decimal data. If you use data types from the group of approximate numeric notation for decimal values, for example float (floating point number), this will lead to rounding errors, so this option is not suitable for financial calculations. To define parameters of the decimal type, the following notation is used:

where p is the number of decimal places, d is the number of decimal places. Instead of p, write the total number of significant digits in the values used, and instead of d, write the number of digits after the decimal point.

The sidebar "Creating a Table" shows the complete summary of the create table command entry. It contains new elements and shows the format for all considered data types (In principle, there are other data types, but so far we do not consider them).

At first, it may seem that the syntax of sql commands is too complicated. But you can easily figure it out if you carefully study the above examples. An additional element appeared on the diagram - a vertical bar; it serves to delineate alternative designs. In other words, when defining each column, you need to select the appropriate data type (as you remember, square brackets enclose optional parameters, and curly braces enclose constructs that can be repeated many times; these special characters are not written in real sql commands). The first part of the diagram contains the full names for the data types, the second - their abbreviated names; in practice, you can use any of them.

The first part of the article is completed. The second will be devoted to the study of the insert, select, update and delete dml commands. It will also cover data selection conditions, comparison and logical operators, the use of null values, and ternary logic.

Creating a table. The syntax for the create table command: in square brackets are optional parameters, in curly brackets - repeated constructs.

Sql name secret

In the early 1970s. ibm has begun to put into practice Dr. Codd's relational database model. Donald Chamberlin and a group of others in the Advanced Research Division have created a prototype language called structured english query language, or simply sequel. Later it was expanded and refined. The new version proposed by ibm is called sequel / 2. It was used as a software interface (api) for designing the first relational database system from ibm - system / r. Due to legal considerations, ibm decided to change the name: instead of sequel / 2, use sql (structured query language). This abbreviation is often pronounced as "si-ku-el".

There are significant differences between the early prototypes of sequel and the now recognized sql standard in various organizations. Jim Melton, who was involved in the preparation of the sql-92 standard, even stated that many are mistaken, believing that the word "structured" correctly reflects the specifics of this language (jim melton and alan r. Simon "understanding the new sql: a complete guide". San francisco : morgan kaufmann, 1993. isbn: 1-55860-245-3). Therefore, in fact, sql is just a name, a sequence of letters s-q-l and nothing more.

Good bad

In this chapter ...

What is SQL
SQL misconceptions
A look at the different SQL standards
Familiarity with standard SQL commands and reserved words
Representation of numbers, symbols, dates, times and other types of data
Undefined values and restrictions
Using SQL on a client / server system
SQL on the web

SQL is a flexible language that can be used in a wide variety of ways. It is the most common tool used to communicate with a relational database. In this chapter, I will explain what SQL is and what it is not, in particular, how SQL differs from other types of computer languages. Then you will become familiar with the commands and data types that standard SQL supports. Also, I will explain basic concepts like undefined values and restrictions... Finally, it will provide an overview of how SQL fits into the client / server environment, as well as the Internet and Intranets of organizations.

What SQL is and what it is not

The first thing to understand about SQL is that it is not procedural like FORTRAN, Basic, C, COBOL, Pascal and Java. To solve a problem using one of these procedural languages, you have to write a procedure that performs the specified operations one by one until the task is completed. The procedure can be a linear sequence or contain branches, but in either case, the programmer specifies the order of execution.

In other words, SQL is non-procedural language. To use it to solve the problem, tell SQL, what exactly you need it as if you were talking to the genie from Aladdin's lamp. And you don't have to say how get what you want for you. The database management system (DBMS) will decide how best to fulfill your request.

Okay. I just said that SQL is not a procedural language. In essence, this is true. However, millions of programmers around (and you may be one) are used to solving problems procedurally, so there has been a lot of pressure in recent years to add some procedural capabilities to SQL. Therefore, the new version of the SQL specification, SQL: 2003, now includes procedural language features such as BEGIN blocks, IF conditionals, functions, and procedures. With these new tools, it is possible to store programs on a server so that they can be reused by many users.

To illustrate what I meant when I said "tell the system what you want", suppose you have an EMPLOYEE table with employee data and you want to select all rows from it that match all the "senior" employees. By "senior" workers, you can mean anyone over 40 or earning more than $ 60,000 a year. The selection you need can be made using the following query:

SELECT * FROM EMPLOYEE WHERE AGE> 40 OR SALARY> 60000;

This statement selects from the EMPLOYEE table all rows in which either the AGE column (age) is greater than 40 or the value in the SALARY column (salary) is greater than 60,000. SQL itself knows how to select information. The database engine checks the database and decides for itself how to execute the query. All you have to do is indicate what data you need.

Remember:
A query is a question that you ask the database. If any of its data satisfies the conditions of your query, then SQL transfers them to you.

Modern SQL implementations lack many of the simple programming constructs that are fundamental to most other languages. In everyday life applications, as a rule, at least some of these constructs are required, so SQL is really sublanguage data. Even with the additions that appeared in SQL along with the SQL: 1999 standard and additional extensions added in SQL: 2003, you still need to use one of the programming languages with SQL, such as C.

You can select information from the database in one of the following ways.

By using a one-time non-programmable query from the computer console, entering an SQL command and reading the results of its execution on the screen. Console is a traditional term for computer hardware that does the work of the keyboard and screen found in modern PCs. Console queries are useful when you need a quick response to a specific query. To satisfy any current need, you may need data from the database that you have never needed before. You may never need them again, but now you need them. Enter the appropriate SQL query from the keyboard, and after a while the result will appear on your screen.
With the help of a program that extracts information from a database, and then creates a report based on this data, which is displayed either on the screen or in print. The SQL language can be used like that. You can put a complex SQL query that may come in handy in the future right into your program. This allows you to reuse it in the future. Thus, the formulation of the request is performed once. Chapter 15 covers how to insert SQL code into programs written in another language.

databases that could function in numerous computer systems of various kinds. Indeed, it allows users to manipulate data, whether they are running on a personal computer, networked workstation, or mainframe.

One of the languages that appeared as a result of the development of the relational data model is the SQL (Structured Query Language) language, which is now very widespread and has actually turned into standard language relational databases. Standard SQL language was released by the American National Standards Institute (ANSI) in 1986, and in 1987 the International Standards Organization (ISO) adopted it as an international one. The current SQL standard is known as SQL / 92.

The use of any standards is associated not only with numerous and quite obvious advantages, but also certain disadvantages. First of all, the standards direct the development of the respective industry in a certain direction; in the case of SQL, having solid foundational principles ultimately leads to the compatibility of its various implementations and contributes to both increasing the portability of software and databases in general, and the versatility of the DBA. On the other hand, standards limit the flexibility and functionality of a particular implementation. Under language implementation SQL refers to the SQL software product of the respective manufacturer. To enhance functionality, many standards-based developers add to standard language SQL various extensions. It should be noted that the standards require any complete language implementations SQL has certain characteristics and broadly reflects the main trends that not only lead to compatibility between all competing implementations, but also contribute to increasing the value of SQL programmers and users relational databases in the modern software market.

All specific language implementations are somewhat different from each other. It is in the manufacturers' best interest to ensure that their implementation conforms to current ANSI standards for portability and user experience. However, each SQL implementation contains enhancements to meet the needs of a particular database server. These enhancements or enhancements to the SQL language are additional commands and options that are additions to the standard package and available in this particular implementation.

Currently, SQL is supported by dozens of different types of database management systems developed for a wide variety of computing platforms, from personal computers to mainframes.

All data manipulation languages created for many DBMS before relational databases were focused on operations with data presented as logical file records. Of course, this required the user to have detailed knowledge of how the data was stored and to make a serious effort to specify what data was needed, where it was located, and how to obtain it.

The SQL language under consideration is focused on operations with data presented in the form of logically interconnected sets of relation tables. The most important feature of its structures is the focus on the final result of data processing, and not on the procedure for this processing. SQL itself determines where the data, indexes, and even the most efficient sequences of operations should be used to get the result, and therefore it is not necessary to specify these details in the database query.

Introduction to client-server technology

In connection with the expansion of the information services market, software manufacturers began to produce more and more intelligent, and therefore voluminous software systems. Many organizations and individual users were often unable to place purchased products on their own computers. For the exchange of information and its distribution, computer networks were created, and generalizing programs and data began to be installed on special file servers.

Thanks to the DBMS working with file servers, many users get access to the same databases. The development of various automated management systems for organizations is simplified. However, with this approach, all processing of requests from programs or from the terminals of user computers is carried out on them, therefore, to implement even a simple request, it is necessary to read from the file server or write to it whole files, and this leads to conflicts and network congestion. To eliminate these shortcomings, it was proposed client-server technology, but at the same time a single language of communication with the server was needed - the choice fell on SQL.

Client-server technology means a way of interaction between software components, in which they form a single system. As the name implies, there is a certain client process that requires certain resources, as well as server process that provides these resources. They don't have to be on the same computer. It is usually customary to place the server on one node of the local network, and clients on other nodes.

In the context of a database, the client manages the user interface and application logic by acting as the workstation that runs the database applications. The client accepts a request from the user, checks the syntax, and generates a database query in SQL or another database language that matches the application logic. It then sends the message to the server, waits for a response, and formats the received data to present it to the user. The server accepts and processes requests to the database, and then sends the results back to the client. This processing includes checking the client's credentials, enforcing integrity requirements, and executing the query and updating the data. In addition, concurrency control and recovery are supported.

The client-server architecture has several advantages.

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. Team 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. Team 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.

Structured Query Language or SQL is a declarative programming language for use in quasi-relational databases. Many of the original SQL features were taken for tuple calculus, but recent extensions to SQL include more and more relational algebra.
SQL was originally created by IBM, but many vendors have developed their own dialects. It was adopted as a standard by the American National Standards Institute (ANSI) in 1986 and ISO in 1987. In the SQL programming language standard, ANSI has stated that the official pronunciation of SQL is "es que el". However, many in the database used the slang pronunciation of Sequel, which reflects the original name of the language, Sequel, which was later changed due to a brand and name conflict between IBM. Programming for beginners.
SQL programming language was revised in 1992 and this version is known as SQL-92 in. Then 1999 was revised again to become SQL: 1999 (AKA SQL3). Programming for Dummies. SQL 1999 supports objects that were not previously supported in other versions, but it wasn't until late 2001 that only a few database management systems supported SQL implementations: 1999.
SQL, although defined as ANSI and ISO, has many variations and extensions, most of which have their own characteristics, such as Oracle Corporation's PL / SQL implementation or Sybase and Microsoft's Transact-SQL implementation, which can be confusing. the basics of programming. It is also not uncommon for commercial implementations to omit support for the main features of the standard, such data types as date and time, preferring their own version. As a result, unlike ANSI C or ANSI Fortran, which can usually be ported from platform to platform without major structural changes, SQL queries can rarely be ported between different database systems without major modifications. Most people in the database field believe that this lack of compatibility is intentional in order to provide each developer with their own database management system and to tie the customer to a specific database.
As the name suggests, the SQL programming language is designed for a specific, limited purpose - querying data contained in a relational database. As such, it is a set of programming language instructions for creating data samples, rather than a procedural language like C or BASIC, which are designed to solve a much wider range of problems. Language extensions such as "PL / SQL" are designed to address this limitation by adding procedural elements for SQL while retaining the benefits of SQL. Another approach is to allow SQL queries to embed procedural programming language commands and interact with the database. For example, Oracle and others support the Java language in a database, while PostgreSQL allows you to write functions in Perl, Tcl, or C.
One joke about SQL: "SQL is neither structured nor a language." The joke is that SQL is not a Turing language. ...

Select * from T

C1	C2
1	a
2	b

C1	C2
1	a
2	b

Select C1 from T

C1
1
2

C1	C2
1	a
2	b

Select * from T where C1 = 1

C1	C2
1	a

Given a table T, the Select * from T query will display all the elements of all rows in the table.
From the same table, the Select C1 from T query will display the items from column C1 of all rows in the table.
From the same table, the Select * from T where C1 = 1 query will display all elements of all rows where the value of column C1 is "1".

SQL keywords

SQL words are divided into a number of groups.

The first is Data Manipulation Language or DML(data management language). DML is a subset of the language used to query databases, add, update, and delete data.

SELECT is one of the most commonly used DML commands and allows the user to specify a query as a set description of the desired result. The query does not specify how the results should be located - translating the query into a form that can be executed in the database is the job of the database system, more specifically the query optimizer.
INSERT is used to add rows (formal set) to an existing table.
UPDATE is used to change data values in an existing row in a table.
DELETE definition of existing rows to be deleted from the table.

Three other keywords can be said to fall into the DML group:

BEGIN WORK (or START TRANSACTION, depending on the SQL dialect) can be used to mark the beginning of a database transaction that either runs completely or never runs at all.
COMMIT establishes that all data changes after the operations are saved.
ROLLBACK specifies that all data changes since the last commit or rollback should be destroyed, up to the point that was committed to the database as a "rollback".

COMMIT and ROLLBACK are used in areas such as transaction control and locking. Both instructions terminate all current transactions (sets of operations on the database) and release all locks on changing data in tables. The presence or absence of BEGIN WORK or a similar statement depends on the specific SQL implementation.

The second group of keywords belongs to the group Data Definition Language or DDL (Data Definition Language). DDL allows the user to define new tables and related items. Most commercial SQL databases have their own DDL extensions that allow control over non-standard, but usually vital elements of a particular system.
The main points of DDL are create and delete commands.

CREATE defines the objects (such as tables) to be created in the database.
DROP determines which existing objects in the database will be dropped, usually permanently.
Some database systems also support the ALTER command, which allows the user to modify an existing object in different ways — for example, adding columns to an existing table.

The third group of SQL keywords is Data Control Language or DCL (Data Control Language). DCL is responsible for data access rights and allows the user to control who has access to view or manipulate the data in the database. There are two main keywords here:

GRANT - Allows the user to perform operations
REVOKE - removes or limits the user's ability to perform operations.

Database systems using SQL

InterBase
MySQL
Oracle
PostgreSQL
SQL Server

How to become a professional in website development and start making money? Inexpensive video courses with an introductory introduction.