你好哈布尔!
3 年多来,我一直在各种培训中心教授 SQL,我的观察之一是,如果给学生一个任务,他们会更好地掌握和理解 SQL,而不仅仅是谈论可能性和理论基础。
在本文中,我将与您分享我给学生布置的任务列表,我们在这些任务上进行了各种头脑风暴,从而对 SQL 有了深刻而清晰的理解。
SQL (ˈɛsˈkjuˈɛl; eng. structured query language - “structured query language”) 是一种声明性编程语言,用于在由适当的数据库管理系统管理的关系数据库中创建、修改和管理数据。
您可以从不同的地方阅读有关 SQL 的信息
本文无意从头教您 SQL。
所以,我们走吧。
我们将使用众所周知的
我注意到我们将只考虑 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;
员工表。 获取manager_id的下属人数大于5且下属所有工资总和大于50000的列表
解
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 上提出了一个有趣的任务,请写在评论中,我会把它添加到列表中。
谢谢。
来源: habr.com