Sql. занимательные задачки
Содержание:
JOIN-соединения – операции горизонтального соединения данных
Если суть РДБ – разделяй и властвуй, то суть операций объединений снова склеить разбитые по таблицам данные, т.е. привести их обратно в человеческий вид.
- JOIN – левая_таблица JOIN правая_таблица ON условия_соединения
- LEFT JOIN – левая_таблица LEFT JOIN правая_таблица ON условия_соединения
- RIGHT JOIN – левая_таблица RIGHT JOIN правая_таблица ON условия_соединения
- FULL JOIN – левая_таблица FULL JOIN правая_таблица ON условия_соединения
- CROSS JOIN – левая_таблица CROSS JOIN правая_таблица
| Краткий синтаксис | Полный синтаксис | Описание (Это не всегда всем сразу понятно. Так что, если не понятно, то просто вернитесь сюда после рассмотрения примеров.) |
|---|---|---|
| JOIN | INNER JOIN | Из строк левой_таблицы и правой_таблицы объединяются и возвращаются только те строки, по которым выполняются условия_соединения. |
| LEFT JOIN | LEFT OUTER JOIN | Возвращаются все строки левой_таблицы (ключевое слово LEFT). Данными правой_таблицы дополняются только те строки левой_таблицы, для которых выполняются условия_соединения. Для недостающих данных вместо строк правой_таблицы вставляются NULL-значения. |
| RIGHT JOIN | RIGHT OUTER JOIN | Возвращаются все строки правой_таблицы (ключевое слово RIGHT). Данными левой_таблицы дополняются только те строки правой_таблицы, для которых выполняются условия_соединения. Для недостающих данных вместо строк левой_таблицы вставляются NULL-значения. |
| FULL JOIN | FULL OUTER JOIN | Возвращаются все строки левой_таблицы и правой_таблицы. Если для строк левой_таблицы и правой_таблицы выполняются условия_соединения, то они объединяются в одну строку. Для строк, для которых не выполняются условия_соединения, NULL-значения вставляются на место левой_таблицы, либо на место правой_таблицы, в зависимости от того данных какой таблицы в строке не имеется. |
| CROSS JOIN | — | Объединение каждой строки левой_таблицы со всеми строками правой_таблицы. Этот вид соединения иногда называют декартовым произведением. |
- Это короче и не засоряет запрос лишними словами;
- По словам LEFT, RIGHT, FULL и CROSS и так понятно о каком соединении идет речь, так же и в случае просто JOIN;
- Считаю слова INNER и OUTER в данном случае ненужными рудиментами, которые больше путают начинающих.
| ID | Name | DepartmentID | ID | Name |
|---|---|---|---|---|
| 1000 | Иванов И.И. | 1 | 1 | Администрация |
| 1001 | Петров П.П. | 3 | 3 | ИТ |
| 1002 | Сидоров С.С. | 2 | 2 | Бухгалтерия |
| 1003 | Андреев А.А. | 3 | 3 | ИТ |
| 1004 | Николаев Н.Н. | 3 | 3 | ИТ |
| ID | Name | DepartmentID | ID | Name |
|---|---|---|---|---|
| 1000 | Иванов И.И. | 1 | 1 | Администрация |
| 1001 | Петров П.П. | 3 | 3 | ИТ |
| 1002 | Сидоров С.С. | 2 | 2 | Бухгалтерия |
| 1003 | Андреев А.А. | 3 | 3 | ИТ |
| 1004 | Николаев Н.Н. | 3 | 3 | ИТ |
| 1005 | Александров А.А. | NULL | NULL | NULL |
| ID | Name | DepartmentID | ID | Name |
|---|---|---|---|---|
| 1000 | Иванов И.И. | 1 | 1 | Администрация |
| 1002 | Сидоров С.С. | 2 | 2 | Бухгалтерия |
| 1001 | Петров П.П. | 3 | 3 | ИТ |
| 1003 | Андреев А.А. | 3 | 3 | ИТ |
| 1004 | Николаев Н.Н. | 3 | 3 | ИТ |
| NULL | NULL | NULL | 4 | Маркетинг и реклама |
| NULL | NULL | NULL | 5 | Логистика |
| ID | Name | DepartmentID | ID | Name |
|---|---|---|---|---|
| 1000 | Иванов И.И. | 1 | 1 | Администрация |
| 1001 | Петров П.П. | 3 | 3 | ИТ |
| 1002 | Сидоров С.С. | 2 | 2 | Бухгалтерия |
| 1003 | Андреев А.А. | 3 | 3 | ИТ |
| 1004 | Николаев Н.Н. | 3 | 3 | ИТ |
| 1005 | Александров А.А. | NULL | NULL | NULL |
| NULL | NULL | NULL | 4 | Маркетинг и реклама |
| NULL | NULL | NULL | 5 | Логистика |
| ID | Name | DepartmentID | ID | Name |
|---|---|---|---|---|
| 1000 | Иванов И.И. | 1 | 1 | Администрация |
| 1001 | Петров П.П. | 3 | 1 | Администрация |
| 1002 | Сидоров С.С. | 2 | 1 | Администрация |
| 1003 | Андреев А.А. | 3 | 1 | Администрация |
| 1004 | Николаев Н.Н. | 3 | 1 | Администрация |
| 1005 | Александров А.А. | NULL | 1 | Администрация |
| 1000 | Иванов И.И. | 1 | 2 | Бухгалтерия |
| 1001 | Петров П.П. | 3 | 2 | Бухгалтерия |
| 1002 | Сидоров С.С. | 2 | 2 | Бухгалтерия |
| 1003 | Андреев А.А. | 3 | 2 | Бухгалтерия |
| 1004 | Николаев Н.Н. | 3 | 2 | Бухгалтерия |
| 1005 | Александров А.А. | NULL | 2 | Бухгалтерия |
| 1000 | Иванов И.И. | 1 | 3 | ИТ |
| 1001 | Петров П.П. | 3 | 3 | ИТ |
| 1002 | Сидоров С.С. | 2 | 3 | ИТ |
| 1003 | Андреев А.А. | 3 | 3 | ИТ |
| 1004 | Николаев Н.Н. | 3 | 3 | ИТ |
| 1005 | Александров А.А. | NULL | 3 | ИТ |
| 1000 | Иванов И.И. | 1 | 4 | Маркетинг и реклама |
| 1001 | Петров П.П. | 3 | 4 | Маркетинг и реклама |
| 1002 | Сидоров С.С. | 2 | 4 | Маркетинг и реклама |
| 1003 | Андреев А.А. | 3 | 4 | Маркетинг и реклама |
| 1004 | Николаев Н.Н. | 3 | 4 | Маркетинг и реклама |
| 1005 | Александров А.А. | NULL | 4 | Маркетинг и реклама |
| 1000 | Иванов И.И. | 1 | 5 | Логистика |
| 1001 | Петров П.П. | 3 | 5 | Логистика |
| 1002 | Сидоров С.С. | 2 | 5 | Логистика |
| 1003 | Андреев А.А. | 3 | 5 | Логистика |
| 1004 | Николаев Н.Н. | 3 | 5 | Логистика |
| 1005 | Александров А.А. | NULL | 5 | Логистика |
SQL syntax used
User’s queries are executed by SQL server that brings some limitations to the
syntax of SQL statements. Now we use Microsoft SQL Server 2019 (15.0) on the rating stages,
and MariaDB-10.4 (compatible with MySQL 8), PostgreSQL 12.3, and Oracle Database 11g on the learn stage in addition. That is why You should follow
the syntax of these realizations when building queries. It should be noted that SQL syntax,
implemented in Microsoft SQL Server, is close to that of SQL-92 standard. But there are some
distinctions, among them is absence of NATURAL JOIN of tables. Supplied help on SQL Data Manipulation Language is held in accordance
with SQL-92 standard and contains information necessary for solving the exercises. In the same place it is possible to find features of used realization (SQL Server 2005).
Самостоятельная работа для закрепления материала
| LCode | LDescr |
|---|---|
| 1 | L-1 |
| 2 | L-2a |
| 2 | L-2b |
| 3 | L-3 |
| 5 | L-5 |
| RCode | RDescr |
|---|---|
| 2 | B-2a |
| 2 | B-2b |
| 3 | B-3 |
| 4 | B-4 |
| LCode | LDescr | RCode | RDescr |
|---|---|---|---|
| 2 | L-2a | 2 | B-2a |
| 2 | L-2a | 2 | B-2b |
| 2 | L-2b | 2 | B-2a |
| 2 | L-2b | 2 | B-2b |
| 3 | L-3 | 3 | B-3 |
| LCode | LDescr | RCode | RDescr |
|---|---|---|---|
| 1 | L-1 | NULL | NULL |
| 2 | L-2a | 2 | B-2a |
| 2 | L-2a | 2 | B-2b |
| 2 | L-2b | 2 | B-2a |
| 2 | L-2b | 2 | B-2b |
| 3 | L-3 | 3 | B-3 |
| 5 | L-5 | NULL | NULL |
| LCode | LDescr | RCode | RDescr |
|---|---|---|---|
| 2 | L-2a | 2 | B-2a |
| 2 | L-2b | 2 | B-2a |
| 2 | L-2a | 2 | B-2b |
| 2 | L-2b | 2 | B-2b |
| 3 | L-3 | 3 | B-3 |
| NULL | NULL | 4 | B-4 |
| LCode | LDescr | RCode | RDescr |
|---|---|---|---|
| 1 | L-1 | NULL | NULL |
| 2 | L-2a | 2 | B-2a |
| 2 | L-2a | 2 | B-2b |
| 2 | L-2b | 2 | B-2a |
| 2 | L-2b | 2 | B-2b |
| 3 | L-3 | 3 | B-3 |
| 5 | L-5 | NULL | NULL |
| NULL | NULL | 4 | B-4 |
| LCode | LDescr | RCode | RDescr |
|---|---|---|---|
| 1 | L-1 | 2 | B-2a |
| 2 | L-2a | 2 | B-2a |
| 2 | L-2b | 2 | B-2a |
| 3 | L-3 | 2 | B-2a |
| 5 | L-5 | 2 | B-2a |
| 1 | L-1 | 2 | B-2b |
| 2 | L-2a | 2 | B-2b |
| 2 | L-2b | 2 | B-2b |
| 3 | L-3 | 2 | B-2b |
| 5 | L-5 | 2 | B-2b |
| 1 | L-1 | 3 | B-3 |
| 2 | L-2a | 3 | B-3 |
| 2 | L-2b | 3 | B-3 |
| 3 | L-3 | 3 | B-3 |
| 5 | L-5 | 3 | B-3 |
| 1 | L-1 | 4 | B-4 |
| 2 | L-2a | 4 | B-4 |
| 2 | L-2b | 4 | B-4 |
| 3 | L-3 | 4 | B-4 |
| 5 | L-5 | 4 | B-4 |
Подход к решению
Чем мне самому очень импонирует эта задача, так это тем, что для её решения не нужны никакие специальные знания алгоритмов или ещё чего бы то ни было специфического из компьютерных наук. Ну, то есть нужно уметь программировать вообще, полезно знать, что такое рекурсия и т.п. По моему это всё сейчас ещё в школе проходят. Но задача распадается на несколько шагов, на каждом из которых есть свои нюансы. В результате и задачка получилась не слишком сложная, но и не сказать чтобы тривиальная.
Единственное действительно нетривиальное, что тут есть — это представить себе, как можно сгенерировать матрицу календаря, когда ширина и высота задаются параметрами. С высотой всё совсем просто — все диалекты SQL так или иначе позволяют генерировать запросами заданное параметром количество записей. Обычно это рекурсивные запросы, хотя иногда и попадаются специальные конструкции. Например, в том же PostgreSQL нашлась специальная конструкция , которая генерирует серию значений от MIN до MAX. Можно воспользоваться и «классическим» рекурсивным запросом вида:
, но специальная конструкция будет короче. Так можно получить нужное количество строк.
Теперь определимся с тем, как сгенерировать количество столбцов, заданных параметром. В принципе всё так же, как и со строками выше, можно сгенерировать необходимое количество записей. А потом, когда их надо будет вывести, сгруппируем и склеим эти записи агрегатной функцией для работы со строками. В PostgreQSL для этого нашлась подходящая функция :
С помощью этой техники мы сгенерируем матрицу-заготовку для календаря, в которой по горизонтали и вертикали будет нужное количество месяцев, заданное в параметрах. Каждый месяц будем представлять в виде знакомест в 6 строк по 7 столбцов для каждого из дней месяца, вот так:
Назовём каждую такую конструкцию месяцеместом, так дальше будет удобно ссылаться. По столбцам месяцеместа мы потом будем расставлять числа в соответствии с днями недели. А по строкам в соответствии с номером недели в месяце — сначала первую строчку заполнять, потом вторую и так далее. Столбцов нужно семь по количеству дней недели, строк пусть будет шесть. Больше шести недель в месяце никак не может быть. Я конечно имею в виду земной Григорианский календарь. Просьба жителям других планет отнестись с пониманием, эта задача была придумана в первую очередь для землян XXI века (013 в тентуре, налево от БМ).
Теперь, когда с самым нетривиальным стало всё понятно, займёмся остальными техническими деталями. Нам нужно будет сгенерировать все дни года, чтобы потом их расставить в полученную выше матрицу. Тут закавыка может быть с тем, чтобы правильно определить это количество дней. Например, с учётом того, что год может быть високосный. Или вот в земном Григорианском календаре в 1582 году отсутствуют дни с 05 по 14 октября (и Oracle честно это показывает!). Так что дни нужного года будем получать так: все дни с первого дня года, указанного в параметрах, и до (но не включая) первого дня следующего года.
И последним шагом нам нужно будет аккуратно собрать всё вместе, добавить названия месяцев и номер года сверху, убрать пустые строчки и т.п.
Итого по порядку:
- Сгенерировать все дни года.
- Сгенерировать матрицу-заготовку с нужным количеством строк и столбцов.
- Вписать дни года в матрицу календаря на нужные места.
- Собрать всё вместе для вывода результатов.
Practical skills of SQL language
This site will help everyone to gain or improve skills in building
SQL Data Manipulation Language statements. To train You will have to build yourself
the SQL statements for retrieval or modification of specific data required in the exercises.
When Your query is incorrect, You will be able to see rows returned by the correct
query along with that returned by Your query. Furthermore, you may execute arbitrary
DML statements on available databases by setting the «Without checking» option. There are
five levels of difficulty (from 1 to 5), You may see it in second column of
exercises list. We propose the exercises on retrieving data (SELECT statement) and
the exercises on modifying data (INSERT, UPDATE, DELETE, and MERGE statements). Your success in the solving the exercises are shown by a rating of participants.
As this takes place, there are three stages:
the first one (first 6 exercises) is performed without time control for an individual
exercise, the second one (begins with the exercise #7) controls time for completion of each
task. At the third stage which refers to optimizing and begins with exercise #139, it is required not only to solve an exercise correctly, but also time of execution of inquiry should be commensurable with time of execution of the author’s solution.
Exercises of the first stage are available without registration and may be solved
in any order You like. The solution of the rest of exercises requires registration.
REGISTRATION IS FREE as this for all other services of the site. In the third column of exercises list You
will be able to see («OK») notes with the numbers of done exercises, but that
is available only to the registered users. In fact, that is the main reason for registration.
If You would like to visit our web site again, You won’t have to recollect which
exercises You have done already and which You haven’t. If You don’t want to register,
You may enter as a guest, but in that case Your results won’t
be traced by the system. Registered users also may discuss the solutions to exercises in our forum.
NOTE: The query stated incorrectly may return the «correct» data on a current state of database.
For this reason You should not be surprised if the results of incorrect query are
coincide with the results of right one with Your query is estimated as incorrect by the Verifying system.
NOTE: Your browser should support Cookies and Javascript to provide correct usage of this site. If you use content filter, it should allow opening child windows to explore help pages.
Операции записи
Большинство операций записи в базе данных довольно просты, если сравнивать с более сложными операциями чтения.
7.1 Update
Синтаксис запроса семантически совпадает с запросом на чтение. Единственное отличие в том, что вместо выбора колонок ‘ом, мы задаем знаения ‘ом.
Если все книги Дэна Брауна потерялись, то нужно обнулить значение количества. Запрос для этого будет таким:
делает то же самое, что раньше: выбирает строки. Вместо , который использовался при чтении, мы теперь используем . Однако, теперь нужно указать не только имя колонки, но и новое значение для этой колонки в выбранных строках.
7.2 Delete
Запрос это просто запрос или без названий колонок. Серьезно. Как и в случае с и , блок остается таким же: он выбирает строки, которые нужно удалить. Операция удаления уничтожает всю строку, так что не имеет смысла указывать отдельные колонки. Так что, если мы решим не обнулять количество книг Дэна Брауна, а вообще удалить все записи, то можно сделать такой запрос:
7.3 Insert
Пожалуй, единственное, что отличается от других типов запросов, это . Формат такой:
Где , , это названия колонок, а , и это значения, которые нужно вставить в эти колонки, в том же порядке. Вот, в принципе, и все.
Взглянем на конкретный пример. Вот запрос с , который заполняет всю таблицу «books»:
Practical skills of SQL language
This site will help everyone to gain or improve skills in building
SQL Data Manipulation Language statements. To train You will have to build yourself
the SQL statements for retrieval or modification of specific data required in the exercises.
When Your query is incorrect, You will be able to see rows returned by the correct
query along with that returned by Your query. Furthermore, you may execute arbitrary
DML statements on available databases by setting the «Without checking» option. There are
five levels of difficulty (from 1 to 5), You may see it in second column of
exercises list. We propose the exercises on retrieving data (SELECT statement) and
the exercises on modifying data (INSERT, UPDATE, DELETE, and MERGE statements). Your success in the solving the exercises are shown by a rating of participants.
As this takes place, there are three stages:
the first one (first 6 exercises) is performed without time control for an individual
exercise, the second one (begins with the exercise #7) controls time for completion of each
task. At the third stage which refers to optimizing and begins with exercise #139, it is required not only to solve an exercise correctly, but also time of execution of inquiry should be commensurable with time of execution of the author’s solution.
Exercises of the first stage are available without registration and may be solved
in any order You like. The solution of the rest of exercises requires registration.
REGISTRATION IS FREE as this for all other services of the site. In the third column of exercises list You
will be able to see («OK») notes with the numbers of done exercises, but that
is available only to the registered users. In fact, that is the main reason for registration.
If You would like to visit our web site again, You won’t have to recollect which
exercises You have done already and which You haven’t. If You don’t want to register,
You may enter as a guest, but in that case Your results won’t
be traced by the system. Registered users also may discuss the solutions to exercises in our forum.
NOTE: The query stated incorrectly may return the «correct» data on a current state of database.
For this reason You should not be surprised if the results of incorrect query are
coincide with the results of right one with Your query is estimated as incorrect by the Verifying system.
NOTE: Your browser should support Cookies and Javascript to provide correct usage of this site. If you use content filter, it should allow opening child windows to explore help pages.
Practical skills of SQL language
This site will help everyone to gain or improve skills in building
SQL Data Manipulation Language statements. To train You will have to build yourself
the SQL statements for retrieval or modification of specific data required in the exercises.
When Your query is incorrect, You will be able to see rows returned by the correct
query along with that returned by Your query. Furthermore, you may execute arbitrary
DML statements on available databases by setting the «Without checking» option. There are
five levels of difficulty (from 1 to 5), You may see it in second column of
exercises list. We propose the exercises on retrieving data (SELECT statement) and
the exercises on modifying data (INSERT, UPDATE, DELETE, and MERGE statements). Your success in the solving the exercises are shown by a rating of participants.
As this takes place, there are three stages:
the first one (first 6 exercises) is performed without time control for an individual
exercise, the second one (begins with the exercise #7) controls time for completion of each
task. At the third stage which refers to optimizing and begins with exercise #139, it is required not only to solve an exercise correctly, but also time of execution of inquiry should be commensurable with time of execution of the author’s solution.
Exercises of the first stage are available without registration and may be solved
in any order You like. The solution of the rest of exercises requires registration.
REGISTRATION IS FREE as this for all other services of the site. In the third column of exercises list You
will be able to see («OK») notes with the numbers of done exercises, but that
is available only to the registered users. In fact, that is the main reason for registration.
If You would like to visit our web site again, You won’t have to recollect which
exercises You have done already and which You haven’t. If You don’t want to register,
You may enter as a guest, but in that case Your results won’t
be traced by the system. Registered users also may discuss the solutions to exercises in our forum.
NOTE: The query stated incorrectly may return the «correct» data on a current state of database.
For this reason You should not be surprised if the results of incorrect query are
coincide with the results of right one with Your query is estimated as incorrect by the Verifying system.
NOTE: Your browser should support Cookies and Javascript to provide correct usage of this site. If you use content filter, it should allow opening child windows to explore help pages.
SQL syntax used
User’s queries are executed by SQL server that brings some limitations to the
syntax of SQL statements. Now we use Microsoft SQL Server 2019 (15.0) on the rating stages,
and MariaDB-10.4 (compatible with MySQL 8), PostgreSQL 12.3, and Oracle Database 11g on the learn stage in addition. That is why You should follow
the syntax of these realizations when building queries. It should be noted that SQL syntax,
implemented in Microsoft SQL Server, is close to that of SQL-92 standard. But there are some
distinctions, among them is absence of NATURAL JOIN of tables. Supplied help on SQL Data Manipulation Language is held in accordance
with SQL-92 standard and contains information necessary for solving the exercises. In the same place it is possible to find features of used realization (SQL Server 2005).
Операции над множествами
Реляционная алгебра является расширением классического набора операторов над множествами (отношение — это множество упорядоченных кортежей; заметьте, что это совсем не равно упорядоченному множеству кортежей). Пусть у нас есть таблица StudentMark(Name, Mark, Subject, Date) – тогда кортеж (Вася, 5, Информатика, 05.10.2010) является упорядоченным – сначала строка Name на первой (ок, или нулевой) позиции, целое число на второй, строка на третьей и дата на четвертой. При этом сами упорядоченные кортежи (Name, Mark, Subject, Date) не упорядочены «внутри» отношения.
— объединение всех строк в A и B, ограничение — одинаковые аттрибуты
— пересечение строк, такое же ограничение
— B минус A, все строки, что присутствуют в B, но не присутствуют в A, такое же ограничение
(B\A; A — слева, B — справа)
Вспомогательные операторы
— join (соединение); join соединяет две записи таблиц A и B, при условии, что для этих двух записей выполнено условие φ.
Выводы
Главный и основной, который я старался донести до аудитории: нет ничего сверхсложного в решении подобных задач.
Что можно сказать про PostgreSQL в сравнении с Oracle по итогам данной задачи. Функционально всё соответствует, выразить можно приблизительно то же и приблизительно так же. Некоторые нюансы удобнее в одном диалекте, некоторые в другом. Функции отличаются, но на то и документация нам дана. Есть ли в чём-то существенная разница? Да, есть. На примере данной задачи я вижу по крайней мере в двух местах.
Во-первых, Oracle поддерживает локали, и в разных локалях неделя может начинаться с разных дней недели. Например, в большей части Европы неделя начинается понедельником, а в США с воскресенья. В PostgreSQL нет настроек локали для первого дня недели и невозможно сгенерировать календарь так, чтобы он начинался с привычного пользователю дня.
Во-вторых, отличается поддержка преобразования дат и работа с календарём. В Oracle за 04 октября 1582 года идёт 15 октября (как и было определено при вводе Григорианского календаря), в PostgreSQL есть 05 и все остальные числа октября 1582 года. Вопрос не так прост, как может показаться сходу, в документации PostgreSQL есть даже специальный раздел, объясняющий проблему и почему в PostgreSQL она решена таким образом. Но факт остаётся фактом: календари в Oracle и PostgreSQL разные, хоть оба и Григорианские, и, соответственно, логика работы с датами существенно отличается
Это может быть важно при портировании
