SQL. Занимательные задачки

Здравствуй, Хабр!

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

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

SQL (ˈɛsˈkjuˈɛl; англ. structured query language — «язык структурированных запросов») — декларативный язык программирования, применяемый для создания, модификации и управления данными в реляционной базе данных, управляемой соответствующей системой управления базами данных. Подробнее…

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

Итак, поехали.

Будем использовть всем известную схему HR в Oracle с ее таблицами (Подробнее):

Отмечу что мы будем рассматривать только задачи на SELECT. Тут нет задач на DML и DDL.

Задачи

Restricting and Sorting Data

Таблица Employees. Получить список с информацией обо всех сотрудниках
Решение

SELECT * FROM employees

Таблица Employees. Получить список всех сотрудников с именем ‘David’
Решение

SELECT *
  FROM employees
 WHERE first_name = 'David';

Таблица Employees. Получить список всех сотрудников с job_id равным ‘IT_PROG’
Решение

SELECT *
  FROM employees
 WHERE job_id = 'IT_PROG'

Таблица Employees. Получить список всех сотрудников из 50го отдела (department_id) с зарплатой(salary), большей 4000
Решение

SELECT *
  FROM employees
 WHERE department_id = 50 AND salary > 4000;

Таблица Employees. Получить список всех сотрудников из 20го и из 30го отдела (department_id)
Решение

SELECT *
  FROM employees
 WHERE department_id = 20 OR department_id = 30;

Таблица Employees. Получить список всех сотрудников у которых последняя буква в имени равна ‘a’
Решение

SELECT *
  FROM employees
 WHERE first_name LIKE '%a';

Таблица Employees. Получить список всех сотрудников из 50го и из 80го отдела (department_id) у которых есть бонус (значение в колонке commission_pct не пустое)
Решение

SELECT *
  FROM employees
 WHERE     (department_id = 50 OR department_id = 80)
       AND commission_pct IS NOT NULL;

Таблица Employees. Получить список всех сотрудников у которых в имени содержатся минимум 2 буквы ‘n’
Решение

SELECT *
  FROM employees
 WHERE first_name LIKE '%n%n%';

Таблица Employees. Получить список всех сотрудников у которых длина имени больше 4 букв
Решение

SELECT *
  FROM employees
 WHERE first_name LIKE '%_____%';

Таблица Employees. Получить список всех сотрудников у которых зарплата находится в промежутке от 8000 до 9000 (включительно)
Решение

SELECT *
  FROM employees
 WHERE salary BETWEEN 8000 AND 9000;

Таблица Employees. Получить список всех сотрудников у которых в имени содержится символ ‘%’
Решение

SELECT *
  FROM employees
 WHERE first_name LIKE '%%%' ESCAPE '';

Таблица Employees. Получить список всех ID менеджеров
Решение

SELECT DISTINCT manager_id
  FROM employees
 WHERE manager_id IS NOT NULL;

Таблица Employees. Получить список работников с их позициями в формате: Donald(sh_clerk)
Решение

SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;

Using Single-Row Functions to Customize Output

Таблица Employees. Получить список всех сотрудников у которых длина имени больше 10 букв
Решение

SELECT *
  FROM employees
 WHERE LENGTH (first_name) > 10;

Таблица Employees. Получить список всех сотрудников у которых в имени есть буква ‘b’ (без учета регистра)
Решение

SELECT *
  FROM employees
 WHERE INSTR (LOWER (first_name), 'b') > 0;

Таблица Employees. Получить список всех сотрудников у которых в имени содержатся минимум 2 буквы ‘a’
Решение

SELECT *
  FROM employees
 WHERE INSTR (LOWER (first_name),'a',1,2) > 0;

Таблица Employees. Получить список всех сотрудников зарплата которых кратна 1000
Решение

SELECT *
  FROM employees
 WHERE MOD (salary, 1000) = 0;

Таблица Employees. Получить первое 3х значное число телефонного номера сотрудника если его номер в формате ХХХ.ХХХ.ХХХХ
Решение

SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
  FROM employees
 WHERE phone_number LIKE '___.___.____';

Таблица Departments. Получить первое слово из имени департамента для тех у кого в названии больше одного слова
Решение

SELECT department_name,
       SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
           first_word
  FROM departments
 WHERE INSTR (department_name, ' ') > 0;

Таблица Employees. Получить имена сотрудников без первой и последней буквы в имени
Решение

SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
  FROM employees;

Таблица Employees. Получить список всех сотрудников у которых последняя буква в имени равна ‘m’ и длинной имени большей 5ти
Решение

