1、查询“c001”课程比“c002”课程成绩高的所有学生的学号
select a.* from
(select * from sc a where a.cno='c001') a,
(select * from sc b where b.cno='c002') b
where a.sno=b.sno and a.score > b.score;
1
2、查询平均成绩大于60 分的同学的学号和平均成绩
select sno,avg(score) from sc
group by sno
having avg(score) >60;
2
3、查询所有同学的学号、姓名、选课数、总成绩
解法一
select a.sno,a.sname,count(b.cno),sum(b.score) from student a
join sc b on a.sno=b.sno
group by sno;
解法二
select a.*,s.sname from
(select sno,sum(score),count(cno) from sc group by sno) a ,student s where a.sno=s.sno
3
4、查询姓“刘”的老师的个数
select count(*) from teacher
where tname like '刘%';
4
5、查询没学过“谌燕”老师课的同学的学号、姓名
#学过谌燕老师课的同学
select a.sno,a.sname from student a
join sc b on a.sno=b.sno
join(select a.cno from course a
join teacher b on
a.tno = b.tno
where tname='谌燕') c on
b.cno = c.cno;
#没学过谌燕老师课的同学
select a.sno,a.sname from student a
where a.sno not in
(select distinct a.sno from sc a
join course b on a.cno=b.cno
join teacher c on c.tno=b.tno
where tname='谌燕');
5
6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名
select a.sno,a.sname from student a
join sc b on a.sno=b.sno
join sc c on b.sno=c.sno
where b.cno='c001' and c.cno='c002' and a.sno=c.sno;
6
7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名
select a.sno,a.sname from student a
join sc b on a.sno=b.sno
join(select a.cno from course a
join teacher b on
a.tno = b.tno
where tname='谌燕') c on
b.cno = c.cno;
7
8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名
解法一
select sno,sname from student
where sno in (select a.sno from
(select * from sc a where a.cno='c001') a,
(select * from sc b where b.cno='c002') b
where a.sno=b.sno and a.score > b.score);
8解法一
解法二
select * from student st
join sc a on st.sno=a.sno
join sc b on st.sno=b.sno
where a.cno='c002' and b.cno='c001' and a.score < b.score
8解法二
9、查询所有课程成绩小于60 分的同学的学号、姓名
select a.sno,a.sname from student a
join sc b on a.sno=b.sno
join course c on c.cno=b.cno
where b.score <60;
9
10、查询没有学全所有课的同学的学号、姓名
select a.sno,a.sname,count(b.cno) from student a
left join sc b on a.sno=b.sno
group by a.sno,a.sname
having count(b.cno)<(select count(distinct cno) from course);
10
11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名
解法一
select distinct a.sno,a.sname from student a
join sc b on a.sno=b.sno
where b.cno in
(select cno from sc a
join student b on a.sno=b.sno
where a.sno='s001')
and a.sno<>'s001';
11解法一
解法二
select st.* from student st,
(select distinct a.sno from
(select * from sc) a,
(select * from sc where sc.sno='s001') b
where a.cno=b.cno) h
where st.sno=h.sno and st.sno<>'s001'
11解法二
12、查询至少学过学号为“s001”同学所有一门课的其他同学学号和姓名
此题解法我认为与11题相同，但网上解法如下
select * from sc
left join student st
on st.sno=sc.sno
where sc.sno<>'s001'
and sc.cno in
(select cno from sc
where sno='s001')
12
13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩
#替换数据应使用
update sc set score =
#“谌燕”老师教的课的平均成绩
(select avg(score) from sc
join (course a,teacher c) on a.tno=c.tno and sc.cno=a.cno
and c.tname='谌燕'
group by sc.cno )
#where cno in
#'谌燕'老师教的课程
(select cno from course a
join teacher b on a.tno=b.tno
where b.tname='谌燕');
14、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名
select sno from sc
where cno in (select cno from sc where sno='s001')
#join sc b on a.cno= b.cno and a.sno='s001'
group by sno
having count(*)=
(select count(*) from sc where sno='s001');
14
15、删除学习“谌燕”老师课的SC 表记录
#删除数据应使用delete from sc where sc.cno in
(select cno from course a
join teacher b on a.tno=b.tno
where b.tname='谌燕');
16、向SC 表中插入一些记录，这些记录要求符合以下条件：没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩
#插入数据应使用insert into sc(sno,cno,score)
#平均成绩
select distinct st.sno,sc.cno,(select avg(score)from sc where cno='c002')
from student st,sc
where not exists
#没有上过编号“c002”课程的同学学号
(select * from sc where cno='c002' and sc.sno=st.sno) and sc.cno='c002';
17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select cno,max(score),min(score) from sc
group by cno
17
18、按各科平均成绩从低到高和及格率的百分数从高到低顺序
select cno,avg(score),
sum(case when score>=60 then 1 else 0 end)/count(*)
as 及格率
from sc
group by cno
order by avg(score) ,及格率 desc;
18
19、查询不同老师所教不同课程平均分从高到低显示
select a.tno,a.tname,b.cname,c.cno,avg(c.score) from teacher a
join (course b,sc c) on a.tno=b.tno and b.cno=c.cno
group by c.cno
order by avg(score) desc
19
20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
select sc.cno,c.cname,
sum(case when score between 85 and 100 then 1 else 0 end) as "[100-85]",
sum(case when score between 70 and 85 then 1 else 0 end) as "[85-70]",
sum(case when score between 60 and 70 then 1 else 0 end) as "[70-60]",
sum(case when score <60 then 1 else 0 end) as "[<60]"
from sc, course c
where sc.cno=c.cno
group by sc.cno ,c.cname;
20
<b> 未解答 21、查询各科成绩前三名的记录:(不考虑成绩并列情况)</b>
select b.cno,a.sno,a.sname,b.score from student a
join sc b on a.sno=b.sno
group by b.cno
order by b.score desc;
22、查询每门课程被选修的学生数
select a.cno,b.cname,count(a.sno) from sc a,course b
where a.cno=b.cno
group by cno;
22
23、查询出只选修了一门课程的全部学生的学号和姓名
select a.sno,a.sname from student a
join sc b on a.sno=b.sno
group by b.sno
having count(b.cno) =1;
23
24、查询男生、女生人数
select ssex,count(sno) from student
group by ssex;
24
25、查询姓“张”的学生名单
select * from student
where sname like '张%';
25
26、查询同名同性学生名单，并统计同名人数
select sname,count(*)from student
group by sname
having count(*)>1;
26
