HQL经典50题

HQL 练习一

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数:
获取01 的课程 02的课程

select t1.s_id 
from student stu join
(select s_id from score where c_id='01') t1
on stu.s_id=t1.s_id
join 
(select s_id from score where c_id='02') t2
on t1.s_id=t2.s_id
where t1.s_score>t2.s_score or s1.s_score=null;
----------------------------------------
select stu.s_id  
from student stu
join score s on stu.s_id =s.s_id and s.c_id='01'
left join score s1 on stu.s_id=s1.s_id and s.c_id ='02'
where s.s_score > s1.s_score

2.查询"01"课程比"02"课程成绩低的学生的信息及课程分数:

select  student.*,t.* from student
join
(select t1.s_id,t1.s1,t2.s2 from
(select s_id,s_score s1 from score where c_id=01)
left join
(select s_id,s_score s2 from score where c_id=02)
on t1.s_id=t2.s_id
where t1.s1<t2.s2) t
on student.s_id=t.s_id
---------------------------
select stu.* ,s1.s_score
from student stu
join score s1 on stu.s_id=s1.s_id and s1.c_id='01'
join score s2 on stu.s_id=s2.s_id and s2.c_id='02'
where s1.s_score<s2.s_score

3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩:

select stu.s_id,s_name
from student stu
left join score s on stu.s_id=s.s_id
group by stu.s_id,s_name
having avg(s_score)>=60

4。查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩:
– (包括有成绩的和无成绩的)
也是学生表中那个没有考试的也要输出来 以 studnet表为左 进行左连接 再取个空
可以查出有成绩的和没成绩的，进行 union all 并起来
也可以使用子查询,查询没有成绩的学生

select s.s_id,stu.s_snme,round(avg(s_score),2) avgs
from student stu join score s on stu.s_id=s.s_id
group by s.s_id,stu.s_snme
having avgs<=60;
--left join
select stu.s_id,stu.s_name,avg(s_score), null
from student stu  left join score s
where s.s_id=null 
--not in
select stu.s_id,stu.s_name,avg(s_score), null
from student stu  where stu.s_id not in (select s_id form score);
-- not exists
select stu.s_id,stu.s_name,avg(s_score), null
not exists当条件成立时  不输出，当条件不成立时 ，左边才输出
exists 当条件成立时 输出，当条件不成立时，不输出
from student stu  where not exists (select 1 from score s where s.s_id=stu.s_id)

with a as(select student_id,sum(score)/3 avgscore from score group by student_id)
select s.student_id,s.student_name,coalesce(avgscore,0)
from student s left join a on s.student_id=a.student_id
where coalesce(avgscore,0)<60

-----------------------------------------------
select y1.*,y2.avgs from 
(select s_id,s_name from student) y1
left join
(select s_id,avg(s_score) avgs from score group by s_id  ) y2
on y1.s_id=y2.s_id
where avgs<60 and avgs is null

5.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩:
可以group by s_id,s_name 都是唯一的，可以一起groupby

select stu.s_id,count(1),sum(s_score)
from student stu join score s on stu.s_id=s.s_id
group by stu.s_id

6.查询"李"姓老师的数量:

select count(1) from teacher where t_name like '李%'

7.查询学过"张三"老师授课的同学的信息:

select stu.*
from student stu
join score s on stu.s_id=s.s_id
join course c on s.c_id=c.c_id
join teacher t on t.t_id=c.t_id
where t.t_name='张三'

8.查询没学过"张三"老师授课的同学的信息:

-- not in
select stu.*
from student stu
where stu.s_id not in
(select stu.s_id
from student stu
join score s on stu.s_id=s.s_id
join course c on s.c_id=c.c_id
join teacher t on c.t_id=t.t_id
where t_name="张三")
--not exists
select 
from student stu
where not exists (
select 1
from score s ,course c ,teacher t where stu.s_id=s.s_id and s.c_id=c.c_id and c.t_id=t.t_id and  t_name="张三")

