Oracle, 笔试, 面试
完成下列操作，写出相应的SQL语句
1.创建表空间neuspace，数据文件命名为neudata.dbf，存放在d:\\data目录下，文件大小为200MB，设为自动增长，增量5MB，文件最大为500MB。(8分)
答：create tablespace neuspace datafile ‘d:\\data\\neudata.dbf’ size 200m auto extend on next 5m maxsize 500m;
2. 假设表空间neuspace已用尽500MB空间，现要求增加一个数据文件，存放在e:\\appdata目录下，文件名为appneudata，大小为500MB，不自动增长。（5分）
答：alter tablespace neuspace add datafile ‘e:\\appdata\\appneudata.dbf’ size 500m;
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 | 日期 | 无 | 无 | 生日 |
| 变长字符 (20) | 无 | 唯一 | 电子邮件 | |
| SCORE | 数值 (5, 2) | 无 | 检查 | 成绩 |
| CLASSNO | 数值 (2) | 无 | 外键，关联到表CLASS的CLASSNO主键 | 班级编号 |
(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>=0 and 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 by 1 start with 20050001 maxvalue 20059999 nocache nocycle;
7. 向表student中插入如下2行。（5分）
| STUNO | SNAME | SEX | BIRTHDAY | SCORE | CLASSNO | |
| 从stuseq取值 | tom | 男 | 1979-2-3 14:30:25 | tom@163.net | .50 | 1 |
| 从stuseq取值 | jerry | 默认值 | 空 | 空 | 空 | 2 |
14:30:25’, ’yyyy-mm-dd fmhh24:mi:ss’), ’tom@163.net
| ’, .50, 1); insert into student (stuno, sname, classno) values(stuseq.nextval, ’jerry’, 2); 8. 修改表student的数据，将所有一班的学生成绩加10分。（4分） 答：update student set score=score+10 where classno=1; 9. 删除表student的数据，将所有3班出生日期小于1981年5月12日的记录删除。（4分） 答：delete from student where classno=3 and 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=2 and score>85.50 and birthday < ’31-10月-82’ and sex=’男’; (3) 查询student表中所有三班成绩为空的学生记录。 答：select * from student where classno=3 and score is null; (4) 表student与class联合查询，要求查询所有学生的学号，姓名，成绩，班级名称。（使用oracle与SQL 99两种格式） 答：select s.stuno, s.sname, s.score, c.cname 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=1 and score > (select avg(score) from student where classno=1); (7) 统计二班学生中所有成绩大于所有班级平均分的人数。 答：select count(*) from student where classno=2 and score > all (select avg(socre) from student group by classno); (8) 查询平均分最高的班级编号与分数。 答：select classno, avg(score) from student group by classno having avg(score) = (select max(avg(score)) from student group by classno); (9) 查询所有学生记录中成绩前十名的学生的学号、姓名、成绩、班级编号。 答：select stuno, sname, score, classno from (select * from student order by score desc) where rownum<=10; (10) 创建视图stuvu，要求视图中包含student表中所有一班学生的stuno, sname, score, classno四个属性，并具有with check option。 答：create view stuvu as select stuno, sname,score,classno from student where classno=1 with check option; |
Copyright © 2019-2025 51dongshi.net 版权所有
违法及侵权请联系：TEL:177 7030 7066 E-MAIL:11247931@qq.com 本站由北京市万商天勤律师事务所王兴未律师提供法律服务
