The Select (SQL) statement

The translation of SQL abbreviation (the language of structurally organized queries) reflects the fact that queries are the most frequently used element in SQL. Select the necessary rows, automatically exclude redundant data, skip or reorder the columns will help Select (SQL) - an operator that tells the DBMS to output certain information.

Operator Syntax

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

Select	Tells the database that we are transmitting the request. This is the key word.
One, two, three ...	List of columns for output
From	Indicates the name of the table from which to select the data. It is also a required keyword.

This is the so-called "short" syntax of the operator, but it indicates to us that without the Select keywords and from the DBMS, our query will not execute.

The complete syntax for the operator is shown in the following figure:

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

To group values and apply an aggregate function to them, the Group by clause is used, and to specify the result after grouping, the proposal Having is used.

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

с информацией: To get a better understanding of the Select operator, let's imagine that in our database there is the following Cats table with the information:

Id	Breed	Name	Birthday	Color
1	Bobtail	Lord	04/01/2017	Gray
2	Curl	Feint	03/16/2012	White
3	Mau	Panther	03/03/2017	Black
4	Bobtail	Tyson	02/23/2017	Gray
5	Burmilla	Athena	01/08/2017	Black

Each row of the table contains the unique number of the kitten, its breed, nickname, date of birth and coloring. Next, we will consider how the Select (SQL) operator works, based on the data from this table.

How data is fetched from a table

As discussed above, keywords are always used to select the right information from the table.

After the Select keyword, columns for output are specified. You can make a list of the required columns separated by a comma, then the whole structure will look like this:

Select color, breed, name From Cats

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

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

Select * From Cats

The result of the above query is the entire Cats table, represented as it appears at the end of the last section.

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

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

Select breed || ',' || Color From cats

The result is the following:

Breed, Color

Bobtail, Gray

Curl, White

Mau, Black

Bobtail, Gray

Burmilla, Black

Excluding redundant data

Distinct - function Select (SQL), which allows to exclude duplication of absolutely identical lines from the result of the sample.

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

Select breed From Cats

That we will receive quite expected result:

Breed

Bobtail

Curl

Mau

Bobtail

Burmilla

As you can see, the Bobtail breed is duplicated twice. Argument Distinct will eliminate duplication, you just need to complete the query:

Select distinct breed From Cats

Refinement of the request

In reality, almost no query outputs data as a complete set of table rows. Let's consider, what sentence in Select (SQL) allows to set criteria for selection only the necessary lines.

This is where. In this sentence, a predicate is used - a conditional expression giving the output the value "true" or "false". The Select statement retrieves only those data from the table for which the conditional expression will be True, or "true."

It will help to sort out with this design a simple sample. Let's say we want to know everything about black cats.

Select * From cats Where color = 'Black'

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

3	Mau	Panther	03/03/2017	Black
5	Burmilla	Athena	01/08/2017	Black

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

Group by

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

The aggregate functions include:

• Count - calculates the number of rows selected by the query.
• Sum is the arithmetic sum of all selected column values.
• Min - prints the minimum of the selected column values.
• Max is the maximum of the selected column values, respectively.
• Avg is the average.

The scheme of this proposal is most easily understood on a concrete example. Let's say we want to know how many kittens of each breed we have. To do this, you need to generate the following simple query:

Select breed, count (*) From cats Group by breed

The result of the execution will be the following table:

Breed	Count
Bobtail	2
Curl	1
Mau	1
Burmilla	1

As you can see, we have two Bobtails with kittens, the rest have only one. In practice, according to this request, based on our table, the breeder can understand which breeds cats are in demand among buyers, and which are not.

It is likely that because of the huge number of entries in a real table, it will still be necessary to clarify the query and withdraw only those breeds of kittens, which are no more, for example, ten. To refine or filter groups, use the Having clause. It allows you to drop certain groups, similar to the Where clause, which discards individual rows. The condition is given by the aggregate function. We will add the query:

Select breed, count (*) From cats Group by breed Having count (*) <= 10

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

Sorting

Let's get acquainted with the Order by - sentence of the Select (SQL) operator, which allows you to sort the output lines 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 construct 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 can be individually sorted either ascending (ASC) or descending (DESC).

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

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

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