Oracle笔记5
21:--平均薪水最高的部门的部门编号（优化）：
selectdeptno,avg_sal from
(select avg(sal) avg_sal,deptno from emp group by deptno)
where avg_sal =
(select max(avg（sal）) from emp group by demtno）
22:--平均薪水最低的部门的部门名称：
1、求平均薪水：
select avg(sal) from emp;
2、平均薪水的等级：
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)
3、求最低的等级：
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)
)
4、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)
)
)
23:--创建视图：
sys账户grant 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 * from v$_dept_avg_sal_info;
--改写22中的方法：
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
)
24:--求部门经理人中平均薪水最低的名称（思考题）
--求比普通员工的薪水还要高的经理人
1、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 full)
)
25:（到此之前的sql语句除了单行语句全部掌握！）
--面试题：比较效率(第一个高)：
select * from emp where deptno = 10 and ename like '%A%';
select * from emp where ename like '%A%' and deptno = 10;
到此select语句讲完，接下来的是DML语句。数据库四大语句！
--DML语句：insert，delete，update，create
（Oracle逻辑结构）
--创建用户：-先必须用超级管理员身份登陆，conn sys/bjsxt as sysdba;
-backup scott
exp（备份成为dmp文件然后可以导入数据给新用户）
-create user
create user mgy identified by mgy default tablespace users quota(配额) 10M on users；
-给用户分配权限
grant create session,create table,create view to mgy;//授权
-import the data
imp(倒入数据)
--DML语句：desc dept；
insert into dept values (50,'game','bj');
备份数据办法：
rollback；
create table emp2 as select * from emp；
insert into dept2 (deptno,dname) values (60,'games2');
insert into dept2 select * from dept;
insert有三种形式：按字段顺序挨个往里差；
指定某些字段往里差，不差的值默认为空；
把子查询拿过来的数据挨个差进去，前提是子查询拿出来的东西必须和这张表的结构一样。
