SQL¶

Вопросы¶

1. Что такое SQL?
2. Какие есть типы JOIN'ов. Кратко опишите каждый из типов.
3. Что такое LEFT JOIN, RIGHT JOIN? Чем они отличаются?
4. Для чего используется слово HAVING?
5. Что такое DDL?
6. Что такое DML?
7. Что такое TCL?
8. Что такое DCL?
9. Какой общий синтаксис команди SELECT?
10. Про NULL в SQL.
11. Если вы собираетесь соединить несколько таблиц в запросе (например, n таблиц), сколько условий соединения вам нужно использовать?
12. Какое практическое применение временных таблиц?
13. Как оператор GROUP BY обрабатывает значение NULL? Ли это общем трактовке таких значений?
14. В чем разница между COUNT (*) и COUNT (столбец)?
15. В чем разница между операторами DISTINCT и GROUP BY?
16. Есть таблица table1 с колонками id и datetime написать запрос который вернет максимальное значение id и значение даты для этого id.
17. Для чего нужны операторы UNION, INTERSECT, EXCEPT?
18. Что лучше использовать соединение или подзапросы?
19. Что делает функция EXISTS?
20. Использование оператора PIVOT.
21. Опишите разницу типов данных DATETIME и TIMESTAMP.
22. Для каких числовых типов недопустимо использовать операцию сложения (вычитания), а значит и функцию SUM?
23. Что такое хранимые процедуры?
24. Функции ранжирования что это и какие существует?
25. Может ли значение в столбце(ах), на который наложено ограничение foreign key, равняться null?
26. Назовите основные свойства транзакции.
27. Как удалить повторяющиеся строки с использованием ключевого слова Distinct?
28. Когда полное сканирование таблицы выгоднее доступа по индексу? Опишите вкратце общие принципы, как оптимизатор выбирает производить ли полное сканирование таблицы или доступ по индексу.
29. Имеет ли смысл индексировать поля таблицы, имеющих тип boolean или подобные им - с небольшим количеством возможных значений?
30. Что такое агрегатная функция? Приведите примеры агрегатных функций в SQL.
31. Дайте определение третьей нормальной форме БД.
32. Что такое денормализация БД? Для чего она нужна?
33. Что такое триггер?
34. Что такое курсоры в базах данных?
35. Какие компромиссы предлагает использование индексов?
36. Что делает SQL операция MERGE?
37. В чем различие между выражениями HAVING и WHERE?
38. Что такое целостность данных? Объясните, что такое ограничения.
39. В чем отличие между кластерными индексами и некластерными?
40. Какие отличия между ограничениями primary и unique?

Ответы¶

Что такое SQL?¶

SQL (structured query language - "язык структурированных запросов") - формальный непроцедурный язык программирования, применяемый для создания, модификации и управления данными в произвольной
реляционной базе данных, управляемой соответствующей системой управления базами данных (СУБД). SQL основывается на исчислении кортежей.

Какие есть типы JOIN'ов. Кратко опишите каждый из типов.¶

JOIN - внутреннее соединение. В результирующем наборе присутствуют только записи, значения связанных полей в которых совпадают.
LEFT JOIN - левое внешнее соединение. В результирующем наборе присутствуют все записи из Table1 и соответствующие им записи из Table2. Если соответствия нет, поля из Table2 будут пустыми.
RIGHT JOIN - правое внешнее соединение. В результирующем наборе присутствуют все записи из Table2 и соответствующие им записи из Table1. Если соответствия нет, поля из Table1 будут пустыми.
FULL JOIN - полное внешнее соединение. Комбинация двух предыдущих. В результирующем наборе присутствуют все записи из Table1 и соответствующие им записи из Table2. Если соответствия нет - поля из Table2 будут пустыми. Записи из Table2, которым не нашлось пары в Table1, тоже будут присутствовать в результирующем наборе. В этом случае поля из Table1 будут пустыми.
CROSS JOIN - Cartesian product. Результирующий набор содержит все варианты комбинации строк из Table1 и Table2. Условие соединения при этом не указывается.

