上海某学校考试题，和大家一起讨论。

Question

1建立一个新用户，用户名为myguest，具有DBA的权限，然后以该用户的身份建立一个表（MYTABLE：ID INTEGER，NAME VARCHAR2（20），MEMO VARCHAR22（100）），然后将该用户的所有数据库对象备份到指定位置(D:\mybackup)。最后一步是在MYTABLE表中添加一行记录，然后用刚才最的备份数据恢复到当前数据库中。2创建一个表student(id varchar2(5),name varchar2(20),salary number,meno varchar2(50)),然后创建一个触发器(如果已经存在则覆盖掉)mytrigger,要求向该表插入数据只前先判断相同id的记 录是否存在，如果存在，则报告信息“该学生已经存在”，并在备注字段中写入“重复”字样。
3使用ORACLE的SQL/PLUS工具，创建两个表(如果已经存在则覆盖掉)student和subject,结构分别为(id varchar2(5),name varchar2(20),age number,memo varchar2(50)).(studentid varchar2(5),subject id varchar2(5),score number),输入四个学生的资料(001 张三，002 李四，003 王五，004 赵六)和三个学科的成绩资料(001 A1 82，001 A2 93，001 A3 91，002 A1 79，002 A2 81，003 A1 86，003 A3 76，004 A2 58，004 A3 77),然后建立一个视图myview，要求显示每个学生三门课程的成绩和平均成绩，每个学生显示一行，无成绩的课程显示“缺考”。
4为ORACLE表空间新增加一个文件，要求初始大小为100M，最大为150M，自动增量为1M，物理文件放在d:\backup目录，创建好以后使之处于online状态。5使用ORACLE的SQL/PLUS工具，创建两个表(如果已经存在则覆盖掉)student和class,结构分别为(studentID varchar2(5),classID varchar2(5),memo varchar2(30)).(classID varchar2(5),classname varchar2(20),memo varchar2(50)),要求两个表之间建立主外键关系，使的当表CLASS中删除一条记录时，student表中的对应的记录被自动级联删除。

Answer 1

第二题
前段时间请教的朋友create table student
(
     id     varchar2(5) ,
    name   varchar2(20),
   salary number      ,
       meno   varchar2(50)
);
create or replace trigger mytrigger
    before insert on student
    for each row
    declare
       is_id_   varchar2(5);
       is_count number(10);
   begin 
       is_id_ := :new.id;
       select count(*) into is_count
     from   student 
     where  id = is_id_ ;
  
     if is_count > 0 then 
        raise_application_error(-20999,'The id: '||is_id_||' is already exists');
     end if;
  end;
大家看看对不对。

Answer 2

id     varchar2(5) ,
name   varchar2(20),
age number,      ,
        meno   varchar2(50)
);
create table subject
(
    studentid     varchar2(5) ,
    subjectid  varchar2(5),
   score number
);
insert into student(id,name,age,meno)vaule(001,’张三’,18,’优秀学生’);
insert into student(id,name,age,meno)vaule(002,’李四’,18,’优秀学生’);
insert into student(id,name,age,meno)vaule(003,’王五’,18,’优秀学生’);
insert into student(id,name,age,meno)vaule(004,’赵六’,18,’优秀学生’);
insert into subject(studentid,subjectid,score)vaule(001,’A1’,82);
insert into subject(studentid,subjectid,score)vaule(001,’A2’,88);
insert into subject(studentid,subjectid,score)vaule(001,’A3’,84);
insert into subject(studentid,subjectid,score)vaule(002,’A1’,92);
insert into subject(studentid,subjectid,score)vaule(002,’A2’,78);
insert into subject(studentid,subjectid,score)vaule(002,’A3’,82);
insert into subject(studentid,subjectid,score)vaule(003,’A1’,69);
insert into subject(studentid,subjectid,score)vaule(003,’A2’,73);
insert into subject(studentid,subjectid,score)vaule(003,’A3’,85);
但是下面的 建立一个视图myview，要求显示每个学生三门课程的成绩和平均成绩，每个学生显示一行，无成绩的课程显示“缺考”就不知道怎么写sql了。

Answer 3

对于“2：如果存在，则报告信息“该学生已经存在”，并在备注字段中写入“重复”字样。”
  这里值得考虑：a)id重复判断用主键约束；b)保错时，事务一般会采用回滚，直接向备注字段中写“重复”也会无效，除非用自治事务；c)从功能上说，既然阻止了重复ID，写“重复”信息也没有多大意义了。

Answer 4

to:wuchengying(吴承瑛)
   这些题目从难易度来说,个人把它定义为中下--即基础及应用的程度.附上解答:
