An example of creating a query (Query) in a MS SQL Server database. The database is located in a local *.mdf file. How to Write SQL Queries - Detailed Examples Placing a DataGridView Control and Setting Up Database Communications

Example

Let's create a view consisting of courses with 3 credits:

24. Aggregate functions

These functions are used to obtain an aggregate result related to the data in question. The following are commonly used aggregate functions:

• COUNT (col_name) - returns the number of rows;
• SUM (col_name) - returns the sum of the values ​​in this column;
• AVG (col_name) - returns the average value of a given column;
• MIN (col_name) - returns the smallest value of a given column;
• MAX (col_name) - Returns the largest value of a given column.

25. Nested subqueries

Nested subqueries are SQL queries that include SELECT , FROM , and WHERE clauses nested within another query.

Example

Let's find courses that were taught in the fall of 2009 and spring of 2010:

SELECT DISTINCT course_id FROM section WHERE semester = 'Fall' AND year= 2009 AND course_id IN (SELECT course_id FROM section WHERE semester = 'Spring' AND year= 2010);

Each of us regularly encounters and uses various databases. When we select an email address, we are working with a database. Databases are used by search services, banks to store customer data, etc.

But despite the constant use of databases, even for many software system developers there are still many blind spots due to different interpretations of the same terms. We'll give a brief definition of basic database terms before covering the SQL language. So.

Database - a file or collection of files for storing ordered data structures and their relationships. Very often a management system is called a database - it is only a repository of information in a specific format and can work with various DBMSs.

Table - Let's imagine a folder in which documents are stored, grouped according to a certain characteristic, for example, a list of orders for the last month. This is a table in a computer. A separate table has its own unique name.

Data type - the type of information allowed to be stored in a separate column or row. These can be numbers or text of a certain format.

Column and Row- We have all worked with spreadsheets, which also have rows and columns. Any relational database works with tables in a similar way. Rows are sometimes called records.

Primary key- Each row of a table can have one or more columns to uniquely identify it. Without a primary key, it is very difficult to update, change, and delete relevant rows.

What is SQL?

SQL(English - structured query language) was developed only for working with databases and is currently the standard for all popular DBMSs. The language syntax consists of a small number of operators and is easy to learn. But, despite its apparent simplicity, it allows the creation of sql queries for complex operations with a database of any size.

Since 1992, there has been a generally accepted standard called ANSI SQL. It defines the basic syntax and functions of operators and is supported by all DBMS market leaders, such as ORACLE. It is impossible to consider all the capabilities of the language in one short article, so we will briefly consider only basic SQL queries. Examples clearly show the simplicity and capabilities of the language:

• creating databases and tables;
• data sampling;
• adding records;
• modification and deletion of information.

SQL Data Types

All columns in a database table store the same data type. Data types in SQL are the same as in other programming languages.

We create tables and databases

There are two ways to create new databases, tables and other queries in SQL:

• via the DBMS console
• Using the interactive administration tools included with the database server.

A new database is created by the operator CREATE DATABASE<наименование базы данных>; . As you can see, the syntax is simple and concise.

We create tables inside the database using the CREATE TABLE statement with the following parameters:

• table name
• column names and data types

As an example, let's create a Commodity table with the following columns:

Create a table:

CREATE TABLE Commodity

(commodity_id CHAR(15) NOT NULL,

vendor_id CHAR(15) NOT NULL,

commodity_name CHAR(254) NULL,

commodity_price DECIMAL(8,2) NULL,

commodity_desc VARCHAR(1000) NULL);

The table consists of five columns. After the name comes the data type, the columns are separated by commas. The column value can accept empty values ​​(NULL) or must be filled (NOT NULL), and this is determined when the table is created.

Retrieving data from a table

The data fetch operator is the most commonly used SQL query. To obtain information, we must indicate what we want to select from such a table. First a simple example:

SELECT commodity_name FROM Commodity

After the SELECT statement we specify the name of the column to obtain information, and FROM defines the table.

The result of the query will be all rows of the table with Commodity_name values ​​in the order in which they were entered into the database, i.e. without any sorting. An additional ORDER BY clause is used to order the result.

To query for several fields, list them separated by commas, as in the following example:

SELECT commodity_id, commodity_name, commodity_price FROM Commodity

It is possible to get the value of all columns of a row as a query result. To do this, use the “*” sign:

SELECT * FROM Commodity

• Additionally SELECT supports:
• Sorting data (ORDER BY operator)
• Selection according to conditions (WHERE)
• Grouping term (GROUP BY)

Add a line

To add a row to a table, SQL queries with the INSERT operator are used. Addition can be done in three ways:

• add a new whole line;
• string part;
• query results.

To add a full row, you must specify the table name and the values ​​of the columns (fields) of the new row. Here's an example:

