查询练习4

查询练习4

1、查询’2班’的学生信息

--两表连接，最后进行条件筛选
select * from Student s inner join Classes c on s.ClassId=c.Id 
where c.ClassName='2班';

--先查询所在班级id,再在学生表中查询班级id
select * from Student where ClassId=(select Id from Classes where ClassName='2班');

2、查询比’刑天’同学年龄大的学生信息

select * from Student where Birthday<(select Birthday from Student where RealName='刑天');

3、查询学号为’2019004’的学生所在班级的最高分

select MAX(Score) as '最高分' from Student where ClassId =(select ClassId from Student where StuCode='2019004');

4、查询班级人数大于2的班级名称

--连接 分组 过滤
select c.ClassName,COUNT(1) as '人数' from Student s inner join Classes c 
on s.ClassId=c.Id group by c.ClassName having COUNT(1)>2

--先查询学生表并且分组 筛选出人数大于2的classId,最后当作条件查询班级表
select ClassName from Classes where Id=(select ClassId from Student 
group by ClassId having count(1)>2)

--查询学生表 根据classId分组 过滤人数，相关子查询查出班级名称
select (select ClassName from Classes where Classes.Id=Student.ClassId) as '班级名称',COUNT(1) '人数' 
from Student group by ClassId having COUNT(1) >2

5、查询和学号’’2019004’’,’’2019006’’两位同学同一个班级的学生信息

--主要练习in的使用

select s.* from Student s inner join Student s2 on s.ClassId=s2.ClassId 
where s.StuCode in('2019004','2019006');

select * from Student where ClassId in(select ClassId from Student
where StuCode in('2019004','2019006'));

6、查询爱好是”篮球”的学生信息以及班级名称

select s.*,c.ClassName from Student s inner join Classes c on s.ClassId=c.Id 
where Likes='篮球'

select *,(select ClassName from Classes where Classes.Id=Student.ClassId) className
from Student where Likes='篮球'

7、查询班级‘所有学生’成绩‘都’高于‘2班’的‘所有学生成绩’的班级名称

--先查询出来2班的最高成绩,再查询学生表中小于2班最高成绩的classId,两表连接查询,查找classId 不再小于2班最高成绩的班级Id中
select distinct c.ClassName from Student s inner join Classes c on s.ClassId=c.Id where c.Id not in 
(
select ClassId from Student where score<
	(select  max(Score) from Student where ClassId=(select Id from Classes where ClassName='2班'))
);

--先查询2班的id,根据id在学生表中查询2班的最高成绩,再查询学生表 并根据classId分组 having过滤最小成绩大于2班的最高成绩,相关子查询查询出来班级名称
select (select ClassName from Classes where Classes.Id=Student.ClassId) as '班级名称' from Student 
group by ClassId  having MIN(Score) >(select MAX(Score) from student where ClassId=(select Id from Classes where ClassName='2班'))

8、查询学生信息，以及对应的班级名称

select s.*,c.ClassName from Student s inner join Classes c on s.ClassId=c.Id;

select *,(select ClassName from Classes where Classes.Id=Student.ClassId) className from Student

9、查询每个班学生分数最高的学生信息

select c.ClassName '班级名称',MAX(Score) '最高成绩' from Classes c 
inner join Student s on c.Id=s.ClassId group by c.ClassName

select MAX(Score) '最高成绩',(select ClassName from Classes where 
Classes.Id=Student.ClassId) '班级名称' from Student group by ClassId

10、查询学生信息，以及学生总人数

select *,(select count(*)  from Student ) as '学生人数' from Student

11、查询大于本班平均分的学生信息

select * from Student s where s.Score>
(select AVG(Score) from Student s1 where s.ClassId=s1.ClassId group by s1.classId);

查询练习1
查询练习2
查询练习3
查询练习5