Топ-30 вопросов по SQL на технических собеседованиях. Часть 2

Перевод второй части статьи «Top SQL Interview Questions».

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

16. Для чего нужна функция COALESCE?
17. Что вы понимаете под нормализацией и денормализацией?
18. Что не так в приведенном ниже SQL-запросе?
19. Что вы знаете о функции STUFF()?
20. Что такое представления? Приведите пример.
21. Что такое хранимая процедура? Приведите пример.
22. Что вы знаете о JOIN? Расскажите о разных видах этого выражения.
23. Объясните, как работает INNER JOIN.
24. Расскажите, чем отличаются представления и таблицы.
25. Как вы понимаете временные таблицы? Напишите запрос для создания временной таблицы.
26. Объясните разницу между OLTP и OLAP.
27. Как вы понимаете SELF JOIN?
28. В чем разница между UNION и UNION ALL?
29. Для чего используется оператор INTERSECT?
30. Как скопировать данные из одной таблицы в другую?

16. Для чего нужна функция COALESCE?

Функция COALESCE принимает набор входящих данных и возвращает первое не-NULL значение этого набора.

Синтаксис:

COALESCE(val1,val2,val3,……,nth val)

Пример:

SELECT COALESCE(NULL, 1, 2, ‘MYSQL’)

Результат:

1

17. Что вы понимаете под нормализацией и денормализацией?

Нормализация и денормализация это, по сути, два метода, используемые в базах данных.

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

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

18. Что не так в приведенном ниже SQL-запросе?

SELECT gender, AVG(age) FROM employee WHERE AVG(age)>30 GROUP BY gender

При выполнении этой команды мы получим следующую ошибку:

Msg 147, Level 16, State 1, Line 1

Выражение WHERE не может содержать агрегации, если она не входит в подзапрос выражения HAVING или список SELECT (агрегируемый столбец при этом должен быть внешней ссылкой).

Msg 147, Level 16, State 1, Line 1
Invalid column name ‘gender’.

Это означает, что при работе с агрегирующими функциями и использовании GROUP BY мы не можем использовать выражение WHERE. Вместо WHERE следует использовать выражение HAVING. При этом выражение GROUP BY должно идти первым, а HAVING — за ним.

SELECT e_gender, AVG(e_age) FROM employee GROUP BY e_gender HAVING AVG(e_age)>30

Результат:

19. Что вы знаете о функции STUFF()?

Функция STUFF вставляет одну строку в другую, начиная с указанной позиции. При этом в исходной строке, начиная с указанной позиции, удаляются символы в количестве, определенном в параметре Length.

Синтаксис:

STUFF(String1, Position, Length, String2)

Здесь String1 — это строка, которая будет перезаписана. Position определяет стартовую позицию для перезаписи строки. Length — это длина подстроки, которая будет удалена из исходной строки. String2 — это строка, которая будет вставлена в String1.

Пример:

SELECT STUFF(‘SQL Tutorial’,1,3,’Python’)

В результате работы функции «SQL Tutorial» превратится в «Python Tutorial».

Результат:

Python Tutorial

20. Что такое представления? Приведите пример

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

Давайте рассмотрим пример. Допустим, мы хотим осуществлять много разных операций с записями, где пол указан как женский (см. таблицу ниже). Мы можем создать таблицу-представление, в которой будут содержаться только эти записи. Давайте это реализуем.

Вот наша таблица сотрудников:

SELECT * FROM employee

Теперь давайте создадим представление:

CREATE VIEW female_employee AS SELECT * FROM employee WHERE e_gender=’Female’
SELECT * FROM female_employee

Результат:

21. Что такое хранимая процедура? Приведите пример

Хранимая процедура это подготовленный SQL-код, который можно сохранить и использовать повторно. Можно сказать, что хранимая процедура это что-то вроде функции, состоящей из многих SQL-выражений. Мы можем комбинировать несколько SQL-выражений в одной хранимой процедуре, а затем выполнять эту процедуру при необходимости.

Хранимая процедура может использоваться как средство модульного программирования. То есть, мы можем создать и сохранить такую процедуру единожды, а затем вызывать ее многократно, когда в этом возникнет необходимость. Благодаря этому также достигается более быстрое выполнение — по сравнению с выполнением нескольких отдельных запросов.

Синтаксис:

CREATE PROCEDURE procedure_name
AS
Sql_statement
GO;

Для выполнения процедуры используется следующий синтаксис:

EXEC procedure_name

Пример:

Мы создадим хранимую процедуру, которая поможет нам получать данные о возрасте сотрудников.

CREATE PROCEDURE employee_age
AS
SELECT e_age from employee
GO

А теперь давайте выполним эту хранимую процедуру:

EXEC employee_age

Результат:

22. Что вы знаете о JOIN? Расскажите о разных видах этого оператора

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

Есть четыре вида оператора JOIN:

1. INNER JOIN. Возвращает записи, имеющие общие значения в обеих таблицах.
2. LEFT OUTER JOIN. Возвращает строки со значениями, общими для таблиц, и все строки левой таблицы (т. е., даже те, для которых нет совпадений в правой таблице).
3. RIGHT OUTER JOIN. Возвращает строки со значениями, общими для таблиц, и все строки правой таблицы (т. е., даже те, для которых нет совпадений в левой таблице).
4. FULL OUTER JOIN. Возвращает все строки левой и правой таблиц.