1建立一个新用户，用户名为myguest，具有DBA的权限，然后以该用户的身份建立一个表
（MYTABLE：ID INTEGER，NAME VARCHAR2（20），MEMO VARCHAR22（100）），
然后将该用户的所有数据库对象备份到指定位置(D:\mybackup)。
最后一步是在MYTABLE表中添加一行记录，然后用刚才最的备份数据恢复到当前数据库中。解答：
  1。以SYSDBA身份登陆SQLPLUS：conn sys/xxxxx as sysdba ；
  2。create user myguest indentified by myguset ;
  3. grant dba to myguest ;
  4. conn myguest/myguest
  5. create table mytable (id INTEGER,NAME VARCHAR2(20),MEMO VARCHAR2(100);
  6. 进入CMD界面，键入命令EXP，按步骤进行EXPORT
     登陆的用户名和密码都为myguest,导出的路径为D:\mybackup\xxx.dmp；其他可以选择默认；
  7. 在刚才的SQLPLUS下执行 
     INSERT INTO MYTABEL (1,'AA','ASDASDAS');
     COMMIT;
  8. 进入CMD界面，键入命令IMP,按步骤进IMPORT
     选择导如的文件为D:\mybackup\xxx.dmp

Answer 5

2创建一个表student(id varchar2(5),name varchar2(20),salary number,meno varchar2(50)),然后创建一个触发器(如果已经存在则覆盖掉)mytrigger,要求向该表插入数据只前先判断相同id的记 录是否存在，如果存在，则报告信息“该学生已经存在”，并在备注字段中写入“重复”字样
这个你自己已经知道方法了;)
-------------------------------------------

Answer 6

