🥇SQL。有趣的謎題

你好哈布爾！

3 年多來，我一直在各種培訓中心教授 SQL，我的觀察之一是，如果給學生一個任務，他們會更好地掌握和理解 SQL，而不僅僅是談論可能性和理論基礎。

在本文中，我將與您分享我給學生佈置的任務列表，我們在這些任務上進行了各種頭腦風暴，從而對 SQL 有了深刻而清晰的理解。

SQL (ˈɛsˈkjuˈɛl; eng. structured query language) 是一種聲明性編程語言，用於在由適當的數據庫管理系統管理的關係數據庫中創建、修改和管理數據。閱讀更多......

您可以從不同的地方閱讀有關 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_clerk)
解決方法

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

使用單行函數自定義輸出

員工表。獲取姓名超過10個字母的所有員工的列表
解決方法

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

員工表。獲取姓名中包含字母“b”的所有員工的列表（不區分大小寫）
解決方法

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

員工表。獲取姓名至少包含 2 個字母 'a' 的所有員工的列表
解決方法

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;

員工表。獲取姓名最後一個字母等於'm'且姓名長度大於5的所有員工列表
解決方法

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

雙表。獲取下週五的日期
解決方法

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

員工表。獲取在公司工作超過 17 年的所有員工的列表
解決方法

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

員工表。獲取電話號碼末位為奇數且由點號分隔的 3 個數字組成的所有員工的列表
解決方法

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

員工表。獲取所有員工的列表，這些員工的 job_id 值在 '_' 符號之後至少有 3 個字符，但是 '_' 之後的這個值不等於 'CLERK'
解決方法

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

員工表。通過替換 PHONE_NUMBER 值中的所有“.”來獲取所有員工的列表在 '-'
解決方法

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

使用轉換函數和條件表達式

員工表。獲取當月第一天來上班的所有員工的列表（任意）
解決方法

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

員工表。獲取 2008 年所有來上班的員工的列表
解決方法

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

雙表。以以下格式顯示明天的日期：明天是一月的第二天
解決方法

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

員工表。獲取所有員工及其開始日期的列表，格式為：21 年 2007 月 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;

員工表。獲取所有具有全薪的員工列表（薪水 + 佣金_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;

員工表。獲取超過 30 名員工的 department_id 列表
解決方法

  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;

國家表。獲取其中超過60個的所有country_names的所有字母的region_id總和列表
解決方法

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

員工表。獲取 department_id 的列表，其中有幾個 (>1) job_id 的員工在工作
解決方法

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

員工表。獲取下屬人數大於5且下屬所有工資總和大於50000的manager_id列表
解決方法

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

員工表。獲取manager_id的所有下屬的平均工資在6000到9000之間沒有拿到獎金的列表（commission_pct為空）
解決方法

  SELECT manager_id, AVG (salary) avg_salary
    FROM employees
   WHERE commission_pct IS NULL
GROUP BY manager_id
  HAVING AVG (salary) BETWEEN 6000 AND 9000;

員工表。從所有以 'CLERK' 結尾的員工 job_id 中獲取最高薪水
解決方法

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

員工表。獲取其經理在任何一年的一月份獲得工作並且這些員工的 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;

表員工，部門。顯示在 IT 部門工作的員工
解決方法

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

表員工、職位、部門。以以下格式顯示員工：First_name、Job_title、Department_name。
示例：
名字 | 職稱 | 部門名稱
唐納德 | 航運 | 文員送貨
解決方法

SELECT first_name,
       (SELECT job_title
          FROM jobs
         WHERE job_id = e.job_id)
           job_title,
       (SELECT department_name
          FROM departments
         WHERE department_id = e.department_id)
           department_name
  FROM employees e;

員工表。獲取其經理在 2005 年找到工作的員工列表，但與此同時，這些工人自己在 2005 年之前找到了工作
解決方法

SELECT *
  FROM employees
 WHERE     manager_id IN (SELECT employee_id
                            FROM employees
                           WHERE TO_CHAR (hire_date, 'YYYY') = '2005')
       AND hire_date < TO_DATE ('01012005', 'DDMMYYYY');

員工表。獲取其經理在任何一年的一月份獲得工作並且這些員工的 job_title 長度超過 15 個字符的員工列表
解決方法

SELECT *
  FROM employees e
 WHERE     manager_id IN (SELECT employee_id
                            FROM employees
                           WHERE TO_CHAR (hire_date, 'MM') = '01')
       AND (SELECT LENGTH (job_title)
              FROM jobs
             WHERE job_id = e.job_id) > 15;

目前為止就這樣了。

我希望這些任務是有趣和令人興奮的。
我將盡可能多地添加到此列表中。
我也很樂意接受任何意見和建議。

PS：如果有人在 SELECT 上提出了一個有趣的任務，請寫在評論中，我會把它添加到列表中。

謝謝。

來源： www.habr.com

SQL。 有趣的謎題

任務

添加評論 取消回复

SQL。有趣的謎題

添加評論取消回复