数据库常用SQL语句练习(含答案)

Emp(员工表)和Dept（部门表）

简单题

1. 案例：查询没有上级领导的员工的编号，姓名，工资

select empno,ename,sal from emp where mgr is null;

2. 案例：查询emp表中没有奖金的员工的姓名，职位，工资，以及奖金

select ename,job,sal,comm from emp where comm is null or comm=0;

既可以是null，也可以是0

3. 案例：查询emp表中含有奖金的员工的编号，姓名，职位，以及奖金

select ename,job,sal,comm from emp where comm>0;

4. 案例：查询含有上级领导的员工的姓名，工资以及上级领导的编号

select ename,sal,mgr from emp where mgr is not null;

5. 案例：查询emp表中名字以‘S’开头的所有员工的姓名

select ename from emp where ename like 's%'

6. 案例：查询emp表中名字的最后一个字符是'S'的员工的姓名

select ename from emp where ename like '%s'

7. 案例：查询倒数的第2个字符是‘E’的员工的姓名

select ename from emp where ename like '%e_'

8. 案例：查询emp表中员工的倒数第3个字符是‘N’的员工姓名

select ename from emp where ename like '%n__'

9. 案例：查询emp表中员工的名字中包含‘A’的员工的姓名

select ename from emp where ename like '%a%'

10. 案例：查询emp表中名字不是以'K'开头的员工的所有信息

select * from emp where ename not like 'k%';

11. 案例：查询emp表中名字中不包含‘A’的所有员工的信息

select * from emp where ename not like '%a%'

12. 案例：做文员的员工人数（job= CLERK 的）

select count(*) from emp where job='clerk';

13. 案例：销售人员job: SALESMAN 的最高薪水

select max(sal) from emp where job='salesman';

14. 案例：最早和最晚入职时间

select min(hiredate),max(hiredate) from emp;

15. 案例：查询emp表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。

select empno,ename,job,sal from emp where sal between 1000 and 2000;

16. 案例：查询emp表中员工在10号部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门的编号

select ename,job,mgr,deptno from emp

where deptno=10 and mgr is not null;

17. 案例：查询emp表中名字中包含'E'，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资

select empno,ename,job,sal from emp where ename like '%e%' and job!='manager';

18. 案例：查询emp表中10号部门或者20号部门中员工的编号，姓名，所属部门的编号

select empno,ename,deptno from emp

where deptno in (10,20);

19. 案例：查询emp表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金

select empno,ename,job,comm from emp

where comm=0 or ename not like '%t_';

20. 案例：查询工资高于3000或者部门编号是30的员工的姓名，职位，工资，入职时间以及所属部门的编号

select ename,job,sal,hiredate,deptno from emp

where sal>3000 or deptno=30;

21. 案例：查询不是30号部门的员工的所有信息

select * from emp where deptno!=30;

22. 案例：查询奖金不为空的员工的所有信息

select * from emp where comm is not null;

23. 案例：查询emp表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列

select empno,ename,job from emp order by empno desc;

24. 案例：查询emp表中部门编号是10号或者30号中，所有员工姓名，职务，工资，根据工资进行升序排列

select ename,job,sal from emp where deptno in (10,30)

order by sal;

25. 案例：查询emp表中所有的数据，然后根据部门的编号进行升序排列，如果部门编号一致，根据员工的编号进行降序排列

select * from emp order by deptno,empno desc;

26. 案例：查询emp表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门的编号，以及上级领导的编号，根据部门编号进行降序排列，如果部门编号一致根据工资进行升序排列。

select empno,ename,sal,deptno,mgr from emp

where sal>1000 or mgr is null

order by deptno desc,sal;

27. 案例：查询emp表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列select empno,ename,sal,comm from emp

where ename not like '%s%'

order by sal,empno desc;

28.案例：统计emp表中员工的总数量

select count(*) from emp;

29.案例：统计emp表中获得奖金的员工的数量

select count(comm) from emp where comm>0;

30.案例：求出emp表中所有的工资累加之和

select sum(sal) from emp;

31.案例：求出emp表中所有的奖金累加之和

select sum(comm) from emp;

32.案例：求出emp表中员工的平均工资

select avg(sal) from emp;

33.案例：求出emp表中员工的平均奖金

select avg(comm) from emp;

34.案例：求出emp表中员工的最高工资

select max(sal) from emp;

35.案例：求出emp表中员工编号的最大值

select max(empno) from emp;

36.案例：查询emp表中员工的最低工资。

