数据库系统原理——数据库定义与操作语言（一）

数据库定义与操作语言

一、ScoreDB中各个表

二、实验目的

理解和掌握数据库SQL DDL语句的语法；
掌握SQL 程序设计基本规范，熟练运用SQL语言实现数据基本查询语句、嵌套查询等；
熟悉数据库的数据更新操作；
熟练SQL有关视图的操作。

三、实验内容

导入ScoreDB数据库,按照“实验2数据”，修改四张表内各个字段的类型以及宽度。完成后，实现以下操作：
2.1数据查询

1. 查询选修了“数据结构”课程的同学姓名。

SELECT StudentName
FROM Student,Course,Score
WHERE Student.studentNo=Score.studentNo AND Course.courseNo=Score.courseNo AND courseName='数据结构'

2. 查询选修了课程号为’001’或’002’课程的学生学号、课程号和分数。

SELECT studentNo,courseNo,score
FROM Score
WHERE courseNo='001' OR courseNo='002'

3. 查询学过课程号为’001’但是没有学过课程号为’002’的课程的学生学号、课程号和分数。
   ①

select studentNo,courseNo,score
from Score S1
where  courseNo!='002' and exists(select  * from Score as S2 where courseNo='001')

②

select studentNo, courseNo, score
from Score
where studentNo in (select studentNo from score where courseNo='001') and studentNo not
in (select studentNo from score where courseNo='002')

4.查询至少有一门课与学号为’0700001’的学生所学相同的学生的学号和姓名。
①

select distinct studentName,Student.studentNo
from Student,Score
where Student.studentNo=Score.studentNo and courseNo in(select courseNo from Score where studentNo='0700001')

②

select distinct a. studentNo, studentName
from Student a， score b
where a. studentNo=b. studentNo and courseNo in (select courseNo from score 
where studentNo ='0700001' ) and a. studentNo<>'0700001'

5.查询至少选修了学号为“0700001”学生所选修的所有课程的学生姓名。

select distinct studentName
from Student,Score x
where Student.studentNo=x.studentNo and not exists(select* from Score y where y.studentNo='0700001' and not exists(select* from Score z where z.studentNo=x.studentNo and z.courseNo=y.courseNo))

ps：
如果只需要查询学号可以用如下代码

select distinct studentNo
from Score x
where   not exists(select* from Score y where y.studentNo='0700001' and not exists(select* from Score z where z.studentNo=x.studentNo and z.courseNo=y.courseNo))

6.查询本月过生日的学生信息。

select *
from Student
where MONTH(birthday)=MONTH(getdate())

7.查询没有成绩的学生信息。（使用IN语句）

select *
from  Student
where studentNo in(select studentNo from Score where score is null)

8.查询（没有）选修过课程的学生姓名。（使用IN语句）

select studentName,studentNo
from Student
where studentNo not in(select studentNo from Score )

9.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩。
①

select Student.studentNo,studentName,S.avg_score
from Student,(select Score.studentNo,avg(score) avg_score from Score where score<60 group by Score.studentNo having count(*)>=2) as S
where S.studentNo=Student.studentNo

②

select a.studentNo, studentName, avg (score) avg_ score
from score a, student b
where a.studentNo in (select studentNo from score where score<60
group by studentNo
having count(*)>=2) and a. studentNo=b. studentNo
group by a. studentNo, studentName

10.查找选修过课程名中包含“系统”的课程的同学学号、姓名和所在班级。

select Student.studentNo,studentName,classNo
from Student,Score,Course
where Student.studentNo=Score.studentNo and Score.courseNo=Course.courseNo and courseName like'%系统%'

11.查找同时选修过“高等数学”和“离散数学”两门课程的同学
学号、姓名以及该同学所选修的所有课程的课程名和相应成绩，按学号(升序)、成绩(降序)排序输出。
①

select Student.studentNo,studentName,courseName,score
from Student,Score,Course
where Student.studentNo=Score.studentNo  and Score.courseNo=Course.courseNo and Student.studentNo IN(SELECT Score.studentNo from Score,Course
where courseName='高等数学'and Course.courseNo=Score.courseNo and studentNo in(select Score.studentNo from Score,Course where courseName='离散数学'
and Course.courseNo=Score.courseNo))
order by Student.studentNo ASC,score DESC

②

select s. studentNo, studentName, courseName, score
from Student s, Course c, Score sc
where s. studentNo =sc. studentNo and c. courseNo=sc. courseNo
and s. studentNo in (select studentNo
from Score
where courseNo in (select courseNo from Course where courseName ='
高等数学'))
and s. studentNo in(select studentNo
from Score
where courseNo in (select courseNo from Course where courseName='离
散数学'))
order by studentNo asc，score desc

12.查询所有学生的选修信息，显示信息包括学号，选修门数，平均分和总分。

select studentNo,COUNT(courseNo) cnt ,avg(score) avg_s,sum(score) sum_s
from Score
group by(studentNo)

13.查询先修课是“计算机基础”的课程。（分别用自连接和IN语句实现）
①自连接

select C1.courseNo
from Course C1,Course C2
where C1.priorCourse=C2.courseNo and C2.courseName='计算机基础'

②IN语句
方法1：

select courseNo
from (select C1.courseNo
from Course C1,Course C2
where C1.priorCourse=C2.courseNo and C2.courseName='计算机基础') as C

方法2：

select courseNo
from Course
where priorCourse in (select courseNo from Course where courseName='计算机基础')

14.查询至少有一门课程成绩超过85分的所有学生的信息，显示信息包括学号、课程号和分数。
①

select Student.studentNo,Score.courseNo,score
from Student,Score
where Student.studentNo=Score.studentNo and Score.studentNo in (select studentNo from Score where score>85)

②

select *
from score
where score>85

15.查询至少有两门课程成绩超过85分的所有学生的学号。

select studentNo 
from Score 
where score>85
group by (studentNo)
having count(*)>=2

16.查询平均成绩超过80分且选修3门及以上的所有学生的信息，显示信息包括学号、课程数目和分数。
①

select Student.studentNo,sc.courseNo,cnt_course,sc.score
from Student,(select s.studentNo,count(courseNo)cnt_course from Score s group by s.studentNo having avg(s.score)>80 and count(*)>=2) as S,Score sc
where s.studentNo=Student.studentNo and s.studentNo=sc.studentNo

②

select studentNo, avg (score) avg_ score, count (*) count_ _cno
from score
group by studentNo
having avg(score)>80 and count (*)>=3

17.查询所选修课程的成绩大于所有“002”号课程成绩的同学学号及相应课程的课程号和成绩。
①

select studentNo,courseNo,score
from Score
where score >(select MAX(score) from Score where courseNo='002')

②

select studentNo, courseNo, score
from Score
where (score>all(select score from Score where courseNo='002') )

18.查询选修了所有课程的学生姓名。

select studentName
from Student
where not exists(select *from Course where not exists (select *from Score where studentNo =Student.studentNo and courseNo=Course.courseNo))