-- 统计课程表的总课程数
select count(*)
from imc_course;
统计讲师个数
select count(course_id)
,count(distinct user_id)
from imc_course;
-- 统计出所有不同难度课程的学习人数
select level_name,sum(study_cnt)
from imc_course a
join imc_level b on a.level_id = b.level_id
group by level_name;
-- 统计出每门课程的平均学习人数
select sum(study_cnt)/count(study_cnt),avg(study_cnt)
from imc_course;
-- 统计出每门课程的平均学习人数
select sum(study_cnt)/count(study_cnt),avg(study_cnt)
from imc_course;
select class_name,avg(study_cnt)
from imc_course a
join imc_class b on a.class_id = b.class_id
group by class_name;
-- 利用课程评价表中的评分，更新课程表中课程的评分
select course_id
,avg(content_score) as avg_content
,avg(level_score) as avg_level
,avg(logic_score) as avg_logic
,avg(score) as avg_score
from imc_classvalue
group by course_id;
-- 查询出学习人数最多的课程
select title,study_cnt
from imc_course
where study_cnt = (select max(study_cnt) from imc_course)
-- 查询出每门课程的学习人数并按学习人数从高到低排列
select title,study_cnt
from imc_course
group by title
order by study_cnt desc
-- 分页返回课程ID和课程名称，每页返回10行记录
select course_id,title
from imc_course
order by course_id asc
limit 20,10
-- 定义一个包含课程ID，课程名称，课程分类，课程方向以及课程难度的视图
create view vm_course
as
select a.course_id,a.title,b.class_name,c.type_name,d.level_name
from imc_course a
join imc_class b on b.class_id = a.class_id
join imc_type c on c.type_id = a.type_id
join imc_level d on d.level_id = a.level_id
-- 查询出学习人数最多的课程
select title,study_cnt
from imc_course where study_cnt = (select max(study_cnt) from imc_course);
