🥇SQL。面白いパズル | プロホスター

こんにちはハブル！

私は 3 年以上、さまざまなトレーニングセンターで SQL を教えてきましたが、その中で観察したことの XNUMX つは、可能性や理論的基礎について話すだけでなく、課題が与えられた場合、学生は SQL をよりよく習得し、理解するようになるということです。

この記事では、SQL を深く明確に理解するために、私が学生に宿題として与え、さまざまな種類のブレインストーミングを実施するタスクのリストを共有します。

SQL (ˈɛsˈkjuˈɛl; 英語の構造化照会言語 - 「構造化照会言語」) は、適切なデータベース管理システムによって管理されるリレーショナルデータベース内のデータを作成、変更、管理するために使用される宣言型プログラミング言語です。続きを読む...

SQL についてはさまざまな情報から読むことができます。ソースの.
この記事は、SQL をゼロから教えることを目的としたものではありません。

それでは行きましょう。

よく知られているものを使用します人事制度 Oracle とそのテーブル (もっと):

SELECT のタスクのみを考慮することに注意してください。 DML および DDL にはタスクはありません。

タスク

データの制限と並べ替え

従業員テーブル。全従業員に関する情報を含むリストを取得する
ソリューション

SELECT * FROM employees

従業員テーブル。「David」という名前の全従業員のリストを取得します
ソリューション

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;

従業員テーブル。名前の最後の文字が「a」であるすべての従業員のリストを取得します
ソリューション

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

従業員テーブル。すべてのマネージャー ID のリストを取得する
ソリューション

SELECT DISTINCT manager_id
  FROM employees
 WHERE manager_id IS NOT NULL;

従業員テーブル。従業員のリストとその役職を次の形式で取得します: Donald(sh_clark)
ソリューション

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 文字の「a」が含まれるすべての従業員のリストを取得します
ソリューション

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

デュアルテーブル。明日の日付を次の形式で表示します: 明日は XNUMX 月 XNUMX 日です
ソリューション

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

従業員テーブル。全従業員のリストとその入社日を次の形式で取得します: 21 年 2007 月 XNUMX 日
ソリューション

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 年 XNUMX 月に出勤した全従業員のリストを取得します。
ソリューション

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 の形式で、全額給与 (salary + Commission_pct(%)) を持つ全従業員のリストを取得します。
ソリューション

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;

グループ機能を使用した集計データのレポート

従業員テーブル。最低給与と最高給与、早着日と遅着日、従業員数を含むレポートを部門 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;

従業員テーブル。従業員が 30 名を超える部門 ID のリストを取得する
ソリューション

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

従業員テーブル。部門 ID のリストと各部門の従業員の四捨五入された平均給与を取得します。
ソリューション

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

国のテーブル。 60 を超えるすべての国名のすべての文字を合計した地域 ID のリストを取得します。
ソリューション

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

従業員テーブル。複数 (1 つ以上) の job_id を持つ従業員が勤務する部門 ID のリストを取得します。
ソリューション

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

従業員テーブル。部下の数が 5 人を超え、部下のすべての給与の合計が 50000 を超える manager_id のリストを取得します。
ソリューション

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

従業員テーブル。ボーナスを受け取っていない部下全員の平均給与が 6000 ～ 9000 である manager_id のリストを取得します (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;

従業員テーブル。「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_history。 Employee テーブルにはすべての従業員が格納されます。 Job_history テーブルには、退職した従業員が保存されます。すべての従業員と社内でのステータス (入社または退職日を伴う退職) に関するレポートを取得します。
例：
名 | スターテス
ジェニファー | 31年2006月XNUMX日退社
クララ | 今、働いている
ソリューション

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

従業員テーブル。任意の年の 15 月にマネージャーが就職し、これらの従業員の job_title の長さが XNUMX 文字を超えている従業員のリストを取得します。
ソリューション

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;

テーブルの従業員、部門。 IT 部門で働く従業員を表示します。
ソリューション

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 で興味深いタスクを思いついた場合は、コメントに書いてください。リストに追加します。

ありがとう。

出所： habr.com

SQL。楽しいパズル

タスク

コメントを追加します返信をキャンセル

SQL。 楽しいパズル

タスク

コメントを追加します 返信をキャンセル

SQL。楽しいパズル

コメントを追加します返信をキャンセル