In the query language SQL occupies a significant place SELECT statement, since with the help of it we carry out a selection in the database. In this article, we'll cover the basics of this very important SQL statement.
There are a lot of various articles, books, tutorials on SQL on the Internet, but all of them are difficult for a person who has never heard about SQL, and maybe even about programming, and therefore all these sources are difficult for him to study. Here I'm trying to explain the basics of a single operator without going too deep ( this is what makes it difficult to study that they immediately move from the basics to complex queries), so you first need to learn the basics, and then move on to more complex ones, which makes it easier to learn the SQL language. This article is great for beginners who want to learn how to write simple SQL queries to fetch data.
SQL is a query language that is used to manipulate (manage) data in relational databases. It has wide popularity and therefore any self-respecting IT-k should know the basics of this language, since almost every company has databases.
SELECT– operator of the SQL language, belongs to the group of data manipulation operators ( Data Manipulation Language, DML) and serves to fetch data from the database.
Note! In order to learn the SQL language and databases, there are special free editions of large DBMS, for example, in SQL Server Microsoft has an Express edition. How to install given DBMS you can read in the material - Description of the installation of Microsoft SQL Server 2016 Express.
Here is the simplest example of using the SELECT statement.
SELECT * FROM Table
But, in practice, often we do not need all the data from the table, and sometimes only some columns, for this we simply specify the name of the desired column instead of * ( or speakers), For example:
SELECT Price FROM Table
where, Price is the name of the column.
Note! As an example, we use a simple table listing computer models, their price and name.
If you need to specify multiple columns, then simply list them separated by commas after the SELECT statement, for example
SELECT price, name, model FROM Table
where, price, name, model are columns from the Table table.
In the sampling process, quite often we need to filter the data according to a certain condition, i.e. not all data, but only those that match the condition, in the SELECT construct, you can use the operator for this WHERE.
SELECT price FROM Table WHERE price > 100
where, WHERE is the condition, i.e. we display only those rows that match our condition ( price over 100).
You can also specify in the condition keyword BETWEEN, i.e. the value falls or does not fall within a certain range, for example
SELECT price FROM table WHERE price BETWEEN 400 AND 600
where, we specify that the price should be between 400 and 600.
To check whether the value of the expression under test belongs to a certain set of values, you can use the predicate IN.
SELECT price FROM table WHERE price IN (400, 600)
IN this case we only want the price with cost 400 and 600.
If we need to get only the unique rows of the source, we can specify the keyword DISTINCT, For example
SELECT DISTINCT price FROM Table WHERE price > 100
Note! The SQL language is case insensitive, queries can be written in one line or split into several. For example, the following two queries are exactly the same.
SELECT price FROM Table
select price from table
Very often it is necessary to sort the result of a query by a certain field ( column). To do this, after the request, specify the construction ORDER BY and those fields ( several can be separated by commas) to sort by.
SELECT price FROM Table ORDER BY price DESC
This is an example of sorting in descending order, but you can also sort in ascending order, instead of DESC we write ASC, but usually we don’t write this way, since ascending sorting is the default.
There are very useful so-called aggregation functions in SQL, namely:
For example, we need to get the average price of a computer, the maximum and minimum, for this we can write the following query
SELECT AVG(price), MAX(price), MIN(price) FROM table
You can group values by columns, for example, we need to find out the average price for each computer model, in this case the query will be like this
SELECT model, AVG (price) AS AVGPrice FROM table GROUP BY model
If you notice, I used the assignment here " alias» for more convenient perception of the results of this query, i.e. after the column, write AS and the name you want to be displayed in the results.
As well as when using conditions in individual columns, you can also specify a condition for the whole group, using the function HAVING. For example, we need to determine the maximum price of a computer, grouped by the models of these computers, but whose maximum price is less than 500.
SELECT model, MAX(price) FROM table GROUP BY model HAVING MAX(price)< 500
In this case, our query will give us computers grouped by models, the maximum price of which is less than 500.
Note! You can read more about grouping in SQL in this article.
In SQL, there is such a value as NULL. In fact, NULL is the absence of a value ( those. empty). In order to display all lines that contain such a value ( for example, we have not yet assigned a price for some computer) we can use the following condition.
SELECT * FROM table WHERE price IS NULL
Which will mean searching for all rows that do not contain a value for the price field.
The note! If you are interested in the T-SQL language, then I recommend reading my book “The Way of the T-SQL Programmer”, in which I talk in detail, with a lot of examples, about the basics of the Transact-SQL language especially for beginners.
That's all for today, we have reviewed with you a small part of the SQL language, namely the SELECT statement. Good luck!
And over the table data.
The SQL language is called built-in, because it contains functions full-fledged language development, but focuses on data access, as a result of which it is part of the application development tools. SQL language standards support languages Pascal programming, Fortran, COBOL, C, etc.
Exists 2 methods to use embedded SQL:
The SQL language (like other database languages) is designed to prepare and execute queries. As a result of executing a data query from one or more tables, a set of records is obtained, which is called performance.
Definition 1
Performance is a table that is formed as a result of query execution.
SQL statements are conditionally divided into 2 sublanguages:
In the table, the symbol * marked specific operators language.
Let's take a look at the most important SQL statements.
Table creation statement:
The name of the table 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 in them. For example, NOT NULL indicates that the field cannot be empty and a value must be entered into it.
Example 1
To create a table books book directory, which contains the fields:
type- type of book
name- book title,
price– book price
operator might look like this:
Table structure change operator:
When changing the table structure, you can add ( ADD), change ( MODIFY) or delete ( DROP) one or more table columns. Recording rules given operator the same as for the operator CREATE TABLE. You do not need to specify to delete a column.
Example 2
To add to table books fields number, which will store the number of books, you can write the operator:
Table drop statement:
Example 3
For example, to drop an existing table named books just use the operator:
Index creation statement:
The statement creates an index on one or more columns of a given table, which speeds up query and lookup operations. Multiple indexes can be created on the same table.
Optional option UNIQUE is responsible for ensuring the uniqueness of values in all columns that are specified in the statement.
ASC sets the automatic sorting of values in columns in ascending order (default), and DESC- in descending order.
Index drop operator:
View creation operator:
When creating a view, you can leave out the column names. Then the column names from the query will be used, which is described by the corresponding operator SELECT.
View delete operator:
Record selection operator:
Operator SELECT selects and performs calculations on data from one or more tables. The result of the statement execution 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 record of the operator INSERT new records are entered with given values in the columns.
In the second record of the operator INSERT new rows are entered, selected from another table through a sentence SELECT.
Delete record operator:
As a result of the operator execution, the 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 records in the table are deleted.
The SQL language standard was adopted in 1992 and is still in use today. It was he who became the standard for many. Of course, some manufacturers use their own interpretations of the standard. But in any system, there are still main components - SQL statements.
With the help of SQL statements, values, tables are managed and received for further analysis and display. They are a set of keywords by which the system understands what to do with the data.
Several categories of SQL statements are defined:
INSERT. Inserts rows into an existing table. It can be used both for one value and several, determined by some condition. For example:
table name (column name 1, column name 2)
VALUES (value 1, value 2).
To use the INSERT statement on multiple values, the syntax is:
table name 1 (column name 1, column name 2)
SELECT column name 1, column name 2
FROM table name 2
WHERE table name 2.column name 1>2
This query will select all data from table 2 that is greater than 2 in column 1 and insert it into the first one.
UPDATE. As the name suggests, this operator SQL query updates the data in an existing table by a certain attribute.
UPDATE table name 1
SET column name 2 = "Basil"
WHERE table name 1.column name 1 = 1
This construction will fill with the value Vasily all the rows in which it encounters the number 1 in the first column.
Data from the table. You can specify any condition or remove all lines.
DELETE FROM table name
WHERE table name.column name 1 = 1
The above query will remove from the database all data with a value of one in the first column. And here is how you can clear the entire table:
The main purpose of SELECT is to select data according to certain conditions. The result of his work is always a new table with selected data. The MS operator can be used in a lot of different queries. Therefore, along with it, you can consider other related keywords.
To select all data from a particular table, use the "*" sign.
FROM table name 1
The result of the work given request will be an exact copy of table 1.
And here there is a selection according to the WHERE condition, which gets from table 1 all values greater than 2 in column 1.
FROM table name 1
WHERE table name 1.column name 1 > 2
You can also specify in the selection that only certain columns are needed.
SELECT table name 1.column name 1
FROM table name 1
The result of this query will be all rows with values from column 1. Using MS SQL statements, you can create your own table by replacing, calculating and substituting certain values on the go.
table name 1.column name 1
table name 1.column name 2
table name 1.column name 3
table name 1.column name 2 * table name 1.column name 3 AS SUMMA
FROM table name 1
This seemingly complex query fetches all values from table 1, then creates new EQ and SUMMA columns. It enters the “+” sign in the first, and the product of the data from columns 2 and 3 in the second. The result can be presented in the form of a table, to understand how it works:
When using the SELECT statement, you can immediately sort the data according to some attribute. For this, the word ORDER BY is used.
table name 1.column name 1
table name 1.column name 2
table name 1.column name 3
FROM table name 1
ORDER BY column name 2
The resulting table will look like this:
That is, all rows were set in such an order that the values in column 2 were in ascending order.
Data can also be retrieved from multiple tables. For clarity, you first need to imagine that there are two of them in the database, something like this:
Table "Employees"
Table "Salary"
Now you need to somehow link these two tables to get common values. Using basic SQL statements, you can do this like this:
Staff.Number
Employees.Name
Salary. Rate
Salary. Accrued
FROM Employees, Salary
WHERE Employees.Number = Salary.Number
Here is a selection of two different tables values grouped by number. The result will be the following dataset:
One of the main operators may perform some computation on the fetch. To do this, he uses certain functions and formulas.
For example, to get the number of records from the "Employees" table, you need to use the query:
SELECT COUNT (*) AS N
FROM Employees
The result is a table with one value and one column.
You can use this query and see what happens:
SUM(Salary. Accrued) AS SUMMA
MAX(Salary.Accrued) AS MAX
MIN(Salary. Accrued) AS MIN
AVG(Salary.Accrued) AS SRED
FROM Salary
The final table will look like this:
In this way, you can select the desired values from the database by performing the calculation of various functions on the fly.
Union, Intersect, and Differences
Combine multiple queries in SQL
SELECT Employees.Name
FROM Employees
WHERE Employees.Number = 1
SELECT Employees.Name
FROM Employees, Salary
WHERE Salary.Number = 1
It should be borne in mind that with such a join, the tables must be compatible. That is to have the same number of columns.
First of all, SELECT defines the area from which it will take data. The FROM keyword is used for this. If it is not specified what exactly to choose.
Then there may be an SQL WHERE clause. With its help, SELECT runs through all the rows of the table and checks the data for compliance with the condition.
If there is a GROUP BY in the query, then the values are grouped according to the specified parameters.
There are several types of them. In SQL, comparison operators can check Various types values.
"=". Denotes, as you might guess, the equality of two expressions. For example, it has already been used in the examples above - WHERE Salary.Number = 1.
">". More sign. If the value of the left side of the expression is greater than, then returns boolean TRUE and the condition is considered to be met.
«<». Знак меньше. Обратный предыдущему оператор.
Signs "<=» и «>=". It differs from simple more and less operators in that if the operands are equal, the condition will also be true.
This keyword can be translated as "similar". The LIKE operator in SQL is used in approximately the same way - it executes a query according to a template. That is, it allows you to expand the selection of data from the database using regular expressions.
For example, the following task was set: from the already known base "Employees" to get all the people whose name ends with "I". Then the query can be written like this:
FROM Employees
WHERE Name LIKE `%i`
The percent sign in this case means a mask, that is, any character and their number. And by the letter "i" SQL will determine that the last character should be just that.
This SQL Server statement is an implementation of multiple selection. It resembles the switch construct in many programming languages. The CASE statement in SQL performs an action on multiple conditions.
For example, you need to select the maximum and minimum values from the Salary table.
Then the query can be written like this:
FROM Salary
WHERE CASE WHEN SELECT MAX(Accrued) THEN Maximum
WHEN SELECT MIN(Accrued) THEN Minimum
In this context, the system looks for the maximum and minimum value in the Accrued column. Then, using END, a “total” field is created, in which “Maximum” or “Minimum” will be entered, depending on the result of the condition.
By the way, SQL also has a more compact form of CASE - COALESCE.
This view allows you to make a variety of changes to tables - creating, deleting, modifying and working with indexes.
The first one worth considering is CREATE TABLE. It does nothing more than create a table. If you just type the CREATE TABLE query, nothing will happen, since you still need to specify a few parameters.
For example, to create the already familiar Employees table, you need to use the commands:
CREATE TABLE Employees
(Number number(10) NOT NULL
Name varchar(50) NOT NULL
Last name varchar(50) NOT NULL)
In this query, the names of the fields and their types are immediately determined in brackets, as well as whether it can be equal to NULL.
Performs one simple task, dropping the specified table. Has an additional IF EXISTS parameter. It absorbs an error on deletion if the table being looked up does not exist. Usage example:
DROP TABLE Employees IF EXISTS.
SQL has an index system that allows you to speed up data access. In general, it is a link that points to a particular column. You can create an index with a simple query:
CREATE INDEX index_name
ON table_name(column_name)
This operator is used in T-SQL, Oracle, PL SQL and many other interpretation technologies.
A very functional operator with numerous options. In general, it changes the structure, definition and placement of tables. The operator is used in Oracle SQL, Postgres and many others.
ADD. Adds a column to a table. Its syntax is: ALTER TABLE table_name ADD column_name stored_data_type. Can have an IF NOT EXISTS option to suppress the error if the column being created already exists;
DROP. Deletes a column. It also has an IF EXISTS key, without which an error will be generated saying that the required column is missing;
CHANGE. Serves to rename the field name to the specified one. Usage example: ALTER TABLE table_name CHANGE old_name new_name;
MODIFY. This command will help you change the type and additional attributes of a specific column. And it is used like this: ALTER TABLE table_name MODIFY column_name data_type attributes;
In SQL, there is such a thing as a view. In short, this is a kind of virtual table with data. It is formed as a result of a selection using the SQL SELECT statement. Views can restrict access to the database, hide them, replace real column names.
The creation process takes place with a simple request:
CREATE VIEW view name AS SELECT FROM * table name
Sampling can occur as the entire database as a whole, and according to some condition.
SQL queries very often use various built-in functions that allow you to interact with data and transform it on the fly. It is worth considering them, as they are an integral part of a structured language.
COUNT. Counts records or rows in a particular table. As a parameter, you can specify the name of the column, then the data will be taken from it. SELECT COUNT * FROM Employees;
A.V.G. applies only to columns with numeric data. Its result is the determination of the arithmetic mean of all values;
MIN and MAX. These functions have already been used in this article. They determine the maximum and minimum values from the specified column;
SUM. It's simple - the function calculates the sum of the column values. It is used exclusively for the numeric data type. By adding the DISTINCT parameter to the query, only unique values will be summed;
ROUND. The function of rounding decimal fractional numbers. The syntax uses the column name and the number of decimal places;
len. A simple function that calculates the length of a column's values. The result will be a new table with an indication of the number of characters;
NOW. This keyword is used to calculate the current date and time.
Many of the SQL statement examples have keywords that perform small tasks but still greatly simplify selections or database operations.
AS. It is used when you need to visualize the result by assigning the specified name to the resulting table.
BETWEEN. A very handy selection tool. It specifies the range of values from which to get the data. The input accepts a parameter from and to what number the range is used;.
NOT. An operator gives the opposite of an expression.
TRUNCATE. Removes data from the specified region of the database. It differs from similar operators in that it is impossible to recover data after its use. It is worth considering that the implementation of this keyword in different interpretations of SQL may differ. Therefore, before trying to use TRUNCATE, it is better to read the help information.
LIMIT. Sets the number of lines to display. The peculiarity of the operator is that it is always located at the end. It takes one required parameter and one optional. The first one specifies how many rows with the selected data to show. And if the second one is used, then the operator works as for a range of values.
UNION. A very handy operator for combining multiple queries. He has already met among the examples of this in this article. You can display the desired rows from several tables by joining them with UNION for more convenient use. The syntax is: SELECT column_name FROM table_name UNION SELECT other_column_name FROM other_table_name. The result is a pivot table with combined queries.
PRIMARY KEY. Translated as "primary key". Actually, it is this terminology that is used in reference materials. It stands for the unique row identifier. It is used, as a rule, when creating a table to specify the field that will contain it.
DEFAULT. Just like the previous operator, it is used in the process of executing the creating query. It defines the default value that will be filled in the field when it is created.
NULL. Beginners and not only programmers, when compiling queries, very often forget about the possibility of obtaining a NULL value. As a result, an error creeps into the code, which is difficult to track down during debugging. Therefore, when creating tables, selecting or recalculating values, you need to stop and think about whether the occurrence of NULL in this section of the query is taken into account.
Memory. This article has shown several functions that can perform certain tasks. When developing a shell to work with the database, you can "outweigh" the calculation of simple expressions on the database management system. In some cases, this gives a significant performance boost.
Restrictions. If you need to get only two from a database with thousands of rows, then you should use operators like LIMIT or TOP. There is no need to extract data using the shell development language.
Compound. After receiving data from several tables, many programmers begin to bring them together using shell memory. But why? After all, you can make one request in which all this will be present. You do not have to write extra code and reserve additional memory in the system.
Sorting. If it is possible to apply ordering in the query, that is, by the DBMS, then you need to use it. This will significantly save on resources when running a program or service.
Lots of requests. If you have to insert many records sequentially, then for optimization you should think about batch inserting data in one query. This will also increase the performance of the entire system as a whole.
Thoughtful placement of data. Before compiling the structure of the database, you need to think about whether such a number of tables and fields is necessary. Maybe there is a way to merge them or discard some. Very often, programmers use an excessive amount of data that will never be used anywhere.
Types. To save space and resources, you need to be sensitive to the types of data you use. If it is possible to use a type that is less "heavy" for memory, then it is necessary to use it. For example, if it is known that in a given field the numeric value will not exceed 255, then why use a 4-byte INT if there is a TINYINT of 1 byte.
In conclusion, it should be noted that the SQL structured query language is now used almost everywhere - sites, web services, PC programs, applications for mobile devices. Therefore, knowledge of SQL will help all branches of development.
At the same time, modifications of the original language standard sometimes differ from each other. For example, PL SQL statements may have a different syntax than in SQL Server. Therefore, before you start developing with this technology, you should read the manuals for it.
In the future, analogues that could surpass SQL in terms of functionality and performance are unlikely to appear, so this area is quite a promising niche for any programmer.
The SQL SELECT statement is intended for queries to select data from a database. It can be used both without conditions (selecting all rows in all columns or all rows in specific columns) or with multiple conditions (selecting specific rows) that are specified in the WHERE clause. Let's get acquainted with the SQL tools that can be used to set these conditions on a data selection, and also learn how to use the SELECT statement in subqueries.
A query with a SELECT statement to select all columns of a table has the following syntax:
SELECT * FROM TABLE_NAME
That is, to select all columns of the table, after the word SELECT, you need to put an asterisk.
Example 1 There is a firm database - Company. It has a table Org (Structure of the company) and Staff (Employees). It is required to select all columns from the tables. The corresponding query to select all columns from the Org table is as follows:
SELECT * FROM ORG
This query will return the following (to enlarge the image, click on it with the left mouse button):
The query to select all columns from the Staff table looks like this:
SELECT * FROM STAFF
This query will return the following:
To select certain columns of the table, instead of an asterisk, we need to list the names of all the columns to be selected, separated by commas:
SELECT COLUMNS TO SELECT FROM TABLE_NAME
Example 2 Let it be required to select the columns Depnumb and Deptname from the Org table, which contain data on the numbers of the firm's departments and their names, respectively. The query to get such a selection would be the following:
SELECT DEPNUMB, DEPTNAME FROM ORG
And from the Staff table, you need to select the columns DEPT, NAME, JOB, which contain, respectively, data on the number of the department in which the employee works, his name and position:
SELECT DEPT, NAME, JOB FROM STAFF
To select certain table rows, along with the SELECT statement, we already need the WHERE keyword, pointing to some value or multiple values contained in the rows of interest to us. The simplest conditions are specified using the comparison and equality operators (, =), as well as the IS keyword. There can be several conditions, then they are listed using the AND keyword. Row selection queries have the following syntax:
Example 4 In the previous example, we selected rows from the table only by the value of one column - DEPT. Suppose now we need to select data about employees who work in the 38th department and whose position is an employee (Clerk). To do this, in the WHERE section, the corresponding values \u200b\u200bmust be listed using the word AND:
Example 5 Let it be necessary to select from the Staff table the identifiers and names of those employees whose commission size is indefinite. To do this, in the WHERE section, before specifying the value of the COMM - NULL column, you need to put not an equal sign, but the word IS:
This query will return the following data:
Comparison signs are also used to indicate the values in the rows to be selected.
Predicates - the words IN, OR, BETWEEN, LIKE in the WHERE clause - also allow you to select specific ranges of values (IN, OR, BETWEEN) or values in rows (LIKE) that you want to select from the table. Queries with IN, OR, BETWEEN predicates have the following syntax:
Queries with the LIKE predicate have the following syntax:
Example 7 Suppose you want to select from the Staff table the names, positions and number of years worked of employees working in departments with numbers 20 or 84. This can be done with the following query:
Query execution result:
Example 8 Suppose now you want to select the same data from the Staff table as in the previous example. The query with the word OR is similar to the query with the word IN and listing the values of interest in brackets. The request will be as follows:
Example 9 Let's select from the same table the names, positions and the number of years worked for employees whose salary is between 15,000 and 17,000 inclusive:
Query execution result:
The LIKE predicate is used to select those strings whose values contain the characters specified after the predicate between apostrophes (").
Example 10 Let's select from the same table the names, positions and the number of years worked for employees whose names begin with the letter S and consist of 7 characters:
The underscore character (_) means any character. Query execution result:
Example 11. Let's select from the same table the names, positions and the number of years worked for employees whose names begin with the letter S and contain any other letters in any number:
The percent symbol (%) means any number of characters. Query execution result:
Values specified using the predicates IN, OR, BETWEEN, LIKE can be inverted using the word NOT. Then the requested data will have the opposite meaning. If we use NOT IN (20, 84), then the data of employees who work in all departments except those with numbers 20 and 84 will be displayed. Using NOT BETWEEN 15000 AND 17000, we can get data for employees whose salary is not included in the interval from 15000 up to 17000. A query with NOT LIKE will return the data of employees whose names do not begin or do not contain characters specified with NOT LIKE.
There is a database "Theater". The Play table contains play data. Table Team - about the roles of actors. The Actor table is about actors. Table Director - about directors. Table fields, primary and foreign keys can be seen in the figure below (to enlarge, click the left mouse button).
Example 12. Display a list of actors who have never been approved for the main role. In the team table, data about the main roles is contained in the mainteam column. If the role is the main one, then "Y" is marked in the corresponding line.
The SQL SELECT queries that have been parsed so far have returned rows that could be in any order. However, it is often necessary to sort strings by numerical order, alphabetically, and other criteria. This is done using the keyword ORDER BY. Such queries have the following syntax:
Example 15 Let it be required to select from the Staff table employees working in department number 84 and sort (arrange) the records by the number of years worked in ascending order:
The word ASC indicates that the sort order is ascending. This word is optional, as ascending sort order is the default. Query execution result:
Example 16 Let it be required to select the same data as in the previous example, but sort (order) the records by the number of years worked in descending order:
The word DESC indicates that the sort order is descending. Query execution result:
When the uniqueness condition is not specified for table row values, identical rows may appear in the query results. Often you want to display only unique strings. This is done by using the DISTINCT statement after the SELECT statement.
Example 17. Let it be required to find out what departments exist and what positions among departments whose numbers are less than 30. This can be done using the following query:
Query execution result:
So far, we have dealt with SQL constructs with a SELECT statement, in which the conditions by which the data is selected and the data being selected are contained in the same database table. In practice, it often happens that the data to be selected is contained in one table, and the conditions are contained in another. This is where subqueries come to the rescue: the values of the selection condition are returned from another query (subquery) that also starts with SELECT. Queries with subqueries can produce one or more rows.
Example 18. All the same ORG and STAFF tables. Let it be required to find out in which department the employee with identification number 280 works, and where this department is located. But information about departments is stored in the ORG table, and information about employees is stored in the STAFF table. This can be done with the following subquery, where the outer SELECT is on the ORG table and the inner SELECT is on the STAFF table:
Query execution result:
Example 19. Let now it is required to find out in which departments (without duplication) employees with a salary of less than 13,000 work. To do this, in the WHERE section of the outer SELECT (query to the ORG table) a condition is specified that takes a range of values (IN), and the inner SELECT (to the STAFF table ) just returns the required range of values:
Relational databases and SQL language
Discipline: Databases
Language operatorsSQL
The SQL language includes operators of different categories. Any SQL statement consists of reserved words and words defined by the user in accordance with established syntactic rules. As with many programming languages, most of the language's statement components are case-insensitive. The exception to this rule is, as usual, character data, which must be case-conscious and use the one that is needed to represent the data.
To write statements in the language, a free format is adopted, which makes it possible to make the SQL program more readable through indentation and alignment.
each phrase in the statement must start on a new line;
the beginning of each phrase must be aligned with the beginning of the rest of the statement's phrases;
each part of the phrase should start on a new line with some indentation relative to the beginning of the whole phrase, which will allow you to highlight the subordinate parts;
Some conventions apply to writing operators:
capital letters are used to write reserved words;
lowercase letters are used to write user-defined words;
the vertical bar "|"" indicates the need to select one of several values;
curly braces define a required element;
square brackets define an optional element;
the ellipsis "..." is used to indicate the optional possibility of repeating the construct, from zero to several times.
Data definition operators (Table 1) are used to describe the data structures used. This category includes the following statements: create table, drop table, alter table, create view, ALTER VIEW, DROP VIEW.
Table 1 . Data Definition Operators
Operator Explanation
create table
DROP table
alter table
CREATE VIEW Create a view
alter view
drop view
Data manipulation operators, which form the next category of operators, are designed to fill tables with data and to update the information loaded into them. This category includes the following operators: delete, insert, update (Table 2).
table 2 . Data Manipulation Operators
OperatorExplanation
Delete Deletes one or more rows that match the conditions
filtering, from base table
INSERT Inserts one row into the base table
update Updates the values of one or more columns in one or more
multiple lines matching filter conditions
To select information from the database, the query language is intended, which in the SQL language is represented by a single select statement (Table 3).
Table 3 Query Language
Operator Explanation
select Selects rows; operator that allows you to form the resulting
table matching the query
In addition to the indicated categories of operators, the purpose of which is not difficult to imagine after reading the explanations in the tables, it is necessary to single out two more: transaction management operators (Table 4) and data administration tools (Table 5).
Table 4. Transaction management
Operator Explanation
commit Complete the transaction - complete the processing of information,
combined into a transaction
rollback Rollback transaction - undo changes made during execution
state of the database, mark it so that you can return to it later
Table 5 . Data administration
Operator Explanation
ALTER DATABASE Change the set of basic objects in the database, restrictions regarding
the entire database
ALTER DBAREA Modify a previously created storage area
ALTER PASSWORD Change the password for the entire database
CREATE DATABASE Create a new database
CREATE DBAREA Create a new storage area and make it available for hosting
DROP DATABASE Drop an existing database
DROP DBAREA Delete an existing storage area (if it does not currently contain
active data is located)
GRANT Grant access rights to a number of actions on some database object
REVOKE Revoke access rights to some object or some actions on
object
In commercial DBMS, the set of basic operators is extended. Most DBMSs include statements to define and delete the run index of stored procedures and statements to define triggers.
It is customary to start acquaintance with this language by considering the capabilities of the query language, which in the SQL language is represented by a single select statement, because this powerful statement, of course, is also the most complex. In addition, it is interesting to see how it can be used in conjunction with data manipulation operators in the future.
Select statementSELECT . Formation of queries to the database
The purpose of the select statement is to select and display data from one or more database tables. This extremely powerful, most commonly used operator implements all relational algebra operations. The same request can be implemented in several ways, which can differ significantly in execution time.
Select statement format:
SELECT *|<список полей>FROM<список таблиц>
The specified order of the phrases in the select statement cannot be changed, but not all parts of it are required. Required clauses include only the select and from clauses. All other parts of the operator can be used at the discretion of the programmer. Explanation:
□ Phrase select:
Keyword presence all(default) means that the resulting table includes all rows that satisfy the query conditions, which can lead to duplicate rows appearing in the resulting table;
Keyword distinct designed to bring the table in line with the principles of relational theory, which assumes the absence of duplicate rows;
Symbol " * " defines a very common situation where the result set includes all columns from the original query table.
□ In a phrase from a list of source tables of the query is specified.
□ In a phrase where conditions for selecting result rows or conditions for joining rows of source tables are defined, similar to the conditional join operation in relational algebra. The following predicates can be used as selection conditions:
Comparisons "= ,<>, >, <, >=, <=" - для сравнения результатов вычисления двух выражений; более сложные выражения строятся с помощью логических операторов AND, OR, NOT; значения выражений вычисляются в порядке, который определяется приоритетом используемых операторов и наличием скобок в выражении;
betweenAandIN- the predicate is true when the evaluated value of the expression falls within the specified range (the predicate notbetweenaandIN true when the value being compared does not fall within the specified interval);
in- the predicate is true when the compared value is included in the set of given values; in this case, the set of values can be specified by a simple enumeration or a built-in subquery (the not in predicate is true when the compared value is not included in the specified set);
like And notlike- predicates, the meaning of which is opposite, require the specification of a template with which the given value is compared; the like predicate is true when the value being compared matches the pattern, and false otherwise;
ISnull- a predicate used to detect the equality of the value of some attribute to an undefined value:
<имя атрибута> ISnull- takes the value true if the specified attribute in this string has an undefined value and false, otherwise;
<имя атрибута> ISNOTnull- everything happens the other way around.
exist And notexist The used in inline subqueries.
□ In a phrase groupby the list of grouping fields is set.
□ In a phrase having the predicates-conditions imposed on each group are set.
□ In a phrase orderby a list of result ordering fields is specified, that is, a list of fields that determines the sort order in the resulting table.
The SQL standard defines the concept of a NULL value, which necessitated the use of three-valued logic, where all logical operations are performed in accordance with the truth table below (Table 6).
Table 6 . truth table
|
A AND B | ||||
|
TRUE | ||||
Request 1
Display information about the departments of the university.
This task is reduced to selecting and displaying information from one table, and all its rows and all its columns are subject to output:
SELECT * FROM department
The result of such a query will be a table containing information about all departments of the university:
|
code kaf |
Name kaf |
Nom_telef |
Nom_Auditoria |
Col_sotr | |
|
Ivanov T.M. |
|||||
|
General Mathematics |
Makhov K L. |
||||
|
Ross L.T. |
|||||
|
Firsov S.S. |
|||||
|
Applied Mathematics |
Lyakhova I.T. |
Request 2
Display phone numbers of university departments.
The result of such a query should contain only two columns: Name_ kaf And Nom_ telef, so the query itself should look like this:
SELECT Name_kaf, Nom_telef FROM kafedra
Result table:
Namekaf Nomjelef
Physics 23-34-24
General Mathematics 23-65-43
Stories 23-78-72
Charts 23-99-77
Applied Mathematics 23-66-62
In the queries formed above, it was required to display all the rows of the table specified in the from clause. If the selection needs to limit the number of output rows according to some condition, then this can be achieved by using the where clause in the query. You can include one or more row selection conditions in the where clause.
Request 3
Display information about the department of graphics.
SELECT * FROM kafedra WHERE Name_kaf = "Charts"
The response to such a request will contain only one line:
Kod.kaf Name_kaf NomjelefNom_Auditoria Col_sotr Zav_kaf
004 Charts 23-99-77 385 18 Firsov C.C.
Request 4
Display information about the university departments located on the ground floor, taking into account the fact that the rooms on the ground floor are in the range from 1 to 99.
The request will look like this:
SELECT * FROM kafedra WHERE Nom_Auditoria BETWEEN 1 AND 99
Query result:
KodjcafName_kafNorn lelef Norn Audit oria Coi_sotr Zavkaf
002 General Mathematics 23-65-43 003 22 Makhov K.L.
mathematics
In general, the rows in the resulting table are displayed in an unordered state in some way. Viewing and analyzing such material is not always convenient. The order by clause is used to sort rows by any column. It includes a comma-separated list of column names by which you want to sort the displayed information. This phrase must always be placed last in the select statement and, if present, it becomes possible to sort rows in ascending (asc) or descending (desc) values of the specified column or combination of the specified columns, regardless of whether these columns are present in the resulting table or not.
Request 5
Display information about the departments of the university in the form sorted by the columnName_ kafin ascending order.
The request will look like this:
SELECT * FROM kafedra ORDER BY Name_caf ASC
The result of this request:
Kod_kaf Name_kaf Nomjelef Nom_Auditoria Col_sotr Zav kaf
004 Charts 23-E9-77 385 18 FirsovS.S.
003 Stories 23-78-72 465 16 Ross L.T.
002 General ma- 23-65-43 003 22 Makhov K.L.
topics
005 Applied 23-66-62 028 24 Lyakhova I.T.
mathematics
001 Physicists 23-34-24 132 25 Ivanov T.M.
It is often useful to sort the displayed information into several columns to improve visibility. To do this, sort column names must be listed separated by commas in the order by clause. In this case, the output table will contain rows ordered by the first column specified in the order by clause, and rows that have equal values in this column will be ordered by the values of the second column, and so on from left to right.