Что такое LEFT JOIN, RIGHT JOIN? Чем они отличаются?¶

Проиллюстрируем каждый тип примерами. Модель данных:

SELECT Table1.Field1, Table2.Field2 FROM Table1 LEFT JOIN Table2 ON Table1.Key1 = Table2.Key2

SELECT Table1.Field1, Table2.Field2 FROM Table1 RIGHT JOIN Table2 ON Table1.Key1 = Table2.Key2

LEFT JOIN - левое внешнее соединение. В результирующем наборе присутствуют все записи из Table1 и соответствующие им записи из Table2. Если соответствия нет, поля из Table2 будут пустыми.
RIGHT JOIN - правое внешнее соединение. В результирующем наборе присутствуют все записи из Table2 и соответствующие им записи из Table1.

Если соответствия нет, поля из Table1 будут пустыми.

Для чего используется слово HAVING?¶

Секция HAVING определяет условие, которое затем применяется к групам строк. Следовательно, это предложение имеет тот же смысл для группы строк, что и предложение WHERE в отношении соодержимого соответствующей таблицы. Синтаксис предложения HAVING HAVING condition где condition содержит агрегатные функции или константы. Важно понимать, что секции HAVING и WHERE взаимно дополняют друг друга. Сначала с помощью ограничений WHERE формируется итоговая выборка, затем выполняется разбивка на группы по значениям полей, заданных в GROUP BY. Далее по каждой группе вычисляется групповая функция и в заключение накладывается условие HAVING.

Пример:

SELECT DeptNum, MAX(SALARY) FROM Employees GROUP BY DeptNum HAVING MAX(SALARY) > 1000

В приведенном примере в результат попадут только отделы, максимальная зарплата в которых превышает 1000.

Что такое DDL?¶

DDL - Команды определения структуры данных. В состав DDL-группы входят команды, позволяющие определять внутреннюю структуру базы данных. Перед тем, как сохранять данные в БД, необходимо создать в ней таблицы и, возможно, некоторые другие сопутствующие объекты

Пример некоторых DDL-команд:

Что такое DML?¶

DML - Команды манипулирования данными. DMLгруппа содержит команды, позволяющие вносить, изменять, удалять и извлекать данные из таблиц.

Примеры DML-команд:

Что такое TCL?¶

TCL - TCL-команды используются для управления изменениями данных, производимыми DML-командами. С их помощью несколько DML-команд могут быть объединены в единое логическое целое, называемое транзакцией. При этом все команды на изменение данных в рамках одной транзакции либо завершаются успешно, либо все могут быть отменены в случае возникновения каких-либо проблем с выполнением любой из них.

TCL-команды:

Что такое DCL?¶

DCL - Команды управления доступом. DCL-команды управляют доступом пользователей к БД и отдельным объектам:

Какой общий синтаксис команди SELECT?¶

В общем виде синтаксис команды SELECT выглядит следующим образом:

В квадратных скобках указаны необязательные элементы команды. Ключевые слова SELECT и FROM должны присутствовать всегда.

Про NULL в SQL.¶

Необходимо отметить, что язык SQL, в отличие от языков программирования, имеет встроенные средства поддержки факта отсутствия каких-либо данных. Осуществляется это с помощью NULL-концепции. NULL не является каким-то фиксированным значением, хранящимся в поле записи вместо реальных данных. Значение NULL не имеет определенного типа. NULL — это индикатор, говорящий пользователю (и SQL) о том, что данные в поле записи отсутствуют. Поэтому его нельзя использовать в операциях сравнения. Для проверки факта наличия-отсутствия данных в SQL введены специальные выражения.

Если вы собираетесь соединить несколько таблиц в запросе (например, n таблиц), сколько условий соединения вам нужно использовать?¶

Тогда нужно использовать n-1 условий соединения чтоб исключить декартовой соединения, может быть и такое, что потребуется больше чем n-1 условий соединения, и совсем другие условия соединения для дальнейшего сокращения результирующего набора данных.

