应该具备的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('','[e-mail]') 

--ORA-01400: 无法将 NULL 插入 ("SCOTT"."STU"."NAME")  

insert into stu(name,email) values('tianyuexing','[e-mail]');  

insert into stu(name,email) values('tianyuexing','[e-mail]');

--ORA-00001: 违反唯一约束条件 (SCOTT.STU_NAME_EMAIL_UIN)