学了数据库这么久，你的sql是否已经入门，那么试试下面的一道试题吧！
一道关于员工与部门查询的SQL笔试题(文章最后附上建表sql语句及数据，建的表不同，答案可能略有不同)
注：所有语句均在MS SQL SERVER2008中测试通过
建表：
DEPARTMENTS: DEPARTMENT_ID(primary key), DEPARTMENT_NAME, LOCATION EMPLOYEES: EMPLOYEE_ID(primary key), EMPLOYEE_NAME, EMPLOYEE_JOB, MANAGER, SALARY, DEPARTMENT_ID
--1.列出EMPLOYEES表中各部门的部门号，最高工资，最低工资
select department_id,min(salary) as '最高工资',max(salary) as '最低工资' from employees group by department_id
--2.列出EMPLOYEES表中各部门EMPLOYEE_JOB为'CLERK'的员工的最低工资，最高工资
select department_id,min(salary) as '最高工资',max(salary) as '最低工资' from employees where employee_job = 'clerk' group by department_id
--3.对于EMPLOYEES中最低工资小于5000的部门，列出EMPLOYEE_JOB为'CLERK'的员工的部门号，最低工资，最高工资
select department_id as '部门号', MIN(salary) as '最低工资',MAX(salary) as '最高工资'
from employees as a
where employee_job='clerk' and 4000>
(
select MIN(salary)
from employees as b
where a.department_id=b.department_id
)
group by department_id
--4.根据部门号由高而低，工资有低而高列出每个员工的姓名，部门号，工资
select employee_name ,department_id ,salary from employees order by employee_id desc,salary asc
--5.写出对上题的另一解决方法
这里，我也没想到另一种解决方法，如果有谁想到，可以在下面留言告知，感谢
--6.列出'张三'所在部门中每个员工的姓名与部门号
select employee_name,department_id
from employees
where department_id =
(
select department_id
from employees
where employee_name='张三'
)
--7.列出每个员工的姓名，工作，部门号，部门名
select a.employee_name,a.employee_job,a.department_id,b.department_name from employees as a,departments as b where a.department_id=b.department_id
--8.列出EMPLOYEES中工作为'CLERK'的员工的姓名，工作，部门号，部门名
select a.employee_name,a.employee_job,a.department_id,b.department_name from employees as a,departments as b where a.department_id=b.department_id and a.employee_job='clerk'
--9.对于EMPLOYEES中有管理者的员工，列出姓名，管理者姓名（管理者外键为MANAGER）
select a.employee_name as 姓名,b.employee_name as 管理者 from EMPLOYEES as a,EMPLOYEES as b where a.MANAGER is not null and a.MANAGER = b.EMPLOYEE_name
--10.对于DEPARTMENTS表中，列出所有部门名，部门号，同时列出各部门工作为'CLERK'的员工名与工作
select a.department_id,a.department_name,b.employee_name,b.employee_job from departments as a,employees as b where a.department_id = b.department_id and b.employee_job = 'clerk'
--11.对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序
第一种方法：
select a.department_id,a.employee_name,a.salary
from employees as a,
(
select department_id,AVG(salary) as '平均工资'
from employees
group by department_id
)as b
where a.department_id = b.department_id and a.salary>b.平均工资
order by a.department_id asc
第二种方法：
select a.department_id,a.employee_name,a.salary
from employees as a
where a.salary>
(
select AVG(salary)
from employees as b
where a.department_id = b.department_id
)
order by a.department_id asc
--12.对于EMPLOYEES，列出各个部门中平均工资高于本部门平均水平的员工数和部门号，按部门号排序
select COUNT(a.salary) as '人数',a.department_id
from employees as a
where a.salary>
(
select AVG(salary)
from employees as b
where a.department_id = b.department_id
)
group by a.department_id
order by a.department_id desc
--13.对于EMPLOYEES中工资高于本部门平均水平，人数多与1人的，列出部门号，人数，按部门号排序
第一种方法：
select c.人数,c.department_id
from
(
select COUNT(a.salary) as '人数',a.department_id
from employees as a
where a.salary>
(
select AVG(salary)
from employees as b
where a.department_id = b.department_id
)
group by a.department_id
) as c
where c.人数>1
order by c.department_id desc
第二种方法：
select count(a.employee_id) as 员工数,a.department_id as 部门号,avg(salary) as 平均工资
from employees as a
where (
select count(c.employee_id)
from employees as c
where c.department_id = a.department_id and c.salary>
(
select avg(salary)
from employees as b
where c.department_id = b.department_id
)
)>1
group by a.department_id order by a.department_id
--14.对于EMPLOYEES中低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数
select a.department_id,a.employee_name,a.salary,
(
select count(b.employee_name)
from employees as b
where b.salary < a.salary and a.department_id = b.department_id
) as 人数
from EMPLOYEES as a
where (
select count(b.EMPLOYEE_NAME)
from EMPLOYEES as b
where b.SALARY5
--15.创建两张表并插入示例数据(两张表的主键设置了自增长，sql语句中未给出)
create table departments
(
department_id int primary key,
department_name nvarchar(50) not null,
location nvarchar(50) not null
)
create table employees
(
employee_id int primary key,
employee_name nvarchar(50) not null,
employee_job nvarchar(20) not null,
manager nvarchar(20) not null,
salary float not null
department_id int not null
)
insert into departments (department_name,location) values('技术部','XXX')
insert into departments (department_name,location) values('销售部','YYY')
insert into departments (department_name,location) values('财务部','ZZZ')
insert into departments (department_name,location) values('客服部','FFF')
insert into departments (department_name,location) values('售后部','AAA')
insert into departments (department_name,location) values('人力资源部','GGG')
insert into departments (department_name,location) values('后勤部','HHH')
insert into employees(employee_name,employee_job,manager,salary,department_id)values('张三','软件设计师','李四',5000,1)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('王小虎','软件设计师','李四',4000,1)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('王宏','软件设计师','李四',3560,1)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('李四','项目经理','头头',8000,1)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('燕小六','销售人员','佟湘玉',5100,2)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('秀才','伙计','佟湘玉',2300,2)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('佟湘玉','掌柜','头头',4500,2)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('小二','会计','和珅',5600,3)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('和珅','老贪','头头',3400,3)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('张麻子','接线员','王五',5400,4)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('力马扎','接线员','王五',6500,4)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('卓玛','客服','王五',5300,4)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('晓红','客服','王五',6700,4)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('宪哥','客服','王五',7700,4)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('王五','客户经理','头头',6600,4)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('jack','人力助理','HR',5500,6)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('tom','人力助理','HR',4400,6)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('lucy','人力助理','HR',6600,6)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('pooly','人力助理','HR',6600,6)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('david','人力助理','HR',8800,6)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('HR','人力资源经理','头头',8800,6)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('小东','售后支持','笑笑',4500,5)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('小丽','售后支持','笑笑',5400,5)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('笑笑','售后部经理','头头',5600,5)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('李逵','后勤','晁盖',6500,7)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('松江','后勤','晁盖',4700,7)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('林冲','后勤','晁盖',7400,7)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('晁盖','后勤部经理','头头',4400,7)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('头头','董事长','无',44400,0)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('一一','CLERK','头头',2300,1)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('二二','CLERK','头头',3400,2)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('三三','CLERK','头头',4500,3)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('四四','CLERK','头头',5600,4)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('无无','CLERK','头头',5400,5)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('六六','CLERK','头头',4300,6)
insert into employees(employee_name,employee_job,manager,salary,department_id)values('七七','CLERK','头头',3200,7)
最后注：本题来自：http://www.nowamagic.net/database/db_EmployeeDepartmentSQL.php感谢文章作者
