The operator must be included in the construction of the select operator. Structured query language - SQL: history, standards, basic language operators. View creation operator
And above the table data.
The SQL language is called built-in, because It contains the functions of a full-fledged development language, and is focused on data access, as a result of which it is part of application development tools. SQL language standards support languages Pascal programming, Fortran, COBOL, C, etc.
Exists 2 Methods to Use Embedded SQL:
- static language use ( static SQL) – the program text contains calls to SQL functions, which are included in the executable module after compilation.
- dynamic language use ( dynamic SQL) – dynamic construction of SQL function calls and their interpretation. For example, you can access data from a remote database during program execution.
The SQL language (like other languages for working with databases) is designed for preparing and executing queries. As a result of executing a data query from one or more tables, a set of records is obtained, which is called presentation.
Definition 1
Performance is a table that is formed as a result of executing a query.
Basic SQL Query Language Operators
SQL language operators are conventionally divided into 2 sublanguages:
- Data Definition Language DDL;
- Data manipulation language DML.
In the table, they are marked with * specific operators language.
Let's look at the most important SQL statements.
Table creation statement:
The name of the table that is being created and the name of at least one column (field) are required operands. For the column name, you must specify the type of data that will be stored in it.
For individual fields, you can specify additional rules for controlling the values that are entered into them. For example, NOT NULL indicates that the field cannot be empty and must be filled with a value.
Example 1
To create a table books book catalog, which contains the fields:
type– type of book,
name- book title,
price– price of the book
the statement might look like this:
Operator for changing table structure:
When changing the table structure, you can add ( ADD), change ( MODIFY) or delete ( DROP) one or more table columns. Recording Rules of this operator the same as for the operator CREATE TABLE. To delete a column you do not need to specify it.
Example 2
To add to a table books fields number, in which the number of books will be stored, you can write the operator:
Table drop operator:
Example 3
For example, to drop an existing table named books it is enough to use the operator:
Index creation operator:
The operator creates an index on one or more columns of a given table, which can speed up query and lookup operations. Multiple indexes can be created for one table.
Optional option UNIQUE is responsible for ensuring that the values in all columns that are specified in the statement are unique.
A.S.C. sets automatic sorting of column values in ascending order (default), and DESC– in descending order.
Index drop operator:
View creation operator:
When you create a view, you don't have to specify column names. Then the column names from the query, which is described by the corresponding operator, will be used SELECT.
View removal operator:
Record selection operator:
Operator SELECT performs sampling and calculations on data from one or more tables. The result of executing the operator is a response table that contains ( ALL) or does not contain ( DISTINCT) lines that are repeated.
Operand FROM contains a list of tables from which records are taken for data selection.
Record modification operator:
New field values in records may not contain values ( NULL) or calculated according to an arithmetic expression.
Operator for inserting new records:
In the first operator record INSERT New records are entered with specified values in the columns.
In the second operator record INSERT new rows are entered, selected from another table through a clause SELECT.
Record deletion operator:
As a result of executing the operator, rows that satisfy the condition specified by the optional operand are deleted from the specified table WHERE. If the operand WHERE is not specified, all table entries are deleted.
SQL (Structured Query Language) - universal computer language, used to create, modify, and manage data in relational databases. The SQL language is based on relational algebra and is a collection of operators.
There are 4 groups of operators. Let's consider a group of data manipulation operators (Data Manipulation Language, DML, SQL DML)
Data Selection
Data selection is the most common operation performed with using SQL. The SELECT statement is one of the most important operators this language used to select data. The syntax of this operator is next view:
SELECT column FROM table
SELECT statements must contain the words SELECT and FROM; other keywords are optional.
The SELECT keyword is followed by information about which fields to include in the resulting data set. An asterisk (*) denotes all table fields, for example:
To select one column, use the following syntax:
SELECT Company
An example of selecting multiple columns looks like this:
SELECT Company, Phone, Mail
To specify the names of the tables from which records are selected, use the FROM keyword, for example:
SELECT * FROM Customers
This query will return all fields from the Customers table.
You can use a WHERE clause (optional) to filter the results returned by a SELECT statement
SELECT * FROM Products WHERE Category = 4
You can use various expressions in the WHERE clause,
WHERE expression1 [(AND | OR) expression2 ...]
For example:
SELECT * FROM Products WHERE Category = 2 AND Postavshik > 10
SELECT Name, Price FROM Products WHERE Category= 3 OR Price< 50
You can use the following operators:
< Меньше
<= Меньше или равно
<>Not equal
> More
>= Greater than or equal to
ORDER clause BY (optional) is used to sort the resulting data set into one or more columns. The keywords ASC (ascending) or DESC (descending) are used to determine the sort order. By default, data is sorted in ascending order.
Data modification
In addition to retrieving data, SQL can be used to update and delete data, copy records to other tables, and perform other operations. Below we'll look at the UPDATE, DELETE, and INSERT statements used to solve some of these problems.
UPDATE statement
To change values in one or more columns of a table, use the UPDATE statement. The syntax of this operator is:
UPDATE table SET column1 = expression1 WHERE criteria
The expression in the SET clause can be a constant or the result of a calculation. For example, to increase the prices of all products that cost less than $10, you could run the following query:
UPDATE Products SET Price = Price * 1.1 WHERE Price< 10
DELETE statement
To delete rows from tables, you should use the DELETE operator, the syntax of which is:
DELETE FROM table WHERE criteria
Remove all products that cost less than 100.
As already mentioned, in the 1970s Edgar Codd proposed using the relational model. In addition to the model itself, he also proposed a language for working with data within this model, called DSL/Alpha. Subsequently, on the basis of DSL/Alpha, the SQUARE language appeared, and on its basis, in turn, the SEQUEL language, which (due to some confusion in trademarks) was later renamed SQL.
Here constraint_* can have the following values (or combinations thereof):
- NOT NULL – cannot be “empty”
- UNIQUE – the value is unique
- PRIMARY KEY – a combination of the first two
- FOREIGN KEY – specifying a foreign key
- CHECK – the value must satisfy the condition
- DEFAULT – default value
There are some discrepancies in different implementations.
If the primary key consists of several columns, it is necessary to place it after declaring the columns in the form
CONSTRAINT pk_table PRIMARY KEY (col1, col2, ...)
where pk_table is the unique name of the constraint.
In many implementations, the CONSTRAINT pk_table portion can be omitted. In this case, the name will be generated automatically.
In any case, the foreign key is declared after the columns are declared.
Syntax:
CONSTRAINT fk_table FOREIGN KEY (col1, ...) REFERENCES tbl_name (ref_col1, ...) ON UPDATE upd_action ON DELETE del_action;
where fk_table is the unique name of the constraint, col1, ... are the names of the columns of this table included in the foreign key, tbl_name is the table for which the specified foreign key is the primary key, ref_col1, ... are the names of the corresponding columns in tbl_name. upd_action and del_action determine how the database reacts to changes and deletions of records from tbl_name , and can take one of the following values:
- SET NULL – referencing columns col1, ... are set to NULL
- RESTRICT - if there are records referencing the value being updated/deleted, the update/deletion fails
- CASCADE - updates/deletes all referencing records
- NO ACTION - do nothing
In many implementations, the CONSTRAINT fk_table portion can be omitted. In this case, the name will be generated automatically.
Question No. 1. SQL and its history. 1
Question No. 2. Description of the main SQL statements.. 1
Arithmetic functions. 4
String processing functions. 5
Special functions. 6
Functions for date processing... 7
Using aggregate functions in queries. 7
Question No. 1. SQL and its history
The only means of communication between database administrators, designers, developers, and users with a relational database is the structured query language SQL (Structured Query Language). SQL is a full-featured language for manipulating data in relational databases. Currently, it is a generally accepted, standard interface for relational databases such as Oracle, Informix, Sybase, DB/2, MS SQL Server and a number of others (ANSI and ISO standards). SQL is a non-procedural language that is designed to process sets consisting of rows and columns of relational database tables. Although there are extensions of it that allow procedural processing. Database designers use SQL to create all the physical objects of a relational database.
Theoretical basis SQL was founded in the famous article by Codd, which laid the foundation for the development of the theory of relational databases. The first practical implementation was carried out in the research laboratories of IBM Chamberlin D.D. and Royce R.F. The industrial use of SQL was first implemented in the Ingres DBMS. One of the first industrial relational DBMSs is Oracle. Essentially, a relational DBMS is software, which controls the operation of the relational database.
First international standard The SQL language was adopted in 1989 (SQL-89). At the end of 1992, a new international standard, SQL-92, was adopted. Currently, most relational DBMS manufacturers use it as a base. However, work on standardizing the SQL language is far from complete and a draft SQL-99 standard has already been developed, which introduces the concept of an object into the language and allows it to be referenced in SQL statements: In the original version of SQL there were no data flow control commands, they appeared in the recently adopted ISO/IEC 9075-5: 1996 Supplementary Part SQL.
Each specific DBMS has its own SQL implementation, which generally supports a certain standard, but has its own characteristics. These implementations are called dialects. Thus, the 1SO/IEC 9075-5 standard provides objects called persistent stored modules or PSM modules (Persistent Stored Modules). In the Oracle DBMS, the PL/SQL extension is an analogue of the above standard extension."
Question No. 2. Description of basic SQL statements
SQL consists of a set of commands for manipulating data in a relational database, which allow you to create relational database objects, modify data in tables (insert, delete, correct), change database relationship schemas, perform calculations on data, make selections from the database, maintain security and data integrity.
The entire set of SQL commands can be divided into the following groups:
· data definition commands (DDL - Data Defininion Language);
· data manipulation commands (DML - Data Manipulation Language);
· data sampling commands (DQL - Data Query Language);
· transaction management commands;
· data management commands.
When executed, each SQL command goes through four processing phases:
· the parsing phase, which includes checking the command syntax, checking the names of tables and columns in the database, and preparing the input data for the optimizer;
· optimization phase, which includes substituting valid database table and column names into the view, identification possible options execution of the command, determining the cost of executing each option, choosing the best option based on internal statistics;
· the executable code generation phase, which includes the construction of executable command code;
· the command execution phase, which involves executing the command code.
Currently optimizer is integral part any industrial implementation of SQL. The optimizer works by collecting statistics about the commands it executes and performing equivalent algebraic transformations on database relationships. These statistics are stored in the system database directory. System catalog is a data dictionary for each database and contains information about tables, views, indexes, columns, users and their access privileges. Each database has its own system catalog, which represents a collection of predefined database tables.
Table 8.1 contains a list of SQL commands according to the accepted standard, with the exception of some commands that are practically not used in dialects.
Table 8.1. Typical list of SQL commands
| Team | Description |
| Commands for defining object data | |
| ALTER TABLE | Changes the table description (relationship schema) |
| CREATE EVENT | Creates a timer event in the database |
| CREATE INDEX | Creates an index on the table |
| CREATE SEQUENCE | Creates a sequence |
| CREATE TABLE | Defines a table |
| CREATE TABLESPACE | Will create a tablespace |
| CREATE TRIGGER | Creates a trigger in the database |
| CREATE VIEW | Defines a view on tables |
| DROP INDEX | Physically removes an index from the database |
| DROP SEQUENCE | Deletes a sequence |
| DROP TABLE | Physically removes a table from the database |
| DROP TABLESPACE | Deletes a tablespace |
| DROP VIEW | Deletes a view |
| Data Manipulation Commands | |
| DELETE | Removes one or more rows from a database table |
| INSERT | Inserts one or more rows into a database table |
| UPDATE | Updates column values in a database table |
| Data Retrieval Commands | |
| SELECT | Executes a query to retrieve data from tables and views |
| UNION | Combines the results of two or more SELECT commands into one selection |
| Transaction Control Commands | |
| COMMIT | Completes a transaction and physically updates the current state of the database |
| ROLLBACK | Ends a transaction and returns the current state of the database at the time of the last completed transaction and checkpoint |
| SAVEPOINT | Assigns a checkpoint within a transaction |
| Data Management Commands | |
| ALTER DATABASE | Modifies storage groups or transaction logs |
| ALTER DBAREA | Changes the size of database storage areas |
| ALTER PASSWORD | Changes the password to access the database |
| ALTER STOGROUP | Changes the composition of storage areas in a storage group |
| CHECK DATABASE | Checks database integrity |
| CHECK INDEX | Checks index integrity |
| CHECK TABLE | Checks table and index integrity |
| CREATE DATABASE | Physically creates the database |
| CREATE DBAREA | Creates a database storage area |
| CREATE STOGROUP | Creates a storage group |
| CREATE SYSNONYM | Creates a synonym for a table or view |
| DEINSTALL DATABASE | Makes the database unavailable to users computer network |
| DROP DATABASE | Physically deletes databases |
| DROP DBAREA | Physically deletes a database storage area |
| DROP STOGROUP | Deletes a storage group |
| GRANT | Defines user privileges and access control to the database |
| INSTALL DATABASE | Makes the database available to network users |
| LOCK DATABASE | Locks the currently active database |
| REVOKE | Revokes user privileges and database access restrictions |
| SET DEFAULT STOGROUP | Defines the default storage group |
| UNLOCK DATABASE | Releases the currently active database |
| UPDATE STATISTIC | Updates statistics for a database |
| Other commands | |
| COMMENT ON | Places comments on the description of database objects in the system catalog |
| CREATE SYNONYM | Defines alternative names for database tables and views in the system catalog |
| DROP SYNONYM | Removes alternative names for database tables and views from the system catalog |
| LABEL | Changes labels system descriptions |
| ROWCOUNT | Calculates the number of rows in a database table |
The set of SQL commands listed in the table is not complete. This list is provided to give you an idea of what SQL can do in general. For getting full list commands, please refer to the appropriate manual for your specific DBMS. It should be remembered that SQL is the only means of communication for all categories of users with relational databases.
Arithmetic functions
SQL supports the full set arithmetic operations and mathematical functions for constructing arithmetic expressions over database columns (+, -, *, /, ABS, LN, SQRT, etc.).
A list of the main built-in mathematical functions is given below in Table 8.2.
| Mathematical function | Description |
| ABS(X) | Returns the absolute value of a number X |
| ACOS(X) | Returns the arc cosine of a number X |
| ASIN(X) | Returns the arcsine of a number X |
| ATAN(X) | Returns the arctangent of X |
| COS(X) | Returns the cosine of a number X |
| EXP(X) | Returns the exponent of a number X |
| SIGN(X) | Returns -] if X< 0, 0, если X = 0, + 1, если X > 0 |
| LN(X) | Returns the natural logarithm of a number X |
| MOD(X,Y) | Returns the remainder when X is divided by Y |
| CEIL(X) | Returns the smallest integer greater than or equal to X |
| ROUND(X,n) | Rounds the number X to a number with n decimal places |
| SIN(X) | Returns the sine of X |
| SQRT(X) | Returns the square root of a number X |
| TAN(X) | Returns the tangent of a number X |
| FLOOR(X) | Returns the largest integer less than or equal to X |
| LOG(a,X) | Returns the logarithm of a number X to base A |
| SINH(X) | Returns the hyperbolic sine of X |
| COSH(X) | Returns the hyperbolic cosine of a number X |
| TANH(X) | Returns the hyperbolic tangent of X |
| TRANC(X.n) | Truncates the number X to a number with n decimal places |
| POWER(A,X) | Returns the value of A raised to the power of X |
The set of built-in functions may vary depending on the version of the DBMS from one manufacturer and also in DBMSs from different manufacturers . For example, in the SQLBase DBMS, Century Inc. There is a function @ATAN2(X,Y), which returns the arctangent of Y/X, but there is no function SIGN(X).
Arithmetic expressions are needed to obtain data that is not directly stored in the columns of database tables, but whose values are required by the user. Let's say you need a list of employees showing the pay each employee received, including bonuses and penalties.
SELECT ENAME, SAL, COMM. FINE, SAL + COMM - FINE
The arithmetic expression SAL + COMM - FINE is output as a new column in the result table, which is calculated as a result of the query. Such columns are also called derived (calculated) attributes or fields.
String processing functions
SQL provides you with a wide range of functions for manipulating string data (string concatenation, CHR, LENGTH, INSTR and others). A list of the main functions for processing string data is given in Table 8.3.
Table 8.3. SQL Functions for String Processing
| Function | Description |
| CHR(N) | Returns the ASCII code character for the decimal code N |
| ASCII(S) | Returns the decimal ASCII code of the first character of a string |
| INSTR(S2,SI,pos|,N|) | Returns the position of the SI string in the S2 string greater than or equal to pos. N - number of occurrences |
| LENGTH(S) | Returns the length of a string |
| LOWER(S) | Replaces all characters in a string with uppercase characters |
| INITCAP(S) | Sets the first character of each word in a string to uppercase and the remaining characters of each word to uppercase |
| SUBSTR(S,pos,[,len|) | Selects a substring of length ten in the string S, starting at position pos |
| UPPER(S) | Converts capital letters in a string to uppercase letters |
| LPAD(S,N |,A|) | Returns the string S left padded with A's to a maximum of N characters. The default padding character is space. |
| RPAD(S,N |,A]) | Returns the string S right padded with A's to a maximum of N characters. The default padding character is space |
| LTRIM(S,|,Sll) | Returns the left-truncated string S. Characters are removed as long as the character to be removed is in the string - pattern SI (default is space) |
| RTRIM(S,|,SI |) | Returns the right-truncated string S. Characters are removed as long as the character to be removed is in the string - pattern S1 (default is space) |
| TRANSLATES,(SI,S2) | Returns the string S with all occurrences of the string SI replaced by the string S2. If S.I.<>S2, then characters that do not match are excluded from the resulting string |
| REPLACED(SI,|,S2|) | Returns the string S for which all occurrences of the substring SI are replaced by the substring S2. If S2 is not specified, then all occurrences of the substring SI are removed from the resulting string S |
| NVL(X,Y) | If X is NULL, then returns Y either a string, a number, or a date, depending on the original type of Y |
The names of the same functions may differ in different DBMSs. For example, the Oracle DBMS function SUBSTR(S, pos, |, len|) in the SQLBase DBMS is called @SUBSTRING(S, pos, Ien). The SQLBase DBMS has functions that are not available in the Oracle DBMS (see the table below for a list of such functions).
Table 8.4. SQLBase DBMS string functions other than string functions Oracle DBMS
| Function | Description |
| @EXACT(SI,S2) | Returns the result of comparing two strings |
| @LEFT(S,lcn) | Returns the left substring of length len |
| @LENGTH(S) | Returns the length of a string |
| @MID(S, pos, len) | Returns a substring of the specified length, starting at position pos |
| @REPEAT(S,n) | Repeats the string S n times |
| @REPLACE(SI,pos,len,S2) | Replace positions pos len characters in string S2 with characters in string SI |
| @RIGHT(S,len) | Returns the right substring of S of length len |
| @SCAN(S,pat) | Returns the position of the substring pat in the string S |
| @STRING(X, scale) | Returns a symbolic representation of a number with the specified scale |
| @TRIM(S) | Removes spaces from the right and left of a string |
| @VALUE(S) | Converts the symbolic representation of a number to a numeric value |
You can use the INITCAP function to ensure that, when retrieving a list of employee names, the last names always begin with a capital letter and all other names are capitalized.
SELECT INITCAP(ENAME)
Special Features
SQL provides a set of special functions for transforming column values. A list of such functions is given in Table 8.5.
Table 8.5. Special Features
In the EMPLOYEE table, you can enter a gender attribute for each employee - add a column SEX of type CHAR(l) (0 - male, 1 - female). Let's say that you need a list of employees, which requires them to be divided by gender and indicated in a numerical format; then you can issue the following command:
SELECT ENAME, LNAME, AGE, " Floor:",TO_NUMBER(SEX)
As an example of using the DECODE function, we present a query that calculates a list of employees indicating their manager. If the manager is unknown, then “does not have” is displayed by default.
SELECT ENAME, DEC0DE(DEPN0, 10, " Dryagin", 20,"Zhilyaeva". 30,"
Korotkov", "does not have")
Let's assume that the head of the organization has an undefined value for the DEPNO column and, therefore, the default provided in DECODE will work for him .