【Database】SQL经典面试题

1、用一条SQL 语句 查询出每门课都大于80 分的学生姓名。

A: select distinct name from table where name not in (select distinct name from table where fenshu<=80)
B: select name from table group by name having min(fenshu)>80

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

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

3、查询出 last_name 为 'Chen' 的 manager 的信息

select manager_id from employees where lower(last_name) = 'chen' ; --返回的结果为 108

select * from employees where employee_id  = 108

4、 通过一条SQL查询（自连接）。

select  m.*  from employees e, employees m    where e.manager_id = m.employee_id and e.last_name = 'Chen' ;

5、通过一条SQL查询（子查询）。

select  *  from employees   where employee_id = ( select manager_id  from employees  where last_name = 'Chen' ) ; 

6、查询工资最低的员工信息: last_name, salary

SELECT last_name, salary FROM employees WHERE salary = (  SELECT min(salary)  FROM employees   )