Jimmy Chen

A Programmer

使用HR数据库进行练习之子查询练习

使用HR数据库进行练习之子查询练习

Practice 1

  编写一条SQL查询语句,获取salary大于last_name为‘Bull’的employee的name(first_name,last_name)和salary

SELECT first_name, last_name, salary FROM employees
    WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Bull');

Practice 2

  编写一条SQL查询语句,获取所有在IT department工作的employee的名字(first_name,last_name)

SELECT first_name, last_name FROM employees
    WHERE department_id IN 
        (SELECT department_id FROM departments WHERE department_name = 'IT');

Practice 3

  编写一条SQL查询语句,获取拥有一个manager以及该manager在USA based department工作的的所有employees的名字(last_name,first_name)

SELECT first_name, last_name FROM employees
    WHERE manager_id IN (SELECT employee_id FROM employees
        WHERE department_id IN (SELECT department_id FROM departments
            WHERE location_id IN (SELECT location_id FROM locations
                WHERE country_id = 'US')));

Practice 4

  编写一条SQL查询语句,获取managers的名字(first_name,last_name)

SELECT first_name, last_name FROM employees
    WHERE employee_id IN (SELECT manager_id FROM employees);

Practice 5

  编写一条SQL查询语句,获取salary大于平均salary的employee的name(first_name,last_name)和salary

SELECT first_name, last_name, salary FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees);

Practice 6

  编写一条SQL查询语句,获取其工作等级中最低工资的员工的名字(first_name,last_name)和薪水。

SELECT first_name, last_name, salary FROM employees
    WHERE salary = (SELECT min_salary FROM jobs
        WHERE employees.job_id = jobs.job_id);

Practice 7

  编写一条SQL查询语句,获取薪水高于平均薪水而且在IT department工作的employee的名字(first_name,last_name)和薪水

SELECT first_name, last_name, salary FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees)
        AND department_id IN (SELECT department_id FROM departments WHERE department_name LIKE 'IT%');

Practice 8

  编写一条SQL查询语句,获取获取薪水高于Mr.Bell的employee的名字(first_name,last_name)和薪水

SELECT first_name, last_name, salary FROM employees
    WHERE salary > (SELECT salary FROM employees
        WHERE last_name = 'Bell');

Practice 9

  编写一条SQL查询语句,获取在所有部门中的薪水最低的employees的名字(first_name,last_name)和薪水

SELECT first_name, last_name, salary FROM employees
    WHERE salary = (SELECT MIN(salary) FROM employees);

Practice 10

  编写一条SQL查询语句,获取salary高于所有部门平均salary的employee的名字(first_name,last_name)和salary

SELECT first_name, last_name, salary FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees);

Practice 11

  编写一条SQL查询语句,获取salary高于所有Shipping CLerk(job_id=‘SH_CLERK’)的employee的名字(first_name,last_name),job_id和salary,并按照salary由低到高排列输出

SELECT first_name, last_name, job_id, salary FROM employees
    WHERE salary > (SELECT MAX(salary) FROM employees WHERE job_id = 'SH_CLERK')
    ORDER BY salary;

或者使用ALL关键字

SELECT first_name, last_name, job_id, salary FROM employees
    WHERE salary > ALL (SELECT salary FROM employees WHERE job_id = 'SH_CLERK')
    ORDER BY salary;

Practice 12

  编写一条SQL查询语句,获取所有不是管理者的employee的名字(first_name,last_name)

SELECT first_name, last_name FROM employees b
    WHERE NOT EXISTS ( SELECT 'A' FROM employees a WHERE a.manager_id = b.employee_id);

Practice 13

  编写一条SQL查询语句,获取所有employee的employee ID,first_name,last_name和department name

SELECT employee_id, first_name, last_name, 
    (SELECT department_name FROM departments d 
        WHERE d.department_id = e.department_id) department_name
    FROM employees e;

Practice 14

  编写一条SQL查询语句,获取所有salary高于其部门平均salary的employees的employee ID,first name,last name和salary

SELECT e.employee_id, e.first_name, e.last_name, e.salary FROM employees e
    WHERE e.salary > ( SELECT AVG(salary) FROM employees a 
        WHERE e.department_id = a.department_id);

Practice 15

  编写一条SQL查询语句,获取employees表中偶数编号的记录

这个要定义变量,有点懵逼,毕竟没了解过

SET @i = 0;
SELECT i, employee_id, first_name, last_name
    FROM (SELECT @i := @i + 1 AS i, employee_id ,first_name, last_name FROM employees) a
    WHERE MOD(a.i, 2) = 0;

Practice 16

  编写一条SQL查询语句,获取employees表中第五高salary的employee

简单的方法就是使用LIMIT关键字,如下面

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 4, 1;

另一种方法

SELECT DISTINCT salary 
    FROM employees e1 WHERE 5 = (SELECT COUNT(DISTINCT salary) 
        FROM employees  e2 
        WHERE e2.salary >= e1.salary);

Practice 17

  编写一条SQL查询语句,获取employees表中salary为倒数第四的employee

和上一个练习一样,简单的方法如下

SELECT DISTINCT salary FROM employees ORDER BY salary LIMIT 3, 1;

另一个方法:

SELECT DISTINCT salary FROM employees e1
    WHERE 4 = (SELECT COUNT(DISTINCT salary) FROM employees e2
    WHERE e1.salary >= e2.salary);

Practice 18

  编写一条SQL查询语句,获取employees表最后十条记录

先使用子查询降序查询最后十条记录,然后在外侧查询中通过升序查询进行排序

SELECT * FROM 
    (SELECT * FROM employees ORDER BY employee_id DESC LIMIT 10) sub
    ORDER BY employee_id ASC;

Practice 19

  编写一条SQL查询语句,列出没有employee的department ID和名字

SELECT department_id, department_name FROM departments d
    WHERE NOT EXISTS (SELECT 'A' FROM employees e
        WHERE d.department_id = e.department_id);

上面的稍显复杂,下面的就简单点

SELECT * FROM departments 
    WHERE department_id 
    NOT IN (select department_id FROM employees);

Practice 20

  编写一条SQL查询语句,获取三个最高salary的employee

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 3;

Practice 21

  编写一条SQL查询语句,获取三个最低salary的employee

SELECT DISTINCT salary FROM employees ORDER BY salary LIMIT 3;

发表评论

电子邮件地址不会被公开。 必填项已用*标注

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d 博主赞过: