学习目标:
sql
学习内容:
13.求每门课程的学生人数
分析:分组查询
Select counm, count(*) from score group by counm;
14.查询不及格的课程
分析:分数小于60
Select counm,tscore from score where tscore<60;
但这样不唯一,用distinct,select distinct counm from score where score.tscore <60;
用group by 也行,select counm from score where tscore<60 group by counm;
15.查询在 sc 表存在成绩的学生信息
分析:distinct
select distinct s.name from students s,score sc where s.stunm=sc.stunm;
看了一下答案,偏题了 ̄□ ̄||,是学生信息不只是学生姓名
select distinct students.* from students, score where students.stunm = score.stunm;
16.查询每门课程被选修的学生数
分析:分组,count()
select counm,count(stunm) from score group by counm;
17.查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
分析:group by , order by
select counm,avg(tscore) from score group by counm order by avg(tscore) desc , counm asc;
18.查询课程编号为 2001 且课程成绩在 10 分以上的学生的学号和姓名
分析:连接
select s.stunm,s.name from students s right join score sc on s.stunm=sc.stunm where sc.counm=‘2001’ and tscore >=10;
select students.stunm,students.name from students,score where counm=“2001” and tscore >=10 and students.stunm = score.stunm;
学习时间:
1月-3月,每天一小时左右
学习产出:
一周一发