SQL. Занимателни пъзели

Здравей Habr!

Повече от 3 години преподавам SQL в различни обучителни центрове и едно от моите наблюдения е, че студентите усвояват и разбират SQL по-добре, ако им се даде задача, а не просто се говори за възможностите и теоретичните основи.

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

SQL (ˈɛsˈkjuˈɛl; англ. структуриран език за заявки) е декларативен език за програмиране, използван за създаване, модифициране и управление на данни в релационна база данни, управлявана от подходяща система за управление на база данни. Повече подробности ...

Можете да прочетете за SQL от различни източници.
Тази статия няма за цел да ви научи на SQL от нулата.

Така че да вървим.

Ще използваме добре познатите HR схема в Oracle с неговите таблици (Още):

Отбелязвам, че ще разглеждаме само задачи на SELECT. Няма задачи на DML и DDL.

задачи

Ограничаване и сортиране на данни

Таблица на служителите. Вземете списък с информация за всички служители
Решение

SELECT * FROM employees

Таблица на служителите. Вземете списък с всички служители на име „Дейвид“
Решение

SELECT *
  FROM employees
 WHERE first_name = 'David';

Таблица на служителите. Вземете списък на всички служители с job_id равен на 'IT_PROG'
Решение

SELECT *
  FROM employees
 WHERE job_id = 'IT_PROG'

Таблица на служителите. Вземете списък на всички служители от 50-ти отдел (department_id) със заплата (salary) над 4000
Решение

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

Таблица на служителите. Вземете списък на всички служители от 20-ти и от 30-ти отдел (department_id)
Решение

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

Таблица на служителите. Вземете списък на всички служители, чиято последна буква в името е „а“
Решение

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

Таблица на служителите. Вземете списък на всички служители от 50-ти и от 80-ти отдел (department_id), които имат бонус (стойността в колоната Commission_pct не е празна)
Решение

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

Таблица на служителите. Вземете списък с всички служители, чието име съдържа поне 2 букви „n“
Решение

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

Таблица на служителите. Вземете списък с всички служители, чието име е по-дълго от 4 букви
Решение

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

Таблица на служителите. Вземете списък на всички служители, чиято заплата е между 8000 и 9000 (включително)
Решение

SELECT *
  FROM employees
 WHERE salary BETWEEN 8000 AND 9000;

Таблица на служителите. Вземете списък с всички служители, чието име съдържа символа „%“
Решение

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

Таблица на служителите. Вземете списък с всички идентификатори на мениджъри
Решение

SELECT DISTINCT manager_id
  FROM employees
 WHERE manager_id IS NOT NULL;

Таблица на служителите. Вземете списък на служителите с техните позиции във формат: Donald(sh_clerk)
Решение

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

Използване на функции с един ред за персонализиране на изхода

Таблица на служителите. Вземете списък с всички служители, чието име е по-дълго от 10 букви
Решение

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

Таблица на служителите. Вземете списък на всички служители, които имат буквата „b“ в името си (без значение за малки и големи букви)
Решение

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

Таблица на служителите. Вземете списък с всички служители, чието име съдържа поне 2 букви „а“
Решение

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

Таблица на служителите. Вземете списък на всички служители, чиято заплата е кратна на 1000
Решение

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

Таблица на служителите. Вземете първото 3-цифрено число от телефонния номер на служителя, ако номерът му е във формат ХХХ.ХХХ.ХХХХ
Решение

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

Таблица на отделите. Вземете първата дума от името на отдела за тези с повече от една дума в името
Решение

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

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

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

Таблица на служителите. Вземете списък на всички служители, чиято последна буква в името е равна на 'm' и дължината на името е по-голяма от 5
Решение

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

Двойна маса. Получете датата следващия петък
Решение

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

Таблица на служителите. Вземете списък на всички служители, които работят в компанията повече от 17 години
Решение

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

Таблица на служителите. Получете списък на всички служители, чиято последна цифра от телефонния номер е нечетна и се състои от 3 числа, разделени с точка
Решение

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

Таблица на служителите. Вземете списък на всички служители, чиято стойност на 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';

Таблица на служителите. Получете списък с всички служители, като замените всички „.“ в стойността PHONE_NUMBER На '-'
Решение

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

Използване на функции за преобразуване и условни изрази

Таблица на служителите. Вземете списък на всички служители, дошли на работа на първия ден от месеца (всеки)
Решение

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

Таблица на служителите. Вземете списък на всички служители, дошли на работа през 2008 г
Решение

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

ДВОЙНА маса. Покажете утрешната дата във формат: Утре е втори ден на януари
Решение

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

Таблица на служителите. Вземете списък на всички служители и началната им дата във формат: 21 юни 2007 г
Решение

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

Таблица на служителите. Вземете списък със служители с повишени заплати с 20%. Покажете заплатата със знака за долар
Решение

SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
  FROM 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'; 

ДВОЙНА маса. Експортиране на текуща дата, + секунда, + минута, + час, + ден, + месец, + година
Решение

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;

Таблица на служителите. Вземете списък на всички служители с пълни заплати (заплата + комисионна_pct(%)) във формат: $24,000.00
Решение

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

Таблица на служителите. Получете списък на всички служители и информация за наличието на бонуси към заплатата (Да/Не)
Решение

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

