SQL入门测试

学了数据库这么久，你的sql是否已经入门，那么试试下面的一道试题吧！

一道关于员工与部门查询的SQL笔试题(文章最后附上建表sql语句及数据，建的表不同，答案可能略有不同)

注：所有语句均在MS SQL SERVER2008中测试通过

建表：

DEPARTMENTS:DEPARTMENT_ID(primary key),DEPARTMENT_NAME,LOCATIONEMPLOYEES: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.SALARY<a.salary )="" a.department_id="b.department_id" and="">5</a.salary>

--15.创建两张表并插入示例数据(注意两张表的主键设置了自增长，语句中为给出)

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

感谢文章作者