实验六：数据库综合查询-答案.doc
文本预览下载声明
实验六：数据库综合查询
查询以‘DB_’开头,且倒数第个字符为‘s’的课程的详细情况；
use student
select *
from course
where cname like DB_%S__
查询名字中第个字为‘阳’的学生姓名和学号及选修的课程号、课程名；
use student
select sname,student.sno,sc.cno,cname
from course,student,sc
where sname like _阳%and student.sno=sc.sno and course.cno=sc.cno
列出选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；
use student
select student.sno 学号,sname 姓名,sdept 所在院系,sc.cno 选修课程号,grade 成绩
from course,student,sc
where (cname=数学or cname=大学英语) and student.sno=sc.sno and course.cno=sc.cno
查询缺少成绩的所有学生的详细情况；
use student
select student.*
from student,sc
where grade is null and student.sno=sc.sno
注意：若成绩存在，则改为grade=数字
查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息
use student
select *
from student
where sage !=(select sage
from student
where sname=张力)
查询所选课程的平均成绩大于张力的平均成绩的学生学号、姓名及平均成绩；
use student
select x.sno 学号,sname 姓名,平均成绩=AVG(grade)
from student x,sc y
group by sname,x.sno
having avg(grade)(select AVG(grade)
from sc,student
where sname=张力 and sc.sno=student.sno )
注意：没有成功
按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和；
use student
select student.sno 学号,sname 姓名,sdept 院系,
已修学分=(SUM(case when grade60 then ccredit else 0 end))
from student ,sc,course
where student.sno =sc.sno and sc.cno =course .cno
group by student.sno,sname,sdept
列出只选修一门课程的学生的学号、姓名、院系及成绩
use student
select sc.sno 学号,sname 姓名,sdept 院系,grade 成绩
from student ,sc
where student.sno=sc.sno and student.sno in(
select sc.sno
from student,sc
where student.sno = sc.sno
group by sc.sno
having count(sc.cno) = 1);
查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号；
use student
select sc.sno 学号,sname 姓名,cno 课程号
from student,sc
where student.sno=sc.sno and sc.cno in(
select sc.cno
from student,sc
where student.sno = sc.sno and student.sname=张力 )
and sname 张力/*此句意思是不包括张力*/
10.只选修“数据库”和“数据结构”两门课程的学生的基本信息；
use student
select student.*
from student,sc,course
where student.sno=sc.sno and
显示全部