select min(sal) from emp;

37.案例：查询emp表中员工的人数，工资的总和，平均工资，奖金的最大值，奖金的最小值,并且对返回的列起别名。

select count(*) 人数,sum(sal) 总和,avg(sal) 平均工资,max(comm) 最大奖金,min(comm) 最小奖金from emp;

38.案例：查询工资在1000~3000之间每一个员工的编号，姓名，职位，工资

select empno,ename,job,sal from emp

where sal between 1000 and 3000;

39.案例：查询员工的编号是7369，7521，

select * from emp where empno in(7369,7521);

40.案例：查询emp表中，职位是ANALYST，

select * from emp where job='analyst';

41.案例：查询emp表中职位不是ANALYST,

select * from emp where job!='analyst';

较难的题

1. 每个部门的人数,根据人数排序

select deptno,count(*) c from emp

group by deptno

order by c;

2. 每个部门中，每个主管的手下人数

select deptno,mgr,count(*) from emp

where mgr is not null

group by deptno,mgr;

3. 每种工作的平均工资

select job,avg(sal) from emp

group by job;

4. 每年的入职人数

select extract(year from hiredate) year,count(*) from emp

group by year;

5. 少于等于3个人的部门信息

select * from dept where deptno in(select deptno from emp group by deptno having count(*)<=3)

6. 拿最低工资的员工信息

select * from emp where sal=(select min(sal) from emp);

7. 只有一个下属的主管信息

select mgr from emp

where mgr is not null

group by mgr

having count(*)=1

select * from emp where empno in(select mgr from emp

where mgr is not null

group by mgr

having count(*)=1);

8. 平均工资最高的部门编号

得到每个部门的平均工资

select deptno,avg(sal) from emp

group by deptno;

得到最高平均工资

select avg(sal) a from emp

group by deptno order by a desc limit 0,1;

通过最高平均工资得到部门的编号

select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp

group by deptno order by a desc limit 0,1);

获取部门信息

select * from dept where deptno in (上面一坨);

9. 下属人数最多的人，查询其个人信息

- 得到下属人数

select mgr,count(*) from emp group by mgr;

- 得到最多下属的人数

select count(*) from emp group by mgr order by count(*) desc limit 0,1;

- 通过人数得到对应的主管编号

select mgr from emp group by mgr having count(*)=(select count(*) from emp group by mgr order by count(*) desc limit 0,1);

- 通过主管编号得到主管信息

select * from emp where empno in (上面一坨);

10. 拿最低工资的人的信息

select * from emp where sal=(select min(sal) from emp);

11. 最后入职的员工信息

select * from emp where hiredate=(select max(hiredate) from emp);

12. 工资多于平均工资的员工信息

select * from emp where sal>(select avg(sal) from emp);

13. 查询员工信息，部门名称

select e.*,d.dname

from emp e join dept d

on e.deptno=d.deptno;

14. 员工信息，部门名称，所在城市

select e.*,d.dname,d.loc

from emp e join dept d

on e.deptno=d.deptno;

15. DALLAS 市所有的员工信息

select e.*

from emp e join dept d

on e.deptno=d.deptno

where d.loc='DALLAS';

16. 按城市分组，计算每个城市的员工数量

select d.loc,count(e.ename)

from emp e right join dept d

on e.deptno=d.deptno

group by d.loc;

17. 查询员工信息和他的主管姓名

select e.*,m.ename

from emp e join emp m

on e.mgr=m.empno

18. 员工信息，员工主管名字，部门名

select e.ename,m.ename,d.dname

from emp e join emp m

on e.mgr=m.empno

join dept d

on e.deptno=d.deptno;

20. 员工和他所在部门名

select e.*,d.dname

from emp e join dept d

on e.deptno=d.deptno

22. 案例：查询emp表中所有员工的姓名以及该员工上级领导的编号，姓名，职位，工资

select e.ename,m.empno,m.ename,m.job,m.sal

from emp e left join emp m

on e.mgr=m.empno;

23. 案例：查询emp表中名字中没有字母'K'的所有员工的编号，姓名，职位以及所在部门的编号，名称，地址

select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc

from emp e left join dept d

on e.deptno=d.deptno

where e.ename not like '%k%';

24. 案例：查询dept表中所有的部门的所有的信息，以及与之关联的emp表中员工的编号，姓名，职位，工资

select d.*,e.empno,e.ename,e.job,e.sal

from emp e right join dept d

on e.deptno=d.deptno;