oracle第十六章作业

-------第十六章、课后作业---------------------
1.创建序列，起始位1，自增为1，最小值为1，最大值为9999
create sequence gh
increment by 1
start with 1
minvalue 1
maxvalue 9999
2.创建序列，起始值为50，每次增加5；
create sequence gh
start with 50
increment by 5
3.在表copy_dept中插入记录，其中部门号码采用上一步中创建的序列生成；
create table copy_dept  as select * from dept
insert into copy_dept values(gh.nextval,'NEUEDU','NANJING')
4.请为工资创建索引，比较<10000,>1000,与round（sal）>10000,哪个索引有效，哪个索引无效；
create index sal_scott_index on emp(sal)
select * from emp where sal<10000
select * from emp where sal>1000
select * from emp where round(sal)>10000
5.创建表，采用“create table copy_emp_index as select * from emp”,
生成500万条数据，把其中的“员工号”字段修改为唯一；
create table copy_emp_index as select * from emp
alter table copy_emp_index modify (empno number(10))
insert into copy_emp_index select * from copy_emp_index
update copy_emp_index set empno = rownum
alter table copy_emp_index modify(empno unique)
select * from copy_emp_index
6.查询表copy_emp_index表中员工号为200001的员工姓名，工资，记录执行时间；
select ename,sal
from copy_emp_index
where empno=200001
--执行时间 0.037 seconds
7.在copy_emp_index表的empno字段上创建索引，再次执行第6题语句，记录执行时间并做对比；
create index cei_index on copy_emp_index(empno)
alter table copy_emp_index modify(empno) 
select ename,sal
from copy_emp_index
where empno=19994
------------十七章------------
1.建立新用户neu
create user neu identified by neu
2.给用户neu授权,使其能够登录到数据库，
grant create session to neu;
能够查询scott下的emp表，能修改emp表的sal,ename两个字段
grant select on scott.emp to neu;
grant update(sal,ename) on scott.emp to neu;
3.回收用户neu的登录权限
revoke create session to neu
4.回收用户neu的所有对象权限
revoke all on scott.emp  from neu
5.建立角色role_neu
create role role_neu
6.给角色role_neu授权,使其能够登录到数据库
grant create session to role_neu
7.赋角色role_neu给用户neu
grant role_neu to neu
8.删除角色role_neu
drop role role_neu
9.删除用户neu
drop user neu cascade

-------------数据库---------------------
数据库中有四张表：teacher教师表，student 学生表，course课程表和stucur选课表，
drop table teacher;
drop table student;
drop table curse;
drop table stucur;
1、创建表
create table teacher(
  tid number primary key,
  name varchar2(10)
);
create table student(
  sid number primary key,
  entrancetime date,
  graduation date,
  name varchar2(10)
);
create table course(
   cid number primary key,
   name varchar2(10),
   tid number references teacher(tid) on delete cascade,
   crd number
);
create table stucur(
   sid number references student(sid) on delete cascade,
   cid number references course(cid) on delete cascade,
   score number,
   time date
);
2、填加数据
insert into teacher values(00001,'吴小月' );
insert into teacher values(00002,'刘小军' );
insert into teacher values(00003,'何小巨' );
insert into teacher values(00004,'于小花' );
insert into student values(00001, to_date('1998-09-01','yyyy-mm-dd'),to_date('2002-09-01','yyyy-mm-dd'), '董存存' );
insert into student values(00002, to_date('2003-09-01','yyyy-mm-dd'),to_date('2007-09-01','yyyy-mm-dd'), '曾国军' );
insert into student values(00003, to_date('2006-09-01','yyyy-mm-dd'),to_date('2010-09-01','yyyy-mm-dd'), '袁懿' );
insert into student values(00004, to_date('2012-09-01','yyyy-mm-dd'),null, '周键' );
insert into course values(00001,'自然' ,00001,2 );
insert into course values(00002,'科学' ,00003,3 );
insert into course values(00003,'基因工程' ,00002,1 );
insert into course values(00004,'人工智能' ,00004,4 );
insert into course values(00005,'地理' ,00001,3);
insert into stucur values(00001, 00001,69,to_date('1999-08-01','yyyy-mm-dd'));
insert into stucur values(00001, 00003,70,to_date('2001-08-01','yyyy-mm-dd'));
insert into stucur values(00003, 00002,85,to_date('2004-06-01','yyyy-mm-dd'));
insert into stucur values(00002, 00004,45,to_date('2006-05-01','yyyy-mm-dd'));
3、写出选择‘自然’课程的学生姓名，入学时间，得分
select name,entrancetime,score
from student s1,stucur s2
where s1.sid=s2.sid and s2.cid=(select cid from course where name='自然')
4、写出1999至2012期间，每门课程的名称以及被选修的次数，并按照选修次数的由多到少排序
  select name,count(s.cid)
  from stucur s,course c
  where  EXTRACT(YEAR FROM time) between 1999 and 2012 and s.cid=c.cid
  group by name
  order by count(s.cid) des
5、假设每门课程60分及以上方可获得学分，统计2008年入学的学生每个人获得的总学分
select sum(crd),student.name 
from course,student,stucur 
where course.cid=stucur.cid and student.sid=stucur.sid
   and student.entrancetime between  '1-1月-2008' and '31-12月-2008' and score>60
group by student.name;
6、曾国军同学2012年3月15日的‘基因工程’课程考试中，获得了95分，把该信息记录至相关表中
insert into stucur values((select sid from student where name='曾国军'),(select cid from course where name='基因工程'),95,to_date('2012-03-15','yyyy-mm-dd'))   
select * from stucur
7、由于录入失误，董存存同学选修的'人工智能'实际成绩87录成了78，获得成绩的时间由2001年3月15日
录成了2003年3月4日，请更正
update stucur set score=87,time=to_date('2001-03-15', 'yyyy-mm-dd')
 where cid=(select cid from course where name='人工智能') 
    and sid=(select sid from student where name='董存存');