数据库原理与应用实验三
–1) 查询选修了 数据结构 课程的学生学号和姓名。
select student.sno,sname from student,Course,SC
where student.sno=SC.sno and Course.cno=SC.cno
and SC.cno=(select cno from Course where cname='数据结构')
–2) 查询比王华同学年龄大的学生学号和姓名。
select sno,sname from student where sage >
(select sage from student where sname='王华')
–3) 查询选修 01 课程的成绩低于张三的学生学号和成绩。
select distinct SC.sno,grade
from student,SC where
SC.cno='01'and grade<
(select grade from SC,student where SC.sno=student.sno and sname='张三')
–4) 查询其他学院中比数计学院学生年龄都大的学
生。
select*from student where sage>all
(
select sage from student where sdept='数计学院'
)
–5) 查询选修了 01 课程的学生姓名。
select student.sname from student,SC
where student.sno=SC.sno and SC.cno='01'
–6) 查询选修了全部课程的学生姓名。
法一:–课程数
select sname from student where sno in
(
select sno from SC group by sno
having count(*)=(select count(*)from Course)
)
法二:–循环
select sname from student
where not exists
(
select*from Course
where not exists
(
select*from SC
where SC.sno=student.sno
and SC.cno=Course.cno
)
)
–7) 查询至少选修了“001”学生所选修课程中一门课程的学生学号的姓名。
select distinct SC.sno,sname from student,SC where student.sno=SC.sno
and cno in
(select cno from SC where SC.sno='003')
select Sc.cno,sname from student ,SC where student.sno=SC.sno
–8) 查询至少选修了“002”学生所选修的全部课程的学生学号的姓名
–(查询选修课程包含 “002”学生所选课程的学生学号和姓名)。
先查看一下数据噻
select*from student
select*from SC
–复杂版
select distinct a.sno,sname from SC a
inner join student on a.sno=student.sno
where not exists
(
select*from SC b where b.sno ='002'
and not exists
(
select*from SC c where c.cno=b.cno and c.sno=a.sno
)
)
–简单版
select sno,sname from student
where not exists
(
select*from SC a where a.sno='002'
and not exists
(
select*from SC b where a.sno=b.sno and a.cno=b.cno
)
)
–9)查询既选修了“数据结构”课程又选修了“数据库原理与应用”课程的学生姓名
select sname from student where
sno in
(
select sno from SC where cno=
(
select cno from Course where cname='数据结构'
)
)
and sno in
(
select sno from SC where cno=
(
select cno from Course where cname='数据库原理与应用'
)
)
–10)查询选修了“数据结构”课程或选修了“数据库原理与应用”课程的学生学号。
select sno from student where sno in
(
select sno from SC,Course where SC.cno=Course.cno
and cname in('数据结构','数据库原理与应用')
)
–11)查询选修了“数据结构”课程而没有选修“数据库原理与应用”课程的学生学号。
select sno from student where
sno in
(
select sno from SC where cno=
(
select cno from Course where cname='数据结构'
)
)
and sno not in
(
select sno from SC where cno=
(
select cno from Course where cname='数据库原理与应用'
)
)
–12)查询全是女同学选修的课程号。
–排除法
select distinct cno from sc
where cno not in
(select cno from Sc left join student on student.sno=sc.sno
where ssex = '男')
–减法
select cno from SC where
cno in
(
select cno from SC left join student on Sc.sno=student.sno
where ssex='女'
)
and cno not in
(
select cno from SC left join student on Sc.sno=student.sno
where ssex='男'
)
group by cno
exists[最优解]
select distinct cno
from SC x
where not exists(select*
from student
where ssex='男'and exists(select*
from SC y
where y.cno=x.cno and y.sno=student.sno))
–1)使用集合运算查询既选修了“数据结构”课程又选修了“数据库原理与应用”课程的学生姓名。
select sname from student where
sno in
(
select sno from SC where cno=
(
select cno from Course where cname='数据结构'
)
)
intersect
select sname from student where
sno in
(
select sno from SC where cno=
(
select cno from Course where cname='数据库原理与应用'
)
)
–2)使用集合运算查询选修了“数据结构”课程或选修了“数据库原理与应用”课程的学生学号。
select sno from student where
sno in
(
select sno from SC where cno=
(
select cno from Course where cname='数据结构'
)
)
union
select sno from student where
sno in
(
select sno from SC where cno=
(
select cno from Course where cname='数据库原理与应用'
)
)
–3)使用集合运算查询选修了“数据结构”课程而没有选修了“数据库原理与应用”课程的
–学生学号。
select sno from student where
sno in
(
select sno from SC where cno=
(
select cno from Course where cname='数据结构'
)
)
except
select sno from student where
sno in
(
select sno from SC where cno=
(
select cno from Course where cname='数据库原理与应用'
)
)
–4)统计选修了课程的学生人数。
select count(*) 选修了课程的人数 from student where sno in
(select sno from SC)
--简单化
select count(distinct sno) 选修了课程的人数 from SC
--5)查询选修成绩合格,并且选课门次超过 4 门以上学生的学生学号、总成绩。
select sno,sum(grade) 总成绩 from SC
where grade>60
group by sno
having count(*)>4
–6)统计各院系的学生人数。
select sdept,count(*)人数 from student group by sdept
–7)统计各年龄的学生人数。
select sage,count(*)人数 from student group by sage
–8)统计每个学生的选修课程数目和平均成绩。
select sname 学生,count(*) 选修课程数目,avg(grade) 平均成绩
from SC,student
where student.sno=SC.sno
group by student.sname
–9)查询每门课程的详细信息及选课人数。
–适用性差
select Course.cno,cname,ccredit,cpno,ctech,count(sno) 选课人数
from Course
left join SC on Course.cno=SC.cno
group by Course.cno,cname,ccredit,cpno,ctech
–from后可以跟查询[最优解]
select Course.*,选课人数
from Course left join (select cno,count(sno) 选课人数
from SC
group by cno)as a
on a.cno=Course.cno