常用sql(mysql) 第一版

表结构

1.列出至少有一名员工的所有部门 NO

1. 常规

select dname from dept where deptno in(select deptno from emp);

2.exists 的用法？

select dname from dept where exists(select NULL from emp where emp.deptno=dept.deptno);

3.使用分组

select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno)>=1);

2. 列出薪金比"SMITH"多的所有员工

select * from emp where sal > (select sal from emp where ename='SMITH')

使用exises

select * from emp A where exists (select null from emp B where A.SAL>B.SAL and Ename ='SMITH')

3.列出所有员工的姓名及直属上级的姓名

1.
select m.ename,n.ename from emp m left join emp n on m.mgr=n.empno
2.
select m.ename ,(select n.ename from emp n where n.empno=m.mgr )as bossname from emp m

4.列出受雇日期早于其直接上级的受雇日期的员工

select m.empno,m.ename,m.mgr,m.hiredate,n.empno,n.ename,n.hiredate from emp m join emp n on m.mgr=n.empno where m.hiredate<n.hiredate

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

1.
select dept.dname,emp.* from dept left join emp on dept.deptno=emp.deptno order by dept.dname
2.
select dept.dname,emp.* from dept left join emp Using(deptno) order by dept.dname

6.列出所有“CLERK”（办事员）的姓名及其部门名称

select ENAME,DNAME,job from emp JOIN dept on emp.deptno=dept.deptno where emp.job ='CLERK';

7.列出最低薪金大于1500的各种工作

select job from emp group by job having min(sal)>1500

select * from emp where job in(select job from emp group by job having min(sal)>1500);

8.列出在部门'sales'（销售部），工作人员的姓名，假定不知道销售部的部门编号

select * from emp join (select deptno from dept where dname='sales') tmp using (deptno);

1. 常规

select dname from dept where deptno in(select deptno from emp);

2.exists 的用法？

select dname from dept where exists(select NULL from emp where emp.deptno=dept.deptno);

3.使用分组

select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno)>=1);

9.列出薪金高于公司平均薪金的所有员工

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

10.列出与"SCOTT"从事相同工作的所有员工

select * from emp A where exists(select job from emp B where ename='SCOTT' AND A.JOB=B.JOB) AND eNAME !='scott';

11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金

select ename,sal,deptno from emp where sal in(select sal from emp where deptno='30') and deptno <>'30';

12.列出薪金高于部门30中员工的薪金的所有员工的姓名和薪金

select ename,sal,deptno from emp where sal >ALL(select SAL from emp where deptno='30') and deptno <>'30';
select ename,sal,deptno from emp where sal >(select MAX(SAL) from emp where deptno='30') and deptno <>'30';

13.列出在每个部门工作的员工数量、平均工资和平均服务期限

select count(empno),avg(sal),deptno from emp right join dept using(deptno) group by deptno

14.列出所有员工的姓名、部门名称和工资(薪金+佣金)

select empno,dname,sal+comm as wage from emp join dept using(deptno);

15.列出所有部门的详细信息和部门人数

select dept.* from dept join emp using(deptno);

16. 列出各种工作的最低工资

select job ,min(sal) from emp group by job

17. 列出各个部门的Manager(经理)的最低薪金

select min(sal) as sal,deptno from emp where job='manager' group by deptno

18. 列出所有员工的年工资(薪金+佣金)，按年薪从低到高排序

select (sal+comm)*12 as money,ename from emp order by money

19.用一条sql语句查询出所有emp表中每个部门工资前三位的数据 展示如下，sal1 工资最高，依次向后

deptno sal1 sal2 sal3

select sal from emp