sql练习

11.查询公司管理者与员工的信息

查询公司管理者

select last_name
from employees
where employee_id in(
                   select manager_id
                   from employees
                   -- where manager_id is not null
                  );

查询公司员工

select last_name
from employees
where employee_id  not in(
                   select manager_id
                   from employees
                   where manager_id is not null
                  );

12.查询员工中工资大于本部门平均工资的员工的last_name,salary和department_id

（相关子查询）

select last_name,salary,department_id
from employees e1
where salary >(
               select avg(salary)
               from employees e2
               where e1.department_id=e2.department_id
              );

方式2

在form中声明子查询

select e.last_name,e.salary,e.department_id
from employees e,(
                  select department_id,avg(salary) avg_sal
                  from employees
                  group by department_id) t_dept_avg_sal
where e.department_id=t_dept_avg_sal.department_id
and e.salary>t_dept_avg_sal.avg_sal;

13.查询员工的id,salary,按照department_name排序

select e.employee_id,e.salary
from employees  e 
order by(
        select department_name
        from  departments  d
                where e.department_id=d.department_id
       ) asc;

14.若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id

select employee_id,last_name,job_id
from employees e
where 2<=(
         select count(*)
         from job_history j
         where e.employee_id=j.employee_id
         );

15.查询公司管理者的employee_id，last_name，job_id，department_id信息

select distinct mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
from employees emp join employees mgr
on emp.manager_id=mgr.employee_id;

方式2:子查询 先查出管理者manager_id,

select employee_id,last_name,job_id,department_id
from employees
where employee_id in (
                                      select distinct manager_id
                    from employees 
                                    );

方式3:使用exists

select employee_id,last_name,job_id,department_id
from employees e1
where exists(
             select *
                         from employees e2
                         where e1.employee_id=e2.manager_id
                         );