思路：将score做自连接
select *from studentwhere s_id in(select distinct s1.s_idfrom score s1join score s2on s1.s_id = s2.s_idwhere s1.c_id = "01"and s2.c_id = "02" ands1.s_score>s2.s_score)
结果：
2.查询"01"课程比"02"课程成绩低的学生的信息及课程分数（重点）:
思路：连接三张表
Select student.* ,a.s_score,b.s_score from student join score a on a.c_id = "01"join score b on b.c_id="02"Where a.s_id = student.s_id and b.s_id = student.s_id and a.s_score < b.s_score
3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩（重点）:
Select student.s_id,student.s_name,round(avg(score.s_score),1) from score join student on score.s_id = student.s_idGroup by student.s_id,student.s_nameHaving avg(score.s_score)>60
4.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩: (包括有成绩的和无成绩的)
Select student.s_id,student.s_name,round(avg(if(score.s_score==NULL,0,score.s_score)),1) from student left join score on student.s_id=score.s_idGroup by student.s_id,student.s_nameHaving avg(score.s_score)<60 or avg(score.s_score) is null
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩:
思路：将student表和score表左连接后查出存在有成绩在score表中的置为1，无成绩在score表中的置为0，方便统计选课数目，再以此表为基础分组聚合
Select t1.sid,t1.name,sum(choice),sum(sumscore)from(Select st.s_id as sid,st.s_name as name, if(sc.s_id is null,0,1) as choice,sc.s_score sumscorefrom student st left join score sc on st.s_id = sc.s_id) t1Group by t1.sid,t1.name
6、查询"李"姓老师的数量:
Select count(1) from teacher where t_name like '李%';
7、查询学过"张三"老师授课的同学的信息(重点):
Select st.* from teacher tjoin course c on t.t_id = c.c_idJoin score sc on sc.c_id = c.c_idJoin student st on st.s_id = sc.s_idWhere t.t_name = '张三'
8、查询没学过"张三"老师授课的同学的信息（重点）:
思路：在先将多表连接，找出学过张三课程的s_id,再和student表进行左连接，此时匹配的存在空值的就为想要的结果
Select st.* from student st left join (Select sc.s_id s_id from teacher t join course c on t.t_id=c.t_idJoin score sc on sc.c_id = c.c_id and t.t_name = '张三') t1On st.s_id = t1.s_idWhere t1.s_id is null;
9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息（重点）:
思路：将score表进行自连接，找出同时出现01和02课程的sid，再将其与student连接，找出student
Select st.* from score sc1 join score sc2 on sc1.s_id = sc2.s_idjoin student st on st.s_id = sc1.s_idWhere sc1.c_id = '01' and sc2.c_id= '02'
10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息（重点）:
Select stu.* from student stuJoin (select s_id from score where c_id = '01') t1on stu.s_id = t1.s_idLeft join(select s_id from score where c_id ='02') t2On Stu.s_id = t2.s_idWhere t2.s_id is null
11、查询没有学全所有课程的同学的信息（重点）:
思路：给student每一列都加上课程总数，然后再将score表中实际学生的选课数量和student做左连接，如果出现了null值，则有不满足选课条件的同学
Select stu.* from student stu Join (Select count(1) num1 from course) t1Left join (Select s_id,count(*) num2 from score group by s_id) t2On stu.s_id = t2.s_id and t1.num1 = t2.num2Where t2.s_id is null
12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息（重点）:
思路：先查询学号为01的同学所学课程,将其与student全匹配上，接着再将成绩表中的信息与student表进行连接，只要满足学号相等，课程号能匹配上说明选择了该课程
Select stu.* from student stujoin (Select c_id from score where s_id = '01') t1Join (select s_id,c_id from score) t2On t1.c_id = t2.c_id and stu.s_id = t2.s_idWhere stu.s_id != '01'Group by stu.s_id,stu.s_name,stu.s_birth,stu.s_sex
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息（重点）:
–备注:hive不支持group_concat方法,可用 concat_ws(’|’, collect_set(str)) 实现
Select * from student stu Join (Select concat_ws( '|',collect_set(c_id)) course2 from score sc group by sc.s_id having sc.s_id = '01') t1Join (select s_id,concat_ws( '|',collect_set(c_id)) course1 from score group by score.s_id ) t2On stu.s_id = t2.s_id and t1.course2 = course1
14、查询没学过"张三"老师讲授的任一门课程的学生姓名（重点）:
思路：先找出张三老师教授的课程直接与student做全匹配，此时再将student与score做左连接，如果score中出现了和全匹配表中相等的课程id，那么说明 该学生学了此课程，如果没有，则会为null
Select * from student stu Join (Select c.c_id cid from teacher t join course c on t.t_id = c.t_idWhere t.t_name = '张三') t1Left join score scOn sc.c_id = t1.cid and stu.s_id = sc .s_idWhere sc.c_id is null
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）:
思路：先查询有两门成绩不及格的同学学号,平均成绩，再和student表进行连接
Select stu.s_id,stu.s_name,t1.avgscore from student stu Join (Select s_id,avg(s_score) avgscore from score where s_score<60 group by s_id Having count(s_id)>=2) t1On stu.s_id = t1.s_id
16、检索"01"课程分数小于60，按分数降序排列的学生信息（和34类似）
Select stu.*,t1.s_score from student stu join(Select s_id,s_score from score where s_score<60 and c_id = '01') t1On stu.s_id = t1.s_idOrder by t1.s_score
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩（重点）:
Select sc.s_id,t1.s_score,t2.s_score,t3.s_score,avg(sc.s_score) from score sc Left join (select s_id,s_score from score where c_id = '01') t1 on sc.s_id = t1.s_idLeft join (select s_id,s_score from score where c_id = '02') t2 on sc.s_id = t2.s_idLeft join (select s_id,s_score from score where c_id = '03') t3 on sc.s_id = t3.s_idGroup by sc.s_id,t1.s_score,t2.s_score,t3.s_score
18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率(重点):
Select sc.c_id,c.c_name,max(sc.s_score) ,min(sc.s_score),round(avg(sc.s_score),2),round(sum(case when s_score>=60 then 1 else 0 end)/count(sc.c_id),2),round(sum(case when s_score>=60 and s_score<70 then 1 else 0 end)/count(sc.c_id),2),round(sum(case when s_score>=70 and s_score<80 then 1 else 0 end)/count(sc.c_id),2),round(sum(case when s_score>=90 then 1 else 0 end)/count(sc.c_id),2)from score sc join course c on sc.c_id = c.c_idGroup by sc.c_id,c.c_name
19、按各科成绩进行排序，并显示排名（重点row_number）:
注意：重点考察窗口函数
Select score.c_id,score.s_id,score.s_score ,row_number() over(partition by c_id order by s_score desc)from score
20、查询学生的总成绩并进行排名（重点）:
Select s_id, sum(s_score),row_number() over(order by sum(s_score) desc)from score Group by s_id
21、查询不同老师所教不同课程平均分从高到低显示:
Select c_id,avg(s_score) as avgscore from score group by c_id order by avgscore
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩:
Select t1.c_id,c1.c_name,t1.s_id,t1.s_score,t1.rk from student s1Join (Select c_id,s_id,s_score,dense_rank() over(partition by c_id order by s_score desc) rkfrom score) t1On s1.s_id = t1.s_idJoin course c1On t1.c_id = c1.c_idWhere t1.rk = 3 or t1.rk = 2
23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
Select t1.c_id,t1.bw100_85,t1.bw100_85/t5.s_sum,t2.bw85_70,t2.bw85_70/t5.s_sum,t3.bw70_60,t3.bw70_60/t5.s_sum,t4.bw60_0,t4.bw60_0/t5.s_sum from (Select c_id,sum(case when s_score<=100 and s_score>85 then 1 else 0 end) as bw100_85from scoreGroup by c_id) t1 join(Select c_id,sum(case when s_score<=85 and s_score>70 then 1 else 0 end) as bw85_70from scoreGroup by c_id) t2 on t1.c_id = t2.c_id Join (Select c_id,sum(case when s_score<=70 and s_score>65 then 1 else 0 end) as bw70_60from scoreGroup by c_id) t3 on t2.c_id = t3.c_idjoin(Select c_id,sum(case when s_score<=60 and s_score>0 then 1 else 0 end) as bw60_0from scoreGroup by c_id) t4 on t3.c_id = t4.c_id Join(Select c_id,count(*) as s_sum from score group by c_id) t5On t4.c_id = t5.c_id
24、查询学生平均成绩及其名次:
select s_id,avg(s_score) as avgscore,row_number() over() from score group by s_id order by avgscore
25、查询各科成绩前三名的记录
Select * from(Select c_id,s_id,s_score,row_number() over(partition by c_id order by s_score desc) as rkfrom score) t1Where t1.rk<=3
26、查询每门课程被选修的学生数:
Select c_id,count(*) from score group by c_id
27、查询出只有两门课程的全部学生的学号和姓名:
Select s_id,count(*) as sum_course from score group by s_idhaving sum_course=2
28、查询男生、女生人数:
Select s_sex,count(*) from student group by s_sex
29、查询名字中含有"风"字的学生信息:
Select * from student where s_name like "%风%";
30、查询同名同性学生名单，并统计同名人数:
Select * from student s1 joinStudent s2 on s1.s_name = s2.s_name and s1.s_sex = s2.s_sex where s1.s_id != s2.s_id
31、查询1990年出生的学生名单:
Select * from student where year(s_birth) = “1990”
32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列:
Select c_id,avg(s_score) as avg_score from score group by c_id Order by avg_score desc,c_id
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩:
Select s1.s_name,t1.* from student s1 join(Select s_id,avg(s_score) as avg_score from score group by s_idhaving avg_score>=85) t1On s1.s_id = t1.s_id
34、查询课程名称为"数学"，且分数低于60的学生姓名和分数:
select s1.s_id,s1.s_name from student s1 Join (select s_id,s_score,c_id from score where s_score<60) t1On s1.s_id = t1.s_idJoin (Select c_id from course where c_name = "数学") t2Where t2.c_id = t1.c_id
35、查询所有学生的课程及分数情况:
select s_id,sum(if(c_id="01",s_score,0)),sum(if(c_id="02",s_score,0)),sum(if(c_id="03",s_score,0))from scoreGroup by s_id
36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数:
select s1.s_id,t2.c_id,t2.c_name,t1.s_score from student s1 join(Select s_id,c_id,s_score from score where s_score>70) t1On s1.s_id = t1.s_idJoin (Select c_id,c_name from course) t2On t1.c_id = t2.c_id
37、查询课程不及格的学生:
select s1.s_id,s1.s_name from student s1 Join(Select distinct s_id from score where s_score<60) t1On s1.s_id = t1.s_id
38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名:
Select s1.s_id,s1.s_name from student s1 join(Select s_id from score where c_id = "01" and s_score>=80) t1On s1.s_id = t1.s_id
39、求每门课程的学生人数:
Select c_id,count(*) from score group by c_id
40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩:
Select s1.c_id,s1.s_id,s1.s_score from score s1 join (Select c_id,c1.t_id from course c1 join(Select t_id from teacher where t_name = "张三") t1On t1.t_id = c1.t_id) tt1On s1.c_id = tt1.c_idOrder by s1.s_score descLimit 1;
41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩:
Select distinct s1.* from score s1 joinScore s2On s1.s_score = s2.s_scoreWhere s1.c_id !=s2.c_id
42、查询每门课程成绩最好的前三名:
Select t1.c_id,t1.s_score,t1.rk from (Select c_id,s_score,row_number() over(partition by c_id order by s_score desc) as rkfrom score) t1Where t1.rk<=3
43、统计每门课程的学生选修人数（超过5人的课程才统计）: – 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
Select c_id,count(*) as rk from score group by c_id order by rk desc,c_id
44、检索至少选修两门课程的学生学号:
Select s_id,count(*) from score group by s_idHaving count(*)>=2
45、查询选修了全部课程的学生信息:
select s_id,count(c_id) as cn from score group by s_idHaving cn in(Select count(c_id) as rn from course)
46、查询各学生的年龄(周岁): – 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select s_name,if(month(current_date)
47、查询本周过生日的学生:
select * from student where weekofyear(CURRENT_DATE)+1 =weekofyear(s_birth);
48、查询下周过生日的学生:
select * from student where weekofyear(CURRENT_DATE)+1 =weekofyear(s_birth);
49、查询本月过生日的学生:
select * from student where MonTH(CURRENT_DATE) =MonTH(s_birth);
50、查询12月份过生日的学生:
select s_name,s_sex,s_birth from student where substring(s_birth,6,2)='12';