Топ вопросы по SQL

Реляционные базы данных являются одними из наиболее часто используемых баз данных по сей день, и поэтому навыки работы с SQL для большинства должностей являются обязательными. В этой статье с вопросами по SQL с собеседований я познакомлю вас с наиболее часто задаваемыми вопросами по SQL (Structured Query Language - язык структурированных запросов). Эта статья является идеальным руководством для изучения всех концепций, связанных с SQL, Oracle, MS SQL Server и базой данных MySQL.

Что подразумевается под СУБД? Какие существуют типы СУБД?

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

Существует два типа СУБД:

Реляционная система управления базами данных: данные хранятся в отношениях (таблицах). Пример - MySQL.
Нереляционная система управления базами данных: не существует понятия отношений, кортежей и атрибутов. Пример - MongoDB.

Из каких подмножеств состоит SQL?

Data Definition Language, DDL (Операторы определения данных)

DDL - это часть SQL, которая служит для определения структуры данных в начальном состоянии, когда база данных только создается. Операторы определения данных используются, главным образом, для создания и реструктуризации объектов базы данных. К этим операторам относятся CREATE, ALTER и DROP.

CREATE служит для создания объектов базы данных, ALTER - для их изменения, DROP - для удаления.

Data Manipulation Language, DML (Операторы манипуляции данными)

DML используется для работы с уже существующими данными, содержащимися в базе данных. С помощью этих операторов пользователи могут получать данные из базы и совершать над ними какие-то манипуляции. К этим операторам относятся SELECT, INSERT, UPDATE, DELETE.

Оператор INSERT позволяет вносить данные в базу данных, SELECT - выбирать их, UPDATE - обновлять их, DELETE - удалять данные из базы.

Data Control Language, DCL (Операторы определения доступа к данным)

DCL используется для контроля доступа к данным в базе данных. Команды DCL обычно служат для создания объектов, имеющих отношение к доступу пользователей к базе, а также к распределению разрешений между пользователями. Для этих операций используются операторы GRANT и REVOKE. Первый служит для выдачи разрешений, а второй - для их отзыва.

Transaction Control Language, TCL (Операторы управления транзакциями)

TCL используется для контроля изменений, осуществленных при помощи DML. Также с помощью TCL происходит объединение операторов в логические транзакции. К операторам управления транзакциями относятся COMMIT, ROLLBACK, SAVEPOINT, BEGIN, TRANSACTION.

В чем разница между операторами DELETE и TRUNCATE?

DELETE:

Используется для удаления строки в таблице;
Вы можете восстановить данные после удаления;
DML-команда;
Медленнее, чем оператор TRUNCATE;

TRUNCATE:

Используется для удаления всех строк из таблицы;
Вы не можете восстановить данные (операции логируются по разному, но в SQL Server есть возможность сделать откат);
DDL-команда;
Быстрее;

Что такое соединения в SQL?

Для соединения строк из двух или более таблиц на основе связанного между ними столбца используется оператор JOIN. Он используется для объединения двух таблиц или получения данных оттуда. В SQL есть 4 типа соединения, а именно:

Inner Join (Внутреннее соединение). В MySQL является наиболее распространенным типом. Оно используется для возврата всех строк из нескольких таблиц, для которых выполняется условие соединения.
Right Join (Правое соединение). В MySQL используется для возврата всех строк из правой (второй) таблицы и только совпадающих строк из левой (первой) таблицы, для которых выполняется условие соединения.
Left Join (Левое соединение). В MySQL используется для возврата всех строк из левой (первой) таблицы и только совпадающих строк из правой (второй) таблицы, для которых выполняется условие соединения.
Full Join (Полное соединение). Возвращает все записи, для которых есть совпадение в любой из таблиц. Следовательно, он возвращает все строки из левой таблицы и все строки из правой таблицы.

В чем разница между типом данных CHAR и VARCHAR в SQL?

И Char, и Varchar служат символьными типами данных, но varchar используется для строк символов переменной длины, тогда как Char используется для строк фиксированной длины. Например, char(10) может хранить только 10 символов и не сможет хранить строку любой другой длины, тогда как varchar(10) может хранить строку любой длины до 10, т.е. например 6, 8 или 2.

Что такое первичный ключ (PRIMARY KEY)?

Первичный ключ - столбец или набор столбцов, которые однозначно идентифицируют каждую строку в таблице.
Однозначно идентифицирует одну строку в таблице.
Нулевые (Null) значения не допускаются.

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

Что такое ограничения (Constraints)?

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

NOT NULL - значение не может быть NULL;
CHECK - значения столбца должны соответствовать заданным условиям;
DEFAULT - предоставляет столбцу значения по умолчанию;
UNIQUE- гарантирует уникальность значений в столбце;

