实验六:数据表高级查询
实验内容
注:以下所有用表都基于XSKC模式
以实验5数据库为基础,请使用T-SQL 语句实现进行以下操作:
- 查询名字中第2个字为‘向’的学生姓名和学号及选修的课程号、课程名;
select sname,student.sno,course.cno,cname
from XSKC.student,XSKC.course,XSKC.sc
where student.sno = sc.sno AND sc.cno = course.cno AND sname like '_向%'
- 列出选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩;
select student.sno,sname,sdept,cno,grade
from XSKC.student,XSKC.sc
where student.sno=sc.sno and cno in(
select cno
from XSKC.course
where cname in ('数学','大学英语'))
- 查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息;
select student.*
from XSKC.student
where sage !=(
select sage
from XSKC.student
where sname='张力')
- 按照“学号,姓名,所在院系,已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和;
select student.sno 学号,sname 姓名,sdept 所在院系,sum(ccredit) 已修学分
from XSKC.student,XSKC.course,XSKC.sc
where student.sno=sc.sno and course.cno=sc.cno and grade>=60
group by student.sno,sname,sdept
- 查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号;
select student.sno,sname,sc.cno
from XSKC.student,XSKC.sc
where student.sno = sc.sno and sc.sno in(
select sc.sno
from XSKC.student,XSKC.sc
where cno in(
select cno
from XSKC.sc,XSKC.student
where sc.sno = student.sno and Sname = '张力'
)
)
- 查询只被一名学生选修的课程的课程号、课程名;
select sc.cno,cname
from XSKC.sc,XSKC.course
where course.cno=sc.cno and sc.cno in(
select cno
from XSKC.sc
group by cno
having count(*)=1)
- 使用嵌套查询出选修了“数据结构”课程的学生学号和姓名;
select student.sno,sname
from XSKC.sc,XSKC.student
where student.sno=sc.sno and sc.cno in(
select cno
from XSKC.course
where cname='数据结构')
- 使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系;
select sname,sage,sdept
from XSKC.student
where sdept!='CS' and sage < any(
select sage
from XSKC.student
where sdept='CS')
- 使用ANY、ALL 查询,列出其他院系中比WM系所有学生年龄小的学生的姓名;
select sname
from XSKC.student
where sdept!='WM' and sage < all(
select sage
from XSKC.student
where sdept='WM')
- 分别使用连接查询和嵌套查询,列出与‘张力’在一个院系的学生的信息;
连接查询:
select b.*
from XSKC.student a,XSKC.student b
where a.sname='张力' and a.sdept=b.sdept
嵌套查询:
select student.*
from XSKC.student
where sdept=(
select sdept
from XSKC.student
where sname='张力')
- 使用集合查询列出CS系的学生以及性别为女的学生学号及姓名;
select sno,sname
from XSKC.student
where sdept='CS'
intersect
select sno,sname
from XSKC.student
where ssex='女'
- 使用集合查询列出CS系的学生与年龄不大于19岁的学生的交集、差集;
select *
from XSKC.student
where sdept='CS'
union
select *
from XSKC.student
where sage<=19
select *
from XSKC.student
where sdept='CS'
except
select *
from XSKC.student
where sage<=19