实验三：SQL Server的数据查询

最新推荐文章于 2022-11-01 22:07:53 发布

CyberTorch

最新推荐文章于 2022-11-01 22:07:53 发布

阅读量824

点赞数

分类专栏：数据库文章标签： sqlserver

本文链接：https://blog.csdn.net/m0_50181189/article/details/119108950

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

在SQL SERVER上附加teaching数据库,其中三张表的含义解释如下：学生表dbo.student有属性sno、sname、spec、birthday、email、sex、scholarship,分别代表学号、姓名、专业、生日、电子邮箱、性别、奖学金；课程表dbo.course有属性cno、cname、credit、teacher，分别代表课程号、课程名、学分、任课教师；选课表dbo.student_course有属性sno、cno、grade，分别代表学号、课号、成绩。要求在teaching数据库中完成下列查询（每小题10分，共50分）:

求至少选修5门课程的学生信息；

SELECT student.sno,student.sname,student.spec,student.birthday,student.email,student.sex,student.scholarship From student,student_course
WHERE student.sno=student_course.sno
group by student.sno,student.sname,student.spec,student.birthday,student.email,student.scholarship,student.sex
having COUNT(student_course.cno)>=5;

求每个学生所选课程的平均成绩，并用查询结果来创建一个新的数据表S_avggrade(sno,sname,avggrade)；

use teaching 
go 
Select s.sno,sname,avg(grade) as avggrade Into S_avggrade
From student s,student_course sc 
Where s.sno=sc.sno  
Group by s.sno,sname

求全部学生均选修的课程的课程号和课程名；

use teaching
go
select student_course.cno,cname
from course ,student_course,student
where student.sno=student_course.sno and course.cno = student_course.cno
group by student_course.cno,cname
having COUNT(*) = (select COUNT(*)from student)

查询平均成绩高于所有学生选课平均成绩的学生情况；

use teaching
select distinct s.sno,s.sname,s.sex,s.spec,s.scholarship,s.email,s.birthday
from student s,student_course sc
where s.sno=sc.sno 
and sc.grade>(select avggrade=avg(grade) from student_course)
go

检索’计算机’专业有课程成绩不及格（<60）的学生信息，包括学号、姓名、课程名和成绩；

use teaching
go
SELECT student.sno,student.sname,course.cname,student_course.grade
FROM student,course,student_course
WHERE student.spec=&apos;计算机&apos; AND student_course.grade<60