Таблица на служителите. Получете нивото на заплатата на всеки служител: По-малко от 5000 се счита за ниско ниво, по-голямо или равно на 5000 и по-малко от 10000 се счита за нормално ниво, по-голямо или равно на 10000 се счита за високо ниво
Решение

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;

Таблица на държавите. За всяка държава покажете региона, в който се намира: 1-Европа, 2-Америка, 3-Азия, 4-Африка (без 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;

Отчитане на обобщени данни с помощта на груповите функции

Таблица на служителите. Вземете отчет по 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;

Таблица на служителите. Колко служители, чиито имена започват с една и съща буква? Подреди по количество. Покажи само тези, където числото е по-голямо от 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;

Таблица на служителите. Колко служители работят в един отдел и получават една и съща заплата?
Решение

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

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

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

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

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

Таблица на служителите. Получете броя на отделите, които имат служители
Решение

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

Таблица на служителите. Вземете списък с department_id с повече от 30 служители
Решение

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

Таблица на служителите. Вземете списък с department_ids и закръглената средна заплата на служителите във всеки отдел.
Решение

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

Таблица на държавите. Вземете списък с region_id сбор от всички букви на всички имена на държави, в които повече от 60
Решение

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

Таблица на служителите. Вземете списък с department_id, в който работят служители на няколко (>1) job_id
Решение

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

Таблица на служителите. Вземете списък с manager_id, чийто брой подчинени е по-голям от 5 и сумата от всички заплати на неговите подчинени е по-голяма от 50000 XNUMX
Решение

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

Таблица на служителите. Вземете списък с 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;

Таблица на служителите. Вземете максималната заплата от всички служители 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';

Таблица на служителите. Вземете максималната заплата сред всички средни заплати за отдела
Решение

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

Таблица на служителите. Вземете броя на служителите с еднакъв брой букви в името си. В същото време показвайте само онези, чието име е по-дълго от 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);

Показване на данни от множество таблици с помощта на съединения

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

  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;

Таблица Служители, отдели, местоположения, държави, региони. Получете подробна информация за всеки служител:
First_name, Last_name, Department, 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);

Таблица на служителите. Покажете всички мениджъри, които имат повече от 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;

Таблица на служителите. Покажете всички служители, които не се отчитат пред никого
Решение

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;

Таблица на служителите, Job_history. Таблицата Employee съхранява всички служители. Таблицата Job_history съхранява служители, които са напуснали компанията. Вземете отчет за всички служители и техния статус в компанията (Наети или напуснали компанията с датата на напускане)
Пример:
първо_име | състояние
Дженифър | Напуска компанията на 31 декември 2006 г
Клара | В момента работя
Решение

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);

Таблица Служители, отдели, местоположения, държави, региони. Вземете списък със служители, които живеят в Европа (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';

Таблица Служители, отдели. Покажи всички отдели с повече от 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;

Таблица Служители, отдели. Покажете всички служители, които не са в нито един отдел
Решение

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;

Таблица Служители, отдели. Показване на всички отдели без служители
Решение

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

Таблица на служителите. Покажете всички служители, които нямат подчинени
Решение

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

Таблица Служители, работни места, отдели. Показване на служители във формат: First_name, Job_title, Department_name.
Пример:
първо_име | длъжност | име на отдел
Доналд | доставка | Чиновник Доставка
Решение

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);

Таблица на служителите. Вземете списък на служителите, чиито мениджъри са получили работа през 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');

Таблица на служителите. Вземете списък със служители, чиито мениджъри са получили работа през месец януари на която и да е година и дължината на 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;

Използване на подзаявки за решаване на заявки

Таблица на служителите. Вземете списък със служители с най-дълго име.
Решение

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

Таблица на служителите. Вземете списък със служители със заплата, по-висока от средната заплата на всички служители.
Решение

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

Таблица Служители, отдели, местоположения. Вземете града, в който служителите печелят най-малко общо.
Решение

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);

Таблица на служителите. Вземете списък със служители, чийто мениджър получава заплата над 15000 XNUMX.
Решение

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

Таблица Служители, отдели. Показване на всички отдели без служители
Решение

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

Таблица на служителите. Покажете всички служители, които не са мениджъри
Решение

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

Таблица на служителите. Покажете всички мениджъри, които имат повече от 6 служители
Решение

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

Таблица Служители, отдели. Покажете служители, които работят в ИТ отдела
Решение

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

Таблица Служители, работни места, отдели. Показване на служители във формат: First_name, Job_title, Department_name.
Пример:
първо_име | длъжност | име на отдел
Доналд | доставка | Чиновник Доставка
Решение

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;

Таблица на служителите. Вземете списък на служителите, чиито мениджъри са получили работа през 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');

Таблица на служителите. Вземете списък със служители, чиито мениджъри са получили работа през месец януари на която и да е година и дължината на 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;

Това е всичко за сега.

Надявам се задачите да са били интересни и вълнуващи.
Ще добавя към този списък колкото е възможно повече.
Ще се радвам и на всякакви коментари и предложения.

PS: Ако някой измисли интересна задача на SELECT, пишете в коментарите, ще я добавя към списъка.

Благодаря.

Източник: www.habr.com