----------------------------------------------
select stu.*
from student stu
where not exists(
select 1 from score s,course c,teacher t where stu.s_id=s.s_id and c.c_id=s.c_id and c.t_id=t.t_id and t_name='张三'
)

9.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息:
直接使用join连接即可
先过滤 在查询

with t1 as
(select stu.*
from student stu
join score s on stu.s_id=s.s_id where c_id='01')
select t1.* from t1 join score s on s.s_id=t1.s_id where c_id='02'

10.查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息:

-- not exists
with t1 as
(select stu.*
from student stu
join score s on stu.s_id=s.s_id where c_id='01')
select t1.* from t1
where not exists(select 1 from score s1 where  t1.s_id=s1.s_id  and c_id='02' )

11、查询没有学全所有课程的同学的信息:

select stu.s_id,count(1) num
from student stu
left join score s on stu.s_id=s.s_id
group by stu.s_id
having num<3

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息:

with t1 as
(select * from score s1 where  exists
(select 1 from score s2  where s_id='01' and s1.c_id=s2.c_id))
select stu.* from t1 join student stu on t1.s_id=stu.s_id

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息:

with t1 as
(select * from score s1 where  exists
(select 1 from score s2  where s_id='01' and s1.c_id=s2.c_id)),
t2 as 
(select stu.* from t1 join student stu on t1.s_id=stu.s_id)
select t2.s_id,t2.s_name from t2 group by t2.s_id,t2.s_name having count(t2.s_id)=3

14、查询没学过"张三"老师讲授的任一门课程的学生姓名:

select stu.*
from student stu
where not exists(select 1 from score s,course c,teacher t where stu.s_id=s.s_id and c.c_id=s.c_id and c.t_id=t.t_id and t_name='张三')

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩:

select t.s_id,s_name,num,avgs from
(select s_id ,count(1) num from score where s_score<60
group by s_id
having num>=2) t1
join 
(select s_id, avg(s_score) avgs from score
group by s_id) t2
on t1.s_id=t2.s_id
join student t
on t.s_id=t1.s_id；

------------------------------------
select stu.s_id,s_name,count(1) num
from student stu
left join score s on stu.s_id=s.s_id
where s_score<60
group by stu.s_id,s_name
having num>=2

16、检索"01"课程分数小于60，按分数降序排列的学生信息:

select stu.*
from student stu
join (select * from score where c_id='01' and s_score <60 order by s_score desc) t
on stu.s_id=t.s_id

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩:

select c_id,c_score avg(s_score) over( partition by c_id) as avgs
from score
order by avgs

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率:

select cid ,cname，
concat(100 *(sum(case when score <60 then 1 else 0 end)/sum(1)),"%") miss,
concat(100 *(sum(case when score >=60 then 1 else 0 end)/sum(1)),"%") pass,
concat(100 *(sum(case when score >=60 and score <70 then 1 else 0 end)/sum(1)),"%") medi,
concat(100 *(sum(case when score >=70 and score <85 then 1 else 0 end)/sum(1)),"%") grea,
concat(100 *(sum(case when score >=85 then 1 else 0 end)/sum(1)),"%") wond
from score left join course on cid=coid group by cid,cname;

19、按各科成绩进行排序，并显示排名:

– row_number() over()分组排序功能(mysql没有该方法)
select c_id,
c_name,row_number() over(partition by c_id 
order by s_score desc) as num
from score;

20、查询学生的总成绩并进行排名:

select s_id ,
sum(s_score) over (partition by s_id ) as summ,
row_number()  over(partition by s_id order by summ desc )
from score join student on score.s_id=student.s_id

21、查询不同老师所教不同课程平均分从高到低显示:

select t_id,s.c_id ,avg(s_score)  avgs from score s join teacher t on s.t_id=t.t_id
group by s.c_id
order by  avgs

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩:

select t.*, t1.s_score from student t
join(
select s_id,c_id,
row_number() over(partition by c_id order by s_score desc) as num
from score) t1
on t.s_id=t1.s_id
where num=2 or num=3

23、***统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

24、查询学生平均成绩及其名次:

