数据库实验（一）
熟悉环境、建立/删除表、插入数据
Drop table 表名
update dbtest set test=1 select * from dbscore
1． 教师信息(教师编号、姓名、性别、年龄、院系名称)
test1_teacher：tid char 6 not null、name varchar 10 not null、sex char 2、age int、dname varchar 10。 根据教师名称建立一个索引。
教师编号 100101 100102 100103 教师姓名 张老师 李老师 马老师 性别 男 女 男 年龄 44 45 46 院系名称 计算机学院 软件学院 计算机学院
1、create table test1_teacher(
tid char(6) primary key, name varchar(10) not null, sex char(2), age int,
dname varchar(10) )
2． 学生信息（学生编号、姓名、性别、年龄、出生日期、院系名称、班级）
test1_student：sid char 12 not null、name varchar 10 not null、sex char 2、age int、birthday date（oracle的date类型是包含时间信息的，时间信息全部为零）、dname varchar 10、class varchar(10)。 根据姓名建立一个索引。 学号 200800020101 200800020102 200800020103 姓名 王欣 李华 赵岩 性别 女 女 男 年龄 １９ ２０ １８ 出生日期 院系名称 班级 2010 2009 2009 1994-2-2 计算机学院 1995-3-3 1996-4-4 软件学院 软件学院 2、create table test1_student(
sid char(12) primary key, name varchar(10) not null, sex char(2), age int,
birthday date,
dname varchar(10), class varchar(10) )
3． 课程信息（课程编号、课程名称、先行课编号、学分）
test1_course：cid char 6 not null、name varchar 10 not null、fcid char 6、 credit numeric 2，1（其中2代表总长度，1代表小数点后面长度）。 根据课程名建立一个索引。
课程号 300001 300002 300003 课程名 数据结构 数据库 操作系统 先行课程号 300001 300001 学分 2 2.5 4
3、create table test1_course(
cid char(6) primary key, name varchar(10) not null, fcid char(6),
credit numeric(2,1) )
4． 学生选课信息（学号、课程号、成绩、教师编号）
test1_student_course：sid char 12 not null、cid char 6 not null、 score numeric 5，1（其中5代表总长度，1代表小数点后面长度）、tid char 6。
学号 200800020101 200800020101 200800020101
课程号 300001 300002 300003 成绩 91.5 92.6 93.7 教师编号 100101 100102 100103 4、 create table test1_student_course( sid char(12) , cid char(6) ,
score numeric(5,1), tid char(6),
primary key(sid,cid),
FOREIGN KEY (sid) REFERENCES test1_student(sid), FOREIGN KEY (cid) REFERENCES test1_course(cid), FOREIGN KEY (tid) REFERENCES test1_teacher(tid) )
5． 教师授课信息（教师编号、课程编号）
test1_teacher_course：tid char 6 not null，cid char 6 not null。
教师编号 100101 100102 100103 课程号 300001 300002 300003
5、create table test1_teacher_course( tid char(6) , cid char(6) ,
primary key(tid,cid),
FOREIGN KEY (tid) REFERENCES test1_teacher(tid), FOREIGN KEY (cid) REFERENCES test1_course(cid) )
二、创建索引
1、create index index_table1 on test1_teacher(name); 2、create index index_table2 on test1_student(name); 3、create index index_table3 on test1_course(name);
三、插入数据 1、
insert into test1_teacher values('100101','张老师','男',44,'计算机学院');
insert into test1_teacher values('100102','李老师','女',45,'软件学院');
insert into test1_teacher values('100103','马老师','男',46,'计算机学院'); 2、
insert into test1_student values('200800020101','王欣','女
',19,to_date('19940202','yyyymmdd'),'计算机学院','2010');
insert into test1_student values('200800020102','李华','女',20,to_date('19950303','yyyymmdd'),'软件学院','2009');
insert into test1_student values('200800020103','赵岩','男',18,to_date('19960404','yyyymmdd'),'软件学院','2009'); 3、
insert into test1_course values('300001','数据结构','',2);
insert into test1_course values('300002','数据库','300001',2.5); insert into test1_course values('300003','操作系统','300001',4); 4、
Insert into test1_student_course
values('200800020101','300001',91.5,'100101');
insert into test1_student_course
values('200800020101','300002',92.6,'100102');
insert into test1_student_course
values('200800020101','300003',93.7,'100103'); 5、
insert into test1_teacher_course values ('100101','300001');
insert into test1_teacher_course values ('100102','300002');
insert into test1_teacher_course values ('100103','300003');
数据库实验（二） 检索查询
1、找出没有选修任何课程的学生的学号、姓名。
create table test2_01 as select sid ，name from pub.student where sid not in (select sid
from pub.student_course)
2、找出至少选修了学号为“200900130417”的学生所选修的一门课的学生的学
号、姓名。
create table test2_02 as select distinct student.sid，name from pub.student, pub.student_course
where student_course.sid = student.sid and student_course.cid in (select cid
from pub.student_course where sid='200900130417') 3、找出至少选修了一门其先行课程号为“300002”号课程的学生的学号、姓名。 create table test2_03 as select distinct student.sid，name from pub.student, pub.student_course
where student_course.sid = student.sid and student_course.cid in (select cid
from pub.course
where fcid='300002')
4、找出选修了“操作系统”并且也选修了“数据结构”的学生的学号、姓名。 create table test2_04 as select sid,name from pub.student where sid in (select sid
from pub.student_course,pub.course where student_course.cid=course.cid and name ='操作系统') and sid in (select sid
from pub.student_course,pub.course where student_course.cid=course.cid and name ='数据结构')
5、查询20岁的所有有选课的学生的学号、姓名、平均成绩(avg_score，此为列名，下同)（平均成绩四舍五入到个位）、总成绩(sum_score)
create table test2_05 as select student.sid,name，cast(avg(score) as numeric(5,0)) avg_score,sum（score） sum_score from pub.student,pub.student_course
where student.sid = student_course.sidand age ='20' group by student.sid，name
6、查询所有课以及这门课的最高成绩，test2_06有两个列：课程号cid、最高