3使用ORACLE的SQL/PLUS工具，创建两个表(如果已经存在则覆盖掉)student和subject,
结构分别为(id varchar2(5),name varchar2(20),age number,memo varchar2(50)).
(studentid varchar2(5),subject id varchar2(5),score number),
输入四个学生的资料(001 张三，002 李四，003 王五，004 赵六和三个学科的成绩资料
(001 A1 82，001 A2 93，001 A3 91，002 A1 79，002 A2 81，003 A1 86,003 A3 76，004 A2 58，004 A3 77),
然后建立一个视图myview，要求显示每个学生三门课程的成绩和平均成绩，每个学生显示一行，无成绩的课程显示“缺考”。解答：
  drop table student; 
  drop table subject;  
  create table student (id varchar2(5),name varchar2(20),age number,memo varchar2(50)); 
  create table subject (studentid varchar2(5),subjectid varchar2(5),score number); 
  insert into student (id,name) values ('001','张三');
  insert into student (id,name) values ('002','李四');
  insert into student (id,name) values ('003','王五');
  insert into student (id,name) values ('004','赵六');
  insert into subject (studentid,subjectid,score) values ('001','A1',82);
  insert into subject (studentid,subjectid,score) values ('001','A2',93);
  insert into subject (studentid,subjectid,score) values ('001','A3',91);
  insert into subject (studentid,subjectid,score) values ('002','A1',79);
  insert into subject (studentid,subjectid,score) values ('002','A2',81);
  insert into subject (studentid,subjectid,score) values ('003','A1',86);
  insert into subject (studentid,subjectid,score) values ('003','A3',76);
  insert into subject (studentid,subjectid,score) values ('004','A2',58);
  insert into subject (studentid,subjectid,score) values ('004','A3',77);
  commit;
  create or replace view myview
  as
  select studentid,
         decode(A1,'','缺考',A1) A1,
         decode(A2,'','缺考',A2) A2,
         decode(A3,'','缺考',A3) A3,
         AVG_SORCE
  FROM  (
         select studentid,
                SUM(decode(subjectid,'A1',SCORE)) A1,
                SUM(decode(subjectid,'A2',SCORE)) A2,
                SUM(decode(subjectid,'A3',SCORE)) A3,
                ROUND(AVG(SCORE),2) AVG_SORCE 
         FROM   SUBJECT 
         GROUP BY studentid
        )               ;------for example
ifsapp@PRACTICE>SELECT * FROM myview
  2  /STUDE A1         A2         A3          AVG_SORCE
----- ---------- ---------- ---------- ----------
001   82         93         91              88.67
002   79         81         缺考               80
003   86         缺考       76                 81
004   缺考       58         77               67.5

Answer 7

上面是视图要改变下,有点错误不好意思
create or replace view myview
  as
  select studentid,
         decode(A1,'','缺考',A1) A1,
         decode(A2,'','缺考',A2) A2,
         decode(A3,'','缺考',A3) A3,
         AVG_SORCE
  FROM  (
         select studentid,
                nvl(SUM(decode(subjectid,'A1',SCORE)),0) A1,
                nvl(SUM(decode(subjectid,'A2',SCORE)),0) A2,
                nvl(SUM(decode(subjectid,'A3',SCORE)),0) A3,
                round((nvl(SUM(decode(subjectid,'A1',SCORE)),0)+
                nvl(SUM(decode(subjectid,'A2',SCORE)),0)+
                nvl(SUM(decode(subjectid,'A3',SCORE)),0))/3,2)  AVG_SORCE 
         FROM   SUBJECT 
         GROUP BY studentid
        )               ;
----------------------------------------
ifsapp@PRACTICE>select * from myview;STUDE A1         A2         A3          AVG_SORCE
----- ---------- ---------- ---------- ----------
001   82         93         91              88.67
002   79         81         0               53.33
003   86         0          76                 54
004   0          58         77                 45

Answer 8

4为ORACLE表空间新增加一个文件，要求初始大小为100M，最大为150M，自动增量为1M，
物理文件放在d:\backup目录，创建好以后使之处于online状态。解答：
  一般使用OEM来做比较的快--界面化操作,就不多说了,如果你喜欢写命令那也是可以的.
  --for example 
  alter tablespace system 
  add datafile 'd:\backup\xxx.dbf' 
      SIZE 100M  
      AUTOEXTEND ON 
      NEXT 1OM 
      MAXSIZE 150M ;
  
  select * from v$datafile 找出新增加的DATAFILE的号X
  alter database datafile x online;

Answer 9

第三题的视图要改变下,有点错误不好意思
ifsapp@PRACTICE>create or replace view myview
  2    as
  3    select studentid,
  4           decode(A1,0,'缺考',A1) A1,
  5           decode(A2,0,'缺考',A2) A2,
  6           decode(A3,0,'缺考',A3) A3,
  7           AVG_SORCE
  8    FROM  (
  9           select studentid,
 10                  nvl(SUM(decode(subjectid,'A1',SCORE)),0) A1,
 11                  nvl(SUM(decode(subjectid,'A2',SCORE)),0) A2,
 12                  nvl(SUM(decode(subjectid,'A3',SCORE)),0) A3,
 13                  round((nvl(SUM(decode(subjectid,'A1',SCORE)),0)+
 14                  nvl(SUM(decode(subjectid,'A2',SCORE)),0)+
 15                  nvl(SUM(decode(subjectid,'A3',SCORE)),0))/3,2)  AVG_SORCE 
 16           FROM   SUBJECT 
 17           GROUP BY studentid
 18          )               ;  视图已建立。ifsapp@PRACTICE>select * from myview;STUDE A1         A2         A3          AVG_SORCE
----- ---------- ---------- ---------- ----------
001   82         93         91              88.67
002   79         81         缺考            53.33
003   86         缺考       76                 54
004   缺考       58         77                 45

Answer 10

5使用ORACLE的SQL/PLUS工具，创建两个表(如果已经存在则覆盖掉)student和class,
结构分别为(studentID varchar2(5),classID varchar2(5),memo varchar2(30)).
(classID varchar2(5),classname varchar2(20),memo varchar2(50)),
要求两个表之间建立主外键关系，使的当表CLASS中删除一条记录时，student表中的对应的记录被自动级联删除。drop table student;
drop table class ;
create table class (classID varchar2(5),classname varchar2(20),memo varchar2(50),primary key (classID));
create table student (studentID varchar2(5),classID varchar2(5),memo varchar2(30) ,
FOREIGN KEY (classID) REFERENCES class (classID),primary key (studentid,classid));对于 最后的自动级联删除我想按照我的 理解就好题目 有矛盾  
不知道其他人有 什么意见

Answer 11

5
使用ORACLE的SQL/PLUS工具，创建两个表(如果已经存在则覆盖掉)student和class,
结构分别为(studentID varchar2(5),classID varchar2(5),memo varchar2(30)).
(classID varchar2(5),classname varchar2(20),memo varchar2(50)),
要求两个表之间建立主外键关系，使的当表CLASS中删除一条记录时，student表中的对应的记录被自动级联删除。drop table student;
drop table class ;
create table class (classID varchar2(5),classname varchar2(20),memo varchar2(50),primary key (classID));
create table student (studentID varchar2(5),classID varchar2(5),memo varchar2(30) ,
FOREIGN KEY (classID) REFERENCES class (classID) on delete cascade 
,primary key (studentid,classid));上面的 那个写的不完全
级联删除在这里的语句就是on delete cascade

Answer 12

包子朋友，小弟佩服了，因为小弟学oracle没多久，感觉还是有点难度的。
比较简单的觉得问题不大，但这里有的问题是需要转个弯的我就不熟悉了。
你的解答我会一个一个仔细去看的，有不明白的再来请教，再次感谢你了。