检索计算机专业学生的选课情况,实验四 SQL Server 数据查询操作 实验报告

实验四SQL Server 数据查询操作

实验内容

1.列出不及格记录的学生名单

select sname from student,sc

where student.snum=sc.snum and score<60

2.列出选修了计算机系课程的学生姓名和年龄

select distinct sname as 姓名,datediff(year,birthday,getdate()) as 年龄from student,sc,section,course

where student.snum=sc.snum and sc.secnum=section.secnum and http://www.doczj.com/doc/ff1753be84868762caaed5a0.htmlum=http://www.doczj.com/doc/ff1753be84868762caaed5a0.htmlum and course.dept='计算机系'

3.检索选修了数据库技术课程的学生姓名和系别

select distinct sname as 姓名,student.dept as 系别from student,sc,course,section

where student.snum=sc.snum and sc.secnum=section.secnum and http://www.doczj.com/doc/ff1753be84868762caaed5a0.htmlum=http://www.doczj.com/doc/ff1753be84868762caaed5a0.htmlum and http://www.doczj.com/doc/ff1753be84868762caaed5a0.htmlame='数据库技术'

4.列出选修了所有课程的学生名单

select distinct sname as 姓名from student,sc,section

where student.snum=sc.snum and sc.secnum=section.secnum

group by sname

having count(http://www.doczj.com/doc/ff1753be84868762caaed5a0.htmlum)=(select count(*) from course)