SELECT *
  FROM employees
 WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;

Таблица Dual. Получить дату следующей пятницы
Решение

SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;

Таблица Employees. Получить список всех сотрудников которые работают в компании больше 17 лет
Решение

SELECT *
  FROM employees
 WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;

Таблица Employees. Получить список всех сотрудников у которых последня цифра телефонного номера нечетная и состоит из 3ех чисел разделенных точкой
Решение

SELECT *
  FROM employees
 WHERE     MOD (SUBSTR (phone_number, -1), 2) != 0
       AND INSTR (phone_number,'.',1,3) = 0;

Таблица Employees. Получить список всех сотрудников у которых в значении job_id после знака ‘_’ как минимум 3 символа но при этом это значение после ‘_’ не равно ‘CLERK’
Решение

SELECT *
  FROM employees
 WHERE     LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
       AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';

Таблица Employees. Получить список всех сотрудников заменив в значении PHONE_NUMBER все ‘.’ на ‘-‘
Решение

SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
  FROM employees;

Using Conversion Functions and Conditional Expressions

Таблица Employees. Получить список всех сотрудников которые пришли на работу в первый день месяца (любого)
Решение

SELECT *
  FROM employees
 WHERE TO_CHAR (hire_date, 'DD') = '01';

Таблица Employees. Получить список всех сотрудников которые пришли на работу в 2008ом году
Решение

SELECT *
  FROM employees
 WHERE TO_CHAR (hire_date, 'YYYY') = '2008';

Таблица DUAL. Показать завтрашнюю дату в формате: Tomorrow is Second day of January
Решение

SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month')     info
  FROM DUAL;

Таблица Employees. Получить список всех сотрудников и дату прихода на работу каждого в формате: 21st of June, 2007
Решение

SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
  FROM employees;

Таблица Employees. Получить список работников с увеличенными зарплатами на 20%. Зарплату показать со знаком доллара
Решение

SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
  FROM employees;

Таблица Employees. Получить список всех сотрудников которые приши на работу в феврале 2007го года.
Решение

SELECT *
  FROM employees
 WHERE hire_date BETWEEN TO_DATE ('01.02.2007', 'DD.MM.YYYY')
                     AND LAST_DAY (TO_DATE ('01.02.2007', 'DD.MM.YYYY'));

SELECT *
  FROM employees
 WHERE to_char(hire_date,'MM.YYYY') = '02.2007'; 

Таблица DUAL. Вывезти актуальную дату, + секунда, + минута, + час, + день, + месяц, + год
Решение

SELECT SYSDATE                          now,
       SYSDATE + 1 / (24 * 60 * 60)     plus_second,
       SYSDATE + 1 / (24 * 60)          plus_minute,
       SYSDATE + 1 / 24                 plus_hour,
       SYSDATE + 1                      plus_day,
       ADD_MONTHS (SYSDATE, 1)          plus_month,
       ADD_MONTHS (SYSDATE, 12)         plus_year
  FROM DUAL;

Таблица Employees. Получить список всех сотрудников с полными зарплатами (salary + commission_pct(%)) в формате: $24,000.00
Решение

SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
  FROM employees;

Таблица Employees. Получить список всех сотрудников и информацию о наличии бонусов к зарплате (Yes/No)
Решение

SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
  FROM employees;

Таблица Employees. Получить уровень зарплаты каждого сотрудника: Меньше 5000 считается Low level, Больше или равно 5000 и меньше 10000 считается Normal level, Больше иои равно 10000 считается High level
Решение

SELECT first_name,
       salary,
       CASE
           WHEN salary < 5000 THEN 'Low'
           WHEN salary >= 5000 AND salary < 10000 THEN 'Normal'
           ELSE 'High'
       END salary_level
  FROM employees;

Таблица Countries. Для каждой страны показать регион в котором он находится: 1-Europe, 2-America, 3-Asia, 4-Africa (без Join)
Решение

SELECT country_name country,
       DECODE (region_id,
               1, 'Europe',
               2, 'America',
               3, 'Asia',
               4, 'Africa',
               'Unknown')
           region
  FROM countries;

SELECT country_name
           country,
       CASE region_id
           WHEN 1 THEN 'Europe'
           WHEN 2 THEN 'America'
           WHEN 3 THEN 'Asia'
           WHEN 4 THEN 'Africa'
           ELSE 'Unknown'
       END
           region
  FROM countries;

Reporting Aggregated Data Using the Group Functions

