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,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) ) )
8.视图:view就是一张虚表,一个子查询 conn sys/change_on_install as sysdba; grant create table,create view to scott; conn scott/tiger; 创建视图
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) 使用视图后可以简写
select dname,t1.deptno,grade,avg_sal_info from
v$_dept_avg_sal t1 join dept on (t1.deptno = dept.deptno) where t1.grade = (
select min(grade) from v$_dept_avg_sal_info )
9.求比普通员工最高薪水还要高的经理人的名称 先求普通员工的最高薪水
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); )
第二十四课 备份与恢复
DOS环境下:exp导出,imp导入 --drop user yugang cascade; --cd \\ --cd temp =-delete *.* --exp
--create user yugang indentified by yugang default tablespace users quota 10M on users --grant create session,create table,create view to yugang --imp the data
第一次输入的用户名密码为:yugang/yugang
第二次输入的用户名密码为:导出数据的用户的用户名和密码 --rollback
create table emp2 as select * from emp; 第二十五课:rownum
rownum是在Oracle中在表的后面加的一个尾字段,并且只能使用诸如rownum
select ename,sal from emp order by sal desc where sal <= 5; 2.求薪水值最高的第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;
第二十六课:homework_dml_transaction SQL面试题
有三张表S、C、SC
S(SNO、SNAME)代表（学号、姓名）
C(CNO、CNAME、CTEACHER)代表（课号、课名、老师） SC(SNO、CNO、SCGRADE)代表（学号、课号、成绩） 1.求出没选过郭富城老师的所有学生姓名
2.列出2门以上（含2门）不及格的学生姓名及平均成绩 3.既学过1号课程又学过2号课程的所有学生姓名
1.select sname from s join sc on (s.sno = sc.sno) join c on (c.cno = sc.cno) where c.cteacher <> '郭富城'; 2.select sname from s 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 sno in (select distinct sno from sc where cno=2) )
事务transaction代表一组不可分割的操作，要么全部执行，要么全部不执行， transaction起始于一条DML语句，结束于commit;语句，或者是DCL、DDL语句，在事务未提交前
可以通过rollback回滚事务，正常退出事务会自动提交，非正常退出事务会自动回滚。 第二十七课：create table create table student (
id number(6), name varchar2(20), sex number(1), age number(3), sdate date,
grade number(2) default 1, class number(4), email varchar2(50) );
第二十八~三十课 constraint
not null,unique(当某字段有unique约束时，可以插入空值，空值之间不重复)、主键、外键、 check约束 create table student (
id number(6),
name varchar2(20) constraint stu_name_nn not null, sex number(1), age number(3), sdate date,
grade number(2) default 1, class number(4),
email varchar2(50) unique ) /
行级约束（放在字段后面）与表级约束（加在表后面）： create table student (
id number(6),
name varchar2(20) constraint stu_name_nn not null, sex number(1), age number(3), sdate date,
grade number(2) default 1, class number(4), email varchar2(50),
constraint stu_email_name_uni unique(name,email) ) / 主键约束
create table student (
id number(6) primary key,
name varchar2(20) constraint stu_name_nn not null, sex number(1),
age number(3), sdate date,
grade number(2) default 1, class number(4), email varchar2(50),
constraint stu_email_name_uni unique(name,email) ) /
create table student (
id number(6),
name varchar2(20) constraint stu_name_nn not null, sex number(1), age number(3), sdate date,
grade number(2) default 1, class number(4), email varchar2(50),
constraint stu_id_pk primary key(id),
constraint stu_email_name_uni unique(name,email) ) / 外键约束
外键约束被参考的字段必须是主键。 create table class (
id number(4) primary key, name varchar2(20) not null ) /
create table student (
id number(6),
name varchar2(20) constraint stu_name_nn not null, sex number(1),