常用sql(mysql) 第一版
表结构
1.列出至少有一名员工的所有部门 NO
- 常规
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);
- 常规
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