The syntax of a SQL query
One of the main functions of SQL is to retrieve data from a DBMS. To build all kinds of database queries, use the SELECT statement. It allows you to perform complex checks and data processing.
The general structure of a query
SELECT [DISTINCT | ALL] table_fields FROM list_of_tables [WHERE the_conditions_of_the_limitations_of_the_sample] [GROUP BY the_grouping_condition] [HAVING the_conditions_of_the_limitations_of_the_sample_after_grouping] [ORDER BY sort_order [ASC | DESC]] [LIMIT limit_the_number_of_sample_records]
In the described query structure, optional parameters are indicated in square brackets.
- DISTINCT is used to exclude duplicate rows from the result.
ALL (default) is used to get all data, including repetitions
- FROM lists the tables used in the query from the database
- WHERE — it is a conditional statement that is used to restrict strings to a condition
- GROUP BY used to group rows
- HAVING applied after grouping rows to filter by values of aggregate functions
- ORDER BY used for sorting. It has two parameters:
- ASC (default) is used to sort ascending
- DESC — descending
- LIMIT used to limit the number of lines to output
Aliases are used to represent columns or tables with a name different from the original. This can be useful for improving the readability of names and creating a shorter column or table name.
For example, if your table has a column good_type_id, you can simply rename it to id, in order to make it shorter and more convenient to use in the future.
To create aliases, use the AS operator:
SELECT good_type_id AS id FROM GoodTypes;
The order of execution
The following steps demonstrate the logical processing order of a SELECTstatement. This order determines when the objects defined in one step become available for offers in the next steps.
For example, in the WHERE clause, the aliases of the columns defined in the SELECT clause are not available, because, according to the list, it runs before SELECT.
- GROUP BY
- ORDER BY
The actual execution of instructions is determined by the DBMS and the order from this list may vary significantly.
You can output any rows and numbers instead of table columns:
SELECT "Hello world", 1;
In order to display all the data from the Company table, you can use the symbol "*", which literally means "all columns":
SELECT * FROM Company;
You can output any column defined in the table, for example, town_to from the Trip table:
SELECT town_to FROM Trip;
You can also output multiple columns. To do this, they must be separated by commas:
SELECT member_name, status FROM FamilyMembers;
Sometimes situations arise in which you need to get only unique records. You can use DISTINCT for this. For example, we list the cities without repetition, in which the planes flew:
SELECT DISTINCT town_to FROM Trip;
Tasks for self-testing
Basic concepts of databases
Conditional WHERE operator