1. 简述Oracle数据库中实例和数据库的概念以及它们之间的关系。
实例是一个非固定的,基于内存的基本进程与内存结构。当服务器关闭后,实例也就不存在了,数据库指的是固定的,基于磁盘的数据文件和控制文件及日志文件、参数文件、归档日志文件等。一般情况下按装的oracle数据库都是一个数据库中 包含一个实例。当然也可以在一个数据库创建多个实例
2. 描述你所了解的数据库对象。
Table(表)用于存放需要化持久的记录,function(函数)用pl/sql语言编写的能完成特定功能的程序块,并能把处理结果返回。Procedure(存储过程)与函数类似,只是不能有返回值。View(视图),tigger(触发器)。
3. 写出你所知道的所有Oracle中的函数。并描述其参数含义。
Sum(col) col为列的名字,substr(col,l1,l2)col列的名字,l1截取字符串的开始位置,
L2截取字符串的结束位置。。
4. 如果Oracle数据库安装在c:\oracle目录下,请写出创建表空间bluedot的命令,并将bluedot表空间所对应的数据文件指定到oracle下默认的数据文件所在目录中。数据文件的名字为bluedot.dbf
create tablespace bluedot datafile ‘c:\oralce\oradata\XE\bluedot.dbf’ size 5m autoextend on next 3m;
5. 现要完成一个学生选课系统,学生可以选择多门课程,每门课程可以被多个学生选择。请根据这个需求画出概念模型,描述实体之间的关系。
6. 请根据上面的概念模型,创建关系模型(数据库表),写出创建表的SQL语句(学生表的名字为S、课程表的名字为C、选课表的名字为SC)。假如,学生具有学号和名字两个属性。课程具有课程号、课程名和学分三个属性。请合理的选择表内的主键和外键,以及是否为空的属性。要求创建一个用户自定义的完整性,就是课程的成绩必须在0到100之间。
create table S(s_id number(10),s_name char(10),entrance_time date, primary
key(s_id));
create table C(c_id number(10),c_name varchar2(40) not null , primary key(c_id));
create table SC(s_id number(10),c_id number(10),result number(4,1) check(result<=100 and result>=0),
primary key(s_id,c_id));
alter table SC
add constraint sfk foreign key (S_ID)
references s (S_ID) on delete cascade;
alter table SC add constraint cfk foreign key(c_id)
references c(c_id) on delete cascade;
7. 根据以上的表结构完成如下的SQL语句:
a) 列出每个学生的姓名、所选的课程和成绩。
select s.s_name,c.c_name ,sc.result from s,sc,c where s.s_id=sc.s_id and sc.c_id=c.c_id;
b) 列出每个学生的学号以及所选课程的总成绩。
select s_id ,sum(result) from sc group by s_id;
c) 查询所有选修了Java课程的学生姓名和成绩,并按成绩降序排列结果。
SQL> select s_name,c_name,result from s,sc,c where s.s_id=sc.s_id and sc.c_id=c.c_id and c.c_name='java';order by result desc
d) 请写出学生表S和成绩表SC表的左连接、右连接和全链接的SQL语句。并写出查询出来的数据示例数据。
select * from s left join sc on s.s_id=sc.s_id;
select * from s right join sc on s.s_id=sc.s_id;
select * from s full join sc on s.s_id=sc.s_id;
8. 如果在学生表中添加一个学生所在系的字段,请创建一个视图用来查询计算机学生的学号、姓名、所选课程以及成绩。
alter table S add department varchar2(30) default 'computer';
select s.s_id, s.s_name,c.c_name,result from s,sc,c where s.s_id=sc.s_id
and sc.c_id=c.c_id and s.department='computer';
9. 如果在学生表中含有一个Date类型的字段表示学生的入学时间,请编写一个函数GET_STU_DATE,入参为学生的学号,返回该学生入学后3个月后的第一个星期一的日期,并以字符型返回,要求返回的字符型日期格式为1998-12-21 18:56:32 AM。
核心代码:
TO_CHAR(TO_DATE(next_day(trunc(add_months(ENTRANCE_TIME,3),'month'),2)
),'YYYY-MM-DD HH24:MI:SS AM')
10. 请编写一个存储过程完成学生转学的功能。要求存储过程的入参为学生的学号和out类型的出参。out类型的出参为400表示转学成功,为500表示存储过程执行有异常,如果为300表示给定学号的学生不存在。
create or replace procedure changeschool(id in number ,rs out number)
is
cursor sc is select * from s where s.s_id=id;
srecord s%rowtype;
begin
open sc;
fetch sc into srecord;
if (sc%found) then
insert into ss(s_id,s_name,entrance_time,department)
values(srecord.s_id,srecord.s_name,srecord.entrance_time,srecord.department);
delete from s where s.s_id=id;
close sc;
commit;
rs:=400;
else
rs:=300;
end if;
exception when others then
rollback;
rs:=500;
end changeschool;
11. 请创建一个序列用来生成学生的学号。学号要求在400-999999之间,序列不需要缓存不需要循环利用。
create sequence S_SEQ
minvalue 400
maxvalue 99999
start with 400
increment by 1
nocache;
12. 为了增加安全性,当有数据库用户修改学生成绩的时候,需要在日志表SC_LOG中记录用户的名字,修改时间以及所修改的学生的学号以及修改之前的学生的成绩。请为这个需求编写一个触发器完成这个功能。
Create table sc_log (id number(20),s_id number(10),oldresult number(4,1),createtime
Date default sysdate);
create sequence sclog_seq start with 1;
create or replace trigger stigger
before update on sc
for each row
declare
begin
if updating then
insert into sc_log(id,s_id,oldresult) values(sclog_seq.nextval,:old.s_id,:old.result);
end if;
end stigger;