Какое практическое применение временных таблиц?¶

Временная таблица - это объект базы данных, который хранится и управляется системой базы данных на временной основе. Они могут быть локальными или глобальными. используется для сохранения результатов
вызова хранимой процедуры, уменьшение числа строк при соединениях агрегирование данных из различных источников или замена курсоров и параметризованных представлений.

Как оператор GROUP BY обрабатывает значение NULL? Ли это общем трактовке таких значений?¶

При использовании GROUP BY все значения NULL считаются равными. Значение NULL - это специальное значение, которое можно присвоить ячейке таблицы. Это значение обычно применяется, когда информация в ячейке неизвестна или неприемлема.

В чем разница между COUNT (*) и COUNT (столбец)?¶

форма COUNT (столбец) подсчитывает количество значений в "столбец". При подсчете количества значений столбца эта форма функции COUNT не принимает во внимание значение NULL. функция COUNT (*) подсчитывает количество строк в таблице, не игнорирует значение NULL, поскольку эта функция оперирует строками, а не столбцами.

В чем разница между операторами DISTINCT и GROUP BY?¶

DISTINCT - указывает, что для вычислений используются только уникальные значения столбца. NULL считается как отдельное значение. Если нужно удалить только дубликаты лучше использовать DISTINCT. GROUP BY группирует выбранный набор строк для получения набора сводных строк по значениям одного или нескольких столбцов или выражений. GROUP BY создает отдельную группу для всех возможных значений (включая значение NULL). GROUP BY лучше использовать для определения групп выходных строк, к которым могут применяться агрегатные функции (COUNT, MIN, MAX, AVG и SUM).

Есть таблица table1 с колонками id и datetime написать запрос который вернет максимальное значение id и значение даты для этого id.¶

Есть множество решений - самое простое - это получать max значение для id и потом вывести значения:

SELECT id,datetime FROM table1 WHERE id = (SELECT max(id) FROM table1);

или

SELECT id,datetime FROM table1 WHERE id in (SELECT max(id) FROM table1);

можно и так (mysql)

SELECT id,datetime FROM table1 ORDER BY id DESC LIMIT 1;

Для чего нужны операторы UNION, INTERSECT, EXCEPT?¶

Оператор UNION - применяется для объединения результатов двух SQLзапросов в единую таблицу, состоящую из похожих срок. Оба запроса Должны возвращать одинаковое число столбцов и совместимые типы данных в соответствующих столбцах.

Оператор INTERSECT - используется для нахождения пересечения двух множеств. Результатом его выполнения будет множество строк, которые присутствуют в обоих множествах.

Оператор EXCEPT - используется для нахождения разности двух множеств. Результатом выполнения является множество строк из множества 1, которые отсутствуют в множестве 2.

Приоритет выполнения операторов над множествами:

INTERSECT -> EXCEPT -> UNION

Что лучше использовать соединение или подзапросы?¶

Обычно лучше использовать JOIN, поскольку в большинстве случаев он понятен, и лучше оптимизируется с помощью Database Engine. но не всегда. Соединение имеет преимущество над подзапросов в случае, когда список выбора SELECT в запросе содержит столбцы более чем одной таблицы. Подзапросы лучшие тогда, когда нужно вычислять агрегатные значение и использовать их во внешних запросах для сравнений.

Что делает функция EXISTS?¶

Аргументом функции EXISTS есть внутренний запрос. она возвращает истину, если запрос возвращает один или более строк, и возвращает ложь если запрос вернет ноль строк.

Использование оператора PIVOT.¶

Реляционный оператор PIVOT можно использовать для изменения возвращающего табличное значение выражения в другой таблице. Оператор PIVOT разворачивает возвращающее табличное значение выражение, преобразуя уникальные значения одного столбца выражения в несколько выходных столбцов, а также, в случае необходимости, объединяет оставшиеся повторяющиеся значения столбца и отображает их в выходных данных.

Опишите разницу типов данных DATETIME и TIMESTAMP.¶