INSERT INTO Commodity VALUES("106 ", "50", "Coca-Cola", "1.68", "No Alcogol ,)

The example adds a new product to the table. Values ​​are listed after VALUES for each column. If there is no corresponding value for the column, then NULL must be specified. The columns are populated with values ​​in the order specified when the table was created.

If you add only part of a row, you must explicitly specify the names of the columns, as in the example:

INSERT INTO Commodity (commodity_id, vendor_id, commodity_name)

VALUES("106 ", ‘50", "Coca-Cola",)

We entered only the identifiers of the product, supplier and its name, and left the remaining fields blank.

Adding Query Results

INSERT is primarily used to append rows, but can also be used to append the results of a SELECT statement.

Changing data

To change information in the fields of a database table, you must use the UPDATE statement. The operator can be used in two ways:

• All rows in the table are updated.
• Only for a specific line.

UPDATE consists of three main elements:

• table in which changes need to be made;
• field names and their new values;
• conditions for selecting rows to change.

Let's look at an example. Let's say the price of a product with ID=106 has changed, so this line needs to be updated. We write the following operator:

UPDATE Commodity SET commodity_price = "3.2" WHERE commodity_id = "106"

We specified the name of the table, in our case Commodity, where the update will be performed, then after SET - the new value of the column and found the desired record by specifying the required ID value in WHERE.

To change multiple columns, the SET statement is followed by multiple column-value pairs separated by commas. Let's look at an example in which the name and price of a product are updated:

UPDATE Commodity SET commodity_name=’Fanta’, commodity_price = "3.2" WHERE commodity_id = "106"

To remove information in a column, you can assign it the value NULL if the table structure allows it. It must be remembered that NULL is precisely “no” value, and not zero in the form of text or number. Let's remove the product description:

UPDATE Commodity SET commodity_desc = NULL WHERE commodity_id = "106"

Removing rows

SQL queries to delete rows in a table are executed using the DELETE statement. There are two use cases:

• Certain rows in the table are deleted;
• All rows in the table are deleted.

An example of deleting one row from a table:

DELETE FROM Commodity WHERE commodity_id = "106"

After DELETE FROM we indicate the name of the table in which the rows will be deleted. The WHERE clause contains the condition by which rows will be selected for deletion. In the example, we delete the product line with ID=106. Specifying WHERE is very important because omitting this statement will delete all rows in the table. This also applies to changing the value of fields.

The DELETE statement does not specify column names or metacharacters. It deletes rows completely, but it cannot delete a single column.

Using SQL in Microsoft Access

Typically used interactively to create tables, databases, to manage, modify, analyze data in a database and to implement SQL Access queries through a convenient interactive query designer (Query Designer), using which you can build and immediately execute SQL statements of any complexity .

The server access mode is also supported, in which the Access DBMS can be used as a generator of SQL queries to any ODBC data source. This feature allows Access applications to interact with any format.

SQL extensions

Since SQL queries do not have all the capabilities of procedural programming languages, such as loops, branching, etc., DBMS manufacturers are developing their own version of SQL with advanced capabilities. First of all, this is support for stored procedures and standard operators of procedural languages.

The most common dialects of the language:

• Oracle Database - PL/SQL
• Interbase, Firebird - PSQL
• Microsoft SQL Server - Transact-SQL
• PostgreSQL - PL/pgSQL.

SQL on the Internet

The MySQL DBMS is distributed under the free GNU General Public License. There is a commercial license with the ability to develop custom modules. As a component, it is included in the most popular assemblies of Internet servers, such as XAMPP, WAMP and LAMP, and is the most popular DBMS for developing applications on the Internet.

It was developed by Sun Microsystems and is currently supported by Oracle Corporation. Databases up to 64 terabytes in size, SQL:2003 syntax standard, replication of databases and cloud services are supported.

Table expressions are called subqueries that are used where the presence of a table is expected. There are two types of table expressions:

derived tables;

generalized table expressions.

These two forms of table expressions are discussed in the following subsections.

Derived tables

Derived table is a table expression included in the FROM clause of a query. Derived tables can be used in cases where using column aliases is not possible because the SQL translator processes another statement before the alias is known. The example below shows an attempt to use a column alias in a situation where another clause is being processed before the alias is known:

USE SampleDb; SELECT MONTH(EnterDate) as enter_month FROM Works_on GROUP BY enter_month;

Trying to run this query will produce the following error message:

Msg 207, Level 16, State 1, Line 5 Invalid column name "enter_month". (Message 207: Level 16, State 1, Line 5 Invalid column name enter_month)

The reason for the error is that the GROUP BY clause is processed before the corresponding list of the SELECT statement is processed, and the enter_month column alias is unknown when the group is processed.

This problem can be solved by using a derived table that contains the preceding query (without the GROUP BY clause) because the FROM clause is executed before the GROUP BY clause:

USE SampleDb; SELECT enter_month FROM (SELECT MONTH(EnterDate) as enter_month FROM Works_on) AS m GROUP BY enter_month;

The result of this query will be like this:

Typically, a table expression can be placed anywhere in a SELECT statement where a table name might appear. (The result of a table expression is always a table or, in special cases, an expression.) The example below shows the use of a table expression in the select list of a SELECT statement:

The result of this query:

Generic table expressions

Common Table Expression (OTB) is a named table expression supported by the Transact-SQL language. Common table expressions are used in the following two types of queries:

non-recursive;

recursive.

These two types of requests are discussed in the following sections.

OTB and non-recursive queries

The non-recursive form of OTB can be used as an alternative to derived tables and views. Typically OTB is determined by WITH clauses and an additional query that references the name used in the WITH clause. In Transact-SQL, the meaning of the WITH keyword is ambiguous. To avoid ambiguity, the statement preceding the WITH statement should be terminated with a semicolon.

USE AdventureWorks2012; SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005") AND Freight > (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005 ")/2.5;

The query in this example selects orders whose total taxes (TotalDue) are greater than the average of all taxes and whose freight charges (Freight) are greater than 40% of the average taxes. The main property of this query is its length, since the subquery needs to be written twice. One possible way to reduce the amount of query construction is to create a view that contains a subquery. But this solution is a bit complicated because it requires creating a view and then deleting it after the query has finished executing. A better approach would be to create an OTB. The example below shows the use of non-recursive OTB, which shortens the query definition above:

USE AdventureWorks2012; WITH price_calc(year_2005) AS (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005") SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT year_2005 FROM price_calc) AND Freight > (SELECT year_2005 FROM price_cal c) /2.5;

The WITH clause syntax in non-recursive queries is as follows:

The cte_name parameter represents the OTB name that defines the resulting table, and the column_list parameter represents the list of columns of the table expression. (In the example above, the OTB is called price_calc and has one column, year_2005.) The inner_query parameter represents a SELECT statement that specifies the result set of the corresponding table expression. The defined table expression can then be used in the outer_query. (The outer query in the example above uses OTB price_calc and its year_2005 column to simplify the doubly nested query.)

OTB and recursive queries

This section presents material of increased complexity. Therefore, when reading it for the first time, it is recommended to skip it and return to it later. OTBs can be used to implement recursions because OTBs can contain references to themselves. The basic OTB syntax for a recursive query looks like this:

The cte_name and column_list parameters have the same meaning as in OTB for non-recursive queries. The body of a WITH clause consists of two queries combined by the operator UNION ALL. The first query is called only once, and it begins to accumulate the result of the recursion. The first operand of the UNION ALL operator does not reference OTB. This query is called a reference query or source.

The second query contains a reference to the OTB and represents its recursive part. Because of this, it is called a recursive member. In the first call to the recursive part, the OTB reference represents the result of the reference query. The recursive member uses the result of the first query call. After this, the system calls the recursive part again. A call to a recursive member stops when a previous call to it returns an empty result set.

The UNION ALL operator joins the currently accumulated rows, as well as additional rows added by the current call to the recursive member. (The presence of the UNION ALL operator means that duplicate rows will not be removed from the result.)

Finally, the outer_query parameter specifies the outer query that OTB uses to retrieve all calls to the join of both members.

To demonstrate the recursive form of OTB, we use the Airplane table defined and populated with the code shown in the example below:

USE SampleDb; CREATE TABLE Airplane(ContainingAssembly VARCHAR(10), ContainedAssembly VARCHAR(10), QuantityContained INT, UnitCost DECIMAL(6,2)); INSERT INTO Airplane VALUES ("Airplane", "Fuselage", 1, 10); INSERT INTO Airplane VALUES ("Airplane", "Wings", 1, 11); INSERT INTO Airplane VALUES ("Airplane", "Tail", 1, 12); INSERT INTO Airplane VALUES ("Fuselage", "Salon", 1, 13); INSERT INTO Airplane VALUES ("Fuselage", "Cockpit", 1, 14); INSERT INTO Airplane VALUES ("Fuselage", "Nose",1, 15); INSERT INTO Airplane VALUES ("Cabin", NULL, 1,13); INSERT INTO Airplane VALUES ("Cockpit", NULL, 1, 14); INSERT INTO Airplane VALUES ("Nose", NULL, 1, 15); INSERT INTO Airplane VALUES ("Wings", NULL,2, 11); INSERT INTO Airplane VALUES ("Tail", NULL, 1, 12);

The Airplane table has four columns. The ContainingAssembly column identifies the assembly, and the ContainedAssembly column identifies the parts (one by one) that make up the corresponding assembly. The figure below shows a graphic illustration of a possible type of aircraft and its component parts:

The Airplane table consists of the following 11 rows:

The following example uses the WITH clause to define a query that calculates the total cost of each build:

USE SampleDb; WITH list_of_parts(assembly1, quantity, cost) AS (SELECT ContainingAssembly, QuantityContained, UnitCost FROM Airplane WHERE ContainedAssembly IS NULL UNION ALL SELECT a.ContainingAssembly, a.QuantityContained, CAST(l.quantity * l.cost AS DECIMAL(6,2) ) FROM list_of_parts l, Airplane a WHERE l.assembly1 = a.ContainedAssembly) SELECT assembly1 "Part", quantity "Quantity", cost "Price" FROM list_of_parts;

The WITH clause defines an OTB list named list_of_parts, consisting of three columns: assembly1, quantity, and cost. The first SELECT statement in the example is called only once to store the results of the first step of the recursion process. The SELECT statement on the last line of the example displays the following result.