Этот пост — мои заметки для https://courses.edx.org/courses/course-v1:Microsoft+DAT201x+2015_T4
Исходные файлы учебных материалов можно найти https://github. com/MicrosoftLearning/QueryingT-SQL
Я опускаю модули 1 и 2 из своих заметок.

Модуль 3. Запросы к нескольким таблицам с помощью объединений

Модуль 4. Использование операторов множества

  • UNION возвращает результирующий набор различных строк, объединенных из всех операторов.
  • UNION удаляет дубликаты при обработке запроса (влияет на производительность).
  • UNION ALL сохраняет дубликаты во время обработки запроса.
  • INTERSECT, чтобы возвращались только те строки, которые возвращаются обоими запросами.
  • ЗА ИСКЛЮЧЕНИЕМ возврата строк из первого запроса, которые не возвращаются вторым запросом.

Модуль 5. Использование функций и агрегирование данных

  • Скалярные функции возвращают одно значение на основе нуля или более входных параметров. например ЛТРИМ(…)
  • Логические функции возвращают логические значения (true или false) на основе выражения или значения столбца. например ISNUMERIC(…), IIF(…) и ВЫБЕРИТЕ(…). Функция ВЫБОР возвращает значение в порядковой позиции, начинающейся с 1.
  • Оконные функции используются для ранжирования строк по разделам или «окнам». К оконным функциям относятся RANK, DENSE_RANK, NTILE и ROW_NUMBER.
    Слово "окно" здесь относится к набору строк.
  • Агрегатные функции используются для предоставления сводных значений для нескольких строк — например, общей стоимости продуктов или максимального количества товаров в заказе. Обычно используемые агрегатные функции включают SUM, COUNT, MIN, MAX, AVG, COUNT_BIG, GROUPING_ID и STDEV.
  • GROUP BY с агрегатными функциями для возврата агрегатов, сгруппированных по одному или нескольким столбцам или выражениям.
  • Все столбцы в предложении SELECT, которые не являются выражениями агрегатных функций, должны быть включены в предложение GROUP BY.
  • Порядок, в котором столбцы или выражения перечислены в предложении GROUP BY, определяет иерархию группировки.
  • Предложение HAVING для фильтрации групп, включенных в результаты запроса. Предложение WHERE вступает в действие перед предложением HAVING. Предложение WHERE исключает ненужные строки, а предложение HAVING удаляет ненужные группы.

Для документации обратитесь к https://msdn.microsoft.com/en-us/library/ms174318.aspx

Модуль 6. Использование подзапросов и APPLY

  • Скалярные подзапросы возвращают одно значение. например ГДЕ customerId = (ВЫБЕРИТЕ MAX(customerId) FROM…)
  • Многозначные подзапросы возвращают набор строк из одного столбца. например ГДЕ customerId IN (ВЫБЕРИТЕ customerId из…)
  • Автономные запросы не связаны с внешним запросом и только передают значения внешнему запросу. Например, скалярные подзапросы и многозначные подзапросы.
  • Коррелированные подзапросы ссылаются на объекты во внешнем запросе. См. документацию https://technet.microsoft.com/en-us/library/ms187638
  • Оператор APPLY позволяет выполнять функцию с табличным значением для каждой строки в наборе строк, возвращаемом оператором SELECT. Концептуально этот подход аналогичен коррелированному подзапросу.
  • CROSS APPLY возвращает совпадающие строки, подобно внутреннему соединению. OUTER APPLY возвращает все строки исходных результатов запроса SELECT со значениями NULL для строк, в которых не было найдено совпадений.

Модуль 7. Использование табличных выражений

  • Представления — это объекты базы данных, которые инкапсулируют запросы SELECT.
  • Временные таблицы имеют префикс # и хранятся во временной рабочей области (базе данных tempdb в SQL Server). Временные таблицы автоматически удаляются по окончании сеанса, в котором они были созданы. Чрезмерное использование временных таблиц может отрицательно сказаться на общей производительности сервера баз данных. В то время как глобальные временные таблицы создаются с символом ##.
  • Табличные переменные имеют префикс @ и хранятся в памяти. Табличные переменные привязаны к пакету, в котором они созданы. Табличные переменные лучше всего работают с небольшими наборами данных. например DECLARE @varProduct таблица (…)
  • Табличные функции (TVF) — это функции, которые возвращают таблицу набора строк. например CREATE FUNCTION …(…params) ВОЗВРАЩАЕТ ТАБЛИЦУ КАК…
  • Производная таблица — это подзапрос, который генерирует набор строк из нескольких столбцов. Производная таблица должна иметь AS, например. SELECT … FROM (SELECT … ) AS tmp WHERE …
    Или это может быть SELECT … FROM (SELECT…) AS tmp(output1, output2…) WHERE…
  • Общие табличные выражения (CTE) обеспечивают более интуитивно понятный синтаксис или определяют наборы строк, чем производные таблицы, и могут использоваться несколько раз в одном запросе. CTE можно использовать для определения рекурсивных запросов.
  • Для выполнения рекурсии используйте общие табличные выражения (CTE). И если нам нужно выполнять рекурсивные запросы, CTE — это то, что нужно.

Модуль 8. Группировка наборов и сводные данные

  • НАБОРЫ ГРУППИРОВКИ для определения пользовательских группировок.
  • ROLLUP для включения промежуточных и общих итогов для иерархических группировок.
  • CUBE, чтобы включить все возможные группировки.
  • Функция GROUPING_ID указывает, использовался ли указанный столбец или выражение для создания строки. например ВЫБЕРИТЕ GROUPING_ID(…) ИЗ…

  • PIVOT для переориентации набора строк путем создания нескольких столбцов из значений в одном столбце.
  • UNPIVOT для переориентации нескольких столбцов в существующем наборе строк в один столбец.

Дополнительные ресурсы

  • Предложение WITH — это способ написания подзапросов, делающий весь оператор более читабельным.