SELECT student_id,count(course_id)AS num FROM score GROUPBY student_id HAVING num >=3;
或者
SELECT student_id FROM(SELECT student_id,count(course_id)AS num FROM score GROUPBY student_id) t
WHERE num >=3;
3.9 查询存在不及格的课程编号
SELECTDISTINCT(course_id)FROM score WHERE score <60;
或
SELECT course_id FROM score WHERE score <60GROUPBY course_id;
3.10 输出所有课程的课程编号、课程名、对应的教师姓名
SELECT t1.*,t2.name FROM course t1 LEFTJOIN teacher t2 ON t1.teacher_id = t2.teacher_id;
3.11 求每门课程的学生人数及平均分,输出课程编号、对应学生人数、平均分
SELECT course_id,count(1) student_num,avg(score) avg_score FROM score GROUPBY course_id;
3.12 求每门课程的学生人数及平均分,输出课程名称、对应学生人数、平均分
SELECT t1.name course_name,t2.student_num,t2.avg_score FROM course t1
LEFTJOIN(SELECT
course_id,count(1) student_num,avg(score) avg_score
FROM score
GROUPBY course_id) t2 ON t1.course_id = t2.course_id;
3.13 查询同时学习" 01 “课程和” 02 "课程的学生编号及01和02课程分数
SELECT t1.student_id,t1.score score1,t2.score score2 FROM score t1
JOIN score t2 on t1.student_id = t2.student_id
WHERE t1.course_id ='01'AND t2.course_id ='02';
或
select a.student_id
,a.score as score_01
,b.score as score_02
from(select*from score
where course_id ='01') a
join(select*from score
where course_id ='02') b
on a.student_id = b.student_id;
3.14 查询" 01 “课程比” 02 "课程成绩高的学生编号及01和02课程分数
SELECT*FROM(SELECT t1.student_id,t1.score score1,t2.score score2 FROM score t1
JOIN score t2 on t1.student_id = t2.student_id
WHERE t1.course_id ='01'AND t2.course_id ='02') t WHERE score1 > score2;
3.15 查询" 01 “课程比” 02 "课程成绩高的学生姓名及01和02课程分数
SELECT t2.name,t.score1,t.score2 FROM(SELECT t1.student_id,t1.score score1,t2.score score2 FROM score t1
JOIN score t2 on t1.student_id = t2.student_id
WHERE t1.course_id ='01'AND t2.course_id ='02') t
LEFTJOIN student t2 ON t.student_id = t2.student_id WHERE score1 > score2;
3.16 查询选择了 "01"课程但没选择 "02"课程的学生姓名
SELECT t1.name FROM student t1 JOIN(SELECT a.student_id FROM(SELECT*FROM score WHERE course_id ='01') a
LEFTJOIN(SELECT*FROM score WHERE course_id ='02') b
ON a.student_id = b.student_id WHERE b.student_id ISNULL) t2 ON t1.student_id = t2.student_id;--hql中 in和not in后不支持子查询,所以只能用join来解决
3.17 查询学过 ‘张老师’ 老师课程的所有同学姓名、生日、性别
SELECT t1.name,t1.birthday,t1.sex FROM student t1 LEFTJOIN(SELECT t1.*FROM score t1 LEFTJOIN(SELECT t2.course_id FROM teacher t1
LEFTJOIN course t2 ON t1.teacher_id = t2.teacher_id
WHERE t1.name ='张老师') t2 ON t1.course_id = t2.course_id
WHERE t2.course_id ISNOTNULL) t2 ON t1.student_id = t2.student_id
WHERE t2.student_id ISNOTNULL;
3.18 查询学习 "01"课程但没有学习 "02"课程学生的学生编号以及"01"课程成绩
SELECT a.student_id,a.score FROM(SELECT*FROM score WHERE course_id ='01') a
LEFTJOIN(SELECT*FROM score WHERE course_id ='02') b ON a.student_id = b.student_id
WHERE b.student_id isNULL;
3.19 查询学习 "02"课程但没有学习 "01"课程学生的学生编号以及"02"课程成绩
SELECT a.student_id,a.score FROM(SELECT*FROM score WHERE course_id ='02') a
LEFTJOIN(SELECT*FROM score WHERE course_id ='01') b ON a.student_id = b.student_id
WHERE b.student_id isNULL;
3.20 查询选课的同学的学生姓名、选课总数、所有课程的成绩总和、课程平均分
SELECT t1.name,t2.course_num,t2.avg_score,t2.sum_score FROM student t1 RIGHTJOIN(SELECT student_id,count(course_id) course_num,sum(score) sum_score,avg(score) avg_score
FROM score GROUPBY student_id) t2 ON t1.student_id = t2.student_id;
SELECT*FROM(SELECT t1.name,t2.course_num,t2.avg_score,t2.sum_score FROM student t1 RIGHTJOIN(SELECT student_id,count(course_id) course_num,sum(score) sum_score,avg(score) avg_score
FROM score GROUPBY student_id) t2 ON t1.student_id = t2.student_id) t WHERE t.avg_score >60ORDERBY t.course_num DESC,t.sum_score DESC;
3.22 检索 “01” 课程分数小于 60 分的学生信息及"01" 课程分数,按照分数排降序
SELECT t1.student_id,t1.name,t1.name,t1.birthday,t2.score FROM student t1 LEFTJOIN(SELECT*FROM score WHERE course_id ='01'AND score <60) t2
ON t1.student_id = t2.student_id WHERE t2.student_id ISNOTNULLORDERBY t2.score DESC;
3.23 查询两门及其以上不及格课程的同学的姓名及其平均成绩
SELECT t1.name,t2.avg_score FROM student t1
RIGHTJOIN(SELECT*FROM(SELECT student_id,count(course_id) course_num,avg(score) avg_score
FROM score WHERE score <60GROUPBY student_id) t WHERE course_num >=2) t2
ON t1.student_id = t2.student_id;
3.24 查询没有学全所有课程的同学的编号 (包含无选课的同学)
--先从成绩表找出选了所有课的同学--因为hive的in和not in 不支持子查询,所以只能再通过left join一次成绩表找出没选所有课的同学--通过学生表左联成绩表找出一门课都没选的同学--union两张表 ->完成--(因为hive版本问题,我使用的版本好像不支union只支持union all,所以只能再在外面包一层DISTINCT来去重-- 这样会影响效率:DISTINCT在mapreduc阶段,map阶段不能用combine消重,数据输出为(key,value)形式然后在reduce阶段进行消重。--重点是,Hive在处理COUNT这种“全聚合(full aggregates)”计算时,它会忽略用户指定的Reduce Task数,而强制使用1。)SELECTDISTINCT(student_id)FROM(SELECT t1.student_id FROM score t1 LEFTJOIN(SELECT t.student_id FROM(SELECT student_id,count(course_id) num FROM score GROUPBY student_id) t
JOIN(SELECTcount(course_id) num FROM course) t2 ON t.num = t2.num) t2
ON t1.student_id = t2.student_id
WHERE t2.student_id ISNULLGROUPBY t1.student_id
UNIONALLSELECT t.student_id FROM(SELECT t1.student_id FROM student t1
LEFTJOIN score t2 ON t1.student_id = t2.student_id WHERE t2.student_id ISNULL) t) s
3.25 查询 1990 年出生的学生名单
SELECT*FROM student WHERE birthday LIKE'%1990%';
3.26 查询名字中含有「风」字的学生信息
SELECT*FROM student WHERE name LIKE'%风%';
3.27 查询「李」姓老师的数量
SELECTcount(1)FROM teacher WHERE name LIKE'李%';
3.28 查询至少有两门课与学号为" 01 "的学生所学相同的学生id
SELECT student_id FROM(SELECT t1.student_id,count(t1.course_id)AS num FROM score t1
LEFTJOIN(SELECT*FROM score WHERE student_id ='01') t2
ON t1.course_id = t2.course_id WHERE t1.student_id <>'01'GROUPBY t1.student_id) t
WHERE t.num >=2;
3.29 查询选修了全部课程的学生id的姓名
SELECT a.name FROM student a LEFTJOIN(SELECT*FROM(SELECT student_id,count(course_id) num1 FROM score GROUPBY student_id) t1
LEFTJOIN(SELECTcount(course_id) num2 FROM course) t2 ON t1.num1 = t2.num2 WHERE t2.num2 ISNOTNULL) b
ON a.student_id = b.student_id WHERE b.student_id ISNOTNULL;
3.30 查询和" 01 "号的同学学习的课程完全相同的其他同学的学生id
SELECT a.student_id FROM(SELECT t1.student_id,count(t1.course_id) num1 FROM score t1 LEFTJOIN(SELECT*FROM score WHERE student_id ='01') t2
ON t1.course_id = t2.course_id GROUPBY t1.student_id) a
LEFTJOIN(SELECTcount(1) num2 FROM score WHERE student_id ='01') b ON a.num1 = b.num2
WHERE b.num2 ISNOTNULLAND a.student_id <>'01';
SELECT*FROM(SELECT student_id,course_id,score,
rank()OVER(PARTITIONBY course_id ORDERBY score DESC)AS stu_rank
FROM score) t WHERE t.stu_rank <=3
3.34 查询出只选修两门课程的学生学号和姓名
SELECT t1.student_id,t1.name FROM student t1 LEFTJOIN(SELECT student_id,count(course_id) course_num FROM score GROUPBY student_id) t2
ON t1.student_id = t2.student_id WHERE t2.course_num =2
SELECT t1.student_id,t1.name,t2.avg_score FROM student t1 LEFTJOIN(SELECT student_id,avg(score) avg_score FROM score GROUPBY student_id) t2
ON t1.student_id = t2.student_id WHERE t2.avg_score >=85;
3.37 查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
SELECT a.name,b.score FROM student a LEFTJOIN(SELECT t1.student_id,t1.score FROM score t1 LEFTJOIN(SELECT*FROM course WHERE name ='数学') t2 ON t1.course_id = t2.course_id
WHERE t1.score <60AND t2.course_id ISNOTNULL) b ON a.student_id = b.student_id
WHERE b.score ISNOTNULL
3.38 查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
SELECT t1.student_id,t1.name FROM student t1 LEFTJOIN(SELECT*FROM score WHERE course_id ='01'AND score >=80) t2
ON t1.student_id = t2.student_id WHERE t2.student_id ISNOTNULL
3.39 查询选修「张三」老师所授课程的学生中,成绩最高的学生编号、课程编号及其成绩
SELECT a.student_id,a.course_id,a.score FROM score a RIGHTJOIN(SELECT t1.course_id FROM course t1 RIGHTJOIN(SELECT*FROM teacher WHERE name ='张老师') t2
ON t1.teacher_id =t2.teacher_id) b ON a.course_id = b.course_id
ORDERBY a.score DESCLIMIT1;
3.40 统计每门课程的学生选修人数(超过 5 人的课程才统计)
SELECT t1.course_id,t1.name,t2.count_num FROM course t1 LEFTJOIN(SELECT course_id,count(student_id) count_num FROM score
GROUPBY course_id HAVINGcount(student_id)>5) t2
ON t1.course_id = t2.course_id
3.41 查询该学生有不同课程但成绩相同的学生编号、课程编号、学生成绩
SELECT t.student_id,t.course_id,t.score FROM(SELECT t1.student_id,t1.course_id,t1.score FROM score t1 LEFTJOIN
score t2 ON t1.student_id = t2.student_id
AND t1.score = t2.score
WHERE t1.course_id <> t2.course_id) t
GROUPBY t.student_id,t.course_id,t.score