🥇SQL. Забавни загатки

Здраво, Хабр!

Веќе повеќе од 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) со плата (плата) поголема од 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) кои имаат бонус (вредноста во колоната комисија_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;

Табела за вработени. Добијте список на сите вработени кои ја имаат буквата „б“ во нивното име (не осетливи на букви)
Решение

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-цифрен број од телефонскиот број на вработениот ако неговиот број е во формат XXX.XXX.XXXX
Решение

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;

Табела за вработени. Добијте листа на сите вработени чија последна буква во нивното име е „м“ и чие име е подолго од 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';

Табела DUAL. Прикажи го утрешниот датум во формат: Утре е втор ден од јануари
Решение

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

Табела 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;

Табела за вработени. Добијте листа на сите вработени со целосни плати (плата + провизија_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-Африка (без придружување)
Решение

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_ids кои имаат повеќе од 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;

Табела за вработени. Добијте листа на идентификатори на оддели во кои работат вработени од неколку (>1) job_ids
Решение

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

Табела за вработени. Добијте листа на менаџери чиј број на подредени е поголем од 5, а збирот на сите плати на неговите подредени е поголем од 50000
Решение

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

Табела за вработени. Добијте листа на менаџери чија просечна плата на сите негови подредени е во опсег од 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;

Табела вработени, одделенија, локации, земји, региони. Добијте детални информации за секој вработен:
Име, Презиме, Одделение, Работа, Улица, Земја, Регион
Решение

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_istory. Табелата Вработен ги складира сите вработени. Табелата 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;

Табела вработени, работни места, одделенија. Прикажи ги вработените во формат: Име_име, Работно_назив, Име_оддел.
Пример:
Прво_име | Работно_назив | Име_оддел
Доналд | Испорака | Службеник Испорака
Решение

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.
Решение

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

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;

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

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

SQL. Забавни загатки

задачи

Додадете коментар Откажи одговор