select ename , dname from emp cross join dept;
交叉连接ename dname找出每种组合

23,外连接
select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);

会把左边那张表没产生连接的也打印出来.

右外连接同理
全外连接
select ename, dname from emp e full join dept d on (e.deptno = d.deptno);

24,
求部门中哪些人的薪水最高
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 ename, sal from emp where sal = (select max(sal) from emp);

工资位于平均工资之上

哪些人是经理人
select ename from emp where empno in(select distinct mgr from emp);

不用组函数求薪水的最高值
select ename, sal from emp where ename not in( select distinct e1.ename fro
m emp e1 join emp e2 on e1.sal < e2.sal);

平均薪水最高的部门编号
改天

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

比普通员工的最高薪水还要高的经理人
1)先选出哪些不是经理人的最高薪水 select max(sal) from emp
where empno not in (select mgr from emp where mgr is not null)
2)
select ename, sal from emp
where sal >
(
select max(sal) from emp
where empno not in (select mgr from emp where mgr is not null)
)
and empno in (select distinct mgr from emp)

25,创建视图view 需要权限
简化用creat view v$dept_avg_sal_info as
select deptno, avg_sal, grade from
(select deptno, avg(sal) avg_sal from emp group by deptno) e
join salgrade s on (e.avg_sal between s.losal and hisal)
授权
conn sys/orcl as sysbda;
grant create table, creat view to scott;

conn scott/tiger

26,
建立新的用户
登录超级管理员 conn sys/orcl as sysdba;

1)删除用户drop user scott cascade;
2)创建新用户
1--backup scott
exp 导出
2--create user
create user username用户名 identified by password密码 default tablespace users默认空间 quota 10M on users分配10M的空间
3--赋权限
grant create session登录, create table建表, create view to username;
3--import data
imp
需要输入用户名的地方输入scott也就是你要导的那个用户名

ORA-01950错误分析 对表空间无权限2009-02-12 21:10ORA-01950错误分析
1.用户没有resource权限。

2.在修改了用户的表空间后，在用户中创建表时会出现以下的错误：ORA-01950: 表空间'HDNHG'中无权限

这个时候就要给修改了表空间的用户重新分配权限如：grant connect,resource to username;

再切换到该用户下创建表OK了。。

3.总的来说这个错误是由于对表空间操作的权限不足造成的,所以这个时候就可以检查出错之前对于所操作的表空间有哪些权限可能被revoke了(或者说原来就没有grant),然后重新赋予相应的权限。

撤销权限revoke

27,
create table emp2 as select * from emp;//相当于emp2是emp的备份
create v iew xxx as xxx;
insert into dept values(50,'game','sdf');//插入
insert into dept (deptno)values(60);//只插deptno这个字段
insert into dept select * from dept;

update
update emp2 set sal = sal * 2,ename = ename || 'sb';

delete from emp2 where ename = 'xx';

drop table xxx;

28,rownum
select empno, ename from emp where rownum <= 5;
只能是<= 或者<不能是= >
解决方法用一个子查询
select empno,ename from(select rownum r, ename, empno from emp) where r > 5;

求薪水最高的前5名雇员
select ename, sal, rownum r from
(
select ename, sal from emp order by sal desc
)
where rownum <= 5

mysql 下可以用limit

29,create
创建表
create table t (a varchar2(10));