使用scott/tiger用户下的emp表和dept表完成下列练习,表的结构说明如下
emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)
dept部门表(deptno部门编号/dname部门名称/loc地点)
工资 = 薪金 + 佣金
1.列出至少有一个员工的所有部门
方法1:select t.* from scott.dept t where exists( select 1 from scott.emp a where a.deptno = t.deptno);
方法2:select t.* from scott.dept t where t.deptno in ( select a.deptno from scott.emp a );
2.列出薪金比“SMITH”多的所有员工。
select t.*
from scott.emp t
where t.sal > (select a.sal from scott.emp a where upper(a.ename) = 'SMITH')
3.列出所有员工的姓名及其直接上级的姓名。
select t.ename, a.ename from scott.emp t, scott.emp a where a.empno = t.mgr
4.列出受雇日期晚于其直接上级的所有员工。
select t.* from scott.emp t, scott.emp a where a.empno = t.mgr and t.hiredate > a.hiredate
5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。
select t.dname,a.* from scott.dept t, scott.emp a where a.deptno(+) = t.deptno
6.列出所有“CLERK”(办事员)的姓名及其部门名称。
select a.ename,t.dname from scott.dept t, scott.emp a where a.deptno = t.deptno and a.job='CLERK';
7.列出最低薪金大于1500的各种工作。
select t.job
from scott.emp t
having(min(t.sal)) > 1500
group by t.job;
8.列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。
select t.* from scott.emp t where t.deptno in (select a.deptno from scott.dept a where a.dname='SALES') ;
9.列出薪金高于公司平均薪金的所有员工。
select t.* from scott.emp t where t.sal > (select avg(a.sal) from scott.emp a);
10.列出与“SCOTT”从事相同工作的所有员工。
select t.* from scott.emp t where exists (select 1 from scott.emp a where a.ename='SCOTT' and t.job = a.job );
select * from scott.emp where job=(select job from scott.emp where ename='SCOTT');
11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select * from emp where sal in
(select sal from emp where deptno=30);
或
select * from emp where sal = any
(select sal from emp where deptno=30);
12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select * from emp where sal>all
(select sal from emp where deptno=30);
--最小值<all
select * from emp where sal < all
(select sal from emp where deptno=30);
13.列出在每个部门工作的员工数量、平均工资和平均服务期限。
select deptno,count(*),
trunc(avg(sal+nvl(comm,0))) avgsal,
to_char(to_date('0001-01-01','yyyy-mm-dd') + avg(sysdate-hiredate)
-366-31,'yy"年"mm"月"dd') avgday
from emp group by deptno;
14.列出所有员工的姓名、部门名称和工资。
select ename,dname,sal+nvl(comm,0) from emp,dept where emp.deptno=dept.deptno;
16.列出所有部门的详细信息和部门人数。
select a.*,(select count(*) from emp where deptno=a.deptno) tot
from dept a ;
17.列出各种工作的最低工资。
select job,min(sal+nvl(comm,0)) from emp group by job;
18.列出MANAGER(经理)的最低薪金。
select min(sal) from emp where job='MANAGER' ;
19.列出所有员工的年工资,按年薪从低到高排序。
select ename,(sal+nvl(comm,0))*12 tot from emp order by tot;
-----orcle的等连接
SELECT d.* FROM EMP E ,DEPT D WHERE E.DEPTNO=D.DEPTNO;
-----orcla的外连接
SELECT d.* FROM EMP E ,DEPT D WHERE E.DEPTNO(+)=D.DEPTNO;
+放在没有匹配行的表一侧,所以dept表的记录完全显示
--标准等联结
select dept.* from emp inner join dept
on emp.deptno = dept.deptno;
--标准的右外联结
select dept.* from emp right outer join dept
on emp.deptno = dept.deptno;
select dept.* from dept left outer join emp
on emp.deptno = dept.deptno;
emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)
dept部门表(deptno部门编号/dname部门名称/loc地点)
工资 = 薪金 + 佣金
1.列出至少有一个员工的所有部门
方法1:select t.* from scott.dept t where exists( select 1 from scott.emp a where a.deptno = t.deptno);
方法2:select t.* from scott.dept t where t.deptno in ( select a.deptno from scott.emp a );
2.列出薪金比“SMITH”多的所有员工。
select t.*
from scott.emp t
where t.sal > (select a.sal from scott.emp a where upper(a.ename) = 'SMITH')
3.列出所有员工的姓名及其直接上级的姓名。
select t.ename, a.ename from scott.emp t, scott.emp a where a.empno = t.mgr
4.列出受雇日期晚于其直接上级的所有员工。
select t.* from scott.emp t, scott.emp a where a.empno = t.mgr and t.hiredate > a.hiredate
5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。
select t.dname,a.* from scott.dept t, scott.emp a where a.deptno(+) = t.deptno
6.列出所有“CLERK”(办事员)的姓名及其部门名称。
select a.ename,t.dname from scott.dept t, scott.emp a where a.deptno = t.deptno and a.job='CLERK';
7.列出最低薪金大于1500的各种工作。
select t.job
from scott.emp t
having(min(t.sal)) > 1500
group by t.job;
8.列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。
select t.* from scott.emp t where t.deptno in (select a.deptno from scott.dept a where a.dname='SALES') ;
9.列出薪金高于公司平均薪金的所有员工。
select t.* from scott.emp t where t.sal > (select avg(a.sal) from scott.emp a);
10.列出与“SCOTT”从事相同工作的所有员工。
select t.* from scott.emp t where exists (select 1 from scott.emp a where a.ename='SCOTT' and t.job = a.job );
select * from scott.emp where job=(select job from scott.emp where ename='SCOTT');
11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select * from emp where sal in
(select sal from emp where deptno=30);
或
select * from emp where sal = any
(select sal from emp where deptno=30);
12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select * from emp where sal>all
(select sal from emp where deptno=30);
--最小值<all
select * from emp where sal < all
(select sal from emp where deptno=30);
13.列出在每个部门工作的员工数量、平均工资和平均服务期限。
select deptno,count(*),
trunc(avg(sal+nvl(comm,0))) avgsal,
to_char(to_date('0001-01-01','yyyy-mm-dd') + avg(sysdate-hiredate)
-366-31,'yy"年"mm"月"dd') avgday
from emp group by deptno;
14.列出所有员工的姓名、部门名称和工资。
select ename,dname,sal+nvl(comm,0) from emp,dept where emp.deptno=dept.deptno;
16.列出所有部门的详细信息和部门人数。
select a.*,(select count(*) from emp where deptno=a.deptno) tot
from dept a ;
17.列出各种工作的最低工资。
select job,min(sal+nvl(comm,0)) from emp group by job;
18.列出MANAGER(经理)的最低薪金。
select min(sal) from emp where job='MANAGER' ;
19.列出所有员工的年工资,按年薪从低到高排序。
select ename,(sal+nvl(comm,0))*12 tot from emp order by tot;
-----orcle的等连接
SELECT d.* FROM EMP E ,DEPT D WHERE E.DEPTNO=D.DEPTNO;
-----orcla的外连接
SELECT d.* FROM EMP E ,DEPT D WHERE E.DEPTNO(+)=D.DEPTNO;
+放在没有匹配行的表一侧,所以dept表的记录完全显示
--标准等联结
select dept.* from emp inner join dept
on emp.deptno = dept.deptno;
--标准的右外联结
select dept.* from emp right outer join dept
on emp.deptno = dept.deptno;
select dept.* from dept left outer join emp
on emp.deptno = dept.deptno;