内测题目
1. 以system 用户登录oracle数据库，写出登录sqlplus的命令
Conn system/admin
2. 先创建一个表空间，名字自定义，如ts1
1.
create tablespace tablespace1
datafile 'D:\app\Administrator\oradata\orcl\tablespace1.dbf'
size 100m
autoextend on next 32m maxsize unlimited
logging
extent management local
segment space management auto;
2.
create tablespace tablespace2
datafile 'D:\app\Administrator\oradata\orcl\tablespace2.dbf'
size 100m;
3. 创建一个用户，名字自定义，如u1 ，u1的default tablespace 使用第2步创建的表空间，temporary tablespace 采用系统自带的temp 临时表空间;
--创建用户
create user user1
identified by u
default tablespace tablespace1
4. 赋予用户DBA角色
--赋予用户权限
grant dba to user1;
--撤销权限
revoke dba from user1;
5. 以第3步创建用户登录oracle数据库
Conn user1/u as sysdba
6. 创建如下表并初始化数据
学生表：
学生编号 数字类型 主键
学生姓名 字符类型
科目表
科目编号 数字类型 主键
科目名称姓名 字符类型
成绩表
成绩编号 数字类型 主键
成绩分数 数字类型，小数点保留1位
学生编号 数字类型 外键
科目编号 数字类型 外键
--1.学生表
create table student(
stuID number(5) primary key,
stuName varchar2(50)
);
create table subject(
subID number(5) primary key,
subName varchar2(50)
);
create table score(
scoreID number(5) primary key,
score number(4,1),
stuID number(5) ,
subID number(5)
);
alter table score
add constraint FK_score_stuID foreign key (stuID) references student(stuID);
alter table score
add constraint FK_score_subID foreign key (subID) references subject(subID);
7创建序列
--创建序列
create sequence seq_student
start with 1
increment by 1
nomaxvalue
cache 20;
create sequence seq_student
start with 1
increment by 1;
create sequence seq_subject
start with 1
increment by 1
nomaxvalue
cache 20;
create sequence seq_subject
start with 1
increment by 1
create sequence seq_score
start with 1
increment by 1
nomaxvalue
cache 20;
create sequence seq_score
start with 1
increment by 1
8针对学生表创建一个before insert 触发器，利用第7步创建的序列，实现对学生表主键的自动增长列
--创建触发器
create or replace trigger tr_indetity_student
before insert on student
for each row
begin
select seq_student.nextval into :new.stuID from dual;
end;
/
create or replace trigger tr_indentity_subject
before insert on subject
for each row
begin
select seq_subject.nextval into :new.subID from dual;
end;
/
create or replace trigger tr_identity_score1
before insert on score
for each row
begin
select seq_score.nextval into :new.scoreID from dual;
end;
/
insert into student(stuName) values('张三1');
insert into student(stuName) values('张三2');
insert into student(stuName) values('张三3');
insert into subject(subName)values('语文');
insert into subject(subName)values('数学');
insert into subject(subName)values('英语');
insert into score(score,stuID,subID)values(91,1,1);
insert into score(score,stuID,subID)values(81,2,1);
insert into score(score,stuID,subID)values(99,3,1);
insert into score(score,stuID,subID)values(98,1,2);
insert into score(score,stuID,subID)values(96,2,2);
insert into score(score,stuID,subID)values(93,3,2);
insert into score(score,stuID,subID)values(98,1,3);
insert into score(score,stuID,subID)values(96,2,3);
insert into score(score,stuID,subID)values(93,3,3);
9 写一个存储过程，用游标循环每一个学生，每一次循环统计每一学生各个科目的成绩，打印到控制到输出如下
姓名 数学 语文 英语 总分
张三 90 56 88 234
李四 91 66 98 255
.............. .............. ..............
.............. .............. ..............
.............. .............. ..............
create or replace procedure show_student_info
as
begin
declare
cursor stu_cursor is
select stu.stuName 姓名,
sum(case sub.subName when '语文' then sco.score end) as 语文,
sum(case sub.subName when '数学' then sco.score end) as 数学,
sum(case sub.subName when '英语' then sco.score end) as 英语,
sum(case sub.subName when '语文' then sco.score end)+sum(case sub.subName when '数学' then sco.score end)+sum(case sub.subName when '英语' then sco.score end) as 总分
from student stu, subject sub, score sco where stu.stuID = sco.stuID and sub.subID = sco.subID group by stu.stuName; begin dbms_output.put_line('姓名 语文 数学 英语 总分');
for i in stu_cursor loop
dbms_output.put_line(i.姓名||' '||i.语文||' '||i.数学||' '||i.英语||' '||i.总分);
end loop;
end;
end;
/
-- 简化版
begin
dbms_output.put_line('姓名 语文 数学 英语 总分');
for ssr in
(select b.sname 姓名,
sum(case c.subname when '语文' then a.resnum end) 语文,
sum(case c.subname when '数学' then a.resnum end) 数学,
sum(case c.subname when '英语' then a.resnum end) 英语,
sum(case c.subname when '语文' then a.resnum end) +
sum(case c.subname when '数学' then a.resnum end) +
sum(case c.subname when '英语' then a.resnum end) as 总分
from result a,student b,subject c
where a.sid= b.sid and a.subid= c.subid
group by b.sname)
loop
dbms_output.put_line(ssr.姓名||' '||ssr.语文||' '||ssr.数学||' '||ssr.英语||' '||ssr.总分);
end loop;
end;
10 针对第9题sql语句的查询条件创建index，以优化查询速度
create index stu_index on student(stuName);