DATETIME предназначен для хранения целого числа: YYYYMMDDHHMMSS. И это время не зависит от временной зоны настроенной на сервере.Хранит: 8 байт

TIMESTAMP хранит значение равное количеству секунд, прошедших с полуночи 1 января 1970 года по усреднённому времени Гринвича. При получении из базы отображается с учётом часового пояса. Хранит: 4 байта

Для каких числовых типов недопустимо использовать операцию сложения (вычитания), а значит и функцию SUM?¶

В качестве операндов операций сложения и вычитания допустимо любое корректное выражение любого типа данных числовой категории, кроме типа данных bit.

Что такое хранимые процедуры?¶

Хранимая процедура - компилируемый набор SQL-инструкций, являющийся частью базы данных и хранимый на сервере. Есть много общего между ХП и обычными процедурами языков программирования: они могут иметь входные параметры и выходной результат, они могут как выполнять различные численные вычисления, так и выполнять стандартные операции с БД. Как и в процедурах других языков программирования, в них могут быть циклы и ветвления.

Функции ранжирования что это и какие существует?¶

Ранжирующие функции возвращают ранжирующее значение для каждой строки в секции. В зависимости от используемой функции значения некоторых строк могут совпадать. Ранжирующие функции являются недетерминированными. Transact-SQL содержит следующие ранжирующие функции:

• RANK
• NTILE
• DENSE_RANK
• ROW_NUMBER

Может ли значение в столбце(ах), на который наложено ограничение foreign key, равняться null?¶

Может, если на данный столбец не наложено ограничение not null, пример: при построении таблицы дерева файловой системы, где столбец foreign key - ссылка на эту же самую таблицу, на кортеж с информацией о родительской директории, тогда для корневой директории файловой системы в столбце родительской директории будет - null.

Назовите основные свойства транзакции.¶

ACID - atomicity (атомарность), consistency (непротиворечивость), isolation (изолированность), durability (устойчивость).

• Свойство атомарности гарантирует неделимость набора операторов, которые изменяют данные в базе данных и являются частью транзакции. Это означает, что или выполняются все изменения данных в транзакции, или в случае любой ошибки все уже выполненные изменения отменяются.
• Согласованность гарантирует, что транзакция не даст возможности базе данных содержать несогласованные данные. Другими словами, трансформация данных в рамках одной транзакции переводит базу данных из одного согласованного состояния в другое согласованное состояние.
• Свойство изолированности разделяет все одновременно выполняющиеся транзакции. Другими словами, ни одна активная транзакция не может видеть изменения данных, выполненные в параллельной, но не завершенной транзакции. Это означает, что для обеспечения изолированности для некоторых транзакций может быть выполнен откат.
• Устойчивость - после своего завершения транзакция сохраняется в системе, которую ничто не может вернуть в исходное (до начала транзакции) состояние, т.е. происходит фиксация транзакции, означающая,
  что ее действие постоянно даже при сбое системы.

Как удалить повторяющиеся строки с использованием ключевого слова Distinct?¶

SELECT DISTINCT columnsName FROM tableName;

где:

• columnsName - одно или несколько реальных имен столбцов,перечисленных через запятую;
• tableName - имя той таблицы, из которой выбираются эти столбцы.

Если в предложение SELECT DISTINCT включить более одного столбца, то в результате уникальность любой строки будет определяться уникальностью соответствующей комбинации всех значений столбцов, включенных в предложение, на этой самой строке среди аналогичных комбинаций, соответствующих другим строкам.

Несмотря на то что значения null никогда не бывают равны друг другу (поскольку считаются неизвестными), предложение DISTINCT , напротив, считает их дубликатами. Поэтому команда SELECT DISTINCT вернет только одно значение null, независимо от того, сколько значений null она встретит.

Когда полное сканирование таблицы выгоднее доступа по индексу? Опишите вкратце общие принципы, как оптимизатор выбирает производить ли полное сканирование таблицы или доступ по индексу.¶

