大数据基础：HQL 入门必练50题_3

大数据基础：HQL 入门必练50题（31-50题）

31、查询1990年出生的学生名单:

--方法一：以出生时间前4位作为年过滤
select sname,substr(sbirth,1,4) year from student group by sname,substr(sbirth,1,4)
having year = 1990;

--方法二：直接有year函数提取出生年过滤
select sname,year(sbirth) year from student group by sname,year(sbirth)
having year = 1990;

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列:

select cid,avg(sscore) avg_score from score group by cid 
order by avg_score desc,cid asc;

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

select a.sid,sname,avg_score from student a
inner join
(select sid,avg(sscore) avg_score from score group by sid
having avg_score > 85) b
on a.sid = b.sid;

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数:

select sname,sscore from student t1
inner join
(select sid,cname,sscore from course a
inner join score b 
on a.cid = b.cid where cname = '数学' and sscore < 60) t2
on t1.sid = t2.sid;

35、查询所有学生的课程及分数情况:

select * from student a 
left join score b on a.sid = b.sid
left join course c on b.cid = c.cid;--运行快，数据冗余

select a.sid,sname,cname,sscore from student a 
left join score b on a.sid = b.sid
left join course c on b.cid = c.cid;--只保留题目需要的有效信息（涉及全表查询，工作中一般不用，不效率）

36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数:

select sname,cname,sscore from student a 
left join score b on a.sid = b.sid
left join course c on b.cid = c.cid
where sscore > 70;

37、查询课程不及格的学生:

--1.不考虑无成绩的人
select sname,cname,sscore from student a 
inner join score b on a.sid = b.sid
inner join course c on b.cid = c.cid
where sscore < 60;--显示不及格学生姓名、课程名称和分数

select distinct sname from student a 
inner join score b on a.sid = b.sid
inner join course c on b.cid = c.cid
where sscore < 60;--只输出名字，去重

--2.考虑无成绩的人,将其算作不不及格
select sname,cname,sscore from student a 
left join score b on a.sid = b.sid
left join course c on b.cid = c.cid
where sscore < 60 or sscore is null;
--显示不及格学生姓名、课程名称和分数

--3.只输出名字，去重
select distinct sname from student a 
left join score b on a.sid = b.sid
left join course c on b.cid = c.cid
where sscore < 60 or sscore is null;

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名:

select a.sid,sname from student a 
inner join score b on a.sid = b.sid 
where sscore >= 80 and cid = 1;

39、求每门课程的学生人数:

select cname,cn from course a inner join
(select cid,count(1) cn from score group by cid) b 
on a.cid = b.cid;

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩:

select t_1.*,max_score from student t_1 inner join
(
select sid,max_score from score t1,
(select max(sscore) max_score from
(select sid,sscore,tname from score b 
inner join course c on b.cid = c. cid
inner join teacher d on c.tid = d.tid where tname = '张三') a) t2 
where t1.sscore = t2.max_score
) t_2 
on t_1.sid = t_2.sid;

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩:

--我认为需求有歧义，是1.一个学生不同课程成绩相同，还是2.两个学生不同学科成绩相同？？？？？？？不知道各位大佬是不是有别的见解

--1.一个学生不同课程成绩相同，需要应用满外连接
select * from
(select * from score where cid=1) a
full join
(select * from score where cid=2) b on a.sid = b.sid
full join
(select * from score where cid=3) c on c.sid = b.sid
where a.sscore = b.sscore or a.sscore = c.sscore or b.sscore = c.sscore;

select distinct a.sid,a.cid,a.sscore from score a,score b  
where a.sid=b.sid and a.cid != b.cid and a.sscore=b.sscore;

--2.此代码同时输出了两种歧义
select distinct a.sid,a.cid,a.sscore from score a,score b
where a.cid <> b.cid and a.sscore=b.sscore;

42、查询每门课程成绩最好的前三名:

select a.sid,sname,sbirth,ssex,c.cname,b.sscore from student a
inner join
(
select t1.* from
    (select * from score where cid=1 order by sscore desc limit 3) t1
union all 
select t2.* from
    (select * from score where cid=2 order by sscore desc limit 3) t2
union all 
select t3.* from
    (select * from score where cid=3 order by sscore desc limit 3) t3
) b 
on a.sid = b.sid
inner join
course c on b.cid = c.cid;

43、统计每门课程的学生选修人数（超过5人的课程才统计）:

– 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select cid,count(1) cn from score group by cid 
having cn > 4 order by cn desc,cid asc;

44、检索至少选修两门课程的学生学号:

select sid,count(1) cn from score group by sid 
having cn >=2;

45、查询选修了全部课程的学生信息:

select * from student a
inner join
(select sid,count(1) cn from score group by sid 
having cn =3) b
on a.sid = b.sid;

46、查询各学生的年龄(周岁):

– 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

--方法一：
select sname,sbirth,
(
year(current_date)-year(sbirth)-
      (case when month(current_date) < month(sbirth) then 1
      when month(current_date) = month(sbirth) and day(current_date) < day(sbirth) then 1
      else 0 end)
) age
from student;--year函数


--方法二：
select sname,sbirth,
(
year(current_date)-substr(sbirth,1,4)-
      (case when month(current_date) < substr(sbirth,6,2) then 1
      when month(current_date) = substr(sbirth,6,2) and day(current_date) < substr(sbirth,9,2) 
      then 1
      else 0 end)
) age
from student;--substr函数，输出结果age为带一位小数的数字，可自行调试优化

47、查询本周过生日的学生:

--(2021-4-8)
select sname,ssex,sbirth from student
    where substr(sbirth,6,2)='04'
    and substring(sbirth,9,2)>=5
    and substring(sbirth,9,2)<=11;

48、查询下周过生日的学生:

--(2021-4-8)
select sname,ssex,sbirth from student
    where substr(sbirth,6,2)='01'
    and substring(sbirth,9,2)>=12
    and substring(sbirth,9,2)<=18;

49、查询本月过生日的学生：

--(2021-4-8)
select sname,ssex,sbirth from student where substr(sbirth,6,2)='04';

50、查询12月份过生日的学生:

select sname,ssex,sbirth from student where substr(sbirth,6,2)='12';