Select statement (SQL)

The translation of the abbreviation SQL itself (the language of structurally organized queries) reflects the fact that queries are the most frequently used element in SQL. Select (SQL) will help you select the necessary rows, automatically eliminate redundant data, skip or reorder columns — an operator containing an indication of the DBMS to display certain information.

Operator Syntax

To use any operator correctly, you must first familiarize yourself with the syntax of the programming language in question. When we speak specifically about the SQL language, Select (statement) has the following syntax:

This is the so-called “short” operator syntax, but it tells us that without the DBMS Select and from keywords our query will not fulfill.

The full operator syntax is shown in the following figure:

Here the Where clause allows you to refine your search by specifying a condition.

The Group by clause is used to group values ​​and apply the aggregate function to them, and the Having clause is used to refine the result after the grouping.

Order by will allow you to sort the values ​​of the selected columns in ascending or descending order.

To get more familiar with the Select operator, imagine that our database has the following Cats table with information:

Each row of the table contains a unique kitten number, its breed, nickname, date of birth and color. Further we will consider how the Select (SQL) statement works, already relying on the data from this table.

How is the data selected from the table

As discussed above, keywords are necessarily used to select the necessary information from the table.

The Select keyword indicates the columns to display. You can make a list of the necessary columns separated by commas, then the whole structure will look like this:

As you can see, we can arrange the columns in the order in which we need them. In addition, we can only display the columns we need.

There is also a short entry for viewing all columns of the table. To do this, after Select, an asterisk (*) is indicated with a space. The whole structure will look like this:

The result of this query is the entire Cats table, presented as it is at the end of the last section.

Many are interested in how to put execution results in SQL Select into a string. Most often this is required when it is necessary to combine the surname, name and patronymic of a person, placed in separated columns.

In our case, combine the breed and color of cats from the Cats table. The nuance is that different DBMSs use different characters for string concatenation. In some cases, this is simply a plus (+), in others it is a double forward line (||) or an ampersand (&), sometimes the Concat operand is also used. Therefore, before combining, you need to read the annotation for the specific DBMS you are working with.

The result is the following:

Redundant Data Exclusion

Distinct is a Select (SQL) function that eliminates duplication of absolutely identical rows from the selection result.

For example, we want to find out which breeds of cats are in our table. If we use a simple query:

Then we get the expected result:

As you can see, the Bobtail breed is duplicated twice. The Distinct argument will eliminate duplication, just add the request:

Request refinement

In reality, almost no query displays data in the form of a complete set of table rows. Consider which sentence in Select (SQL) allows you to specify criteria for selecting only the necessary rows.

Such a sentence is Where. In this sentence, a predicate is used - a conditional expression that gives the value "true" or "false" at the output. The Select statement will retrieve only the data from the table for which the conditional expression will be True, or true.

A simple selection will help to understand this design. Let's say we want to know all about black cats.

The result of this query will be the following table rows:

You can also combine conditions using the logical operators And, Or, Not.

Group offer

The Group by clause used in Select (SQL) allows you to group queries by the value of a specific column (or columns), and then apply an aggregate function to them.

Aggregate functions include:

• Count - calculates the number of rows selected by the request.
• Sum is the arithmetic sum of all the selected column values.
• Min - displays the minimum of the selected column values.
• Max - accordingly, the maximum of the selected column values.
• Avg is the average value.

The working scheme of this proposal is easiest to understand with a specific example. Let's say we want to know how many kittens of each breed we have. To do this, form the following simple query:

The result of the execution will be the following table:

As you can see, we have two Bobtail kittens, the rest are just one at a time. In practice, on such a request, based on our table, the breeder can understand which cats are in demand among buyers and which are not.

It is likely that due to the huge number of entries in a real table, you will still want to refine the query and display only those breeds of kittens, of which there are no more than, for example, ten. To refine or filter groups, the Having clause is used. It allows you to drop specific groups, similar to the Where clause, which discards individual lines. The condition is specified by the aggregate function. Let's add a request:

Since we specified the condition “the number of kittens of each breed is not more than 10”, the result will be the same as in the example without specification. But here it is important to understand the very scheme of the Having sentence. But if we change the logical condition to Having count (*) = 1, then the result is reduced to three lines and displays the breeds of kittens, which are left only one by one.

Sorting

Let's get acquainted with Order by - a sentence of the Select (SQL) operator that allows you to sort the output rows by increasing or decreasing values ​​in one or more columns.

It is important to remember that Order by is the final sentence of the entire construction of the Select statement. It is placed after Select, From, Where, Group by, Having.

When sorting, there are three important points:

1) You can specify any number of columns, each of which individually can be sorted either ascending (ASC) or descending (DESC).

2) All specified columns in the Order by clause must be present among selectable columns in Select.

3) It is not necessary to list specific column names for sorting; you can simply indicate their numbers under which they appear in the Select statement.

We hope that with the help of our article you have gained basic knowledge about using SQL queries and now you can easily select the necessary information from your DBMS.