实验报告6
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
数据库原理及应用实验报告
实验名称：数据查询（二）
实验内容及结果（代码及运行结果，如实验内容不涉及代码则书写实验步骤描述
（1）use学生课程数据库
select sdept,count(cgrade)as'人数',max(cgrade)as
'max',min(cgrade)as
'min',avg(cgrade)as'avg'
from sc join course as c on
o=o join student as s on s.sno=sc.sno
group by sdept,cname
having cname='数据库'
cf 1 95 95 95
（2）use学生课程数据库
select sdept,count(cgrade)as'人数',max(cgrade)as
'max',min(cgrade)as
'min',avg(cgrade)as'avg'
from sc join course as c on
o=o join student as s on s.sno=sc.sno
group by sdept,cname
having cname='数据库'and
sdept='cf'
cf 1 95 95 95
（3）use学生课程数据库
select cname ,sum(cgrade)as
'sum',avg(cgrade)as'avg'
from sc join course as c on
o=o
group by cname
c语言68 68 大物75 75
高数95 95
近代史90 90
毛概50 50
数据库95 95
数字逻辑61 61
体育88 88
线代100 100
英语85 85
(4) use学生课程数据库
select cname ,sum(cgrade)as 'sum',avg(cgrade)as'avg' from sc join course as c on o=o
where cname<>'高数'
group by cname
c语言68 68
大物75 75
近代史90 90
毛概50 50
数据库95 95
数字逻辑61 61
体育88 88
线代100 100
英语85 85
use学生课程数据库
select cname ,sum(cgrade)as
'sum',avg(cgrade)as'avg' from sc join course as c on o=o
group by cname
having cname<>'高数'
c语言68 68
大物75 75
近代史90 90
毛概50 50
数据库95 95
数字逻辑61 61
体育88 88
线代100 100
英语85 85
(5) use学生课程数据库
select sno,sname,sgender,sage from student
where sdept=
(select sdept
from student
where sname='鸣人')
01 王超风man 20
03 李世民woman 18 04 王祥坤man 19
07 鸣人man 17
(6) use学生课程数据库
select s.sno,sname,sdept,cgrade from student as s join sc on
s.sno=sc.sno
where sage=
(select sage
from student
where sname='李潇')
02 王乾坤cf 100
04 王祥坤cs 68
06 李潇ma 85
(7) use学生课程数据库
select s.sno,sname,cgrade
from student as s join sc on
s.sno=sc.sno
where sgender=
(select sgender
from student
where sname='李潇')
02 王乾坤100
03 李世民61
05 李昊95
06 李潇85
08 李小龙50
10 佐助88
(8) use学生课程数据库
select sno,sname,sgender,sage from student
where sno in(select sno
from sc
where cgrade>'80')
01 王超风man 20
02 王乾坤woman 19
05 李昊woman 21
06 李潇woman 19
09 张飞man 22
10 佐助woman 18 (9)use学生课程数据库
select sdept
from student
group by sdept
having avg(sage)<=all(select avg(sage) from student
group by sdept) cs
(10)use学生课程数据库
select s.sno,sname,cgrade
from student as s join sc on s.sno=sc.sno where cno in(select cno
from sc
where sname='鸣人')
07 鸣人75
(11) use学生课程数据库
select*
from student
where sno in(select sno
from sc
where cno in(select cno
from course
where cname='数据库'
))
05 李昊woman 21 cf