Oracle-02

1./*列出所有部门的详细信息和部门人数*\

SQL> select d.deptno,d.dname,d.loc,e.count from dept d,(select deptno,count(*) count from emp group by deptno) e where e.deptno=d.deptno;

更好的写法是：SQL> select d.*,nvl(e.count,0) from dept d,(select deptno,count(*) count from emp group by deptno) e where e.deptno=d.deptno;

d.*就代表部门的全部信息

nvl函数是如果e.count为NULL的话则用0代替，否则返回e.count

2.\* 列出与scott从事相同工作的所有员工及部门名称*\

SQL> select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and e.job=
(select job from emp where ename=Upper('scott')) and e.ename!='SCOTT';

3.列出各种工作的最低工资及从事此工作的雇员名称

SQL> select e.ename,min,d.job from emp e,(select min(sal) min,job from emp group by job) d where e.sal=d.min;

4.列出各个部门manager的最低薪金*/

SQL> select deptno,min(sal) from emp where job='MANAGER' group by deptno;

5./*列出所有员工的年工资，按年薪从低到高排序这个地方要用到NVL*/

SQL> select ename,(sal+nvl(comm,0))*12 yearSal from emp order by yearSal;