7.查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
– 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
select s_id,s_name
from student
where s_id in
(select a.s_id
from
(
select s_id
from score
where c_id = ‘01’) as a
inner JOIN
(
select s_id
from score
where c_id = ‘02’
) as b
on a.s_id = b.s_id
)
8.-- 8.查询课程编号为“02”的总成绩
SELECT sum(s_score)
from score
where c_id = ‘02’
10.查询没有学全所有课的学生的学号和姓名
– 分析:(1)先查询课程表有多少课程
– (2)查询出每个学生学习了几门课
select s_id,s_name
from student
where s_id in
(
select s_id
from score
group by s_id
having count(c_id) < (select count(c_name)
from course
)
)
11-- 查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名
– 分析:(1)先查出学号为“01”的学生所学的课程
– (2)然后in ()
– 查询出s_id对应的c_id有哪些?
select c_id
from score
where s_id = ‘01’;
select *
FROM student
where s_id in
(
select DISTINCT s_id
from score
where c_id in (
select c_id
from score
where s_id = ‘01’
)and s_id !=‘01’
)
18.查询各科成绩最高分、最低分和平均分:如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优先率,优秀率 及格为>=60 ,中等为:70-80,优良为:>=90