查询练习3

查询练习3

1、查询姓“王”的并且分数高于70分的学生信息

--模糊查询,条件筛选
select * from Student where RealName like '王%' and Score > 70

2、查询“刑天”同学的班级名称和老师名字

--三表联合查询
select c.ClassName,t.TeacherName from Student s inner join Classes c
on s.ClassId=c.Id inner join Teacher t
on c.TeacherId=t.Id where RealName='刑天';

3、现在将分数列换一种显示方式，分数小于60的显示不及格，
分数在60到80分(不包括)的显示良好，大于等于80分的显示优秀。

--分等级显示
select *,case 
when Score<60 then '不及格'
when Score<80 then '良好'
when Score>=80 then '优秀'
else '无' end as '成绩'
from Student;

4、现在将分数列换一种显示方式，分数小于60的显示不及格，分数在60到80分(不包括)的显示良好，大于等于80分的显示优秀。 并且显示各个分数段的人数

--在第3题基础上增加难度,将查询结果当作一个结果集进行查询
select a.CJ,COUNT(1) from 
(select *,case when score<=60 then '不及格'when score <80 then '良好'when score >=80 then '优秀' end as 'CJ' from Student) A 
group by a.CJ

5、查询各班级名称以及对应的学生人数，按照班级人数降序排序。

--首先两表连接,再根据班级名称分组,再调用系统内置的分组统计函数count,最后在进行排序 
select c.ClassName,count(1) as '人数' from Student s inner join Classes c 
on s.ClassId=c.Id group by c.ClassName order by count(1) desc;

--先查询学生表,根据班级id进行分组,调用函数count并进行order by排序,最后相关子查询
select COUNT(1) 人数,(select ClassName from Classes where 
Student.ClassId=Classes.Id) 班级 from Student 
group by ClassId order by COUNT(1) desc;

6、查询兴趣爱好种类不少于两种的班级信息

--COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目
--两表联查,调用count函数,having过滤
select c.ClassName,COUNT(distinct Likes) Likes from Student s left join Classes c
on s.ClassId=c.Id group by c.ClassName having COUNT(distinct Likes)>=2;
--查询学生表,根据班级id分组,调用函数count并进行having过滤,最后相关子查询
select  count(distinct Likes) likes ,(select ClassName from Classes where  
Classes.Id=Student.ClassId) ClassName from student 
group by ClassId having COUNT(distinct Likes)>=2

7、修改学号为”2019005”的姓名为“秦明”，性别改为“男“

----语法:UPDATE 表名 SET COLUMN1 = 新值 1,COLUMN2=新值 1  WHERE 条件
update Student set RealName='秦明',Sex='男'where StuCode='2019005';

8、删除学生表中id为7的学生信息

delete student where Id=7; 

9、查询没有带班的老师信息

select * from Classes c right join Teacher t on c.TeacherId=t.Id where c.Id is null;
select * from Teacher t left join Classes c on t.Id = c.TeacherId where c.TeacherId is null
select * from Teacher where id not in(select TeacherId from Classes);

10、查询”湛燕”老师所带的班级名称，以及每个班级的学生人数

--三表联查,查询湛燕老师的班级,根据班级进行分组,再调用函数count
select c.ClassName '班级',COUNT(StuCode) '人数' from Student s right join Classes c
on s.ClassId=c.Id right join Teacher t
on c.TeacherId=t.Id 
where t.TeacherName='湛燕' 
group by c.ClassName;

--先查询湛燕老师班级id,根据id再查询所教班级的信息,相关子查询根据班级id分组查出学生人数
select *,(select COUNT(1) from Student group by ClassId having Student.ClassId=Classes.Id) 人数 
from Classes where TeacherId=(select Id from Teacher where TeacherName='湛燕');

11、查询所带学生数量大于2的教师姓名

--三表联合查询,根据教师姓名进行分组,最后筛选出来数量大于2的教师
select t.TeacherName,COUNT(1) 人数 from Student s inner join Classes c
on s.ClassId=c.Id inner join Teacher t
on c.TeacherId=t.Id 
group by t.TeacherName
having COUNT(1)>2

12、查询所有的学生姓名以d及对应的组长姓名

--自连接
select s.RealName '组长',s2.RealName '组员' from Student s left join Student s2 
on s.Id=s2.LeaderId;

13、查询学生分数都高于60的班级名称

--内连接,根据班级名分组,再进行过滤成绩
select c.Id,c.ClassName from Student s inner join Classes c 
on s.ClassId=c.Id group by c.Id,c.ClassName having min(Score)>60;

--查询学生表 根据班级id分组 并查询最小成绩大于60的班级id,最后查询根据班级id查询班级名称
select * from Classes where Id in(select ClassId from Student group by ClassId having MIN(Score)>60);

--查询学生表 根据班级id分组 并查询最小成绩大于60的班级id,班级名称利用相关子查询
select (select ClassName from Classes where Classes.Id=Student.ClassId) from Student group by ClassId having MIN(Score)>60

14、请为各班学生生成一个班内排名列

select *,Row_number() over(partition by classid order by score desc) from Student  

15、请查询所有学生信息所在班级的班级人数

select c.ClassName, count(1) '人数'  from Student s inner join Classes c
on s.ClassId=c.Id
group by c.ClassName;

select (select ClassName from Classes where 
Classes.Id=Student.ClassId),COUNT(StuCode) '人数' from Student group by ClassId

16、请描述一下nvarchar(10)和varchar(10)的区别。

varchar(10)  十个字节(可以写5个汉字，10个英文字母、数字、字符)
nvarchar(10) 十个字符(字母、数字、字符、汉字都可以写10个)

17、请描述一下内连接和左外连接的区别

内连接：显示共有的数据
左外连接：保证查出左表的所有数据，右表无法匹配的自动用null显示

查询练习1
查询练习2
查询练习4
查询练习5