plsql练习2

--1、列出至少有一个员工的部门所有信息。

select a.dname,a.loc,b.* from dept a,(select deptno,count(*) from emp group by deptno having count(*)>1) b  where  a.deptno=b.deptno;

--2、列出所有"clerk"(办事员)的姓名及其部门名称，部门的人数。

select c.ename,d.* from emp c,(select b.dname,b.loc,a.* from dept b,(select deptno,count(*) from emp group by deptno having count(*)>1) a  where  a.deptno=b.deptno) d where c.deptno=d.deptno and c.ename = 'CLARK';

--3、查询出部门名称、部门的员工数，部门的平均工资，部门的最低收入雇员的姓名

select b.dname,b.counts,b.avgsal,b.minsal ,a.ename

from emp a,

(select b.deptno,b.dname,count(*) counts,round(avg(sal),2) avgsal,min(sal) minsal

from emp a,dept b,salgrade c

where a.deptno=b.deptno and a.deptno=b.deptno

and a.sal between c.losal and hisal

group by  b.deptno,b.dname) b,

salgrade c

where a.sal=b.minsal

and a.sal between c.losal and c.hisal

--4、列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数。

select  job ,count(*) from emp group by job having min(sal)>1500;  

--5、列出在部门“sale”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。

select e.ename,d.*

from emp e,dept d

where e.deptno=d.deptno

and d.dname='SALES';

--6、列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。

select e.ename,d.dname,em.ename,sg.grade

from emp e,dept d,emp em,salgrade sg

where e.deptno=d.deptno and

e.mgr=em.empno(+) and

e.sal between sg.losal and sg.hisal

and e.sal>(select avg(sal) from emp);

--7、列出与"scott"从事相同工作的所有员工及部门名称。

select e.*,d.dname

from emp e,dept d

where e.deptno=d.deptno

and e.job=(select job from emp where ename='SCOTT')

and e.ename<>'SCOTT';

--8、列出薪金高于部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。

select e.ename,e.sal,d.dname

 from emp e,dept d

 where e.deptno=d.deptno

 and e.sal>ALL(select sal from emp where deptno=30);

 --9、列出每个部门的员工数量，平均工资和平均服务期限(入职到现在的工作时间)。

 select deptno,count(*), avg(sal), avg(months_between(sysdate,hiredate)/12) from emp group by deptno;

 --10、列出所有部门的详细信息和部门人数。

SELECT deptno dno,COUNT(empno) cou

FROM emp  

GROUP BY deptno; 

SELECT d.*,NVL(ed.cou,0) FROM dept d,(SELECT deptno dno,COUNT(empno) cou

FROM emp  

GROUP BY deptno) ed  

WHERE 

d.deptno=ed.dno(+); 

--11、列出各种工作的最低工资及从事此工作的雇员姓名。

SELECT MIN(sal) m FROM emp GROUP BY job;  

SELECT e.ename FROM emp e

WHERE e.sal IN(SELECT MIN(sal) m FROM emp GROUP BY job);

--12、列出各个部门最低薪金的经理(manager)。

SELECT deptno,MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno; 

--13、求出部门名称中，带'S'字符的部门编号，部门总工资、部门人数。

 SELECT deptno FROM dept WHERE dname LIKE '%S%'; 

SELECT SUM(sal),COUNT(empno) 

FROM emp e  

WHERE e.deptno IN (SELECT deptno FROM dept WHERE dname LIKE '%S%') 

GROUP BY deptno; 

--14、给任职日期超过10年的人加薪10%；

UPDATE emp SET sal=sal*1.1 WHERE MONTHS_BETWEEN(sysdate,hiredate)/12>10;