Что такое уникальный ключ (UNIQUE KEY)?

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

Что такое внешний ключ (FOREIGN KEY)?

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

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

Что подразумевается под целостностью данных?

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

В чем разница между кластеризованным и некластеризованным индексами в SQL?

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

Напишите SQL-запрос для отображения текущей даты.

В SQL есть встроенная функция GetDate(), которая помогает возвращать текущий timestamp/дату.

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

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

Что такое сущности и отношения?

Сущности: человек, место или объект в реальном мире, данные о которых могут храниться в базе данных. В таблицах хранятся данные, которые представляют один тип сущности. Например - база данных банка имеет таблицу клиентов для хранения информации о клиентах. Таблица клиентов хранит эту информацию в виде набора атрибутов (столбцы в таблице) для каждого клиента.

Отношения: отношения или связи между сущностями, которые имеют какое-то отношение друг к другу. Например - имя клиента связано с номером учетной записи клиента и контактной информацией, которая может быть в той же таблице. Также могут быть отношения между отдельными таблицами (например, клиент к счетам).

Что такое индекс?

Индексы помогают ускорить поиск в базе данных. Если в выражении WHERE не задан индекс никакого столбца, SQL-сервер в поиске соответствия будет проверять каждую строку во всей таблице. Если у вас много данных, эта операция может оказаться довольно медленной.

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

Синтаксис:

CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN);

Опишите различные типы индексов.

Простые индексы. Создаются только для одного столбца таблицы.

Синтаксис:

CREATE INDEX index_name ON table_name(column_name);

Составные индексы. Создаются для двух или большего количества столбцов таблицы.

Синтаксис:

CREATE INDEX index_name ON table_name (column1, column2);

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

Синтаксис:

CREATE UNIQUE INDEX index ON table_name(column_name);

Что такое нормализация и каковы ее преимущества?

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

Лучшая организация базы данных;
Больше таблиц с небольшими строками;
Эффективный доступ к данным;
Большая гибкость для запросов;
Быстрый поиск информации;
Проще реализовать безопасность данных;
Позволяет легко модифицировать;
Сокращение избыточных и дублирующихся данных;
Более компактная база данных;
Обеспечивает согласованность данных после внесения изменений;

Объясните различные типы нормализации.

Существует много последовательных уровней нормализации. Это так называемые нормальные формы. Каждая последующая нормальная форма включает предыдущую. Первых трех нормальных форм обычно достаточно.

Первая нормальная форма (1NF) - нет повторяющихся групп в строках;
Вторая нормальная форма (2NF) - каждое неключевое (поддерживающее) значение столбца зависит от всего первичного ключа;
Третья нормальная форма (3NF) - каждое неключевое значение зависит только от первичного ключа и не имеет зависимости от другого неключевого значения столбца;

В чем разница между командами DROP и TRUNCATE?

Команда DROP удаляет саму таблицу, и нельзя сделать Rollback команды, тогда как команда TRUNCATE удаляет все строки из таблицы.

Что такое свойство ACID в базе данных?

ACID означает атомарность (Atomicity), согласованность (Consistency), изолированность (Isolation), долговечность (Durability). Он используется для обеспечения надежной обработки транзакций данных в системе базы данных.

Атомарность. Гарантирует, что транзакция будет полностью выполнена или потерпит неудачу, где транзакция представляет одну логическую операцию данных. Это означает, что при сбое одной части любой транзакции происходит сбой всей транзакции и состояние базы данных остается неизменным.

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

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

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

Что вы подразумеваете под "триггером" в SQL?

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

Какие операторы доступны в SQL?

В SQL доступно три типа оператора, а именно:

Арифметические операторы;
Логические операторы;
Операторы сравнения;

Совпадают ли значения NULL со значениями нуля или пробела?

Значение NULL вовсе не равно нулю или пробелу. Значение NULL представляет значение, которое недоступно, неизвестно, присвоено или неприменимо, тогда как ноль - это число, а пробел - символ.

В чем разница между перекрестным (Cross Join) и естественным (Natural Join) соединением?

Перекрестное соединение создает перекрестное или декартово произведение двух таблиц, тогда как естественное соединение основано на всех столбцах, имеющих одинаковое имя и типы данных в обеих таблицах.

Что такое подзапрос в SQL?

Подзапрос - это запрос внутри другого запроса, в котором определен запрос для извлечения данных или информации из базы данных. В подзапросе внешний запрос называется основным запросом, тогда как внутренний запрос называется подзапросом. Подзапросы всегда выполняются первыми, а результат подзапроса передается в основной запрос. Он может быть вложен в SELECT, UPDATE или любой другой запрос. Подзапрос также может использовать любые операторы сравнения, такие как >, < или =.

Какие бывают типы подзапросов?

