SQL练习题总结一

ymingliu
1,068 阅读9分钟

课程参考

阿里云开发者社区——数据库学习路线

数据库及SQL/MySQL基础练习题

1、只要是说查询员工，或者所有员工，要 select *；

2、查询奖金高于工资60%的员工

mysql> select * from emp where comm>sal*0.6;

3、部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料

mysql> select * from emp where (deptno=10 and job='经理') or (deptno=20 and job='销售员');

4、不是经理也不是销售员

mysql> select * from emp where (deptno=10 and job='经理') or (deptno=20 and job='销售员') or (job not in ('经理','销售员') and sal>=20000);

5、无奖金

mysql> select * from emp where comm is null or comm<1000;

6、名字是三个字

mysql> select * from emp where ename like '___';

7、2000年入职的员工 (模糊查询)

mysql> select * from emp where hiredate like '2000-%';

8、编号升序排序

mysql> select * from emp order by empno asc;

9、用工资降序、如果工资相同，用入职日期升序排序

mysql> select * from emp order by sal desc, hiredate asc;

10、查询每个部门的平均工资

mysql> select deptno, avg(sal)  平均工资 from emp group by deptno;

11、查询每个部门的员工数量

mysql> select deptno, count(*) from emp group by deptno;

12、每种工作最高最低工资，人数

mysql> select job, max(sal), min(sal), count(*) from emp group by job;

SQL/MySQL进阶及查询练习题

1、查出至少有一个员工的部门，显示部门编号、部门名称、部门位置、部门人数。

  • 列：d.deptno, d.dname, d.loc, 部门人数
  • 表：dept d, emp e
  • 条件：e.deptno=d.deptno

思路：先统计每个部门的人数，作为一张新表，再利用内连接将该表与部门表合并，并去除笛卡尔积，最后显示需要的列（单独写出两部分，再利用连接，去除笛卡尔积）

mysql> select d.*, z1.cnt from dept d inner join (select deptno, count(*) cnt from emp group by deptno) z1 where d.deptno=z1.deptno;
deptno dname loc cnt
10 教研部 北京 3
20 学工部 上海 5
30 销售部 广州 6

2、列出所有员工的姓名及其直接上级的姓名

  • 列：员工姓名，上级姓名
  • 表：emp e, emp m
  • 条件：员工的mgr=上级的empno

思路：因为员工可能没有上级，但也要显示出来，因此用左外连接

mysql> select e.ename, m.ename from emp e left outer join emp m on e.mgr=m.empno;
ename ename
甘宁 庞统
黛绮丝 关羽
殷天正 关羽
刘备 曾阿牛
谢逊 关羽
关羽 曾阿牛
张飞 曾阿牛
诸葛亮 刘备
曾阿牛 NULL
韦一笑 关羽
周泰 诸葛亮
程普 关羽
庞统 刘备
黄盖 张飞
张三 甘宁

3、列出受雇日期早于直接上级的所有员工的编号，姓名，部门名称

  • 列：empno, ename, d.dname
  • 表：emp e, dept d, emp m
  • 条件：e.hiredata<m.hiredata

思路：(1) 先不查部门名称，只查部门编号

  • 列：e.empno, e.ename, e.deptno
  • 表：emp e, emp m
  • 条件：e.mgr=m.empno, e.hiredata<m.hiredata
mysql> select e.empno, e.ename, e.deptno from emp e, emp m where e.mgr=m.empno and e.hiredate<m.hiredate;

(2) 再加入部门表，取编号对应部门的名称

mysql> select e.empno, e.ename, d.dname from emp e, emp m, dept d where e.mgr=m.empno and e.hiredate<m.hiredate and e.deptno=d.deptno;
empno ename dname
1002 黛绮丝 销售部
1003 殷天正 销售部
1004 刘备 学工部
1006 关羽 销售部
1007 张飞 教研部
1001 甘宁 学工部

4、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

  • 列：d.dname，e.*
  • 表：emp e, dept d
  • 条件：e.deptno=d.deptno

思路：(1) 先不管没有员工的部门

mysql> select * from emp e, dept d where e.deptno=d.deptno;

(2) 用右外连接显示没有员工的部门

mysql> select * from emp e right outer join dept d on e.deptno=d.deptno;

5、列出最低薪金大于15000的各种工作及从事此工作的员工人数

  • 列：e.job, count(*)
  • 表：emp e
  • 条件：min(sal)>15000
  • 分组：job
mysql> select job, count(*) from emp e group by job having min(sal)>15000;
job count(*)
经理 3
分析师 2
董事长 1
保洁员 1

6、列出在销售部工作的员工姓名，假定不知道销售部的部门编号

  • 列：e.ename
  • 表：emp e, dept d
  • 条件：e.deptno=d.deptno and d.dname=‘销售部'

思路：(1) 利用子查询，先把销售部的部门编号查出来

mysql> select e.ename from emp e where e.deptno=(select deptno from dept where dname='销售部');

(2) 或者先连接两张表，再提取部门名称为销售部的员工

mysql> select e.ename from emp e, dept d where e.deptno=d.deptno and d.dname='销售部';
ename
黛绮丝
殷天正
谢逊
关羽
韦一笑
程普

7、列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级

思路：(1) 先不查工资等级

  • 列：e.ename, d.dname, m.ename
  • 表：emp e, emp m, dept d
  • 条件：e.sal>(select avg(e.sal) from emp) and e.mgr=m.empno and e.deptno=d.deptno
