Oracle 数据库上机试验指导2(答案)

实验二代码参考：

1.-- 创建学生表

create table student

(

studid char(6) primary key,

studname varchar2(16) not null,

gender char(4) check(gender in('男','女')),

addr varchar2(256)

);

-- 创建课程表

create table course

(

courseid char(6) primary key,

coursename varchar2(32) not null unique,

teacher varchar2(16) ,

score number check(score in (1,3,5))

);

-- 创建选课表

create table courSelc

(

recid number(4) primary key,

studid char(6),

courseid char(6),

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

constraint fk_stud foreign key(studid) references student(studid),

constraint fk_cour foreign key(courseid) references course(courseid) );

2. alter table student modify studName null;

3.1 alter table course modify courseName null;

3.2 alter table course add examdate date;

4.insert into course values('M1001','数学','奥巴马','3',to_date('2012-1-

1','yyyy-mm-dd'));

insert into course values('J2002','军事理论','拿破仑','5',to_date('2012-1-1','yyyy-mm-dd'));

insert into course values('S3003','摔跤','普京','1',to_date('2012-1-1','yyyy-mm-dd'));

update course set examdate =to_date('2012-1-1','yyyy-mm-dd') where courseid='M1001';

update course set examdate =to_date('2012-1-1','yyyy-mm-dd') where courseid='J2002';

update course set examdate =to_date('2012-1-1','yyyy-mm-dd') where courseid='S3003';

5. select * from course;

6.修改参数: nls_date_format

alter session set nls_date_format='yyyy-mm-dd';

运行select * from course;查看查询结果。

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

运行select * from course;查看查询结果。

nls_date_format参数对数据显示的影响。

7.1.1 select * from student where gender='男';

1.2 select * from student where gender='女';

7.2 select * from student where studname=null;

7.3 select studname 姓名,studid 学号,gender 性别 from student;

7.4 select * from course where examdate between to_date('2012-3-1','yyyy-mm-dd') and to_date('2012-6-1','yyyy-mm-dd');

7.5 select s.studid,s.studname,c.coursename,cs.score from student s,course c,courSelc cs where s.studid=cs.studid and c.courseid=cs.courseid and

c.coursename='数学';

7.6 select count(*) from courSelc cs join course c on c.courseid=cs.courseid where c.coursename='数学';

7.7 select avg(cs.score) from courSelc cs join course c on

c.courseid=cs.courseid where c.coursename='数学';