网站设计要先做图么,营销网络是什么,怎么建设大型商务网站,seo外贸公司推广学习目标#xff1a;
sql
学习内容#xff1a;
37.查询各科成绩最高分、最低分和平均分#xff1a; 以如下形式显示#xff1a;课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 及格为60,中等为#xff1a;70-80,优良为#xff1a;80-90,优秀…学习目标
sql
学习内容
37.查询各科成绩最高分、最低分和平均分 以如下形式显示课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 及格为60,中等为70-80,优良为80-90,优秀为90 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
Select s.counm,c.name,count(stunm),max(tscore)ma,avg(tscore)a,min(tscore)m,sum(case when s.tscore90 then 1 else 0 end)/count() as 优秀,sum(case when s.tscore80 and s.tscore90 then 1 else 0 end)/count() as 优良,sum(case when s.tscore70 and s.tscore80 then 1 else 0 end)/count() as 中等,sum(case when s.tscore60 then 1 else 0 end)/count() as 及格 from score s left join course c on s.counmc.counm group by s.counm,c.name order by count(*) desc,s.counm asc; 38.按各科成绩进行排序,并显示排名, score 重复时保留名次空缺 select s.stunm,s.name,sc.tscore,c.name from students s left join score sc on s.stunmsc.stunm left join course c on sc.counmc.counm group by s.stunm,c.name,s.name,sc.tscore; 39.统计各科成绩各分数段人数课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比 Select c.counm,c.name, sum(case when s.tscore85 and s.tscore100 then 1 else 0 end) as “1”, sum(case when s.tscore70 and s.tscore85 then 1 else 0 end) as “2”, sum(case when s.tscore60 and s.tscore70 then 1 else 0 end)as “3”, sum(case when s.tscore60 then 1 else 0 end) as “4” from score s left join course c on s.counmc.counm group by c.counm,c.name
学习时间
1月-3月每天一小时左右
学习产出
一周一发
