SQL - 子查询

#单行子查询
#1.查询工资大于149号员工工资的员工信息
SELECT e.last_name ,e.salary 
FROM employees e 
WHERE e.salary > (
    SELECT e2.salary 
    FROM employees e2 
    WHERE e2.employee_id = 149
);

#2.返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT e.job_id ,e.salary 
FROM employees e 
WHERE e.job_id = (
    SELECT e.job_id
    FROM employees e 
    WHERE e.employee_id = 141
) 
AND e.salary = (
    SELECT e.salary 
    FROM employees e 
    WHERE e.employee_id = 143
);

#3.返回公司工资最少的员工的last_name,job_id和salary
SELECT e.last_name ,e.job_id ,e.salary 
FROM employees e 
HAVING e.salary = (
    SELECT MIN(e.salary) 
    FROM employees e 
) ;

#4.查询与141号员工的manager_id和department_id相同的其他员工
#的employee_id，manager_id,department_id
SELECT e.employee_id ,e.manager_id ,e.department_id 
FROM employees e 
WHERE e.manager_id = (
    SELECT e.manager_id
    FROM employees e 
    WHERE e.employee_id = 141
)
AND e.department_id = (
    SELECT e.department_id 
    FROM employees e 
    WHERE e.employee_id = 141
)
AND e.employee_id != 141;

#5.查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT e.department_id ,MIN(e.salary)
FROM employees e
GROUP BY e.department_id
HAVING MIN(e.salary) > (
    SELECT MIN(e.salary) 
    FROM employees e
    WHERE e.department_id = 50
);

#6.显示员工的employee_id,last_name和location
#其中若员工department_id与location_id为1800的department_id相同,
#则location为'Canada',其余则为'USA'
SELECT e.employee_id ,e.last_name ,
CASE WHEN d.department_id = (
    SELECT d.department_id 
    FROM departments d JOIN locations l ON d.location_id = l.location_id 
    WHERE l.location_id = 1800
) THEN 'Canada'
ELSE 'USA'END 'Location'
FROM employees e JOIN departments d ON e.department_id = d.department_id 
JOIN locations l ON d.location_id = l.location_id ;