СКЛ. Забавне загонетке

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

Већ више од 3 године предајем СКЛ у разним центрима за обуку и једно од мојих запажања је да студенти боље савладавају и разумеју СКЛ ако им се да задатак, а не само говоре о могућностима и теоретским основама.

У овом чланку поделићу са вама своју листу проблема које дајем ученицима као домаћи задатак и на којима спроводимо разне врсте браинсторминга, што доводи до дубоког и јасног разумевања СКЛ-а.

СКЛ (ˈɛсˈкјуˈɛл; енглески структурирани језик упита) је декларативни програмски језик који се користи за креирање, модификовање и управљање подацима у релационој бази података којом управља одговарајући систем за управљање базом података. Сазнајте више

О СКЛ-у можете читати из разних извори.
Овај чланак није намењен да вас научи СКЛ од нуле.

Па идемо.

Користићемо добро познато ХР дијаграм у Орацле-у са својим табелама (више):

Напомињем да ћемо разматрати само СЕЛЕЦТ задатке. Овде нема ДМЛ или ДДЛ задатака.

задаци

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

Табела запослених. Добијте листу са информацијама о свим запосленима
одлука

SELECT * FROM employees

Табела запослених. Добијте листу свих запослених по имену 'Давид'
одлука

SELECT *
  FROM employees
 WHERE first_name = 'David';

Табела запослених. Добијте листу свих запослених са јоб_ид једнаким 'ИТ_ПРОГ'
одлука

SELECT *
  FROM employees
 WHERE job_id = 'IT_PROG'

Табела запослених. Добијте списак свих запослених из 50. одељења (департмент_ид) са платом (платом) већом од 4000
одлука

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

Табела запослених. Добијте списак свих запослених из 20. и 30. одељења (департмент_ид)
одлука

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

Табела запослених. Добијте листу свих запослених чије је последње слово у имену 'а'
одлука

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

Табела запослених. Добијте листу свих запослених из 50. и 80. одељења (департмент_ид) који имају бонус (вредност у колони Цоммиссион_пцт није празна)
одлука

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

Табела запослених. Добијте листу свих запослених чија имена садрже најмање 2 слова 'н'
одлука

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;

Табела запослених. Добијте листу запослених са њиховим позицијама у формату: Доналд(сх_цлерк)
одлука

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;

Табела запослених. Добијте први троцифрени број телефона запосленог ако је његов број у формату КСКСКС.КСКСКС.КСКСКСКС
одлука

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;

Табела запослених. Добијте листу свих запослених чија вредност јоб_ид после знака '_' има најмање 3 знака, али ова вредност после '_' није једнака 'ЦЛЕРК'
одлука

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

Табела запослених. Добијте листу свих запослених тако што ћете заменити све „.“ у вредности ПХОНЕ_НУМБЕР на '-'
одлука

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.
одлука

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;

Табела запослених. Добијте листу свих запослених са пуним платама (плата + провизија_пцт(%)) у формату: $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;

Извештавање о агрегираним подацима помоћу функција групе

Табела запослених. Примите извештај од департмент_ид са минималном и максималном зарадом, раним и касним терминима доласка на посао и бројем запослених. Сортирај по броју запослених (опадајуће)
одлука

  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;

Табела запослених. Добијте листу департмент_идс који имају више од 30 запослених
одлука

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

Табела запослених. Добијте листу департмент_идс и заокружену просечну плату запослених у сваком одељењу.
одлука

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

Табле Цоунтриес. Добијте листу регион_ид као збир свих слова свих имена земаља у којима има више од 60
одлука

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

Табела запослених. Добијте листу департмент_идс у којима раде запослени неколико (>1) јоб_идс
одлука

  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 и који не примају бонусе (цоммиссион_пцт је празан)
одлука

  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;

Табела запослених. Добијте максималну плату од свих запослених јоб_ид који се завршава речју 'СЛОВНИК'
одлука

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;

Запослени у столу, Историја посла. Табела запослених чува све запослене. Табела Јоб_хистори чува запослене који су напустили компанију. Добијте извештај о свим запосленима и њиховом статусу у компанији (рад или напуштање компаније са датумом одласка)
Пример:
име | статус
Јеннифер | Напустио је компанију 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);

Запослени у табели, одељења, локације, земље, региони. Добијте листу запослених који живе у Европи (регион_наме)
одлука

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

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

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

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

То је све за сада.

Надам се да су задаци били занимљиви и узбудљиви.
Додаћу на ову листу задатака што је више могуће.
Такође ће ми бити драго да примим било какве коментаре и сугестије.

ПС: Ако неко смисли занимљив задатак СЕЛЕЦТ, пишите у коментарима и ја ћу га додати на листу.

Хвала.

Извор: ввв.хабр.цом