有关Oracle 的查询语句的几个小练习(针对ORCL数据库的emp,dept,salgrade等数据表)

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

select dept.*,m.cou from dept,(select deptno,count(empno) cou from emp group by deptno having count(empno)>=1 order by deptno) m where dept.deptno=m.deptno ;

2、列出薪金比SMITH对的所有员工：

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

3、列出所有员工的姓名以及其直接上级的姓名：

select e.ename,m.ename from emp e,emp m where e.mgr=m.empno;

4、列出受雇日期早于其直接上级的所有员工的编号、姓名，部门名称

select e.empno,e.ename,d.dname from emp e,emp m,dept d where e.mgr=m.empno and e.hiredate<m.hiredate and e.deptno=d.deptno;

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

select emp.empno,emp.ename,dept.deptno,dept.dname from emp right join dept on emp.deptno=dept.deptno order by emp.deptno;

6、列出所有"CLERK(职员)"的姓名以及部门名称，部门的人数

select e.ename,d.dname,c.cou from emp e,dept d,(select deptno,count (*) cou from emp group by deptno ) c where e.job='CLERK' and e.deptno=d.deptno and c.deptno=d.deptno;

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

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

8、列出在部门"SALES"工作的员工的姓名，假定不知道销售部的部门编号

select ename from emp where deptno=(select deptno from dept where dname='SALES') ;

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

select  e.ename,d.dname,m.ename,s.grade from emp e,dept d,emp m,salgrade s where e.sal>(select avg(sal) from emp) and e.mgr=m.empno(+) and e.deptno=d.deptno and e.sal between s.losal and s.hisal;

外连接:
除了显示匹配相等连接条件的数据外,还可以显示某一个表中无法匹配相等连接条件的记录!
------------------------------------------------
1) 左条件(+) = 右条件
左条件所在的表必须严格进行相等连接条件的匹配,而右条件所在的表除了匹配相等连接条件外，还可以显 示无法匹配连接条件的数据!
也称为右外连接.
--------------------------------
可以用下 列语句取代:
SELECT...FROM 表1 RIGHT OUTER JOIN 表2 ON 条件;
2) 左条件 = 右条件(+)
右条件所在的表必须严格进行相等连接条件的匹配,而左条件所在的表除了匹配相等连接条件外，还可以显示无法匹配连接条件的数据!
也 称为左外连接.
--------------------------------
可以用下列语句取代:
SELECT...FROM 表1 LEFT OUTER JOIN 表2 ON 条件; 

10、列出与"SCOTT"从事相同工做的所有员工及部门名称

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

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

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

12、列出薪金高于在部门30工作的所有员工的薪金的员工的姓名和薪金，部门名称

select e.ename,e.sal,d.dname from emp e,dept d where sal>(select max(sal) from emp where deptno=30) and e.deptno=d.deptno;

或者

select e.ename,e.sal,d.dname from emp e,dept d where sal>ALL(select sal from emp where deptno=30) and e.deptno=d.deptno;

13、列出在每个部门工作的员工数量、平均工资以及平均工作年限

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

小练习：

select months_between(sysdate,'6-8月-1989')/12 from dual;

14、列出各种工作的最低工资以及从事此工作的雇员姓名

select ename,job,sal from emp where (job,sal) in (select job,min(sal) from emp group by job);

15、列出各个部门的MANAGER的最低薪资

select d.dname,min(e.sal) from emp e,dept d where e.deptno=d.deptno and e.job='MANAGER' group by d.dname;

16、列出所有员工的年工资，按年薪从低到高排序

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

17、查出某个员工的上级主管，并要求这些主管中的薪水超过3000

select  e.ename,m.ename manager,m.sal from emp e,emp m where e.mgr=m.empno and m.sal>3000;

18、求出部门中带‘S’字符的部门员工的工资合计，部门人数

select d.dname,sum(e.sal),count(e.empno) from emp e,dept d where e.deptno=d.deptno and d.dname like '%S%' group by d.dname;

19、给任期超过10年的人加薪20%

update emp set sal=sal*1.2 where months_between(sysdate,hiredate)/12>10;