【1】需求: 谁的工资比Abel 高?
方式一: 第一步线查出Abel共子 第二步进行过滤
SELECT salary
FROM employees
WHERE last_name = 'Abel';
SELECT first_name,last_name,salary
FROM employees
WHERE salary>11000
方式二: 自连接
SELECT e2.last_name,e2.salary
FROM employees e1 JOIN employees e2
ON e1.last_name = 'Abel' AND e2.salary>e1.salary
方式三: 子查询
写子查询时,建议从里向外写
单行子查询 : 查询的结果只有一条数据
多行子查询 : 查询的结果有多条数据
SELECT first_name,salary
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name='Abel' -- 单行子查询
)
SELECT first_name ,salary
FROM employees
WHERE (
SELECT salary
FROM employees
WHERE last_name ='Abel'
)<salary
【2】 需求:返回job_id 与141号 员工相同,salary比143好员工 多的 员工姓名,job_id 和工资
两个条件 : 1.与141号员工的job_id相同 2.salary比143号员工的要多
SELECT first_name,job_id,salary
FROM employees
WHERE job_id =(
SELECT job_id
FROM employees
WHERE employee_id=141
)
AND salary>(
SELECT salary
FROM employees
WHERE employee_id=141
)
【3】需求:返回公司工资最少的员工的last_name,job_id 和salary 求最低工资
过滤: 薪水和最低工资相同的哪个员工
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
)
【4】需求:查询最低工资大于50号 部门最低工资的部门id 和其最低工资
SELECT department_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
)
in -- salary in (10,20,30) 只要等于其中的一个即可 (满足一个即可)
any -- salary > any (10,20,30) 只要大于其中的一个即可 (满足一个即可)
all -- salary > all (10,20,30) 必须大于所有的数据 (必须都得满足)
【5】需求 : 返回 其它部门 中比 job_id为‘IT_PROG’部门任意 工资低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id,first_name,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT salary
FROM employees
WHERE job_id='IT_PROG'
)AND job_id <>'IT_PROG'
【6】需求:返回其它部门中比 job_id为‘IT_PROG’部门所有工资都低的 员工的员工号、姓名、job_id
SELECT employee_id,first_name,job_id,salary
FROM employees
WHERE salary < ALL(
SELECT salary
FROM employees
WHERE job_id='IT_PROG'
)AND job_id<>'IT_PROG'