How to write SQL queries - detailed examples

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

But, despite the constant use of databases, even for many developers of software systems there are many "blank spots" due to different interpretations of the same terms. We will give a brief definition of the basic terms of databases before considering the SQL language. So.

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

Table - imagine a folder in which documents are grouped by a certain attribute, for example, a list of orders for the last month. This is the table in the computer database. A separate table has its own unique name.

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

Column and row - we all worked with spreadsheets, which also have rows and columns. Any relational database works with tables in the same way. Strings are sometimes called records.

Primary key - each table row can have one or more columns for its unique identification. Without a primary key, it is very difficult to update, modify, and delete the desired rows.

What is SQL?

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

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

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

SQL Data Types

All columns in the database table store one data type. The 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:

• SQL statements through the DBMS console
• Using the interactive administration tools included with the database server.

A new database is created with the CREATE DATABASE statement <database name>; . 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, 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 be null (NULL) or must be filled (NOT NULL), and this is determined when creating the table.

Fetching data from a table

The data retrieval operator is the most commonly used SQL query. For information, you must specify what we want to choose from such a table. First a simple example:

SELECT commodity_name FROM Commodity

After the SELECT statement, specify the column name for 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. To order the result, an additional ORDER BY operator is used.

To query across several fields, list them with a comma, as in the following example:

SELECT commodity_id, commodity_name, commodity_price FROM Commodity

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

SELECT * FROM Commodity

• Additionally, SELECT supports:
• Sort data (ORDER BY statement)
• Selection according to conditions (WHERE)
• Grouping term (GROUP BY)

Add a line

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

• add a new whole line;
• part of the line;
• 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 is 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 specified after VALUES for each column. If there is no corresponding value for the column, then NULL must be specified. Columns are filled with values ​​in the order specified when creating the table.

If you add only part of the row, you must explicitly specify the column names, 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

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

Data change

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

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

UPDATE consists of three main elements:

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

Consider an example. Suppose a product with ID = 106 has changed value, so this line needs to be updated. We write the following statement:

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 desired ID value in WHERE.

To change multiple columns after the SET statement, several column-value pairs are specified, separated by commas. We look at an example in which the name and price of the goods are updated:

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

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

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

Delete lines

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

• certain rows are deleted in the table;
• all rows in the table are deleted.

Example of deleting a single row from a table:

DELETE FROM Commodity WHERE commodity_id = '106'

After DELETE FROM, specify the name of the table in which rows will be deleted. The WHERE statement contains a condition by which rows to be deleted will be selected. In the example, we delete the product line with ID = 106. Specifying WHERE is very important since Skipping this statement will delete all rows in the table. This also applies to changing field values.

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

Using SQL in Microsoft Access

Microsoft Access is usually used interactively to create tables, databases, to manage, modify, analyze data in a database and to embed SQL Access queries through a convenient interactive Query Designer, using which you can build and immediately execute SQL statements 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 databases of any format.

SQL Extensions

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

The most common dialects of the language:

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

SQL to the Internet

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

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