SQL练习(一)

--列出至少有一个员工的所有部门。select distinct deptno from emp where (select count(deptno) from emp)>0;select dname,deptno from dept where deptno in(select deptno from emp);select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno) >=1);--列出薪金比“SMITH”多的所有员工。select ename from emp where sal > (select sal from emp where ename=upper('smith'));select * from emp where sal > (select sal from emp where ename = 'SMITH'); --列出所有员工的姓名及其直接上级的姓名。select e.ename,(select ename from emp m where m.empno = e.mgr) as boss_name from emp e;select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name from emp a; --列出受雇日期早于其直接上级的所有员工。select e.ename from emp e where e.hiredate <(select hiredate from emp m where m.empno = e.mgr);select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr); --列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门select dname,emp.* from dept left join emp on emp.deptno = dept.deptno;--列出所有“CLERK”(办事员)的姓名及其部门名称。select distinct ename,dname from emp,dept where emp.deptno = dept.deptno and emp.job=upper('clerk');select ename,dname from emp e join dept d on e.deptno=d.deptno where e.job=upper('clerk');--列出最低薪金大于1500的各种工作。select distinct job from emp group by job having min(sal) > 1500;select distinct job as HighSalJob from emp group by job having min(sal)>1500; --列出最低薪金各种工作。select distinct job from emp where sal in (select min(sal) from emp group by job);--列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。select ename ,d.deptno from emp e join dept d on e.deptno = d.deptno where d.dname=upper('sales');select ename from emp where emp.deptno = (select deptno from dept where dname = upper('sales'));--列出薪金高于公司平均薪金的所有员工。select ename,job from emp where emp.sal > (select avg(sal) from emp);--列出与“SCOTT”从事相同工作的所有员工。select ename from emp where job = (select job from emp where ename=upper('scott'));--列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。select ename,sal,deptno from emp where sal in (select sal from emp where deptno=30) and deptno !=30;select * from emp;insert into emp values(7846,'KEITH','MANAGER',7839,DATE'2017-11-27',2850,2500,10);--列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。select ename,sal from emp where sal > (select max(sal) from emp where deptno=30);--列出在每个部门工作的员工数量、平均工资和平均服务期限。select deptno,(select dept.dname from dept where dept.deptno= e.deptno),count(job),avg(nvl(sal,0)+nvl(comm,0)),avg(months_between(sysdate,hiredate)/12) from emp e group by deptno order by deptno;select (select b.dname from dept b where a.deptno=b.deptno) as deptname ,count(deptno) as deptcount,avg(sal) as deptavgsal from emp a group by deptno;--列出所有员工的姓名、部门名称和工资。select ename,(select dname from dept where e.deptno=dept.deptno),sal from emp e;select ename,dname,sal from emp e,dept d  where e.deptno=d.deptno;select ename,dname,sal from emp e join dept d on e.deptno=d.deptno;--列出所有部门的详细信息和部门人数。select d.deptno,count(e.deptno),dname,loc from dept d join emp e on d.deptno=e.deptno group by d.deptno,d.dname,d.loc;--errorselect a.deptno,a.dname,a.loc,(select count(deptno) from emp b where b.deptno=a.deptno group by b.deptno) as deptcount from dept a;  --列出各种工作的最低工资。select distinct job,(select min(sal) from emp e2 where e1.job = e2.job) from emp e1;select job,avg(sal) from emp group by job; --列出各个部门的MANAGER(经理)的最低薪金。select deptno,min(sal) from emp where job='MANAGER'group by deptno;--列出所有员工的年工资,按年薪从低到高排序。select e.*,(nvl(sal,0)+nvl(comm,0))*12 "年薪" from emp e order by "年薪"select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal;