实验五：数据查询及更新
一、实验目的
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.以实验数据库为基础数据，进行如下数据更新。
- 修改student表，将cs系姓名为“李咏”的学生姓名为“李勇”；
- 将学号为“200515010”的学生信息重新设置为“王丹丹、女、20、MA”；
- 修改course表,将“数据处理”的学分改为3学分；
- 修改CS表，将选修课程“1”的同学成绩加5分；
- 删除数据表student中无专业的学生记录；
- 删除数据表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'
- 删除数据表student中无专业的学生记录；
use student
delete
from dbo.student_test
where sdept is null
- 删除数据表course中学分低于1学分的课程信息
use student
delete
from dbo.course_test
where ccredit<1
