一套Oracle面试题笔试题及参考答案

_一套Oracle面试题笔试题及参考答案

完成下列操作，写出相应的SQL语句

创建表空间neuspace，数据文件命名为neudata.dbf，存放在d:\data 目录下，文件大小为200MB，设为自动增长，增量5MB，文件最大为500MB。(8分)

答：create tablespace neuspace datafile‘d:\data\neudata.dbf’size200m auto extend on next5m maxsize500m;

2.假设表空间neuspace已用尽500MB空间，现要求增加一个数据文件，存放在e:\appdata目录下，文件名为appneudata，大小为500MB，不自动增长。（5分）

答：alter tablespace neuspace add datafile ‘e:\appdata\appneudata.dbf’size500m;

3.以系统管理员身份登录，创建账号tom，设置tom的默认表空间为neuspace。为tom分配connect和resource系统角色，获取基本的系统权限。然后为tom分配对用户scott的表emp的select权限和对SALARY,MGR属性的update权限。(8分)

答：create user tom identified by jack default tablespace neuspace; Grant connect,resource to tom;

Grant select,update(salary,mgr)on scott.emp to tom;

4.按如下要求创建表class和student。（15分）

属性

类型(长度)

默认值

约束

含义

CLASSNO数值(2)无主键班级编号

CNAME变长字符(10)无非空班级名称

属性

类型(长度)

默认值

约束

含义

STUNO数值(8)无主键学号

SNAME变长字符(12)无非空姓名

SEX字符(2)男无性别

BIRTHDAY日期无无生日

EMAIL变长字符(20)无唯一电子邮件

SCORE数值(5,2)无检查成绩

CLASSNO数值(2)无外键，关联到表CLASS的CLASSNO主键班级编号

答：create table class

(classno number(2)constraint class_classno_pk primary key, cname varchar2(10)not null);

create table student

(stuno number(8)constraint student_stuno_pk primary key, sname varchar2(12)not null,

sex char(2)default‘男’,

birthday date,

email varchar2(20)constraint student_email_uk unique,

score number(5,2)constraint student_score_ck check(score>=0and score<=100),

classno number(2)constraint student_classno_fk references class(classno)

);

5.在表student的SNAME属性上创建索引student_sname_idx（5分）答：create index student_sname_idx on student(sname);

6.创建序列stuseq，要求初值为20050001，增量为1，最大值为20059999。（6分）

答：create sequence stuseq increment by1start with20050001 maxvalue20059999nocache nocycle;

7.向表student中插入如下2行。（5分）

STUNO SNAME SEX BIRTHDAY EMAIL SCORE CLASSNO

从stuseq取值tom男1979-2-314:30:25tom@89.501从stuseq取值jerry默认值空空空2

答：insert into student values(stuseq.nextval,’tom’,’男’, to_date(‘1979-2-3

14:30:25’,’yyyy-mm-dd fmhh24:mi:ss’),’tom@’, 89.50,1);

insert into student(stuno,sname,classno) values(stuseq.nextval,’jerry’,2);

8.修改表student的数据，将所有一班的学生成绩加10分。（4分

）

答：update student set score=score+10where classno=1;

9.删除表student的数据，将所有3班出生日期小于1981年5月12日的记录删除。（4分）

答：delete from student where classno=3and birthday>’12-5月-81’;

10.完成以下SQL语句。(40分)

(1)按班级升序排序，成绩降序排序，查询student表的所有记录。

答：select*from student order by classno,score desc;

(2)查询student表中所有二班的成绩大于85.50分且出生日期大于1982-10-31日的男生的记录。

答：select*from student where classno=2and score>85.50and birthday<’31-10月-82’and sex=’男’;

(3)查询student表中所有三班成绩为空的学生记录。

答：select*from student where classno=3and score is null; (4)表student与class联合查询，要求查询所有学生的学号，姓名，成绩，班级名称。（使用oracle与SQL99两种格式）

答：select s.stuno,s.sname,s.score,ame from student s,class

c where s.classno=c.classno;

(5)按班级编号分组统计每个班的人数，最高分，最低分，平均分，并按平均分降序排序。

答：select classno,count(*),max(score),min(score),avg(score) from student group by classno order by avg(score)desc;

(6)查询一班学生记录中所有成绩高于本班学生平均分的记录。

答：select*from student where classno=1and score>(select avg(score)from student where classno=1);

(7)统计二班学生中所有成绩大于所有班级平均分的人数。

答：select count(*)from student where classno=2and score>all (select avg(socre)from student group by classno);

(8)查询平均分最高的班级编号与分数。