68. 右外连接
select empno, ename, d.deptno, dname, loc from emp e, dept d where e.deptno(+) = d.deptno;
select empno, ename, d.deptno, dname, loc from emp e right outer join dept d on e.deptno = d.deptno;
select empno, ename, d.deptno, dname, loc from emp e right join dept d on e.deptno = d.deptno;
69. select e.empno, e.ename, m.empno, m.ename from emp e, emp m where e.mgr = m.empno;
70. select e.empno, e.ename, m.empno, m.ename from emp e, emp m where e.mgr = m.empno(+);
71.
select * from emp e, dept d where e.deptno = d.deptno and d.deptno = 30;
select * from emp e inner join dept d on e.deptno = d.deptno where d.deptno = 30;
select * from emp e join dept d on e.deptno = d.deptno where d.deptno = 30;
select * from emp e natural join dept d where deptno = 30;
select * from emp e join dept d using(deptno) where deptno = 30;
72.
select e.ename, d.deptno, d.dname, d.loc from emp e right outer join dept d on e.deptno = d.deptno;
select e.ename, d.deptno, d.dname, d.loc from emp e right join dept d on e.deptno = d.deptno;
select e.ename, d.deptno, d.dname, d.loc from emp e, dept d where e.deptno(+) = d.deptno;
73. select count(ename) from emp;
74. select min(sal) from emp;
75. select max(sal) from emp;
76. select sum(sal) from emp;
77. select avg(sal) from emp;
78. select sum(sal) from emp where deptno = 20;
79. select avg(sal) from emp where deptno = 20;
80. 求出每个部门的雇员数量
select deptno, count(deptno) from emp group by deptno;
select deptno, count(empno) from emp group by deptno;
81. 求出每个部门的平均工资
select deptno, avg(sal) from emp group by deptno;
82. 按部门分组, 并显示部门的名称, 及每个部门的员工数
select d.dname, count(e.empno) from emp e, dept d
where e.deptno = d.deptno
group by d.dname;
select d.deptno, d.dname, temp.c
from (select deptno, count(e.empno) c from emp e group by e.deptno) temp, dept d
where temp.deptno = d.deptno;
83. 要求显示出平均工资大于 2000 的部门编号和平均工资
84. 显示非销售人员工作名称以及从事同一工作雇员的月工资的总和,并且要满足从事同一工作的雇员的月工资合计大于 5000, 输出结果按月工资的合计升序排序.
select job, sum(sal) su from emp where job <> 'SALESMAN' group by job having sum(sal) > 5000 order by su;
select temp.job, sum(temp.sal) s
from (select job, sal from emp e where job <> 'SALESMAN') temp
group by temp.job
having sum(temp.sal) > 5000
order by s;
85. 求出平均工资最高的部门工资
select max(avg(sal)) from emp group by deptno;
86. 要求查询出比雇员编号为 7654 工资高的所有雇员信息
select * from emp where sal >(select sal from emp where empno = 7654);
87. 要求查询出工资比 7654 高, 同时与 7788 从事相同工作的全部雇员信息
select * from emp
where sal >(select sal from emp where empno = 7654)
and job = (select job from emp where empno = 7788);
88. 要求查询出工资最低的雇员姓名, 工作, 工资
select ename, job, sal from emp where sal = (select min(sal) from emp);
89. 要求查询出: 部门名称，部门的员工数，部门的平均工资，部门的最低收入雇员的姓名
select d.dname, temp.c, temp.a, e.ename
from dept d,
(select deptno, count(empno) c, avg(sal) a, min(sal) m from emp group by deptno) temp,
emp e
where d.deptno = temp.deptno and e.sal = temp.m;
select d.deptno, temp.dname, temp.c, temp.a, e.ename, e.sal
from
(select d.dname , count(e.empno) c, avg(e.sal) a, min(e.sal) m
from emp e, dept d
where e.deptno = d.deptno
group by d.dname) temp,
emp e,
dept d
where temp.m = e.sal
and temp.dname = d.dname;
90. 求出每个部门的最低工资的雇员的信息
select * from emp where sal in(select min(sal) from emp group by deptno);
select * from emp where sal =any(select min(sal) from emp group by deptno);
select * from
(select min(sal) m from emp group by deptno) temp,
emp e
where e.sal = temp.m;
91. 范例 90 中, 比子查询条件中最低(小)的工资要大的雇员信息
select * from emp where sal >any(select min(sal) from emp group by deptno);
select