Таблица Employees. Получить репорт по department_id с минимальной и максимальной зарплатой, с ранней и поздней датой прихода на работу и с количествов сотрудников. Сорировать по количеству сотрудников (по убыванию)
Решение

  SELECT department_id,
         MIN (salary) min_salary,
         MAX (salary) max_salary,
         MIN (hire_date) min_hire_date,
         MAX (hire_date) max_hire_Date,
         COUNT (*) count
    FROM employees
GROUP BY department_id
order by count(*) desc;

Таблица Employees. Сколько сотрудников имена которых начинается с одной и той же буквы? Сортировать по количеству. Показывать только те где количество больше 1
Решение

SELECT SUBSTR (first_name, 1, 1) first_char, COUNT (*)
    FROM employees
GROUP BY SUBSTR (first_name, 1, 1)
  HAVING COUNT (*) > 1
ORDER BY 2 DESC;

Таблица Employees. Сколько сотрудников которые работают в одном и тоже отделе и получают одинаковую зарплату?
Решение

SELECT department_id, salary, COUNT (*)
    FROM employees
GROUP BY department_id, salary
  HAVING COUNT (*) > 1;

Таблица Employees. Получить репорт сколько сотрудников приняли на работу в каждый день недели. Сортировать по количеству
Решение

SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
    FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;

Таблица Employees. Получить репорт сколько сотрудников приняли на работу по годам. Сортировать по количеству
Решение

SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
    FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');

Таблица Employees. Получить количество департаментов в котором есть сотрудники
Решение

SELECT COUNT (COUNT (*))     department_count
    FROM employees
   WHERE department_id IS NOT NULL
GROUP BY department_id;

Таблица Employees. Получить список department_id в котором работают больше 30 сотрудников
Решение

  SELECT department_id
    FROM employees
GROUP BY department_id
  HAVING COUNT (*) > 30;

Таблица Employees. Получить список department_id и округленную среднюю зарплату работников в каждом департаменте.
Решение

  SELECT department_id, ROUND (AVG (salary)) avg_salary
    FROM employees
GROUP BY department_id;

Таблица Countries. Получить список region_id сумма всех букв всех country_name в котором больше 60ти
Решение

  SELECT region_id
    FROM countries
GROUP BY region_id
  HAVING SUM (LENGTH (country_name)) > 60;

Таблица Employees. Получить список department_id в котором работают работники нескольких (>1) job_id
Решение

  SELECT department_id
    FROM employees
GROUP BY department_id
  HAVING COUNT (DISTINCT job_id) > 1;

Таблица Employees. Получить список manager_id у которых количество подчиненных больше 5 и сумма всех зарплат его подчиненных больше 50000
Решение

  SELECT manager_id
    FROM employees
GROUP BY manager_id
  HAVING COUNT (*) > 5 AND SUM (salary) > 50000;

Таблица Employees. Получить список manager_id у которых средняя зарплата всех его подчиненных находится в промежутке от 6000 до 9000 которые не получают бонусы (commission_pct пустой)
Решение

  SELECT manager_id, AVG (salary) avg_salary
    FROM employees
   WHERE commission_pct IS NULL
GROUP BY manager_id
  HAVING AVG (salary) BETWEEN 6000 AND 9000;

Таблица Employees. Получить максимальную зарплату из всех сотрудников job_id которыз заканчивается на слово ‘CLERK’
Решение

SELECT MAX (salary) max_salary
  FROM employees
 WHERE job_id LIKE '%CLERK';

SELECT MAX (salary) max_salary
  FROM employees
 WHERE SUBSTR (job_id, -5) = 'CLERK';

Таблица Employees. Получить максимальную зарплату среди всех средних зарплат по департаменту
Решение

  SELECT MAX (AVG (salary))
    FROM employees
GROUP BY department_id;

Таблица Employees. Получить количество сотрудников с одинаковым количеством букв в имени. При этом показать только тех у кого длина имени больше 5 и количество сотрудников с таким именем больше 20. Сортировать по длинне имени
Решение

  SELECT LENGTH (first_name), COUNT (*)
    FROM employees
GROUP BY LENGTH (first_name)
  HAVING LENGTH (first_name) > 5 AND COUNT (*) > 20
ORDER BY LENGTH (first_name);

  SELECT LENGTH (first_name), COUNT (*)
    FROM employees
   WHERE LENGTH (first_name) > 5
GROUP BY LENGTH (first_name)
  HAVING COUNT (*) > 20
ORDER BY LENGTH (first_name);

Displaying Data from Multiple Tables Using Joins

