关于面试的sql语句，这些你不能不会

1. 查询每个月倒数第 2 天入职的员工的信息.

select last_name,hire_data from employees where hire_data=last_day(hire_data)-1

2. 查询出 last_name 为 ‘Chen’ 的 manager 的信息.

select * from employees where manager_id=(select manager_id from employees where laast_name="Chen")

3. 查询平均工资高于 8000 的部门 id 和它的平均工资.

select department_id ,avg(salary) from employees group by department_id having avg(salary)>8000

4. 查询工资最低的员工信息: last_name, salary

select last_name,salary form employees where salary=(select min(salary) from employees)

5. 查询平均工资最低的部门信息

select *
from departments
where department_id = ( select department_id from employees
	group by department_id having avg(salary) = (
		select min(avg(salary)) from employees
		group by department_id
		)
	)

6. 查询平均工资最低的部门信息和该部门的平均工资

select d.*, (select avg(salary) from employees where department_id = d.department_id)
from departments d where d.department_id = ( 
	select department_id FROM employees group by department_id having avg(salary) = (
		select min(avg(salary)) from employeesgroup by department_id
		)
	)

7. 查询平均工资最高的 job 信息

select *from jobs where job_id = (
	select job_id from employees group by job_id having avg(salary) = (
		select max(avg(salary))from employees group by job_id
		)
	)

8. 查询平均工资高于公司平均工资的部门有哪些?

select department_id from employees group by department_id
having avg(salary) > (
	select avg(salary) from employees
	)

9. 查询出公司中所有 manager 的详细信息.

select employee_id, last_name from employees where employee_id in  (  
	select distinct manager_idfrom employees
	)

10. 各个部门中 最高工资中最低的那个部门的 最低工资是多少

select min(salary) from employees where department_id = ( 
	select department_id from employees group by department_id 
	having max(salary) = ( 
		select min(max(salary)) from employees
		group by department_id
		)
	)

11. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

select last_name, department_id, email, salary from employees where employee_id = ( 
	select manager_id from departments where department_id = ( 
		select department_id from employees group by department_id having avg(salary) = (
			select max(avg(salary)) from employees group by department_id
			)
		)	
	)

12. 查询 1999 年来公司的人所有员工的最高工资的那个员工的信息.

select * from employees where to_char(hire_date, 'yyyy') = '1999' and salary= (
	select max(salary) from employees where to_char(hire_date, 'yyyy') = '1999'
	)

13. 返回其它部门中比 job_id 为‘IT_PROG’部门所有工资都低的员工的 员工号、姓名、job_id 以及 salary

select employee_id, last_name, job_id, salary from	employees where	salary < all (
	select salary from employees where	job_id = 'IT_PROG'
	) and	job_id <> 'IT_PROG'