Ответы на вопросы на собеседование SQL (часть 2).

• Что делает функция 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 нельзя использовать таким образом:
В данном случае больше подходит HAVING:
То есть, использовать 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 запись.