Таблица Employees, Departaments, Locations, Countries, Regions. Получить список регионов и количество сотрудников в каждом регионе
Решение

  SELECT region_name, COUNT (*)
    FROM employees e
         JOIN departments d ON (e.department_id = d.department_id)
         JOIN locations l ON (d.location_id = l.location_id)
         JOIN countries c ON (l.country_id = c.country_id)
         JOIN regions r ON (c.region_id = r.region_id)
GROUP BY region_name;

Таблица Employees, Departaments, Locations, Countries, Regions. Получить детальную информацию о каждом сотруднике:
First_name, Last_name, Departament, Job, Street, Country, Region
Решение

SELECT First_name,
       Last_name,
       Department_name,
       Job_id,
       street_address,
       Country_name,
       Region_name
  FROM employees  e
       JOIN departments d ON (e.department_id = d.department_id)
       JOIN locations l ON (d.location_id = l.location_id)
       JOIN countries c ON (l.country_id = c.country_id)
       JOIN regions r ON (c.region_id = r.region_id);

Таблица Employees. Показать всех менеджеров которые имеют в подчинении больше 6ти сотрудников
Решение

  SELECT man.first_name, COUNT (*)
    FROM employees emp JOIN employees man ON (emp.manager_id = man.employee_id)
GROUP BY man.first_name
  HAVING COUNT (*) > 6;

Таблица Employees. Показать всех сотрудников которые ни кому не подчиняются
Решение

SELECT emp.first_name
  FROM employees  emp
       LEFT JOIN employees man ON (emp.manager_id = man.employee_id)
 WHERE man.FIRST_NAME IS NULL;

SELECT first_name
  FROM employees
 WHERE manager_id IS NULL;

Таблица Employees, Job_history. В таблице Employee хранятся все сотрудники. В таблице Job_history хранятся сотрудники которые покинули компанию. Получить репорт о всех сотрудниках и о его статусе в компании (Работает или покинул компанию с датой ухода)
Пример:
first_name | status
Jennifer | Left the company at 31 of December, 2006
Clara | Currently Working
Решение

SELECT first_name,
       NVL2 (
           end_date,
           TO_CHAR (end_date, 'fm""Left the company at"" DD ""of"" Month, YYYY'),
           'Currently Working')
           status
  FROM employees e LEFT JOIN job_history j ON (e.employee_id = j.employee_id);

Таблица Employees, Departaments, Locations, Countries, Regions. Получить список сотрудников которые живут в Europe (region_name)
Решение

 SELECT first_name
  FROM employees
       JOIN departments USING (department_id)
       JOIN locations USING (location_id)
       JOIN countries USING (country_id)
       JOIN regions USING (region_id)
 WHERE region_name = 'Europe';
 
 SELECT first_name
  FROM employees  e
       JOIN departments d ON (e.department_id = d.department_id)
       JOIN locations l ON (d.location_id = l.location_id)
       JOIN countries c ON (l.country_id = c.country_id)
       JOIN regions r ON (c.region_id = r.region_id)
 WHERE region_name = 'Europe';

Таблица Employees, Departaments. Показать все департаменты в которых работают больше 30ти сотрудников
Решение

SELECT department_name, COUNT (*)
    FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
  HAVING COUNT (*) > 30;

Таблица Employees, Departaments. Показать всех сотрудников которые не состоят ни в одном департаменте
Решение

SELECT first_name
  FROM employees  e
       LEFT JOIN departments d ON (e.department_id = d.department_id)
 WHERE d.department_name IS NULL;

SELECT first_name
  FROM employees
 WHERE department_id IS NULL;

Таблица Employees, Departaments. Показать все департаменты в которых нет ни одного сотрудника
Решение

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

Таблица Employees. Показать всех сотрудников у которых нет ни кого в подчинении
Решение

SELECT man.first_name
  FROM employees  emp
       RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
 WHERE emp.FIRST_NAME IS NULL;

Таблица Employees, Jobs, Departaments. Показать сотрудников в формате: First_name, Job_title, Department_name.
Пример:
First_name | Job_title | Department_name
Donald | Shipping | Clerk Shipping
Решение

SELECT first_name, job_title, department_name
  FROM employees  e
       JOIN jobs j ON (e.job_id = j.job_id)
       JOIN departments d ON (d.department_id = e.department_id);

Таблица Employees. Получить список сотрудников менеджеры которых устроились на работу в 2005ом году но при это сами эти работники устроились на работу до 2005 года
Решение