Существует два типа подзапросов, а именно: коррелированные и некоррелированные.

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

Перечислите способы получить количество записей в таблице?

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

SELECT * FROM table1;

SELECT COUNT(*) FROM table1;

SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2;

Поясните, в чем разница между выражениями HAVING и WHERE?

WHERE:

Реализовано в строковых операциях;
Относится к отдельной строке;
Используется для выборки конкретных данных из определенных строк, согласно заданным условиям;
Не может содержать агрегатные функции;
Может использоваться с SELECT, UPDATE и DELETE;
Выражение GROUP BY идет после выражения WHERE;

HAVING:

Реализовано в столбцовых операциях;
Относится к суммированной строке или группам;
Используется для выборки всех данных и отделения тех из них, которые соответствуют заданным условиям;
Может содержать агрегатные функции;
Не может использоваться без оператора SELECT;
Выражение GROUP BY идет перед выражением HAVING;

Как бы вы нашли вторую по величине зарплату?

SELECT MAX(e_salary) FROM employee WHERE e_salary NOT IN (SELECT MAX(e_salary) FROM employee);

Расскажите, чем отличаются SQL и PL/SQL?

SQL:

SQL это структурированный язык запросов к базам данных.
SQL это отдельный запрос, который используется для выполнения команд DML и DDL.
SQL это декларативный язык, ориентированный на данные.
Используется главным образом для манипуляций с данными.
Предоставляет возможность взаимодействия с сервером базы данных.
Не может содержать в себе код PL/SQL.

PL/SQL:

Это язык программирования для баз данных, использующий SQL.
PL/SQL это блок кодов, используемый для написания всей процедуры или функции.
PL/SQL это процедурный язык, ориентированный на приложение.
Используется для создания приложения.
Не предоставляет возможности взаимодействия с сервером базы данных.
Может содержать SQL, поскольку сам является расширением SQL.

Как вы понимаете символьные функции?

Символьные функции используются для манипуляций с символами. К ним относятся:

UPPER

Возвращает строку в верхнем регистре. Синтаксис:

UPPER('string')

Пример:

SELECT UPPER('demo string');

Результат:

DEMO STRING

LOWER

Возвращает строку в нижнем регистре. Синтаксис:

LOWER('STRING')

Пример:

SELECT LOWER('DEMO STRING');

Результат:

demo string

CONCAT

Используется для конкатениции (объединения) двух строк. Синтаксис:

CONCAT('str1', 'str2')

Пример:

SELECT CONCAT('Data', 'Science');

Результат:

DataScience

LENGTH

Используется для получения длины строки. Синтаксис:

LENGTH('String')

Пример:

SELECT LENGTH('Hello World');

Результат:

Что такое AUTO_INCREMENT?

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

По умолчанию значение AUTO_INCREMENT начинается с 1 и увеличивается на 1 при каждом добавлении новой записи.

Для чего используется ключевое слово ORDER BY?

Для сортировки данных в порядке возрастания (ASC) или убывания (DESC). Пример использования:

SELECT * FROM user ORDER BY name DESC;

Выбираются пользователи, которые будут отсортированы по имени в порядке убывания. Дополните ответ на этот вопрос по SQL тем, что без указания DESC данные были бы отсортированы по умолчанию - в порядке возрастания:

SELECT * FROM user ORDER BY name;

Для чего нужен оператор UNION?

Он используется для объединения полученных данных из двух или более запросов, которые должны иметь одинаковое количество столбцов с одинаковыми типами данных и расположенных в том же порядке. Пример использования:

SELECT column(s) FROM first_table
UNION
SELECT column(s) FROM second_table;

Как работают подстановочные знаки?

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

Какими бывают подстановочные знаки?

% - заменить ноль или более символов;
_ - заменить один символ.

Примеры:

SELECT * FROM user WHERE name LIKE '%test%';

Данный запрос позволяет найти данные всех пользователей, имена которых содержат в себе "test".

SELECT * FROM user WHERE name LIKE 't_est';

А в этом случае имена искомых пользователей начинаются на "t", после содержат какой-либо символ и "est" в конце.

Что делают псевдонимы Aliases?

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

SELECT very_long_column_name AS alias_name FROM table;

Для чего нужен оператор INSERT INTO SELECT?

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

INSERT INTO second_table
SELECT * FROM first_table
WHERE condition;

Как выбрать записи с нечётными id?

SQL запрос для выбора записей с нечётными id должен выглядеть следующим образом:

SELECT * FROM users WHERE id % 2 != 0;

Если остаток от деления id на 2 равен нулю, перед нами чётное значение, и наоборот.

Как найти дубли в поле email?

SELECT email, COUNT(email)
FROM customers
GROUP BY email
HAVING COUNT(email) > 1;

Функция COUNT() возвращает количество строк из поля email. Оператор HAVING работает почти так же, как и WHERE, вот только применяется не для всех столбцов, а для набора, созданного оператором GROUP BY.

При выборке из таблицы прибавьте к дате 1 день.

SELECT DATE_ADD(date, 1 DAY) AS new_date FROM table;

Функция DATE_ADD() прибавляет к дате заданный промежуток времени. Синтаксис выглядит следующим образом:

SELECT DATE_ADD(дата, INTERVAL что_прибавить) FROM имя_таблицы WHERE условие;

Выберите только уникальные имена.

SELECT DISTINCT name FROM users;

SELECT DISTINCT возвращает разные значения, даже если в выбранном столбце есть дубли.

Найдите в таблице среднюю зарплату работников.

SELECT AVG(salary) FROM workers;

Функция AVG() применяется только к числовым типам данных и возвращает среднее значение по столбцу.

А теперь получите список сотрудников с зарплатой выше средней.

SELECT * FROM workers
WHERE salary > (SELECT AVG (salary) FROM workers);

Даны таблицы workers и departments. Найдите все департаменты без единого сотрудника.

SELECT department_name
FROM workers w
RIGHT JOIN departments d ON (w.department_id = d.department_id)
WHERE first_name IS NULL;

Замените в таблице зарплату работника на 1000, если она равна 900, и на 1500 в остальных случаях.

Замена значений - одна из наиболее часто встречаемых задач по SQL. Решить её не сложно:

UPDATE table SET salary =
CASE
WHEN salary = 900 THEN 1000
ELSE 1500
END;

Оператор UPDATE используется для изменения существующих записей. Но ответы на подобные вопросы с собеседований по SQL должны быть более развёрнутыми. Уточните, что после UPDATE следует указать, какие записи должны быть обновлены. В противном случае обновятся все записи в таблице.

В нашем примере условие задаётся через оператор CASE: если текущая зарплата равна 900, изменяем её на 1000, в остальных случаях - на 1500.

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

SELECT CONCAT(name, salary) AS new_field FROM users;

Функция CONCAT() используется для конкатенации (объединения) строк, неявно преобразуя при этом любые типы данных в строки.

Переименуйте таблицу.

ALTER TABLE first_table RENAME second_table;

С помощью оператора ALTER TABLE можно добавлять, удалять, изменять столбцы, а также изменять название таблицы.

Что вернет условие 2 <> NULL?

Сравнение с NULL - это, наверное, первый подводный камень, на который натыкаются люди при работе с базой данных. Вопреки привычной логике условие:

2 <> NULL

возвращает ложь (FALSE), как впрочем и условие:

2 = NULL

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

Возвращаясь к сути вопроса, мы не можем сказать "Два не равно NULL" потому, что мы не знаем значения справа от знака неравенства, а там как раз может оказаться двойка.

Что вернет условие 3 NOT IN (1, 2, NULL)?

Здесь та же история, что и в предыдущем случае. Условие:

3 NOT IN (1, 2, NULL)

возвращает ложь (FALSE), как и условие:

3 IN (1, 2, NULL)

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

Другими словами:

3 IN (1, 2, NULL)

это то же самое, что и

(3 = 1) OR (3 = 2) OR (3 = NULL)

В случае с NOT IN условие:

3 NOT IN (1, 2, NULL)

это то же самое, что и

(3 <> 1) AND (3 <> 2) AND (3 <> NULL)

Как мы знаем из предыдущего примера, 3 <> NULL возвращает ложь, а значит и все условие:

(3 <> 1) AND (3 <> 2) AND (3 <> NULL)

тоже будет ложным.

Почему не выполнится этот запрос?

SELECT 
    user_name,
    YEAR(user_birth_date) AS year_of_birth
FROM 
    users
WHERE
    year_of_birth = 2000;

Запрос не выполнится из-за обращения к псевдониму year_of_birth в выражении WHERE. Дело в том, что псевдонимы полей в SQL используются для форматирования данных уже полученных из базы. Поэтому их можно использовать только в выражениях, которые отвечают за оформление результата, таких как GROUP BY, ORDER BY и HAVING. В выражениях, отвечающих за получение данных, таких как WHERE, нужно использовать оригинальные имена полей.

Имеет ли значение порядок колонок в составном индексе?

Да.

CREATE NONCLUSTERED INDEX MyInd on users (user_name, user_birth_date);

это не то же самое, что

CREATE NONCLUSTERED INDEX MyInd on users (user_birth_date, user_name);

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

Чем отличается IN от EXISTS?

IN:

Работает результирующим набором.
Не применяется во вложенных запросах.
Сравнивает все значения в списке результатов
Имеет сравнительно низкую производительность при работе с большими результатами и подзапросами.

EXISTS:

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