23. Объясните, как работает INNER JOIN

INNER JOIN дает нам те записи, в которых есть значения, совпадающие для обеих таблиц.

Предположим, у нас есть Таблица А и Таблица Б. Применив INNER JOIN к этим двум таблицам, мы получим только те записи, где есть общие значения для обеих таблиц.

Синтаксис:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column_x=table2.column_y;

Пример

SELECT * FROM employee
SELECT * FROM department

Результат:

На скриншоте вы видите результат вывода всех записей из двух таблиц. А теперь давайте выполним внутреннее соединение этих таблиц. При этом мы получим записи, в которых значения столбца e_dept таблицы employee совпадут со значениями столбца d_name таблицы department.

Синтаксис:

SELECT employee.e_name, employee.e_dept, department.d_name, department.d_location
FROM employee 
INNER JOIN department
ON employee.e_dept=department.d_name

Результат:

После внутреннего соединения мы получили только записи, в которых совпадает название отдела. Как видим, оператор нашел совпадения для отделов Support, Analytics и Sales.

24. Расскажите, чем отличаются представления и таблицы

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

25. Как вы понимаете временные таблицы? Напишите запрос для создания временной таблицы

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

Синтаксис:

CREATE TABLE #table_name();

Следующий запрос создаст временную таблицу:

CREATE TABLE #book(b_id int, b_cost int)

Теперь давайте добавим записи:

INSERT into #book values(1,100)
INSERT into #book values(2,232)
SELECT * FROM #book

Результат:

26. Объясните разницу между OLTP и OLAP

OLTP расшифровывается как Online Transaction Processing — обработка транзакций в реальном времени. Можно сказать, что это категория приложений, эффективных для поддержки программ, ориентированных на транзакции. Один из самых важных атрибутов OLTP-системы это ее возможности в плане поддержки прочности.

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

OLAP расшифровывается как Online Analytical Processing — интерактивная аналитическая обработка.

Это категория программ, которые характеризуются сравнительно меньшей частотой онлайн-транзакций. Эффективность систем OLAP зависит от времени отклика. Такие системы обычно используются для интеллектуального анализа данных, обычно — в многомерных схемах.

27. Как вы понимаете SELF JOIN?

SELF JOIN используется для соединения таблицы с ней самой. При этом каждая строка таблицы соединяется с самой собой и с другими строками — в зависимости от заданных условий.

Синтаксис:

SELECT a.column_name, b.column_name
FROM table a, table b
WHERE condition

Пример

Возьмем следующую таблицу пользователей:

Объединим таблицу с использованием SELF JOIN:

SQL> SELECT a.ID, b.NAME, a.SALARY
   FROM CUSTOMERS a, CUSTOMERS b
   WHERE a.SALARY < b.SALARY;

Результат:

28. В чем разница между UNION и UNION ALL?

Оператор UNION используется для объединения результатов выполнения двух или более выражений SELECT. Например, первое выражение SELECT возвращает рыбок на рисунке А, а второе выражение SELECT — рыбок на рисунке В. Оператор UNION возвращает результат обоих выражений SELECT, как показано на рисунке AUB. При этом, если в обеих таблицах есть одинаковые записи, в результат выведется только одна из них.

Синтаксис:

SELECT column_list FROM table1
UNION
SELECT column_list FROM table2

Выполним это на SQL-сервере.

У нас есть две таблицы, к которым мы применим оператор UNION.

SELECT * FROM student_details1
UNION
SELECT * FROM student_details2

Результат:

Оператор UNION ALL выдаст нам результат обоих выражений SELECT, причем дубликаты сохранятся.

Давайте реализуем это на SQL-сервере.

Синтаксис:

SELECT * FROM student_details1
UNION ALL
SELECT * FROM student_details2

Результат:

29. Для чего используется оператор INTERSECT?

Оператор INTERSECT помогает скомбинировать результаты двух выражений SELECT и вернуть только те записи, которые являются общими для двух наборов результатов. Таким образом, если у нас есть Таблица А и Таблица В, и мы сделаем выборку записей из обеих таблиц, а затем применим к ним оператор INTERSECT, мы получим только те записи, которые есть в обеих таблицах.

Синтаксис:

SELECT column_list FROM table1
INTERSECT
SELECT column_list FROM table2

Давайте рассмотрим пример применения оператора INTERSECT.

SELECT * FROM student_details1
SELECT * FROM student_details1

Результат:

SELECT * FROM student_details1
INTERSECT
SELECT * FROM student_details2

Результат:

30. Как скопировать данные из одной таблицы в другую?

У нас есть таблица с данными сотрудников.

Нужно скопировать эти данные в другую таблицу. Для этого мы можем использовать оператор INSERT INTO SELECT. Но прежде нам нужно создать другую таблицу, которая имела бы точно такую же структуру, как наша исходная таблица.

Синтаксис:

CREATE TABLE employee_duplicate(
e_id int,
e_name varchar(20),
e_salary int,
e_age int,
e_gender varchar(20)
e_dept varchar(20)
)

Для копирования данных мы используем следующий запрос:

insert into employee_duplicate select * from employees

Давайте посмотрим на скопированную таблицу.

SELECT * FROM employee_duplicate

Результат:

[customscript]techrocks_custom_after_post_html[/customscript]

[customscript]techrocks_custom_script[/customscript]