测试工程师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

原文发布于微信公众号 - 软件测试经验与教训(udatest)

原文发表时间:2018-08-20

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏更流畅、简洁的软件开发方式

为or、in平反——or、in到底能不能利用索引?

  先说一个笑话,作为开场白。俺也换换风格试一试,呵呵。   在以前,有三个书生赶考,在路上遇到了一个算命先生,于是就问算命先生:我们三个人赶考,结果如何呀?...

246100
来自专栏数据和云

杀手SQL- 一条关于 &#39;Not in&#39; SQL 的优化案例

编辑手记:在 DBA 所优化的数据库环境中,绝大多数性能问题其实是由于 SQL 编写不当导致的。SQL 的世界无奇不有,今天我们一起见识一条让你绝对想吐血的杀手...

35360
来自专栏乐沙弥的世界

Oracle 重建索引的必要性

      索引重建是一个争论不休被不断热烈讨论的议题。当然Oracle官方也有自己的观点,我们很多DBA也是遵循这一准则来重建索引,那就是Oracle建议对于...

13910
来自专栏编程一生

预告片优化方案

8620
来自专栏性能与架构

为什么SQL优化中建议用UNION来代替OR

在SQL优化相关资料中,通常可以看到一个建议:用UNION来代替OR 举例 采用 OR 语句: SELECT * FROM a, b WHERE a.p...

439100
来自专栏猿人谷

【黑魔法】Covering Indexes、STRAIGHT_JOIN

今天给大家介绍两个黑魔法,这都是压箱底的法宝。大家在使用时,一定要弄清他们的适用场景及用法,用好了,就是一把开天斧,用不好那就是画蛇添足。自从看过耗子哥(左耳朵...

12720
来自专栏性能与架构

Mysql InnoDB行锁优化建议

InnoDB存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面带来的性能损耗可能比表级锁定要更高一些,但是在整体并发处理能力方面是要远远优于MyISAM的表级...

37050
来自专栏数据和云

走在专家的路上,每天优化一条SQL

前段时间我们分享过一篇文章,巧用复合索引,有效降低系统IO,围绕B*Tree索引的使用,解读了如何合理地使用索引,尤其是复合索引,以及通过正确的索引类型来提高性...

29960
来自专栏用户2442861的专栏

数据库外键的作用,以及和主键的区别

http://blog.csdn.net/tiantian1980/article/details/1603126

1.1K20
来自专栏杨建荣的学习笔记

MySQL反连接的优化总结(r10笔记第51天)

今天同事有一个环境发现一条语句执行时间很长,感到非常奇怪。刚好有些时间,就抽空琢磨了下这个问题。 总体来看这个环境还是相对比较繁忙的,线程大概是200多个。 #...

32770

扫码关注云+社区

领取腾讯云代金券