mysql> select e.ename, d.dname, m.ename from emp e, emp m, dept d where e.sal>(select avg(sal) from emp) and e.mgr=m.empno and e.deptno=d.deptno;
ename dname ename
诸葛亮 学工部 刘备
庞统 学工部 刘备
刘备 学工部 曾阿牛
关羽 销售部 曾阿牛

(2) 再连接工资等级表

  • 列：e.ename, d.dname, m.ename, s.grade
  • 表：emp e, emp m, dept d, salgrade s
  • 条件：e.sal>(select avg(e.sal) from emp) and e.mgr=m.empno and e.deptno=d.deptno and e.sal between s.losal and s.hisal
mysql> select e.ename, d.dname, m.ename, s.grade from emp e, emp m, dept d, salgrade s where e.sal>(select avg(sal) from emp) and e.mgr=m.empno and e.deptno=d.deptno and e.sal between s.losal and s.hisal;
ename dname ename grade
诸葛亮 学工部 刘备 4
庞统 学工部 刘备 4
刘备 学工部 曾阿牛 4
关羽 销售部 曾阿牛 4

(3) 最后消除员工没有上级，员工部门不存在的情况

mysql> select e.ename, d.dname, m.ename, s.grade
—> from
—>   emp e left outer join dept d on e.deptno=d.deptno
—>         left outer join emp m on e.mgr=m.empno
—>         left outer join salgrade s on e.sal between s.losal and s.hisal
—> where e.sal>(select avg(sal) from emp)

或

mysql> select e.ename, d.dname, m.ename, s.grade from emp e left outer join dept d on e.deptno=d.deptno left outer join emp m on e.mgr=m.empno left outer join salgrade s on e.sal between s.losal and s.hisal where e.sal>(select avg(sal) from emp)
ename dname ename grade
刘备 学工部 曾阿牛 4
关羽 销售部 曾阿牛 4
诸葛亮 学工部 刘备 4
曾阿牛 教研部 NULL 5
庞统 学工部 刘备 4
张三 NULL 甘宁 5

10、列出与庞统从事相同工作的所有员工及部门名称

  • 列：e.*, d.dname
  • 表：emp e, dept d
  • 条件：e.deptno=d.deptno and e.job=(select job from emp where ename=‘庞统')
mysql> select e.*, d.dname from emp e left outer join dept d on e.deptno=d.deptno where e.job=(select job from emp where ename='庞统');
empno ename job mgr hiredate sal comm deptno dname
1008 诸葛亮 分析师 1004 2007-04-19 30000 NULL 20 学工部
1013 庞统 分析师 1004 2001-12-03 30000 NULL 20 学工部

11、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金，部门名称

  • 列：e.ename, e.sal, d.dname
  • 表：emp e, dept d
  • 条件：e.sal>(select max(sal) from emp group by deptno having deptno=30) and e.deptno=d.deptno
mysql> select e.ename, e.sal, d.dname from emp e left outer join dept d on e.deptno=d.deptno where e.sal>(select max(sal) from emp group by deptno having deptno=30);
ename sal dname
刘备 29750 学工部
诸葛亮 30000 学工部
曾阿牛 50000 教研部
庞统 30000 学工部
张三 80000 NULL

或者

mysql> select e.ename, e.sal, d.dname from emp e left outer join dept d on e.deptno=d.deptno where e.sal > all (select sal from emp where deptno=30);

12、查出年份、利润、年度增长比（即要求多出一列，显示增长比）

mysql> select * from tb_year;
year zz
2010 100
2011 150
2012 250
2013 800
2014 1000

思路：(1) 先把相邻两年的利润放在一行

mysql> select * from tb_year y1, tb_year y2;
year zz year zz
2010 100 2010 100
2011 150 2010 100
2012 250 2010 100
2013 800 2010 100
2014 1000 2010 100
2010 100 2011 150
2011 150 2011 150
2012 250 2011 150
2013 800 2011 150
2014 1000 2011 150
2010 100 2012 250
2011 150 2012 250
2012 250 2012 250
2013 800 2012 250
2014 1000 2012 250
2010 100 2013 800
2011 150 2013 800
2012 250 2013 800
2013 800 2013 800
2014 1000 2013 800
2010 100 2014 1000
2011 150 2014 1000
2012 250 2014 1000
2013 800 2014 1000
2014 1000 2014 1000 
mysql> select * from tb_year y1, tb_year y2 where y1.year=y2.year+1;
year zz year zz
2011 150 2010 100
2012 250 2011 150
2013 800 2012 250
2014 1000 2013 800

注意用左外连接

mysql> select * from tb_year y1 left outer join tb_year y2 on y1.year=y2.year+1;
year zz year zz
2010 100 NULL NULL
2011 150 2010 100
2012 250 2011 150
2013 800 2012 250
2014 1000 2013 800

(2) 计算增长比

mysql> select y1.*, (y1.zz-y2.zz)/y1.zz*100 from tb_year y1 left outer join tb_year y2 on y1.year=y2.year+1;
year zz (y1.zz-y2.zz)/y1.zz*100
2010 100 NULL
2011 150 33.33333333333333
2012 250 40
2013 800 68.75
2014 1000 20

(3) 根据显示要求修改

mysql> select y1.*, ifnull(concat((y1.zz-y2.zz)/y2.zz*100, '%'), 0) 增长比 from tb_year y1 left outer join tb_year y2 on y1.year=y2.year+1;
year zz 增长比
2010 100 0
2011 150 50%
2012 250 66.66666666666666%
2013 800 220.00000000000003%
2014 1000 25%

因此，本题最终要的是一个隐秘的关联关系，即y1.year=y2.year+1，将两数据放在一行是为了方便数学运算。

我的博客

avatar
文章
阅读
粉丝