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
           AND INSTR (phone_number,'.',1,2) > 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'
               WHEN salary >= 10000 THEN 'High'
               ELSE 'Unknown'
           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, пишите в коментариях, добавлю в список.

    Спасибо.
    Ads
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More

    Comments 63

      0
      Таблица Employees. Получить список всех сотрудников у которых длинна имени больше 4 букв
      Решение
      SELECT *
      FROM employees
      WHERE first_name LIKE '%____%';


      Интересное решение.
      Обычно использую прямо в лоб LENGTH(first_name) > 4 (как в другом примере здесь видел)
      Какие есть преимущества у такого способа по сравнению с length?

      И спасибо за задачки — очень интересно.
        –2
        Конечно же тут лучше использовать функцию length.
        Данное решение здесь указано так потому что задача находится в теме «Restricting and Sorting Data», а предпологается что на этом этапе (это начало SQL) студент еще не знает о существовании функции length.
          +1
          Разумеется, функцию LENGTH использовать нельзя, т.к. это обойдется нам в index scan, как минимум.

          DedZamkad
          SELECT *
          FROM employees
          WHERE first_name LIKE '%____%';

          И так тоже не хорошо, т.к. из %% тоже получим сканирование индекса.
          Вот так, при некотором везении, получим seek:
          WHERE first_name LIKE '[А-Я][-''. А-Я][-''. А-Я][-''. А-Я]%'
          Ну, конечно, тут придется еще дополнительно учесть регистр, если коллейшн регистрозависимый, и особенности хранения данных.
          Например — грязь в виде лидирующих пробелов (чего быть не должно), пробелов, тире, точек, апострофов внутри составного имени ([-''. А-Я] — как раз учитывает это).
          Впрочем, это всё для MSSQLSERVER, а в ставке Гитлера — все малохольные а у Оракула — всё иначе.
          :-)
            +1
              0
              О. Хорошая штука.
              В MSSQLSERVER — так нельзя. Придется явно создать в таблице вычисляемое поле, и проиндексировать его. Тогда оно будет подхватываться функцией во Where, если выражение будет тем же самым.
          +2
          Может, я что-то не понимаю, но чтобы выполнялось условие «длина>4» не должно ли здесь быть 5 символов подчеркивания вместо 4?
            –1
            Верно подмечено) Спасибо, исправил
              +3
              Кстати, явное преимущество у length в читаемости запроса.
                +1
                Но даже если использовать LIKE — один из знаков процента (причём любой) явно избыточен.
              +2

              Более того достаточно условия first_name LIKE '____%'

            • UFO just landed and posted this here
                0
                Принципиальной разницы нет. Оба варианта верны и имеют место быть
                • UFO just landed and posted this here
                    0
                    --почему не YEAR(DATE) а какой то ужас с текстом?
                    В Оракле для работы с датой используется функция TO_DATE. YEAR(DATE) — в оракле такого на сколько я знаю нет.

                    Схему базы можно взять тут
                    • UFO just landed and posted this here
                        –1
                        SELECT EXTRACT (MONTH FROM SYSDATE) FROM DUAL

                        но все равно TO_DATE, TO_CHAR очень удобные и функциональные. С EXTRACT активно не имел дело
                  0
                  DataGrip кстати предлагает такую траснформацию в коде :)image
                  • UFO just landed and posted this here
                      0
                      Последнее верно :)
                      • UFO just landed and posted this here
                          0
                          Ничего не мешает познакомиться :) Тем более там и MySQL и SQL Server поддержка есть.
                          www.jetbrains.com/datagrip/features
                          • UFO just landed and posted this here
                  0

                  SQL многие произносят как cиквел, и это исторически верно, поскольку SQL — продолжение языка Square, и первое написание было SEQUEL (Structured English Query Language). В английской википедии этот
                  момент подробно описан.

                    0
                    Самое интересное — это при помощи селект выбор графа.
                    пусть будет таблица ID родитель, остальные колонки — дети.
                    у детей могут быть дети в этой или другой таблице, и т.п.
                    выбрать всех детей родителя, так называемый субсет.

                    Интересно как сама БД реагирует на селект. Наивное решение было бы просмотреть все строки и выбрать нужные, но подозреваю там оптимизации в виде неких хешей вроде
                    значение поля -> все строки где это значение.
                      0
                      Проведите свое собственное расследование и напишите статью на хабре об этом и поделитесь тут ) я с удовольствием почитаю
                    +2
                    Таблица Employees. Получить список сотрудников менеджеры которых устроились на работу в январе месяце любого года и длинна job_title этих сотрудников больше 15ти символов

                    Не лучше ли требовать со студентов решение с JOIN-ами вместо подзапросов, приучать к хорошему стилю?
                      0
                      Эта задача есть как и в теме Join так и в теме Subqueries. Correlated subsquery можно заменить joinами и наобарот. Я всетаки предпочитаю чтобы студенты знали это.
                        0

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

                        –5

                        Такие задачки очень удобно решать в каком-нибудь конструкторе запросов, в MS Access, например. :)

                          –3
                          ещё прикольная у меня идейка сделать интерпретатор естественного языка вроде предложение на обычнося языке -> запрос
                            +2

                            На мой взгляд, тренироваться писать SQL запросы в конструкторе — зло. Это может быть и удобно чтобы порешать задачки, но на практическое применение переносится крайне плохо. Где будет ваш конструктор, когда вам нужно будет забрать данные по jdbc/odbc?

                              –1
                              писать SQL запросы в конструкторе — зло

                              Так и запишем, Microsoft делает злые вещи.


                              тренироваться писать SQL запросы в конструкторе — зло

                              Наоборот, очень помогло по-быстрому освоить SQL.


                              Это может быть и удобно чтобы порешать задачки, но на практическое применение переносится крайне плохо.

                              В 90-x очень плотно работал с базами данными. Очень сильно помогало по-быстрому строить запросы и отчеты. Конструкторы запросов — это про быструю разработку.


                              Где будет ваш конструктор, когда вам нужно будет забрать данные по jdbc/odbc?

                              Рядом будет. В результатах работы конструктора мы имеет также и сам SQL запрос.

                                +2
                                Конструкторы запросов — это про быструю разработку.

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


                                Рядом будет. В результатах работы конструктора мы имеет также и сам SQL запрос.

                                Я правильно понимаю, что вы сразу используете тот запрос, который вам сгенерировал конструктор, и никак его не допиливаете?
                                Какой был самый сложный запрос, который вы писали?

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

                                  Какие-то простые запросы может и да. Несколько более сложные запросы проще накидать в конструкторе.


                                  Я правильно понимаю, что вы сразу используете тот запрос, который вам сгенерировал конструктор, и никак его не допиливаете?

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


                                  Какой был самый сложный запрос, который вы писали?

                                  Большой-большой секрет маленькой компании.


                                  В начале освоения SQL конструктор очень хорошо помогал. Сейчас, кончено, и без конструктора могу. Конструктор еще помогал найти решения, о которых я бы даже и не подозревал зная только стандарт SQL. Что помогало строить запросы одной строчкой, без необходимости писать дополнительный код и выполнять несколько запросов, создавая лишнюю работу и лишний трафик между клиентом и сервером БД.

                                    0
                                    сразу написать запрос получится быстрее, чем накликивать его мышкой в конструкторе.
                                    не всегда :). Порой, всё же, быстрее накликать 15 таблиц, чтобы через внешние ключи из 3-х таблиц выбрать несколько столбцов, да ещё и с парой фильтров по связующим таблицам. Но, надо помнить, что конструктор придуман не для разработчиков. И запросы там могут быть не оптимальны.
                              +1

                              Спасибо за статью.
                              Расскажите, вы намеренно собрали запросы с группировкой и с having в один раздел?
                              Как студенты справлялись с появлением сразу двух новых концепций?


                              Можете добавить, на каких примерах вы объясняли каждую из тем?

                                –1
                                --Расскажите, вы намеренно собрали запросы с группировкой и с having в один раздел?
                                Я преподаю по официальной книге по подготовке к сертификации. И в ней having и group by в одной и теме. Они связаны, их лучше проходить вместе.

                                --Можете добавить, на каких примерах вы объясняли каждую из тем?
                                Так вот целью данной статьи и было показать какие задачи мы решаем со студентами по темам. Я разделил задачи по темам.
                                  +1
                                  целью данной статьи и было показать какие задачи мы решаем со студентами по темам.

                                  Это понятно. Но вы ведь перед началом решения даёте какую то теорию? Какой пример вы разбираете в каждой из тем?

                                    0
                                    У меня есть ppt презентации от Oracle для преподователей. По ним и провожу уроки
                                0

                                Ещё интересно, как проверяете решения?
                                Глазами смотрите в запрос или как-то более надёжно?
                                Что делаете, если у студента оригинальное решение?
                                Что если данных много и глазами проверить трудно?

                                  0
                                  Хороший вопрос. В основном проверяю на глаз потому что сразу вижу что не так и правильно ли составлен SQL. Если что то сложное и оригинальное решение проверяю в первую очередь верен ли результат выполнения, во вторую — саму реализацию. Стараюсь придерживатся принципа KISS
                                    +1

                                    Посмотрите как на sql-ex.ru проверка делается (где-то там рассказывались детали).

                                  +1
                                  И ни одной задачи на CTE… Печаль.
                                  И Rank обошли стороной.
                                    +1

                                    И все статистические оконные функции, и outer/cross apply, да даже банального exists нет (а в главе Using Subqueries to Solve Queries точно есть задачи, которые удобнее и понятнее решать exists). Не увидел union/except/intersect.
                                    Есть запросы, где разный регистр ключевых слов ("Reporting Aggregated Data Using the Group Functions" — первая задача)
                                    В некоторых запросах учит откровенно плохому (мне глаз резануло WHERE TO_CHAR (hire_date, 'YYYY') = '2008')
                                    Дальше просто лень перечислять.


                                    Итого — так себе списочек.

                                      0
                                      Спасибо за отзыв ) Буду рад вашим подробным замечаниям… напишите пожалуйста что бы вы исправили.

                                      EXISTS — задач нет, но добавлю в список.
                                      union/except/intersect — это тема также идет после subqueries. Ее тоже добавлю.

                                      Конечно задачи для новичков и тех кто учится SQL. Для человека опытного задачи могут показаться не интересными и не нужными. Предложите свой список. Было бы интересно глянуть
                                        +1
                                        Так проблема не в списочке задач а в списочке решений :)
                                          0
                                          Ну так напишите ваши решения ) В чем проблема? поделитесь знаниями)
                                            0
                                            Это был не комент к посту, а комент к коменту
                                        –1
                                        Тут половина задач тривиальны, есть куча кривоватых решений…
                                        Совсем не понятно, почему это названо занимательными задачами.
                                          +1
                                          Предложите пожалуйста ваши решения там где считаете что решение кривое. Будет полезно как и мне так и всем читателям. Спасибо
                                            0
                                            На кривоватость вам уже указали. Скажите лучше, с какой стороны эти задачки занимательны?
                                              0
                                              так вы таки скажите как выбрать с помощью select граф или ещё называется субсет?
                                            0

                                            Получается, что ваши студенты смогут найти решение любой из ваших задач в интернете просто загуглив условие?
                                            Я попробовал на "Таблица Employees. Получить список сотрудников с самым длинным именем." и первая ссылка в выдаче — на эту статью.

                                              +1
                                              Решение задачи «Таблица Employees. Получить список всех сотрудников у которых последня цифра телефонного номера нечетная и состоит из 3ех чисел разделенных точкой» — неверное. Запрос вернёт любые значения, где последний символ — чётная цифра, а количество точек не превышает 2. Одна точка, нет точек, куча букв — неважно. Ибо схема на поле PHONE_NUMBER вообще никаких ограничений не накладывает, кроме указания типа, и туда можно писать что угодно — хоть кличку любимого попугая.
                                                +1
                                                Решение задачи «Таблица Employees. Получить список всех сотрудников у которых в значении job_id после знака '_' как минимум 3 символа но при этом это значение после '_' не равно 'CLERK'» также неверно. Запись со значением JOB_ID = 'IS_A_CLERK' будет выведена… и со значением 'AA_BB_CC'… Знак подчёркивания следует искать от конца (третий параметр функции INSTR равен -1).
                                                  +1
                                                  Решение задачи «Таблица Employees. Получить уровень зарплаты каждого сотрудника: Меньше 5000 считается Low level, Больше или равно 5000 и меньше 10000 считается Normal level, Больше иои равно 10000 считается High level» неверное. Если salary не указано (в поле NULL — на поле нет ограничения NOT NULL) — для такой записи будет выведено 'High', что вряд ли можно считать корректным…
                                                    0
                                                    да, учитывая возможность там NULL значений запрос можно переписать в виде
                                                    SELECT first_name,
                                                           salary,
                                                           CASE
                                                               WHEN salary < 5000 THEN 'Low'
                                                               WHEN salary >= 5000 AND salary < 10000 THEN 'Normal'
                                                               WHEN salary >= 10000 THEN 'High'
                                                               ELSE 'Unknown'
                                                           END
                                                               salary_level
                                                      FROM employees;
                                                    
                                                    0
                                                    Мы предпологаем что в job_id всего один символ "_" и поэтому ищем первое его вхождение.

                                                    --Запись со значением JOB_ID = 'IS_A_CLERK' будет выведена
                                                    не будет, так как не удовлетворяет условию
                                                    LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3


                                                    --и со значением 'AA_BB_CC'
                                                    не будет выведена, так как не удовлетворяет обоим условиям
                                                      +1
                                                      По мнению livesql.oracle.com значения
                                                      SELECT LENGTH (SUBSTR ('IS_A_CLERK', INSTR ('IS_A_CLERK', '_') + 1)) FROM DUAL;
                                                      SELECT LENGTH (SUBSTR ('AA_BB_CC', INSTR ('AA_BB_CC', '_') + 1)) FROM DUAL;
                                                      равны 7 и 5 соответственно. Оба больше 3. Так что первое условие удовлетворяется для обоих значений.

                                                      По его же мнению значения
                                                      SELECT SUBSTR ('IS_A_CLERK', INSTR ('IS_A_CLERK', '_') + 1) FROM DUAL;
                                                      SELECT SUBSTR ('AA_BB_CC', INSTR ('AA_BB_CC', '_') + 1) FROM DUAL;
                                                      равны 'A_CLERK' и 'BB_CC', оба не равны 'CLERK', так что и второе условие удовлетворяется для обоих значений.

                                                      Итого — оба значения будут выведены.

                                                      Мы предпологаем что в job_id всего один символ "_"
                                                      Вот только Вы как-то забыли явно указать этот, несомненно важный, момент. А структурой данных, на которую Вы ссылаетесь, подобные ограничения не устанавливаются.
                                                    0
                                                    Добавил условие на проверку наличия второй точки.
                                                    SELECT *
                                                      FROM employees
                                                     WHERE     MOD (SUBSTR (phone_number, -1), 2) != 0
                                                           AND INSTR (phone_number,'.',1,3) = 0
                                                           AND INSTR (phone_number,'.',1,2) > 0;
                                                    
                                                      +1
                                                      А как насчёт проверки, что
                                                      состоит из 3ех чисел разделенных точкой

                                                      А то, знаете ли, какое-нибудь 'AAA.BBB.CCC4' выведется, хотя и не соответствует предъявленным требованиям. И никаких ограничений в структуре, запрещающих указанное значение, опять-таки нет.

                                                  Only users with full accounts can post comments. Log in, please.