第一章.选择与过滤练习

  1. # 1.查询工资大于12000的员工姓名和工资
  2. SELECT last_name,salary
  3. FROM employees
  4. WHERE salary>12000;
  7. # 2.查询员工号为176的员工的姓名和部门号
  8. SELECT last_name,department_id
  9. FROM employees
  10. WHERE employee_id=176;
  13. # 3.选择工资不在5000到12000的员工的姓名和工资
  14. SELECT last_name,salary
  15. FROM employees
  16. WHERE salary NOT BETWEEN 5000 AND 12000;
  19. # 4.选择在20或50号部门工作的员工姓名和部门号
  20. SELECT last_name,department_id
  21. FROM employees
  22. WHERE department_id IN (20,50);#department_id=20 or department_id=50
  25. # 5.选择公司中没有管理者的员工姓名及job_id
  26. SELECT last_name,job_id
  27. FROM employees
  28. WHERE manager_id IS NULL;
  31. #6.选择公司中有奖金的员工姓名，工资和奖金
  32. SELECT last_name,salary,commission_pct
  33. FROM employees
  34. WHERE commission_pct IS NOT NULL;
  37. # 7.选择员工姓名的第三个字母是a的员工姓名
  38. SELECT last_name
  39. FROM employees
  40. WHERE last_name LIKE '__a%';
  43. # 8.选择姓名中有字母a和e的员工姓名
  44. SELECT last_name
  45. FROM employees
  46. #where last_name like '%a%e%' or last_name like '%e%a%';
  47. WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';

第二章.多表查询练习

  1. # 1.显示所有员工的姓名，部门号和部门名称。
  2. SELECT e.`first_name`,e.`department_id`,d.`department_name`
  3. FROM employees e LEFT JOIN departments d
  4. ON e.`department_id`=d.`department_id`;
  7. # 2.查询90号部门员工的job_id和90号部门的location_id
  8. SELECT e.`department_id`,e.`job_id`,d.`location_id`
  9. FROM employees e JOIN departments d
  10. ON e.`department_id` = d.`department_id`
  11. WHERE e.`department_id`=90;
  14. # 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
  15. SELECT e.`commission_pct`,e.`last_name`,d.`department_name`,d.`location_id`,l.`city`
  16. FROM employees e JOIN departments d
  17. ON e.`department_id`=d.`department_id`
  18. JOIN locations l
  19. ON d.`location_id`=l.`location_id`
  20. WHERE e.`commission_pct` IS NOT NULL;
  23. # 4.选择city在Toronto工作的员工的 last_name , job_id , 
  24. #department_id , department_name 
  25. SELECT e.`last_name`,e.`job_id`,e.`department_id`,d.`department_name`,l.`city`
  26. FROM employees e JOIN departments d
  27. ON e.`department_id`=d.`department_id`
  28. JOIN locations l
  29. ON d.`location_id`=l.`location_id`
  30. WHERE l.`city`='Toronto';
  33. # 5.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，
  34. #结果类似于下面的格式
  35. employees    Emp#    manager     Mgr#
  36. kochhar        101          king     100
  38. SELECT e1.first_name "employees",e1.employee_id "Emp#",
  39.     e2.first_name "manager",e2.employee_id "Mgr#"
  40. FROM employees e1 JOIN employees e2
  41. ON e1.manager_id=e2.employee_id;

第三章.单行函数练习

  1. # 1.显示系统时间(注：日期+时间)
  3. SELECT NOW();
  5. # 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
  7. SELECT employee_id,first_name,salary,salary*1.2 "new salary"
  8. FROM employees;
  10. # 3.将员工的姓名按首字母排序，并写出姓名的长度（length)
  11. SELECT first_name,LENGTH(first_name)
  12. FROM employees
  13. ORDER BY first_name;
  17. -- 4.做一个查询，产生下面的结果
  18. -- <last_name> earns <salary> monthly but wants <salary*3> Dream Salary
  19. -- King earns 24000 monthly but wants 72000
  20. SELECT CONCAT(last_name," earns ",TRUNCATE(salary,0)," monthly but wants ",
  21.     TRUNCATE(salary*3,0))
  22. FROM employees;
  26. -- 5.    使用case-when，按照下面的条件：
  27. -- job                  grade
  28. -- AD_PRES              A
  29. -- ST_MAN               B
  30. -- IT_PROG              C
  31. -- SA_REP               D
  32. -- ST_CLERK             E
  34. -- 产生下面的结果
  35. -- Last_name    Job_id    Grade
  36. -- king           AD_PRES    A
  38. SELECT last_name,job_id,CASE job_id
  39.             WHEN 'AD_PRES' THEN 'A'
  40.             WHEN 'ST_MAN' THEN 'B'
  41.             WHEN 'IT_PROG' THEN 'C'
  42.             WHEN 'SA_REP' THEN 'D'
  43.             WHEN 'ST_CLERK' THEN 'E'
  44.             END AS "GRADE"
  45. FROM employees;

第四章.分组函数练习

  1. #1.where子句可否使用组函数进行过滤? 
  2. 不能
  4. #2.查询公司员工工资的最大值，最小值，平均值，总和
  6. SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
  7. FROM employees;
  9. #3.查询各job_id的员工工资的最大值，最小值，平均值，总和
  11. SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
  12. FROM employees
  13. GROUP BY job_id;
  16. #4.选择具有各个job_id的员工人数
  17. SELECT job_id,COUNT(job_id)
  18. FROM employees
  19. GROUP BY job_id;
  23. # 5.查询员工最高工资和最低工资的差距（DIFFERENCE）
  25. SELECT MAX(salary)-MIN(salary) AS "DIFFERENCE"
  26. FROM employees;
  29. # 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，
  30. #   没有管理者的员工不计算在内
  31. SELECT manager_id,MIN(salary)
  32. FROM employees
  33. WHERE manager_id IS NOT NULL
  34. GROUP BY manager_id
  35. HAVING MIN(salary)>=6000;
  39. # 7.查询所有部门的名字，location_id，员工数量和工资平均值
  40. SELECT d.`department_name`,d.`location_id`,
  41.     COUNT(e.`employee_id`),AVG(e.`salary`)
  42. FROM employees e JOIN departments d
  43. ON e.`department_id`=d.`department_id`
  44. GROUP BY d.`department_name`,d.`location_id`;

第五章.子查询练习

  1. # 1.查询和Zlotkey相同部门的员工姓名和工资
  3. SELECT department_id,first_name,salary
  4. FROM employees
  5. WHERE department_id=(
  6.     SELECT department_id
  7.     FROM employees
  8.     WHERE last_name='Zlotkey'
  9. );
  12. # 2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
  14. SELECT employee_id,first_name,salary
  15. FROM employees
  16. WHERE salary > (
  17.     SELECT AVG(salary)
  18.     FROM employees
  19. );
  22. #3*.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
  24. SELECT e.`employee_id`,e.`first_name`,e.`salary`
  25. FROM employees e JOIN (
  26.     SELECT department_id,AVG(salary) a_s
  27.     FROM employees
  28.     GROUP BY department_id
  29. ) a
  30. ON e.`department_id`=a.department_id AND e.`salary`>a.a_s;
  32. #4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
  33. SELECT employee_id,first_name
  34. FROM employees
  35. WHERE department_id IN (
  36.     SELECT department_id
  37.     FROM employees
  38.     WHERE first_name LIKE '%u%'
  39. )
  43. # 5.查询在部门的location_id为1700的部门工作的员工的员工号
  44. SELECT employee_id
  45. FROM employees
  46. WHERE department_id IN (
  47.     SELECT department_id
  48.     FROM employees
  49.     WHERE location_id=1700
  50. );
  54. # 6.查询管理者是King的员工姓名和工资
  55. SELECT first_name,salary
  56. FROM employees
  57. WHERE manager_id IN (
  58.     SELECT employee_id
  59.     FROM employees
  60.     WHERE last_name='King'
  61. );
  64. # 7. 查询工资最低的员工信息: last_name, salary
  65. SELECT last_name, salary
  66. FROM employees
  67. WHERE salary =(
  68.     SELECT MIN(salary)
  69.     FROM employees 
  70. );