SQL: средний уровень

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

В этой статье мы углубимся в задачи среднего уровня тестирования, которые встречаются на платформе HeadHunter. Вы узнаете, как правильно строить сложные соединения таблиц (INNER JOIN, OUTER JOIN), писать подзапросы (SUBQUERY) и применять индексы для оптимизации производительности запросов. Мы рассмотрим реальный пример теста, детально разберем вопрос и покажем, как правильно выбрать ответ, чтобы успешно подтвердить ваш уровень знаний.

👉🏻Навигация и ссылки по всем материалам в Telegram

Вопрос 1:

Получите список имен и фамилий сотрудников с зарплатой до 50 000 рублей из таблицы Employees.

Варианты ответа:

FROM Employees SELECT first_name, last_name WHERE salary < 50000;
SELECT first_name, last_name FROM Employees WHERE salary < 50000;
CHOOSE first_name, last_name FROM Employees HAVING salary < 50000;
SELECT first_name, last_name WHERE salary < 50000;
SELECT first_name, last_name WHERE salary < 50000 FROM Employees;

Обоснование:

FROM Employees SELECT first_name, last_name WHERE salary < 50000;
Этот вариант неверен, так как оператор FROM должен следовать за оператором SELECT, а не наоборот.
SELECT first_name, last_name FROM Employees WHERE salary < 50000;
Это правильный вариант. Здесь корректно использованы ключевые слова SQL: сначала указаны поля first_name и last_name, затем таблица Employees, и применено условие фильтрации WHERE salary < 50000.
CHOOSE first_name, last_name FROM Employees HAVING salary < 50000;
Этот вариант неверен, поскольку SQL не использует ключевое слово CHOOSE. Для фильтрации используется WHERE или, в случае группировки, HAVING.
SELECT first_name, last_name WHERE salary < 50000;
Этот вариант неверен, так как отсутствует указание таблицы FROM Employees.
SELECT first_name, last_name WHERE salary < 50000 FROM Employees;
Этот вариант неверен, так как порядок ключевых слов нарушен: WHERE должно следовать за FROM, а не перед ним.

📌Правильный ответ:

SELECT first_name, last_name FROM Employees WHERE salary < 50000;

Вопрос 2

Переименуйте столбец new_email в email в существующей таблице Clients.

Варианты ответа:

INTO TABLE Clients RENAME new_email TO email;
UPDATE TABLE Clients RENAME COLUMN new_email TO email;
UPDATE TABLE Clients RENAME new_email TO email;
ALTER TABLE Clients RENAME COLUMN new_email TO email;
ALTER TABLE Clients RENAME new_email TO email;

Обоснование:

INTO TABLE Clients RENAME new_email TO email;
Этот вариант неверен, так как в SQL не существует ключевого слова INTO TABLE в контексте изменения структуры таблицы.
UPDATE TABLE Clients RENAME COLUMN new_email TO email;
Этот вариант неверен, так как UPDATE используется для изменения данных в таблице, а не для изменения структуры таблицы.
UPDATE TABLE Clients RENAME new_email TO email;
Этот вариант также неверен, так как, как и в предыдущем случае, UPDATE предназначен для модификации данных, а не столбцов.
ALTER TABLE Clients RENAME COLUMN new_email TO email;
Это правильный вариант. Для изменения имени столбца используется команда ALTER TABLE, а уточнение RENAME COLUMN правильно указывает на необходимость изменения имени столбца.
ALTER TABLE Clients RENAME new_email TO email;
Этот вариант неверен, так как отсутствует ключевое слово COLUMN, которое обязательно в некоторых диалектах SQL (например, PostgreSQL).

📌Правильный ответ:

ALTER TABLE Clients RENAME COLUMN new_email TO email;

Вопрос 3

Каков будет результат выполнения следующего кода для таблицы Cars, если car_id — первичный ключ с автоинкрементом?

Код:

Варианты ответа:

Добавится новая запись (Null, 5, 'BMW X5 M50d', Null)
Отобразится ошибка
Заменится последняя запись на новую (3, 5, 'BMW X5 M50d', Null)
Заменится последняя запись на новую (3, 5, 'BMW X5 M50d', 7)
Добавится новая запись (4, 5, 'BMW X5 M50d', Null)

Обоснование:

Добавится новая запись (Null, 5, 'BMW X5 M50d', Null)
Этот вариант неверен. Хотя значения для автоинкрементного поля car_id не указаны в запросе, оно автоматически присвоит следующее значение (4), а не Null.
Отобразится ошибка
Этот вариант неверен, так как запрос корректен: автоинкремент обеспечит автоматическое заполнение столбца car_id.
Заменится последняя запись на новую (3, 5, 'BMW X5 M50d', Null)
Этот вариант неверен, так как команда INSERT добавляет новую строку, а не заменяет существующие.
Заменится последняя запись на новую (3, 5, 'BMW X5 M50d', 7)
Этот вариант неверен, так как команда INSERT не заменяет записи. Также поле number не указано в запросе, поэтому его значение будет NULL.
Добавится новая запись (4, 5, 'BMW X5 M50d', Null)
Этот вариант верен. Поскольку car_id — автоинкремент, ему автоматически присваивается значение 4 (следующее за текущим максимумом 3). Поле number, не указанное в запросе, получает значение NULL.

📌Правильный ответ:

Добавится новая запись (4, 5, 'BMW X5 M50d', Null)

Вопрос 4

Отсортируйте все покупки от последней к первой, а по каждому дню — от меньшей суммы продаж к большей в таблице Sales.

Варианты ответа:

SELECT * FROM Sales ORDER BY date, sale_amount DESC, ASC;
SELECT * FROM Sales SORT BY date DESC, sale_amount ASC;
SELECT * FROM Sales ORDER BY date, sale_amount;
SELECT * FROM Sales ORDER BY date DESC, sale_amount ASC;
SELECT * FROM Sales ORDER BY sale_amount ASC, date DESC;

Обоснование:

SELECT * FROM Sales ORDER BY date, sale_amount DESC, ASC;
Этот вариант неверен, так как синтаксис сортировки содержит ошибку: DESC, ASC некорректен.
SELECT * FROM Sales SORT BY date DESC, sale_amount ASC;
Этот вариант неверен, так как SQL не поддерживает ключевое слово SORT BY. Корректный оператор — ORDER BY.
SELECT * FROM Sales ORDER BY date, sale_amount;
Этот вариант неверен, так как сортировка не выполняет требуемое условие: даты сортируются от меньшей к большей, а требуется наоборот (от последней к первой).
SELECT * FROM Sales ORDER BY date DESC, sale_amount ASC;
Этот вариант верен. Даты сортируются от последней к первой (DESC), а внутри каждого дня суммы продаж сортируются от меньшей к большей (ASC).
SELECT * FROM Sales ORDER BY sale_amount ASC, date DESC;
Этот вариант неверен, так как сначала сортируются суммы, а затем даты. Это противоречит требованию сортировки по датам в первую очередь.

📌Правильный ответ:

SELECT * FROM Sales ORDER BY date DESC, sale_amount ASC;

Вопрос 5

Найдите модуль разницы между средней и максимальной заработной платой сотрудников в таблице Employees.

Варианты ответа:

SELECT AVG(salary) - MAX(salary) FROM Employees;
SELECT ABS(MEDIAN(salary) - MAX(salary)) FROM Employees;
SELECT ABS(SUM(*)/COUNT(*) - MAX(salary)) FROM Employees;
SELECT ABS(AVG(salary) - MAX(salary)) FROM Employees;
SELECT MEDIAN(salary) - MAX(salary) FROM Employees;

Обоснование:

SELECT AVG(salary) - MAX(salary) FROM Employees;
Этот вариант неверен, так как он вычисляет разницу между средней и максимальной зарплатами, но не использует функцию ABS() для получения модуля разницы.
SELECT ABS(MEDIAN(salary) - MAX(salary)) FROM Employees;
Этот вариант неверен, так как MEDIAN() не является стандартной функцией SQL в большинстве систем баз данных (например, MySQL). Однако он мог бы быть применим в базах, поддерживающих MEDIAN().
SELECT ABS(SUM(*)/COUNT(*) - MAX(salary)) FROM Employees;
Этот вариант неверен, так как SUM(*) и COUNT(*) не относятся к salary. Вместо этого используется неправильная агрегация всех записей.
SELECT ABS(AVG(salary) - MAX(salary)) FROM Employees;
Этот вариант верен. Он вычисляет разницу между средней и максимальной зарплатами с помощью AVG() и MAX() и берет модуль разницы с использованием ABS().
SELECT MEDIAN(salary) - MAX(salary) FROM Employees;
Этот вариант неверен по тем же причинам, что и вариант 2 — отсутствие функции MEDIAN() в большинстве SQL систем.

📌Правильный ответ:

SELECT ABS(AVG(salary) - MAX(salary)) FROM Employees;

Вопрос 6

Найдите численность населения городов, оканчивающихся на -burg, по регионам в таблице Cities.

Варианты ответа:

SELECT region_id, population FROM Cities WHERE city_name LIKE '%burg' GROUP BY region_id;
SELECT region_id, SUM(population) FROM Cities WHERE city_name LIKE '%burg' GROUP BY region_id;
SELECT SUM(population) FROM Cities GROUP BY region_id HAVING city_name LIKE '%burg';
SELECT region_id, SUM(population) FROM Cities GROUP BY region_id HAVING city_name LIKE '%burg';
SELECT region_id, SUM ALL population FROM Cities WHERE city_name LIKE '%burg' GROUP BY region_id;

Обоснование:

SELECT region_id, population FROM Cities WHERE city_name LIKE '%burg' GROUP BY region_id;
Этот вариант неверен, так как в выборке population используется без агрегирующей функции (например, SUM()). Это вызовет ошибку, так как GROUP BY требует либо агрегирующих функций, либо использования группировочных полей.
SELECT region_id, SUM(population) FROM Cities WHERE city_name LIKE '%burg' GROUP BY region_id;
Это правильный вариант. Здесь правильно используется агрегирующая функция SUM(population) для подсчёта населения городов, фильтруются только города, чьи имена заканчиваются на -burg, и данные корректно группируются по region_id.
SELECT SUM(population) FROM Cities GROUP BY region_id HAVING city_name LIKE '%burg';
Этот вариант неверен, так как HAVING используется для фильтрации агрегированных данных, а city_name не является частью группировки или агрегатной функции. Такое использование вызовет ошибку.
SELECT region_id, SUM(population) FROM Cities GROUP BY region_id HAVING city_name LIKE '%burg';
Этот вариант неверен по той же причине, что и вариант 3: HAVING фильтрует данные после агрегации, но city_name здесь не может быть использован.
SELECT region_id, SUM ALL population FROM Cities WHERE city_name LIKE '%burg' GROUP BY region_id;
Этот вариант неверен, так как синтаксис SUM ALL не существует в стандартном SQL.

📌Правильный ответ:

SELECT region_id, SUM(population) FROM Cities WHERE city_name LIKE '%burg' GROUP BY region_id;

Вопрос 7

Получите список сотрудников, содержащий названия их отделов, а также имена и фамилии сотрудников в столбце employees_list, из таблиц Employees и Departments, учитывая, что общий столбец между ними — dep_id.

Варианты ответа:

SELECT dep_name, JOIN(first_name, '', second_name) AS employees_list FROM Employees LEFT JOIN Departments ON Employees.dep_id = Departments.dep_id;
SELECT dep_name, CONCAT(first_name, ' ', second_name) AS employees_list FROM Employees INNER JOIN Departments ON Employees.dep_id = Departments.dep_id;
SELECT dep_name, SUM(first_name, '', second_name) AS employees_list FROM Employees OUTER JOIN Departments ON Employees.dep_id = Departments.dep_id;
SELECT dep_name, (first_name + second_name) AS employees_list FROM Employees RIGHT JOIN Departments ON Employees.dep_id = Departments.dep_id;
SELECT dep_name, ADD(first_name, second_name) AS employees_list FROM Employees INNER JOIN Departments ON Employees.dep_id = Departments.dep_id;

Обоснование:

SELECT dep_name, JOIN(first_name, '', second_name) AS employees_list FROM Employees LEFT JOIN Departments ON Employees.dep_id = Departments.dep_id;
Этот вариант неверен, так как функция JOIN() не существует в стандартном SQL для объединения строк.
SELECT dep_name, CONCAT(first_name, ' ', second_name) AS employees_list FROM Employees INNER JOIN Departments ON Employees.dep_id = Departments.dep_id;
Это правильный вариант. Функция CONCAT() используется для объединения строк с пробелом между именем и фамилией. INNER JOIN связывает таблицы Employees и Departments по столбцу dep_id, предоставляя названия отделов (dep_name) и соответствующих сотрудников.
SELECT dep_name, SUM(first_name, '', second_name) AS employees_list FROM Employees OUTER JOIN Departments ON Employees.dep_id = Departments.dep_id;
Этот вариант неверен, так как SUM() предназначена для численных данных и не может объединять строки.
SELECT dep_name, (first_name + second_name) AS employees_list FROM Employees RIGHT JOIN Departments ON Employees.dep_id = Departments.dep_id;
Этот вариант неверен, так как оператор + не применяется для объединения строк в SQL.
SELECT dep_name, ADD(first_name, second_name) AS employees_list FROM Employees INNER JOIN Departments ON Employees.dep_id = Departments.dep_id;
Этот вариант неверен, так как функция ADD() не существует для объединения строк в SQL.

📌Правильный ответ:

SELECT dep_name, CONCAT(first_name, ' ', second_name) AS employees_list FROM Employees INNER JOIN Departments ON Employees.dep_id = Departments.dep_id;

Вопрос 8

Найдите имена сотрудников, зарплата которых больше средней зарплаты всех сотрудников в таблице Employees.

Варианты ответа:

SELECT first_name, last_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM employees);
SELECT first_name, last_name FROM Employees WHERE (SELECT SUM(salary) / COUNT(*) FROM employees) > salary;
SELECT first_name, last_name FROM Employees GROUP BY first_name, last_name HAVING salary > AVG(salary);
SELECT first_name, last_name FROM Employees WHERE salary > AVG(salary);
SELECT first_name, last_name FROM Employees WHERE AVG(salary) > salary;

Обоснование:

SELECT first_name, last_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM employees);
Это правильный вариант. Используется подзапрос для вычисления средней зарплаты (AVG(salary)), и результат сравнивается с зарплатой каждого сотрудника через условие WHERE.
SELECT first_name, last_name FROM Employees WHERE (SELECT SUM(salary) / COUNT(*) FROM employees) > salary;
Этот вариант неверен, так как условие сравнивает результат вычисления средней зарплаты с фиксированным значением, не обеспечивая логически верной выборки сотрудников с зарплатой выше среднего.
SELECT first_name, last_name FROM Employees GROUP BY first_name, last_name HAVING salary > AVG(salary);
Этот вариант неверен. HAVING используется для фильтрации агрегированных данных после группировки. Здесь группировка по first_name и last_name не требуется, так как выборка выполняется для индивидуальных записей сотрудников.
SELECT first_name, last_name FROM Employees WHERE salary > AVG(salary);
Этот вариант неверен, так как AVG(salary) в данном контексте не может быть использован без подзапроса. SQL требует подзапрос для агрегированных функций в условиях WHERE.
SELECT first_name, last_name FROM Employees WHERE AVG(salary) > salary;
Этот вариант неверен, так как порядок сравнения некорректен. Кроме того, AVG(salary) не может использоваться без подзапроса.

📌Правильный ответ:

SELECT first_name, last_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM employees);

Вопрос 9

Вам нужно добавить столбец city в представление PeopleView с данными из двух таблиц Respondents и Info. Какая ошибка допущена в запросе?

Варианты ответа:

Вместо CREATE VIEW PeopleView OR REPLACE нужно написать CREATE OR REPLACE VIEW PeopleView
Перед SELECT есть AS, который не нужен
Не нужно указывать обе таблицы в строке с FROM
В запросе не нужно использовать VIEW
Не нужно указывать, из каких таблиц взяты данные, можно написать age, phone_number, city, address

Обоснование:

CREATE VIEW PeopleView OR REPLACE нужно написать CREATE OR REPLACE VIEW PeopleView
Это правильный ответ. Синтаксис команды для изменения или создания представления должен быть CREATE OR REPLACE VIEW, а не CREATE VIEW OR REPLACE.
Перед SELECT есть AS, который не нужен
Этот вариант неверен, так как ключевое слово AS в данном контексте используется корректно для определения представления.
Не нужно указывать обе таблицы в строке с FROM
Этот вариант неверен, так как обе таблицы указаны корректно, чтобы связать их через WHERE.
В запросе не нужно использовать VIEW
Этот вариант неверен, так как VIEW необходим для создания представления.
Не нужно указывать, из каких таблиц взяты данные, можно написать age, phone_number, city, address
Этот вариант неверен, так как при наличии нескольких таблиц обязательно нужно указывать, из какой таблицы берутся данные.

📌Правильный ответ:

CREATE VIEW PeopleView OR REPLACE нужно написать CREATE OR REPLACE VIEW PeopleView

Вопрос 10

Индексацию какого столбца следует рассмотреть в первую очередь, если необходимо проиндексировать таблицу для повышения производительности запросов?

Варианты ответа:

Столбец с большим количеством уникальных значений
Столбец, который часто обновляется
Столбец с двумя различными значениями (например, да/нет)
Столбец, который используется в операторах SELECT без WHERE
Столбец с большим количеством значений NULL

Обоснование:

Столбец с большим количеством уникальных значений
Этот вариант верен. Индексация столбцов с высокой кардинальностью (то есть с большим количеством уникальных значений) значительно улучшает производительность поиска, особенно при использовании таких столбцов в условиях фильтрации (WHERE).
Столбец, который часто обновляется
Этот вариант неверен, так как индексирование столбцов, которые часто обновляются, может снизить производительность операций обновления, поскольку индексы нужно поддерживать в актуальном состоянии.
Столбец с двумя различными значениями (например, да/нет)
Этот вариант неверен. Столбцы с низкой кардинальностью (например, только два значения) менее эффективны для индексирования, так как индекс не принесет значительного улучшения производительности.
Столбец, который используется в операторах SELECT без WHERE
Этот вариант неверен. Индексы улучшают производительность только при фильтрации данных (WHERE), соединении таблиц или сортировке. При выборке всех данных без условий (SELECT без WHERE) индексы не используются.
Столбец с большим количеством значений NULL
Этот вариант неверен, так как большое количество NULL-значений в индексе снижает его полезность, особенно если эти значения не используются в условиях фильтрации.

📌Правильный ответ:

Столбец с большим количеством уникальных значений

Вопрос 11

Выберите ошибочное утверждение об обработке транзакций в языке SQL.

Варианты ответа:

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

Обоснование:

Параллельное выполнение транзакций возможно, только если эти транзакции независимы и не влияют друг на друга.
Это верное утверждение. Независимые транзакции могут выполняться параллельно, не нарушая целостности данных.
Фиксация транзакции обеспечивает сохранение изменений в базе данных, сделанных при выполнении этой транзакции.
Это верное утверждение. Команда COMMIT фиксирует изменения, сделанные транзакцией, в базе данных.
Транзакции могут быть вложенными, то есть одна транзакция может содержать другую.
Это верное утверждение. Некоторые СУБД поддерживают вложенные транзакции.
При исполнении транзакции можно выбрать, какие операции внутри нее должны быть выполнены, а какие — нет.
Это ошибочное утверждение. Транзакция выполняется как единое целое: либо все операции внутри нее фиксируются (COMMIT), либо все отменяются (ROLLBACK).
Операции отмены транзакции выполняются в порядке, обратном порядку их записи в файл журнала транзакций.
Это верное утверждение. При ROLLBACK изменения отменяются в обратном порядке, чтобы восстановить состояние данных.

📌Правильный ответ:

При исполнении транзакции можно выбрать, какие операции внутри нее должны быть выполнены, а какие — нет.

Вопрос 12

Какой фактор следует учитывать в первую очередь для повышения производительности запроса, включающего несколько объединений?

Код:

Варианты ответа:

Типы данных соединяемых столбцов
Соглашение об именах таблиц
Количество строк в результирующем наборе
Наличие ограничения первичного ключа для присоединяемых столбцов
Порядок соединения таблиц

Обоснование:

Типы данных соединяемых столбцов
Это важный фактор, так как несоответствие типов данных может замедлить производительность и привести к необходимости преобразований (например, CAST), что увеличивает затраты на обработку.
Соглашение об именах таблиц
Этот фактор не влияет на производительность запроса. Это относится к соглашениям о стилях именования, которые облегчают понимание структуры базы данных, но не оптимизируют выполнение.
Количество строк в результирующем наборе
Хотя это может влиять на время выполнения, количество строк — это скорее следствие выполнения запроса, а не прямой фактор оптимизации. Производительность улучшится за счет оптимизации соединений.
Наличие ограничения первичного ключа для присоединяемых столбцов
Это важный фактор. Индексы, создаваемые на основе первичных ключей, значительно ускоряют выполнение соединений. При отсутствии индекса соединения выполняются медленнее.
Порядок соединения таблиц
Хотя оптимизатор SQL обычно определяет порядок выполнения соединений, правильное упорядочение таблиц может повлиять на производительность в системах с менее продвинутыми оптимизаторами.

📌Правильный ответ:

Наличие ограничения первичного ключа для присоединяемых столбцов

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

Заключение

Прекрасно! Теперь вы не только уверенно оперируете базовыми конструкциями SQL, но и справляетесь с более сложными задачами: соединениями таблиц, подзапросами и использованием индексов. Это умение выделяет вас как профессионала, готового к более сложным вызовам. Пройдя тест среднего уровня на платформе HeadHunter, вы докажете свою способность эффективно работать с данными и оптимизировать запросы.

P.S.

Дорогие читатели! Если материалы данной статьи помогли вам успешно пройти тест на платформе HeadHunter, буду признателен, если вы поставите лайк 👍🏻 именно той статье, которая соответствовала вашему уровню подготовки. Также, если тестирование оказалось неудачным ❌, пожалуйста, оставьте комментарий 📝 с указанием количества ошибок допущенных в тесте.

Эта обратная связь чрезвычайно важна. Она позволит в дальнейшем проанализировать эффективность материалов, а также создать аналитическое заключение для всей серии статей по прохождению тестирования на платформе. Спасибо за вашу помощь в совершенствовании контента!