实验五：数据查询及更新

一、实验目的
1.掌握SELECT语句的基本语法和查询条件表示方法；
2.掌握数据表的连接查询、嵌套查询、集合查询的使用方法。
3.掌握创建及管理视图的方法；
二、实验学时
2学时
三、实验要求
1.了解SELECT语句的基本语法格式和执行方法；
2.掌握连接查询、嵌套查询和集合查询的语法规则；
3.掌握使用界面方式和命令方式创建及管理视图；
4.完成实验报告；
四、实验内容
1.以实验3数据库为基础，请使用T-SQL 语句实现进行以下操作：
1)查询选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；
2)查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息；
3)按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和；
4)查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号；
5)查询只被一名学生选修的课程的课程号、课程名；
6)使用嵌套查询出选修了“数据结构”课程的学生学号和姓名；
7)使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系；
8)使用ANY、ALL 查询，列出其他院系中比WM系所有学生年龄小的学生的姓名；
9)使用集合查询查询选修1号课程同时选修2号课程的同学的学号与姓名；
补充题目：
1）显示选修02号课程的成绩前两名的学生学号及成绩。
2）显示选修各个课程的及格的人数，及格比率；
3）显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”；
4）列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；
5）显示选修课程数最多的学号及选修课程数最少的学号；

2.以实验数据库为基础数据，进行如下数据更新。

1. 修改student表，将cs系姓名为“李咏”的学生姓名为“李勇”；
2. 将学号为“200515010”的学生信息重新设置为“王丹丹、女、20、MA”；
3. 修改course表,将“数据处理”的学分改为3学分；
4. 修改CS表，将选修课程“1”的同学成绩加5分；
5. 删除数据表student中无专业的学生记录；
6. 删除数据表course中学分低于1学分的课程信息；

答案

1.以实验3数据库为基础，请使用T-SQL 语句实现进行以下操作：

1)查询选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；

use student
select a.sno,a.sname,a.sdept,c.cno,b.grade
from XSKC.student a,XSKC.sc b,XSKC.course c
where a.sno=b.sno and b.cno=c.cno and (c.cname='数学' or c.cname='大学英语'  )

2)查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息；

use student
select b.*
from XSKC.student a	,XSKC.student b
where a.sname='张力'and a.sage<>b.sage

3)按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和；

use student
select a.sno,a.sname,a.sdept,sum(c.ccredit) 已修学分
from XSKC.student a	,XSKC.sc b ,XSKC.course c			/*,XSKC.sc b,XSKC.course c*/
where a.sno=b.sno and  b.cno=c.cno and b.grade>=60
group by a.sno,a.sname,a.sdept;

4)查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号；

use student
select b.sno,b.sname,c.cno
from XSKC.student b,XSKC.sc c
where c.cno in (
select cno
from XSKC.sc
where sno in (select sno
from XSKC.student
where sname='张力')) and b.sno=c.sno  

5)查询只被一名学生选修的课程的课程号、课程名；

use student
select cno,cname
from XSKC.course 
where cno in(
select cno 
from XSKC.sc
group by cno
having count(*)=1
)

6)使用嵌套查询出选修了“数据结构”课程的学生学号和姓名；

use student
select sno,sname
from XSKC.student 
where sno in
(select sno from XSKC.sc sc where sc.cno in
(select c.cno from XSKC.course c  where  c.cname='数据结构'
))

7)使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系；

use student
select sname,sage,sdept 
from XSKC.student 
where sage< 
	      (select Max(sage) 
		  from XSKC.student
		   where sdept='CS') and sdept!='CS' 

8)使用ANY、ALL 查询，列出其他院系中比WM系所有学生年龄小的学生的姓名；

use student
select sname
from XSKC.student 
where sage < ALL 
	       (
		   select sage 
		   from XSKC.student
		   where sdept='WM') and sdept!='WM'

9)使用集合查询查询选修1号课程同时选修2号课程的同学的学号与姓名；

use student
select sno,sname
from XSKC.student s 
where sno in
			(select sno
			 from XSKC.sc
			 where cno='1'
			 intersect
			 select sno
			 from XSKC.sc
			 where cno='2'
) 

补充题目：
1）显示选修02号课程的成绩前两名的学生学号及成绩。

use student
select  top(2) sno,grade
from XSKC.sc
where cno='2'
order by grade desc

2）显示选修各个课程的及格的人数，及格比率；

use student
select
COUNT(
case 
when grade>=60 then 1
end
)各课程及格人数,

CAST(100*count(case when grade>=60 
then 1 end)/count(*) as float)及格比率

from XSKC.sc
group by cno

3）显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”；

use student
select sdept 院系,count(case when ssex='男' then 1 end ) 男生人数,count( case when ssex='女' then 1 end) 女生人数
from XSKC.student
where sdept is not null
group by sdept

4）列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；

use student
select s.sno,均分=avg(grade) 
from XSKC.student s,XSKC.sc sc
where s.sno in (
			select sno
			from XSKC.sc
			where grade<60
			group by sno
			having count(*)>=2
			)and s.sno=sc.sno
group by s.sno;

5）显示选修课程数最多的学号及选修课程数最少的学号；

select sno 学号,COUNT(cno)选修课程数
from XSKC.sc
group by sno

having COUNT(cno)>=all (select COUNT(cno)from XSKC.sc group by sno)
or COUNT(cno)<=all(select COUNT(cno) from XSKC.sc group by sno)

2.以实验数据库为基础数据，进行如下数据更新。

修改student表，将cs系姓名为“李咏”的学生姓名为“李勇”；

use student
update dbo.student_test
set sname='李勇'
where sname='李咏'

2)将学号为“200515010”的学生信息重新设置为“王丹丹、女、20、MA”；

use student
update dbo.student_test
set sname='王丹丹',ssex='女',sage=20,sdept='MA'
where sno='200515010'

3)修改course表,将“数据处理”的学分改为3学分；

use student
update dbo.course_test
set ccredit=3
where cname='数据处理'

4)修改CS表，将选修课程“1”的同学成绩加5分；

use student
update dbo.sc_test
set grade=grade+5
where cno='1'

5. 删除数据表student中无专业的学生记录；

use student
delete
from dbo.student_test
where sdept is null

6. 删除数据表course中学分低于1学分的课程信息

use student
delete
from dbo.course_test
where ccredit<1