数据库简单小练习

1. 查询没有上级领导的员工编号empno,姓名,工资

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

2. 查询有奖金的员工姓名和奖金

   select ename,comm from emp where comm is not null;
   

3. 查询名字中包含精的员工姓名和工资

   select ename,sal from emp where ename like '%精%';
   

4. 查询名字中第二个字是八的员工信息

   select * from emp where ename like '_八%';
   

5. 查询1号部门工资大于2000的员工信息

   select * from emp where deptno=1 and sal>2000;
   

6. 查询2号部门或者工资小于1500的员工信息

   select * from emp where deptno=2 or sal<1500;
   

7. 查询工资为3000,1500,5000的员工信息按照工资升序排列

   select * from emp where sal in(3000,1500,5000) order by sal;
   

8. 查询3号部门的工资总和

   select sum(sal) from emp where deptno=3;
   

9. 查询每个部门工资大于1000的员工人数,按照人数升序排列

   select deptno,count(*) from emp where sal>1000 group by deptno order by count(*);
   

10. 查询每种工作中有领导的员工人数按照人数升序排列

    select job,count(*) from emp where mgr is not null group by job order by count(*) desc;
    

11. 查询所有员工信息,按照部门编号升序排列,若部门编号一致则工资降序排列

    select * from emp order by deptno ,sal desc;
    

12. 查询有领导的员工,每个部门的编号和最高工资

    select deptno,max(sal) from emp where mgr is not null group by deptno;
    

13. 查询有领导的员工,按照工资升序排列,第3页的2条数据

    select * from emp where mgr is not null order by sal limit 4,2;
    

14. 查询每个部门的工资总和,只查询有上级领导的员工并且要求工资总和大于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;