Полное сканирование производится многоблочным чтением. Сканирование по индексу - одноблочным. Также, при доступе по индексу сначала идет сканирование самого индекса, а затем чтение блоков из таблицы. Число блоков, которые надо при этом прочитать из таблицы зависит от фактора кластеризации. Если суммарная стоимость всех необходимых одноблочных чтений больше стоимости полного сканирования многоблочным чтением, то полное сканирование выгоднее и оно выбирается оптимизатором. Таким образом, полное сканирование выбирается при слабой селективности предикатов зароса и/или слабой кластеризации данных, либо в случае очень маленьких таблиц.

Имеет ли смысл индексировать поля таблицы, имеющих тип boolean или подобные им - с небольшим количеством возможных значений?¶

Индекс по логическим полям имеет смысл, только в случае, если значения ИСТИНА и ЛОЖЬ распределены примерно равномерно по таблице. Предельный случай - две трети и одна треть.

Что такое агрегатная функция? Приведите примеры агрегатных функций в SQL.¶

Агрегатная функция - это функция, которая возвращает одиночное значение на основании множества записей.

Вот список некоторых агрегатных функций SQL:

• COUNT - Возвращает количество строк источника записей
• COUNT - Возвращает количество значений в указанном столбце
• SUM - Возвращает сумму значений в указанном столбце
• AVG - Возвращает среднее значение в указанном столбце
• MIN - Возвращает минимальное значение в указанном столбце
• MAX - Возвращает максимальное значение в указанном столбце

Дайте определение третьей нормальной форме БД.¶

Определение третьей нормальной форме БД.

• Любое поле любой записи хранит только одно значение. (1NF) Например, если в поле хранится список идентификаторов, разделённых запятыми, то это нарушение данного определения.
• Выполняется условие 1NF и любое неключевое поле полностью зависит от ключа. (2NF) Например, у нас есть запись с полями (Идентификатор, Название CD-Диска, Название группы), где ключом является поле «Идентификатор». При этом, очевидно, что поле «Название группы» зависит не только от «Идентификатора» но и от поля «Название CDДиска». Поэтому такая БД не находится во второй нормальной форме.
• Выполняется условие 2NF и нет неключевых полей зависящих от значения других неключевых полей. Например у нас в записи хранятся код региона и его название. Понятно, что название региона зависит от кода, и наоборот, поэтому такая БД не будет находиться в третьей нормальной форме.

Что такое денормализация БД? Для чего она нужна?¶

Денормализация - это процесс осознанного приведения базы данных к виду, в котором она не будет соответствовать правилам нормализации. Обычно это необходимо для повышения производительности и скорости извлечения данных, за счет увеличения избыточности данных. Если приложению необходимо часто выполнять выборки, которые занимают слишком много времени (например, объединение данных из множества таблиц), то следует рассмотреть возможность проведения денормализации Возможное решение следующее: вынести результаты выборки в отдельную таблицу. Это позволит увеличить скорость выполнения запросов, но также означает появление необходимости в постоянном обслуживании этой новой таблицы. Прежде чем приступать к денормализации, необходимо убедится, что ожидаемые результаты оправдывают издержки, с которыми придется столкнуться.

Что такое триггер?¶

Триггер - это SQL процедура, которая срабатывает при каком-нибудь событии (INSERT, DELETE или UPDATE). Триггеры хранятся и управляются СУБД. Триггеры используются для поддержания ссылочной целостности данных в одинаковый манер реагируя на события изменения этих данных. Триггер не может быть вызван или выполнен вручную, СУБД автоматически вызывает его после модификации данных в соответствующей таблице. В этом и есть его отличие от хранимых процедур, которые нужно выполнять вручную вызовом CALL. Также триггер может вызывать другие процедуры. Триггер также может содержать вызовы INSERT, DELETE и UPDATE внутри себя, таким образом вызывая другой триггер. Такие триггеры называются вложенными (nested).

Что такое курсоры в базах данных?¶

Курсор - это объект базы данных, который позволяет приложениям работать с записями "по-одной", а не сразу с множеством, как это делается в обычных
SQL командах.

