应当具备的Oracle基本功(1)

应该具备的Oracle基本功(1)

以下的文章，主要为大家在实际工作中提供一种解决方法。

---用户名：scott

---密? 码：tiger

---*********Oracle表连接与子查询示例************

---求部门中哪些人的薪水最高

select?ename,sal?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?ename?from?emp?where?empno?in?(select?distinct?mgr?from??emp);?

?

---不用组函数，求薪水的最高值

select?sal?from?emp?where?sal?not?in?(select?distinct?e1.sal?from?emp?e1?join?emp?e2?on?(e1.sal?<?e2.sal))；?

---用组函数，求薪水的最高值

select?max(sal)?from?emp；

?

---求平均薪水最高的部门的部门编号

select?deptno?,?avg_sal?from??(select?avg(sal)?avg_sal,deptno?from?emp?group?by?deptno)?t ?where?avg_sal?= ??(select??max(avg_sal)?from?(select?avg(sal)?avg_sal,deptno?from?emp?group?by?deptno)?t)；?

----组函数嵌套的写法

select?deptno?,?avg_sal?from??(select?avg(sal)?avg_sal,deptno?from?emp?group?by?deptno)?t ?where?avg_sal?= ??(select??max(avg(sal))?from?emp?group?by?deptno)；?

---求平均薪水最高的部门的名称

select?dname?from?dept? ?where?deptno?= ?( ??select?deptno?from??(select?avg(sal)?avg_sal,deptno?from?emp?group?by?deptno)?t ??where?avg_sal?= ??(select??max(avg_sal)?from?(select?avg(sal)?avg_sal,deptno?from?emp?group?by?deptno)?t) ?)；?

---求平均薪水的等级最低的部门的部门名称

select?avg(sal)?avg_sal,deptno?from?emp?group?by?deptno??

?

--部门平均薪水

select?min(avg_sal)?from?( ?select?avg(sal)?avg_sal,deptno?from?emp?group?by?deptno ?)?

--平均工资的最小值

select?avg_sal,deptno?from? (select?avg(sal)?avg_sal,deptno?from?emp?group?by?deptno)?t ?where?avg_sal?= ?( ?select?min(avg_sal)?from?( ?select?avg(sal)?avg_sal,deptno?from?emp?group?by?deptno ?) ?)?

--平均工资的最小值及部门编号

select?t.avg_sal,t.deptno,s.grade?from? ?(select?avg(sal)?avg_sal,deptno?from?emp?group?by?deptno)?t ?join?salgrade?s?on?(t.avg_sal?between?s.losal?and?s.hisal)? ?where?avg_sal?= ?( ?select?min(avg_sal)?from?( ?select?avg(sal)?avg_sal,deptno?from?emp?group?by?deptno ?) ?)?

--平均工资的最小值及部门编号和工资等级

select?d.dname,t.avg_sal,t.deptno,s.grade?from? ?(select?avg(sal)?avg_sal,deptno?from?emp?group?by?deptno)?t ?join?salgrade?s?on?(t.avg_sal?between?s.losal?and?s.hisal)? ?join?dept?d?on?(t.deptno?=?d.deptno) ?where?avg_sal?= ?( ?select?min(avg_sal)?from?( ?select?avg(sal)?avg_sal,deptno?from?emp?group?by?deptno ?) ?)?

--平均工资的最小值及部门编号和工资等级及部门名称

----Another 按照题意的写法

select?t1.deptno,t1.avg_sal,grade,d.dname?from?( ?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) ?)?t1 ?join?dept?d?on?(t1.deptno?=?d.deptno)? ?where?grade?= ?(? ?select?min(grade)?from?( ?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) ?) ?);?

---创建视图或者表，如果没有权限

conn?sys/sys?as?sysdba;?

--已连接。

grant?create?table,?create?view?to?scott;?

--授权成功。

---创建视图

create?view?v$_dept_avg_sal_info?as???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);?

--视图已建立。

---创建这个v$_dept_avg_sal_info视图可以简化上面那个查询的重复代码

select?t1.deptno,t1.avg_sal,grade,d.dname?from?v$_dept_avg_sal_info?t1 ?join?dept?d?on?(t1.deptno?=?d.deptno)? ?where?grade?= ?(? ?select?min(grade)?from?v$_dept_avg_sal_info ?);?

---求比普通员工的最高薪水还要高的经理的名称

select?max(sal)?from?emp?where?empno?not?in?(select?distinct?mgr?from?emp?where?mgr?is?not?null);?

--普通员工的最高薪水

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) ?);?

--普通员工的最高薪水还要高的经理的名称

--- Oracle 联机归档日志 备份方式

---求薪水最高的第6名到第10名雇员(rownum)

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;?

---五种约束条件

create?table?stu ?(? ?id?number(2), ?name?varchar2(20)?constraint?stu_name_nn?not?null,--非空约束 ?sex??number(2), ?age?number(3), ?sdate?date, grade?number(3)?default?1, ?class?number(3), ?email?varchar2(50), ?constraint?stu_name_email_uin?unique(name,email)--唯一主键 ?)?; ???insert?into?stu(name,email)?values('','tianyuexing@163.com')?--ORA-01400:?无法将?NULL?插入?("SCOTT"."STU"."NAME") ?insert?into?stu(name,email)?values('tianyuexing','tianyuexing@163.com'); ?insert?into?stu(name,email)?values('tianyuexing','tianyuexing@163.com');--ORA-00001:?违反唯一约束条件?(SCOTT.STU_NAME_EMAIL_UIN)?