Oracle数据库中要记住的SQL语言

--求部门中哪些人的薪水最高
select ename, sal, emp.deptno from emp
join (select max(sal) max_sal, deptno from emp group by deptno) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno);
--求部门平均薪水的等级
select deptno, avg_sal, grade from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal);
--求部门平均的薪水等级
select deptno, avg(grade) from
(select deptno, ename, grade from emp join salgrade s on (emp.sal between s.losal and
s.hisal)) t
group by deptno;
--雇员中有哪些人是经理人
select empno, ename from emp where empno in (select distinct mgr from emp);
--不要用组函数，求薪水的最高值（面试题）
select sal from emp where sal not in
(select e1.sal from emp e1 join emp e2 on(
e1.sal < e2.sal));
--求平均薪水最高的部门的部门编号
select deptno, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
 (select deptno, avg(sal) avg_sal from emp group by deptno)
);

select deptno, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno)
where avg_sal =
(select max(avg(sal)) from emp group by deptno);
--求平均薪水最高的部门的部门名称
select dname from dept where deptno =
(
 select deptno from
  (select deptno, avg(sal) avg_sal from emp group by deptno)
   where avg_sal =
    (select max(avg_sal) from
     (select deptno, avg(sal) avg_sal from emp group by deptno)
)
);
--求平均薪水的等级最低的部门的部门名称
select dname, t1.deptno, grade, avg_sal from
  (
  select deptno, grade, avg_sal from
    (select deptno, avg(sal) avg_sal from emp group by deptno) t
  join salgrade s on (t.avg_sal between s.losal and s.hisal)
  ) t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
  select min(grade) from
  (
    select deptno, grade, avg_sal from
      (select deptno, avg(sal) avg_sal from emp group by deptno) t
    join salgrade s on (t.avg_sal between s.losal and s.hisal)
  )
)

--试图：虚表或子查询
conn sys/cuilin as sysdba
grant create table, create view to scott

create view v$_dept_avg_sal_info as
select deptno, grade, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)

select dname, t1.deptno, grade, avg_sal from
v$_dept_avg_sal_info t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
select min(grade) from v$_dept_avg_sal_info
)
--求部门经理人中平均薪水最低的部门名称（思考题）

--求比普通员工的最高薪水还要高的经理人名称
select ename from emp where empno in
(select distinct mgr from emp where mgr is not null)
and sal >
(
select max(sal) from emp where empno not in
(select distinct mgr from emp where mgr is not null)
)
--求薪水最高的前5名雇员
select ename, sal from
(select ename, sal from emp order by sal desc)
where rownum <= 5
--求薪水最高的第6到第10名雇员（重点掌握）
select ename, sal from
  (
    select ename, sal, rownum r from
      (select ename, sal from emp order by sal desc)
  )
where r >= 6 and r <= 10
--练习：求最高入职的5名员工

--面试题：比较效率
  select * from emp where deptno = 10 and ename like '%A%';
  select * from emp where ename like '%A%' and deptno = 10;
  参考：1.因为“短路”现象，上面的比下面的执行效率更高
 2.Oracle数据库可能对这些语句进行适当的优化，在实际当中不见得下面的肯定比上面的执行
 效率更高

--SQL面试题
有3个表S，C，SC
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
2，列出2门以上（含2门）不及格学生姓名及平均成绩。
3，即学过1号课程有学过2号课所有学生的姓名。
请用标准SQL语言写出答案，方言也行（请说明是使用什么方言）。
答案：
1、select sname from s join sc on (s.sno = sc.sno) join c on (c.cno = sc.cno)
where c.cteacher <> 'liming';
2、select sname where sno in
(select sno from sc where scgrade < 60 group by sno having count(*) > 2);
3、select sname from s where sno in
(select sno from sc where cno = 1 and cno in
(select distinct sno from sc where cno = 2));

 

注：1、以上为《尚学堂Oracle视频教程》的一部分内容

        2、转载请注明：http://blog.csdn.net/gunner086

谢谢合作！