Порядок работы с курсором такой:

• Определить курсор (DECLARE)
• Открыть курсор (OPEN)
• Получить запись из курсора (FETCH)
• Обработать запись
• Закрыть курсор (CLOSE)

Какие компромиссы предлагает использование индексов?¶

Некоторые из них:

• Более быстрые выборки, но более медленные изменения. (При изменениях тратиться время на перестройку индекса).
• Для хранения индексов необходимо дополнительное дисковое пространство.

Что делает SQL операция MERGE?¶

Операция MERGE официально появилась в стандарте ANSI SQL:2008. Она позволяет одновременно вставлять или изменять записи таблицы согласно критерию. При выполнении критерия строки изменяются, иначе - вставляются. Ее можно заменить последовательным вызовом INSERT и UPDATE. В некоторых базах данных похожая операция называется UPSERT.

В чем различие между выражениями HAVING и WHERE?¶

WHERE - это ограничивающее выражение. Оно выполняется до того, как будет получен результат операции.
HAVING - фильтрующее выражение. Оно применяется к результату операции и выполняется уже после того как этот результат будет получен, в отличии от where.

Выражения WHERE используются вместе с операциями SELECT, UPDATE, DELETE, в то время как HAVING только с SELECT и предложением GROUP BY. Например, WHERE нельзя использовать таким образом:

SELECT name, SUM(salary) FROM Employees WHERE SUM(salary) > 1000 GROUP BY name;

В данном случае больше подходит HAVING:

SELECT name, SUM(salary) FROM Employees GROUP BY name HAVING (salary) > 1000;

То есть, использовать WHERE в запросах с агрегатными функциями нельзя, для этого и был введен HAVING.

Что такое целостность данных? Объясните, что такое ограничения.¶

Целостность данных - важное свойство SQL. При правильном использовании оно обеспечивает корректность и валидность хранимых данных в любой момент времени. Также, с их помощью можно обнаруживать ошибки в приложениях, которые тяжело найти другими способами. Целостность данных поддерживается с помощью ограничений. В SQL стандарта ANSI есть 4 основных ограничения: PRIMARY KEY, CHECK, UNIQUE и FOREIGN KEY. Они не являются обязательными для таблицы.

• PRIMARY KEY - набор полей (1 или более), значения которых образуют уникальную комбинацию и используются для однозначной идентификации записи в таблице. Для таблицы может быть создано только одно такое ограничение. Данное ограничение используется для обеспечения целостности сущности, которая описана таблицей.
• CHECK используется для ограничения множества значений, которые могут быть помещены в данный столбец. Это ограничение используется для обеспечения целостности предметной области, которую описывают таблицы в базе.
• Ограничение UNIQUE обеспечивает отсутствие дубликатов в столбце или наборе столбцов. Разница между PRIMARY KEY и UNIQUE описана в primary и unique ключи
• Ограничение FOREIGN KEY защищает от действий, которые могут нарушить связи между таблицами. FOREIGN KEY в одной таблице указывает на PRIMARY KEY в другой. Поэтому данное ограничение нацелено на то, чтобы не было записей FOREIGN KEY, которым не отвечают записи PRIMARY KEY. Таким образом, FOREIGN KEY поддерживает ссылочную целостность данных.

В чем отличие между кластерными индексами и некластерными?¶

Некластерные индексы создаются СУБД по умолчанию. Данные физически расположены в произвольном порядке, но логически упорядочены согласно индексу. Такой тип индексов подходит для таблиц, где часто изменяются значения.

При кластерном индексировании данные физически упорядочены, что серьезно повышает скорость выборок данных (но только в случае последовательного доступа к данным). Для одной таблицы может быть создан только один кластерный индекс

Какие отличия между ограничениями primary и unique?¶

Ограничения primary и unique призваны обеспечить уникальность значений столбца, на котором они определены. Но по умолчанию ограничение primary создает кластерный индекс на столбце, а unique - некластерный. Другим отличием является то, что primary не разрешает NULL записей, в то время как unique разрешает только одну NULL запись.