select s_id
avg(s_score)  over(partition by s_id) as avgs
row_number() over(order by avgs desc) as row
from score  

25、查询各科成绩前三名的记录

select c_id,c_name,
row_number() over(partition by c_id order by s_score desc)
from score;

26、查询每门课程被选修的学生数:

select c_id,count(s_id) from student t
join score s on t.s_id =s.s_id
group by c_id;

27、查询出只有两门课程的全部学生的学号和姓名:

select t.s_id,s_name,count(c_id)  ccn from student t
join score s on t.s_id=s.s_id
where ccn =2;

28、查询男生、女生人数:

select s_sex,count(s_id) from student
group by s_sex;

29、查询名字中含有"风"字的学生信息:

seelct * from student where s_name like '%风%'；

30、查询同名同性学生名单，并统计同名人数:

select * from (
select s_name, count(s_id) num from student group by s_name,s_sex) t
where num>1;

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

select * from student where year(s_birth)=1990;

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

with
t1 as (select c_id ,avg(s_score) avgs from score group by c_id)
select * from t1 order by avgs desc,c_id;

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

with
t1 as (select s_id,avg(s_score) avgs from score group by s_id )
select t1.s_id,t.s_name,avgs from t1 join student t on t1.s_id=t.s_id
where avgs>=85;

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

select s_name,s_score from 
 (select * from course where c_name='数学' ) t1
join (select * from score where s_score <60) t2
on t1.c_id=t2.c_id
join student t
on t.s_id=t2.s_id;

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

select * from studnet t join score s
on t.s_id=s.s_id;

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

select s_name,c_name,s_score from 
(select s_id ,count(1) num from score group by s_id) t2
join 
(select c_name,s_score,s_id  from  score s join course c on s.c_id=c.c_id where s_score>70) t1
on t2.s_id=t1.s_id
join student t
on t.s_id=t1.s_id
where num>=3;

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

 select * from score where s_score <60;

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

select tt.s_id,tt.s_name from
(select * from score where c_id=01 and s_score>80) t
join student tt
on t.s_id=tt.s_id;

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

select c_id ,count(1) from score group by c_id;

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

select t.* from
(select * from teacher t join course c on t.t_id=c.t_id
where t_name='张三') w
join score s
on w.c_id=s.c_id
join student t
on s.t_id=c.s_id
order by s_score desc 
limit 1;

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

select t1.s_id,t1.c_id,t1.s_score from 
(select s_id,c_id,s_score from score ) t1
join
(select s_id,c_id,s_score from score ) t2
on t1.s_id =t2.s_id
where t1.s_score=t2.s_score and t1.c_id !=t2.c_id;

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

with
t1 as
(select s_id ,c_id,s_score
row_number() over(partition by c_id order by s_score) as num
from score)
select * from t1 where num<=3;

43、统计每门课程的学生选修人数（超过5人的课程才统计）:
– 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select c_id,count(1) num from score group by c_id 
having num>5
order by num desc,c_id;

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

with
t1 as
(select s_id ,count(1) num  from score group by s_id)
select * from t1 where num >=2;

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

with
t1 as
(select s_id ,count(1) num  from score group by s_id)
select * from t1 where num =3;

46、查询各学生的年龄(周岁):
– 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

select s_id,
case 
when month(current_date())<month(s_birth) then year(current_date())-year(s_birth)-1
when month(current_date())=month(s_birth)  and day(current_date())<day(s_birth) then year(current_date())-year(s_birth)-1
else year(current_date())-year(s_birth)
end  age
from student;

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

select * from student where s_birth>date_sub(current_date(),datediff(next_day(current_date(),'su'),current_date())-1) 
and s_birth<date_add(current_date(),7-datediff(next_day(current_date(),'su'),current_date()));

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

select * from student where s_birth>date_add(date_sub(current_date(),datediff(next_day(current_date(),'su'),current_date())-1) ,7)
and s_birth<date_add(date_add(current_date(),7-datediff(next_day(current_date(),'su'),current_date())),7);

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

select * from student where  month(s_birth)=month(current_date());

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

select * from student where month(s_birth)=12;