1. Ben的领导是谁（Ben向谁报告）。
SQL> select first_name,manager_id from employees where manager_id =(select manager_id from employees where first_name='Ben');
2. Ben领导谁。（谁向Ben报告）。
SQL> select first_name,employee_id from employees where manager_id = (select employee_id from employees where first_name='Ben');
3. 哪些员工和Biri(last_name)同部门
SQL> select last_name,department_id from employees where department_id = (select department_id from employees where last_name='Biri');
4. 哪些员工跟Smith(last_name)做一样职位
SQL> select last_name,job_id from employees where job_id = (select distinct job_id from employees where last_name='Smith');
5. 哪些员工跟Biri(last_name)不在同一个部门
SQL> select last_name,job_id from employees where job_id not in ((select distinct job_id from employees where last_name='Smith'));
6. 整个公司中，最高工资和最低工资相差多少
SQL> select max(salary),min(salary),max(salary)-min(salary) gzc from employees;
MAX(SALARY) MIN(SALARY) GZC
----------- ----------- ----------
24000 2100 21900
7. 显示整个公司的最高工资、最低工资、工资总和、平均工资，保留到整数位。
SQL> select max(salary),min(salary),sum(salary),trunc(avg(salary)) from employees;
MAX(SALARY) MIN(SALARY) SUM(SALARY) TRUNC(AVG(SALARY))
----------- ----------- ----------- ------------------
24000 2100 691416 6461
8. 整个公司有多少个领导
SQL> select count(manager_id) from employees;
COUNT(MANAGER_ID)
-----------------
106
9. 各个部门中工资大于1500的员工人数
SQL> select department_id,count(*) from employees where
2 salary > 1500 group by department_id;
10.该部门中工资高于1000的员工数量超过2人，列出符合条件的部门：显示部门名字
SQL> select department_name from departments where department_id in (select department_id from employees where salary>1000 group by department_id having count(employee_id)>2);
DEPARTMENT_NAME
------------------------------
Finance
Purchasing
Executive
Shipping
Sales
IT
6 rows selected.
11. 哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）
SQL> select first_name,salary from employees where salary > (select avg(salary) from employees);
12. 哪些员工的工资，介于32和33部门平均工资之间
SQL> select first_name,salary from employees where salary between (select avg(salary) from employees where department_id =33)
2 and
3 ( select avg(salary) from employees where department_id =32 );
13.列出各个部门中工资最高的员工的信息：名字、部门号、工资
SQL> select department_id,last_name,salary from employees where (department_id,salary) in (select department_id,max(salary) from employees group by department_id) order by 1;
14.哪些部门的人数比32号部门的人数多
SQL> select department_id,count(last_name) from employees group by department_id
2 having count(last_name) > (select count(last_name) from employees where department_id = 32);
15. 求出每个部门的雇员数量
SQL> select department_id,count(first_name) from
2 employees group by department_id;
16. 查询出比7654工资要高的全部雇员的信息
SQL> select FIRST_NAME,SALARY from employees where salary > 7656;
17. 要求查询工资比7654高，同时与7788从事相同工作的全部雇员
SQL> select first_name,salary from employees where salary > 7654 and job_id = (select job_id from employees where salary = 7788);