SELECT emp.*
  FROM employees emp JOIN employees man ON (emp.manager_id = man.employee_id)
 WHERE     TO_CHAR (man.hire_date, 'YYYY') = '2005'
       AND emp.hire_date < TO_DATE ('01012005', 'DDMMYYYY');

Таблица Employees. Получить список сотрудников менеджеры которых устроились на работу в январе месяце любого года и длина job_title этих сотрудников больше 15ти символов
Решение

SELECT emp.*
  FROM employees  emp
       JOIN employees man ON (emp.manager_id = man.employee_id)
       JOIN jobs j ON (emp.job_id = j.job_id)
 WHERE TO_CHAR (man.hire_date, 'MM') = '01' AND LENGTH (j.job_title) > 15;

Using Subqueries to Solve Queries

Таблица Employees. Получить список сотрудников с самым длинным именем.
Решение

SELECT *
  FROM employees
 WHERE LENGTH (first_name) =
       (SELECT MAX (LENGTH (first_name)) FROM employees);

Таблица Employees. Получить список сотрудников с зарплатой большей средней зарплаты всех сотрудников.
Решение

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

Таблица Employees, Departments, Locations. Получить город в котором сотрудники в сумме зарабатывают меньше всех.
Решение

SELECT city
    FROM employees e
         JOIN departments d ON (e.department_id = d.department_id)
         JOIN locations l ON (d.location_id = l.location_id)
GROUP BY city
  HAVING SUM (salary) =
         (  SELECT MIN (SUM (salary))
              FROM employees e
                   JOIN departments d ON (e.department_id = d.department_id)
                   JOIN locations l ON (d.location_id = l.location_id)
          GROUP BY city);

Таблица Employees. Получить список сотрудников у которых менеджер получает зарплату больше 15000.
Решение

SELECT *
  FROM employees
 WHERE manager_id IN (SELECT employee_id
                        FROM employees
                       WHERE salary > 15000)

Таблица Employees, Departaments. Показать все департаменты в которых нет ни одного сотрудника
Решение

SELECT *
  FROM departments
 WHERE department_id NOT IN (SELECT department_id
                               FROM employees
                              WHERE department_id IS NOT NULL);

Таблица Employees. Показать всех сотрудников которые не являются менеджерами
Решение

SELECT *
  FROM employees
 WHERE employee_id NOT IN (SELECT manager_id
                             FROM employees
                            WHERE manager_id IS NOT NULL)

Таблица Employees. Показать всех менеджеров которые имеют в подчинении больше 6ти сотрудников
Решение

SELECT *
  FROM employees e
 WHERE (SELECT COUNT (*)
          FROM employees
         WHERE manager_id = e.employee_id) > 6;

Таблица Employees, Departaments. Показать сотрудников которые работают в департаменте IT
Решение

SELECT *
  FROM employees
 WHERE department_id = (SELECT department_id
                          FROM departments
                         WHERE department_name = 'IT');

Таблица Employees, Jobs, Departaments. Показать сотрудников в формате: First_name, Job_title, Department_name.
Пример:
First_name | Job_title | Department_name
Donald | Shipping | Clerk Shipping
Решение

SELECT first_name,
       (SELECT job_title
          FROM jobs
         WHERE job_id = e.job_id)
           job_title,
       (SELECT department_name
          FROM departments
         WHERE department_id = e.department_id)
           department_name
  FROM employees e;

Таблица Employees. Получить список сотрудников менеджеры которых устроились на работу в 2005ом году но при это сами эти работники устроились на работу до 2005 года
Решение

SELECT *
  FROM employees
 WHERE     manager_id IN (SELECT employee_id
                            FROM employees
                           WHERE TO_CHAR (hire_date, 'YYYY') = '2005')
       AND hire_date < TO_DATE ('01012005', 'DDMMYYYY');

Таблица Employees. Получить список сотрудников менеджеры которых устроились на работу в январе месяце любого года и длина job_title этих сотрудников больше 15ти символов
Решение

SELECT *
  FROM employees e
 WHERE     manager_id IN (SELECT employee_id
                            FROM employees
                           WHERE TO_CHAR (hire_date, 'MM') = '01')
       AND (SELECT LENGTH (job_title)
              FROM jobs
             WHERE job_id = e.job_id) > 15;

На этом пока всё.

Надеюсь, задачи были интересными и увлекательными.
Буду по возможности дополнять этот список задач.
Также буду рад любым замечаниям и предложениям.

P.S.: Если кому то в голову придет интересная задача на SELECT, пишите в коментариях, добавлю в список.

Спасибо.

Источник: habr.com