- 查询没有上级领导的员工编号empno,姓名,工资
select empno,ename,sal from emp where mgr is null;
- 查询有奖金的员工姓名和奖金
select ename,comm from emp where comm is not null;
- 查询名字中包含精的员工姓名和工资
select ename,sal from emp where ename like '%精%';
- 查询名字中第二个字是八的员工信息
select * from emp where ename like '_八%';
- 查询1号部门工资大于2000的员工信息
select * from emp where deptno=1 and sal>2000;
- 查询2号部门或者工资小于1500的员工信息
select * from emp where deptno=2 or sal<1500;
- 查询工资为3000,1500,5000的员工信息按照工资升序排列
select * from emp where sal in(3000,1500,5000) order by sal;
- 查询3号部门的工资总和
select sum(sal) from emp where deptno=3;
- 查询每个部门工资大于1000的员工人数,按照人数升序排列
select deptno,count(*) from emp where sal>1000 group by deptno order by count(*);
- 查询每种工作中有领导的员工人数按照人数升序排列
select job,count(*) from emp where mgr is not null group by job order by count(*) desc;
- 查询所有员工信息,按照部门编号升序排列,若部门编号一致则工资降序排列
select * from emp order by deptno ,sal desc;
- 查询有领导的员工,每个部门的编号和最高工资
select deptno,max(sal) from emp where mgr is not null group by deptno;
- 查询有领导的员工,按照工资升序排列,第3页的2条数据
select * from emp where mgr is not null order by sal limit 4,2;
- 查询每个部门的工资总和,只查询有上级领导的员工并且要求工资总和大于5400,最后按照工资总和降序排列,只查询结果中的第一条数据.
select deptno,sum(sal) from emp where mgr is not null group by deptno having sum(sal)>5400 order by sum(sal) desc limit 0,1;
数据库简单小练习
最新推荐文章于 2021-06-02 21:00:07 发布