Hive sql 初级题 05

查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select student_info.stu_id, stu_name, avg(score) avg from student_info left join score_info on score_info.stu_id=student_info.stu_id group by student_info.stu_id,stu_name having avg>=85;

查询学生的选课情况：学号，姓名，课程号，课程名称

select s.stu_id, s.stu_name, si.course_id, course_name from student_info s join score_info si on s.stu_id = si.stu_id join course_info ci on si.course_id = ci.course_id;

查询出每门课程的及格人数和不及格人数

select c.course_id, c.course_name, t1.`及格人数`, t1.`不及格人数` from course_info c join ( select course_id, count(if(score >= 60,1,null)) as `及格人数`, count(if(score < 60,1,null)) as `不及格人数` from score_info group by course_id ) t1 on c.course_id = t1.course_id;

查询学过“李体音”老师所教的所有课的同学的学号、姓名

select student_info.stu_id, stu_name from (select stu_id from score_info where course_id in (select course_id from course_info c join teacher_info ti on c.tea_id = ti.tea_id where tea_name = '李体音') group by stu_id having count(*) = 2) t1 join student_info on t1.stu_id = student_info.stu_id;

查询学过“李体音”老师所讲授的任意一门课程的学生的学号、姓名

select si.stu_id, stu_name from score_info join student_info si on score_info.stu_id = si.stu_id where course_id in (select course_id from course_info c join teacher_info ti on c.tea_id = ti.tea_id where tea_name = '李体音') group by si.stu_id, stu_name;

查询至少有一门课与学号为“001”的学生所学课程相同的学生的学号和姓名

select si.stu_id, si.stu_name from score_info sc join student_info si on sc.stu_id = si.stu_id where sc.course_id in ( select course_id from score_info where stu_id = '001' --001的课程 ) and sc.stu_id != '001' --排除001学生 group by si.stu_id, si.stu_name;