华南理工大学 数据库实验一 代码

华南理工大学 数据库实验一 代码

实验目的：
通过交互式SQL的使用， 掌握数据库的创建、插入、更新、查询等基本操作
实验要求：
1 创建Student数据库（oracle可以不创建新的数据库，直接创建下列表就可以了）
包括Students,Courses,SC表，表结构如下：
Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)
Courses(CNO,CNAME,LHOUR,CREDIT,SEMESTER)
SC(SNO,CNO,GRADE)
(注：下划线表示主键，斜体表示外键)，并插入一定数据。
2 完成如下的查询要求及更新的要求。
（1）查询身高大于1.80m的男生的学号和姓名；
（2）查询计算机系秋季所开课程的课程号和学分数；
（3）查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩；
（4）查询至少选修一门电机系课程的女生的姓名（假设电机系课程的课程号以EE开头）；
（5）查询每位学生已选修课程的门数和总平均成绩；
（6）查询每门课程选课的学生人数,最高成绩,最低成绩和平均成绩；
（7）查询所有课程的成绩都在80分以上的学生的姓名、学号、且按学号升序排列；
（8）查询缺成绩的学生的姓名，缺成绩的课程号及其学分数；
（9）查询有一门以上(含一门)三个学分以上课程的成绩低于70分的学生的姓名；
（10）查询1984年~1986年出生的学生的姓名,总平均成绩及已修学分数。
（11） 在STUDENT和SC关系中，删去SNO以’01’开关的所有记录。
（12）在ＳＴＵＤＥＮＴ关系中增加以下记录：
<0409101 何平　　女　　1987-03-02　　1.62>
<0408130 向阳　　男　　1986-12-11　　1.75>
（13）将课程CS-221的学分数增为３，讲课时数增为６０
3．补充题：
(1) 统计各系的男生和女生的人数。
(2) 列出学习过‘编译原理’，‘数据库’或‘体系结构’课程，且这些课程的成绩之一在90分以上的学生的名字。
(3) 列出未修选‘电子技术’课程，但选修了‘数字电路’或‘数字逻辑’课程的学生数。
(4) 按课程排序列出所有学生的成绩，尚无学生选修的课程，也需要列出，相关的学生成绩用NULL表示。
(5) 列出平均成绩最高的学生名字和成绩。(SELECT句中不得使用TOP n子句)
4．选做题：
对每门课增加“先修课程”的属性，用来表示某一门课程的先修课程，每门课程应可记录多于一门的先修课程。要求：

1. 修改表结构的定义，应尽量避免数据冗余，建立必要的主键，外键。
2. 设计并插入必要的测试数据，完成以下查询：
   列出有资格选修数据库课程的所有学生。(该学生已经选修过数据库课程的所有先修课，并达到合格成绩。)
   注意：须设计每个查询的测试数据，并在查询之前用INSERT语句插入表中。

（一）必做题代码
1．
select sno,sname from students where ssex=‘男’ and height>1.80;
2．
select cno,cname from courses where cno in
(select cno from sc,students where sc.sno=students.sno and students.department=‘计算机系’);
3．
select students.sname,sc.cno,courses.credit,sc.grade from sc,students,courses
where sc.sno=students.sno and courses.cno=sc.cno
and students.department=‘计算机系’ and courses.semester=‘秋’
and students.ssex=‘男’;
4．
select students.sname from students,sc
where students.ssex=‘女’ and students.sno=sc.sno
and sc.cno like’EE%’;
5．
select sname,count(cno),avg(grade) from sc,students
where sc.sno=students.sno group by sname;
6．
select cname,count(sno),max(grade),min(grade),avg(grade)
from sc,courses where sc.cno=courses.cno
group by cname;
7．
select distinct sname,sc.sno from students,sc
where sc.sno=students.sno
and sc.grade>80
order by sc.sno asc;
8．
select sname,courses.cno,credit from students,courses,sc
where sc.cno=courses.cno and sc.sno=students.sno
and grade is null;
9．
select distinct sname from students,courses,sc
where sc.cno=courses.cno and sc.sno=students.sno
and courses.credit>=3 and grade<70;
10．
select sname,avg(grade),sum(credit)
from students natural join sc students natural join courses
where bdate>=to_date(‘1984-01-01’,‘yyyy-mm-dd’) and bdate<=to_date(‘1986-12-31’,‘yyyy-mm-dd’)
group by sname;
11．
delete from students
where sno like ‘01%’;
delete from sc
where sno like ‘01%’;
12．
insert into students(sno,sname,ssex,bdate,height)
values(‘0409101’,‘何平’,‘女’,to_date(‘1987-03-02’,‘yyyy-mm-dd’),1.62);
insert into students(sno,sname,ssex,bdate,height)
values(‘0408130’,‘向阳’,‘男’,to_date(‘1986-12-11’,‘yyyy-mm-dd’),1.75);
13．
update courses
set credit=3,lhour=60
where cno=‘CS-221’;
（二）补充题代码
1．
select department,
sum(case when ssex=‘男’ then 1 else 0 end),
sum(case when ssex=‘女’ then 1 else 0 end),count(sno) 总人数
from students group by department;
2．
select sname from students natural join sc natural join courses
where cname=‘编译原理’ or cname=‘数据库’ or cname=‘体系结构’ and grade>90;
3．
select count(distinct sc.sno)
from courses,sc
where sc.sno not in
(select sc.sno from courses,sc
where courses.cno=sc.cno and cname=‘电子技术’)
and courses.cno=sc.cno
and sc.cno in
(select cno from courses
where cname=‘数字逻辑’ or cname=‘数字电路’);
4．
select distinct courses.cno,courses.cname,sno,grade
from courses left join sc on (courses.cno=sc.cno)
group by courses.cno,courses.cname,sno,grade;
5．
select sname,r from
(select sname,avg(grade) r
from students,sc
where students.sno=sc.sno
group by sname,students.sno
order by r desc)
where rownum=1;