在学习SQL语句，基本语法时，找些面试题学习稳固知识点，接接地气。下面的SQL面试题大概会有100题，分十季分享给大家，希望大家能坚持学下来，一定会有收获的！

第八季来了：

问：

1. 分别统计每位员工的应发工资总和，并且只显示工资总和在5000元以上的信息

2. 统计worker表中各部门党员的人数，并且显示党员人数在2个人以上的相关信息

3. 查询职工的职工号/姓名/部门名

4. 查询2011年2月份各部门职工的工资。

5. 求出各个部门党员的人数，要求显示部门名和党员人数

6. 显示所有平均工资高于2600的部门名和对应的平均工资

7. 查询worker与study表的左外连接、右外连接、完全外连接、交叉连接

8. 统计salary表的工资总和

9. 利用公用表表达式，查询出‘人事处’职工的职工号、职工姓名、部门名

10. 利用公用表表达式，查询出‘人事处’职工的职工号、职工姓名、部门名、发工资日期、实发工资

答案：

1. 查询如下：

select wid as 职工, SUM(totalsalary) as 工资总和 from salary group by wid having SUM(totalsalary) >= 5000

2. 查询如下：

select depid as 部门, count() as 党员人数 from worker where wparty = '是' group by depid having count() >= 1

3. 方法1：在where中指定内部连接条件

select worker.wid 职工号, worker.wname 姓名, depart.dname 部门名 from worker, depart where worker.depid = depart.did order by worker.wid desc

方法2：在from中指定内部连接条件

select wid 职工号, wname 姓名, dname 部门名 from worker inner join depart on worker.depid = depart.did

4. 方法1：

select worker.wid as 职工号, worker.wname as 姓名, depart.dname as 部门名, salary.actualsalary as '2011年2月份工资'

from worker, depart, salary

where worker.depid = depart.did and worker.wid = salary.wid and year(salary.sdate) = 2011 and MONTH(salary.sdate) = 2 order by depart.dname

方法2：

select worker.wid 职工号, worker.wname 姓名, depart.dname 部门名, salary.actualsalary '2011年2月份工资'

from worker

inner join depart on worker.depid = depart.did

inner join salary on worker.wid = salary.wid

where YEAR(salary.sdate) = 2011 and MONTH(salary.sdate) = 2

5. select depart.dname 部门名, COUNT(*) 党员人数 from worker inner join depart on depart.did = worker.depid where worker.wparty = '是' group by depart.dname

6. select depart.dname 部门名, AVG(salary.actualsalary) 平均工资 from worker inner join depart on depart.did = worker.depid inner join salary on worker.wid = salary.wid group by depart.dname having AVG(salary.actualsalary) > 2600

7. 左外连接

select * from worker left outer join study on worker.wid = study.wid

右外连接

select * from worker right outer join study on worker.wid = study.wid

完全外连接

select * from worker full outer join study on worker.wid=study.wid

交叉连接

select * from worker cross join study

8. select * from salary union select '小计', NULL, SUM(totalsalary), SUM(actualsalary) from salary

9. with result(wid, wname, dname) as

( select wid, wname, dname from worker

inner join depart on worker.depid = depart.did

where depart.dname = '人事处' )

select * from result

10.with result(wid, wname, dname) as

( select wid, wname, dname from worker

inner join depart on worker.depid = depart.did

where depart.dname = '人事处' )

select result.wid 职工号, wname 职工名, dname 部门名, salary.sdate 发工资日期, salary.actualsalary 实发工资

from result inner join salary on result.wid = salary.wid