测试工程师SQL面试题

测试人员工作在工作中会用到SQL来辅助测试，求职时也常常会在笔试环节遇到各种各样的sql设计题目，张老师整理了一些工作中常用的sql知识点，希望对大家有所帮助。

sql语句学习全攻略

创建表

1、创建student和score表

create or replace table employees(

empID INT(10) NOT NULL UNIQUE PRIMARY KEY ,

empName VARCHAR(20) NOT NULL , #姓名

sex VARCHAR(4) , #性别

birth date, #出生日期

deptID INT(20) , #部门编码

jobs VARCHAR(20) , #工作岗位

firJob date, #开始工作的日期

hiredate date,#开始工作的日期

politicalStatus VARCHAR(20) , # 政治面貌

leader INT(10) #领导编码

);

create or replace table salary(

sid INT(10) NOT NULL UNIQUE PRIMARY KEY ,

empID INT(10) not null,

salary INT(10) not null,#工资

lastedit date #上次调薪日期

);

create or replace table departments(

deptid INT(10) NOT NULL UNIQUE PRIMARY KEY ,

deptname VARCHAR(20) not null,#部门名称

faterdeptid INT(10) # 上级部门编码

);

问题列表

单表查询：

1、显示所有职工的基本信息。

2、查询所有职工所属部门的部门号，不显示重复的部门号。

3、求出所有职工的人数。

4、列出最高工和最低工资。

5、列出职工的平均工资和总工资。

6、创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。

7、显示所有女职工的年龄

8、列出所有姓刘的职工的职工号、姓名和出生日期。

9、列出1990年以前出生的职工的姓名、参加工作日期。

10、列出总人数大于4的部门号和总人数。

11、列出所有陈姓和李姓的职工姓名。

12、列出所有部门号为1002和1003的职工号、姓名。

13、将职工表worker中的职工按出生的先后顺序排序。

14、求出各部门党员的人数。

多表查询：

1、列出每名职工的职工号、姓名和部门名。

2、列出市场部的所有女职工的姓名和政治面貌。

3、显示所有职工的姓名、部门名和工资数。

4、显示所有职工的职工号、姓名、部门名和工资，并按部门名顺序排列。

5、显示各部门名和该部门的所有职工平均工资。

6、显示所有平均工资高于1200的部门名和对应的平均工资。

7、查询刘欣所在的部门。

8、查询部门名为财务处的职工的情况。

9、列出工资在1000－2000之间的所有职工姓名。

10、显示工资最高的前3名职工的职工号和姓名。

数据检索-单表查询（参考答案）

1、显示所有职工的基本信息

select * from employees

2、查询所有职工所属部门的部门号，不显示重复的部门号

select distinct e.deptID from employees e

1、从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

SELECT id,name,department FROM student;

3、求出所有职工的人数

select count(1) from employees

4、列出最高工和最低工资

select max(s.salary) as '最高工资',min(s.salary) as '最低工资' from salary s

5、列出职工的平均工资和总工资

select AVG(s.salary) as '最高工资',SUM(s.salary) as '最低工资' from salary s

6、创建一个只有职工号、姓名和参加工作的新表，名为工作日期表

Create table emp2 (select e.empID,e.empName,e.firJob from employees e);

在其他数据库中，可以尝试使用：

select e.empID,e.empName,e.firJob into newtable from employees e;

7、显示所有女职工的年龄

select e.empID, e.empName, (DATEDIFF(CURDATE(),e.birth) DIV 365) as '年龄' from employees e where e.sex = '女';

8、列出所有姓刘的职工的职工号、姓名和出生日期

select e.empID,e.empName,e.birth from employees e where e.empName like '张%'

9、列出1990年以前出生的职工的姓名、参加工作日期

select e.empName,e.firJob from employees e where YEAR(e.firJob)<1990

10、列出总人数大于4的部门号和总人数

select e.deptID, count(1) from employees e group by e.deptID having count(1)>4

10、列出总人数大于4的部门号和总人数

select e.deptID, count(1) from employees e group by e.deptID having count(1)>4

10、列出总人数大于4的部门号和总人数

select e.deptID, count(1) from employees e group by e.deptID having count(1)>4

11、列出所有陈姓和李姓的职工姓名

select e.empName from employees e where e.empName like'张%' or e.empName like'李%'

12、列出所有部门号为1002和1003的职工号、姓名

select e.empID, e.empName from employees e where e.deptID in (1002,1003);

13、将职工表worker中的职工按出生的先后顺序排序

select * from employees e order by e.birth asc

14、求出各部门党员的人数

select e.deptID,count(1) from employees e where e.politicalStatus = '党员' GROUP BY e.deptID

数据检索-多表查询（参考答案）

1、列出每名职工的职工号、姓名和部门名

select e.empID, e.empName, d.deptname from employees e,departments d where e.deptID = d.deptid

2、列出市场部的所有女职工的姓名和政治面貌

select e.empName,e.politicalStatus,d.deptname from employees e,departments d where e.deptID = d.deptid and e.sex= '女' and d.deptname = '市场部'

3、显示所有职工的姓名、部门名和工资数

select e.empName,d.deptname,s.salary from employees e LEFT JOIN departments d on e.deptID = d.deptid LEFT JOIN salary s on e.empID = s.empID

4、显示所有职工的职工号、姓名、部门名和工资，并按部门名顺序排列

select e.empid, e.empName,d.deptname,s.salary from employees e LEFT JOIN departments d on e.deptID = d.deptid LEFT JOIN salary s on e.empID = s.empID ORDER BY e.deptID

5、显示各部门名和该部门的所有职工平均工资

SELECT d.deptname, AVG(s.salary) from departments d LEFT JOIN employees e on d.deptid = e.deptID LEFT JOIN salary s on e.empID = s.empID GROUP BY d.deptname

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

SELECT d.deptname, AVG(s.salary) from departments d LEFT JOIN employees e on d.deptid = e.deptID LEFT JOIN salary s on e.empID = s.empID GROUP BY d.deptname having AVG(s.salary)>1200

7、查询刘欣所在的部门

select d.deptname from employees e, departments d where e.deptID = d.deptid and e.empName = '刘欣'

或者

select d.deptname from departments d where d.deptid =(select deptid from employees e where e.empName ='刘欣')

8、查询部门名为财务处的职工的情况

select * from employees e,departments d where e.deptID = d.deptid and d.deptname ='财务处'

或者

select * from employees e where e.deptid =(select deptid from departments d where d.deptname ='财务处')

9、列出工资在1000－2000之间的所有职工姓名

select e.empName,s.salary from salary s,employees e where s.salary between 1000 and 2000 and s.empID = e.empID

10、显示工资最高的前3名职工的职工号和姓名

select e.empID, e.empName from salary s, employees e where s.empID = e.empID ORDER BY s.salary desc LIMIT 3