【数据库原理与应用实验三】

数据库原理与应用实验三

–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