数据库上机三 ： 数据查询（二）nuistNJUPT的博客-

数据库上机（三）：数据查询（二）、数据更新

一、实验目的
1.掌握SQL嵌套查询和集合查询等各种高级查询的设计方法。
2.熟悉数据库的数据更新操作，能够使用SQL语句对数据库进行数据的插入、修改、删除操作。

二、实验内容和要求

1. 用子查询实现如下查询：
   （1）查询选了“C001”课程的学生姓名和所在系。

select Sname , Sdept from Student  where Sno IN( select Sno from SC where Cno = 'C001' ) ; 

（2）查询通信工程系成绩80分以上的学生的学号和姓名。

select Sno, Sname from Student  where  Sno IN( select DISTINCT Student.Sno from SC, Student where SC.Sno = Student.Sno AND Sdept = '通信工程系' AND Grade > 80 

（3）查询计算机系考试成绩最高的学生的姓名。

select  Sname from Student where Sno =(select Sno  from SC where Grade = ( select MAX(Grade) from SC,Student where SC.Sno = Student.Sno AND Sdept = '计算机系' )) ; 

（4）查询年龄最大的男生的姓名、所在系和年龄。

select  Sname, Sdept, Sage from Student  where Ssex = '男' AND Sage =( select Max(Sage) from Student  where Ssex = '男' ) ; 

2 查询C001课程的考试成绩高于该课程平均成绩的学生的学号和成绩。

select Student.Sno, Grade from Student, SC where Student.Sno = SC.Sno  AND Cno = 'C001' AND Grade >( select AVG(Grade) from SC where Cno = 'C001' ); 

3 查询计算机系学生考试成绩高于计算机系学生平均成绩的学生的姓名、考试的课程名和考试成绩。

select Sname, Cname, Grade  from Student, SC, Course where Student.Sno = SC.Sno AND  SC.Cno = Course.Cno AND Sdept = '计算机系' AND  Grade > ( select AVG(Grade) from SC,Student  where SC.Sno = Student.Sno AND Sdept = '计算机系' ) 

4 查询VB课程考试成绩高于VB平均成绩的学生姓名和VB成绩。

select Sname, Grade from Student, SC, Course where Student.Sno = SC.Sno AND Course.Cno = SC.Cno AND Cname = 'VB' AND Grade >( select AVG(Grade) from Course, SC where SC.Cno = Course.Cno AND Cname = 'VB' ) ; 

5 查询没选VB的学生姓名和所在系。

select DISTINCT Sname, Sdept from Student,SC where Student.Sno = SC.Sno AND Cno <> 'VB' ; 

6 查询每个学期学分最高的课程信息，列出开课学期、课程名和学分。

select Semester , Cname, Credit from Course x1 where Credit = ( select MAX(Credit) from Course x2 where x1.Semester = X2.Semester ) ; 

7 查询每门课程考试成绩最高的学生信息，列出课程号、学生姓名和最高成绩，结果按课程号升序排序，不包括没考试的课程。

select Cno, max(Grade) 最高成绩 from Student, SC where Student.Sno = SC.Sno Group by Cno  

8 查询计算机系学生姓名、年龄和年龄情况，其中年龄情况为：如果年龄小于18，则显示“偏小”；如果年龄在18-22，则显示“合适”；如果年龄大于22，则显示“偏大”。

select Sname , Sage, case when Sage <18 then '偏小' when Sage between 18 and 22 then '合适' when Sage >22 then '偏大' end 年龄情况 from Student  where Sdept = '计算机系' 

9 统计每门课程的选课人数，包括有人选的课程和没有人选的课程，列出课程号，选课人数及选课情况，其中选课情况为：如果此门课程的选课人数超过100人，则显示“人多”；如果此门课程的选课人数在40～100，则显示“一般”；如果此门课程的选课人数在1～40，则显示“人少”；如果此门课程没有人选，则显示“无人选”。
提示：选课情况列中显示的内容

select Course.Cno 课程号, COUNT(Student.Sno) 选课人数, case when COUNT(Student.Sno) > 100 then '人多' when COUNT(Student.Sno) between 40 and 100 then '一般' when COUNT(Student.Sno) < 40 then '人少' else '无人选' end 选课情况 from  Course left outer join SC on (SC.Cno = Course.Cno), Student where Student.Sno = SC.Sno group by Course.Cno   

10 查询计算机系选了VB课程的学生姓名、所在系和考试成绩，并将结果保存到新表VB_Grade中。

select Sname, Sdept, Grade into VBGrade from Student, SC, Course where Student.Sno = SC.Sno And Course.Cno = SC.Cno AND Cname = 'VB' AND Sdept = '计算机系' 

11 统计每个系的女生人数，并将结果保存到新表Girls中。

select Sdept, COUNT(Sno) 女生人数 into Girls from Student where Ssex = '女' group by Sdept  

12 创建一个新表，表名为test，其结构为（COL1, COL 2, COL 3），其中，
COL1：整型，允许空值。
COL2：普通编码定长字符型，长度为10，不允许空值。
COL3：普通编码定长字符型，长度为10，允许空值。
试写出按行插入如下数据的语句（空白处表示是空值）。
COL1 COL2 COL3
B1
1 B2 C2
2 B3

create table test ( COL1 int NULL, COL2 char(10) NOT NULL, COL3 char(10) NULL ) insert into test values (NULL, 'B1', NULL), (1, 'B2','C2'), (2, 'B3', NULL); 

13.利用第10题建立的VB_Grade表，将信息管理系选了VB课程的学生姓名、所在系和考试成绩插入到VB_Grade表中。

insert into VBGrade(Sname, Sdept, Grade) select Sname, Sdept, Grade from Student, SC, Course where Student.Sno = SC.Sno AND SC.Cno = Course.Cno  AND Sdept = '信息管理系' AND Cname = 'VB' ; 

14 将所有选修C001课程的学生的成绩加10分。

UPDATE SC SET Grade = Grade + case when Cno = 'C001' then 10 end 

15将计算机系所有学生的“计算机文化学”的考试成绩加10分。

UPDATE SC SET Grade = Grade + case when Sdept = '计算机系' AND Cname= '计算机文化学' then 10 end from Course, SC, Student where Course.Cno = SC.Cno AND Student.Sno = SC.Sno ; 

16 修改“VB”课程的考试成绩，如果是通信工程系的学生，则增加10分；如果是信息管理系的学生则增加5分，其他系的学生不加分。

UPDATE SC SET Grade = Grade + case when Sdept = '通信工程系' AND Cname = 'VB' then 10 when Sdept = '信息管理系' AND Cname = 'VB'then 5 end from Course,SC, Student where Course.Cno = SC.Cno AND Student.Sno = SC.Sno ; 

17 删除成绩小于50分的学生的选课记录。

delete from SC where Grade < 50 

18 删除计算机系VB考试成绩不及格学生的VB选课记录。

delete from SC  where Grade < 60 AND Cno IN(select Cno        from Course        where Cname = 'VB') AND Sno IN (select Sno         from Student         where Sdept = '计算机系'); 

19 删除“VB”考试成绩最低的学生的VB修课记录。

delete from SC from  SC join Course ON (SC.Cno = Course.Cno) where Cname = 'VB' AND Grade IN( select MIN(Grade) from SC join Course ON (SC.Cno = Course.Cno) where Cname = 'VB' ) ; 

20 删除没人选的课程的基本信息。

delete from course from Course LEFT outer join  SC on(SC.Cno = Course.Cno) where SC.Sno is NUll 

三、实验小结
*

没有最终的成功，也没有致命的失败，最可贵的是继续前行的勇气！*

本文原创Author WangGuodong , 如果对你有帮助，点个赞再走吧，谢谢！