Задача # 4. Разбор задачи по SQL: Поиск пассажиров с именами, оканчивающимися на "man"

SQL — один из ключевых навыков для аналитиков, разработчиков и инженеров данных. На собеседованиях часто дают практические задачи для проверки понимания языка. Разберём типовые задачи и их решения.

Предыдущее задание:

Задача 4. Поиск пассажиров с именами, оканчивающимися на "man"

Условие задачи

Требуется вывести имена людей (пассажиров), которые заканчиваются на подстроку "man". Результирующая таблица должна содержать одно поле:

• name — имя пассажира

Анализ структуры базы данных

Из предыдущих задач мы знаем, что в базе имеется таблица:

• Passenger — содержит информацию о пассажирах

Для решения задачи нам понадобится только эта таблица, так как она хранит имена пассажиров.

Определение критериев выборки

Нам нужно:

1. Выбрать записи, где значение столбца name заканчивается на "man"
2. Учесть возможные варианты написания (разный регистр букв)
3. Вернуть только столбец с именами

Формирование SQL-запроса

Основной запрос для решения задачи:

SELECT name
FROM Passenger
WHERE name LIKE '%man';

Этот запрос:

1. Использует оператор LIKE для поиска по шаблону
2. Шаблон '%man' означает:
   % — любое количество любых символов
   man — буквальная подстрока в конце

Особенности работы оператора LIKE

1. Чувствительность к регистру:

• В PostgreSQL оператор LIKE является регистрозависимым. Для поиска без учёта регистра в этой СУБД используется оператор ILIKE.
• В MySQL, по умолчанию, оператор LIKE использует параметры сортировки без учёта регистра для сравнения строк. Это означает, что при сравнении двух строк MySQL считает буквы «A» и «a» одинаковыми.
• Однако есть возможность изменить параметры сортировки и сделать оператор LIKE чувствительным к регистру. Для этого в MySQL используется предложение COLLATE. Например, параметр utf8_binделает оператор LIKE чувствительным к регистру.

2. Экранирование спецсимволов:
Для поиска самого символа % используем \% (MySQL).

В SQL оператор LIKE использует символы % и _ для соответствия шаблонам: % согласуется с любой последовательностью символов, а _ — с одиным произвольным символ. Поиск именно этих символов требует их экранирования, обычно с помощью символа \. Вот примеры этого синтаксиса:

SELECT * FROM table_name WHERE column_name LIKE '%\%%' ESCAPE '\';

Альтернативные варианты решения

1. Регистронезависимый поиск (универсальный способ):

SELECT name
FROM Passenger
WHERE LOWER(name) LIKE '%man';

2. Использование регулярных выражений (в некоторых СУБД):

-- Для PostgreSQL
SELECT name
FROM Passenger
WHERE name ~ 'man$';

-- Для MySQL
SELECT name
FROM Passenger
WHERE name REGEXP 'man$';

Оптимизация запроса

Для больших таблиц стоит:

1. Создать индекс по столбцу name
2. Рассмотреть полнотекстовый поиск, если такие запросы частые

Пример результатов

Для записей:

• "Batman"
• "Superman"
• "Iron Man"
• "Woman"
• "Alex"

Запрос вернет:

• "Batman"
• "Superman"
• "Iron Man"
• "Woman"

Заключение

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

🔑 Итоговое решение:

SELECT name
FROM Passenger
WHERE name LIKE '%man';