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;