常用SQL 语句的练习，包括查询，聚合的一些用法

注：仅作为对自己学习SQL的一些记录，由浅及深，后续会继续补充，本文主要是在SQL Server中实践
** 本文练习的数据是创建了四张表，Student,Score，Course,Teacher,方便做表的交叉查询，表间通过主外键链接，也希望对入门SQL的同学有一定参考价值

** 本文在题目的筛选上也参考了知乎用户：猴子聊数据分析，感谢大神给了我练习的一些思路

1.建表过程，比较简单，这里注意的是，每个定义列的数据类型，以及定义主键

## 创建 Student 表
Create table Student(
	s_id varchar(50) ,
	s_name varchar(20) not null ,
	s_birth varchar(20) not null,
	s_sex varchar(10) not null,
	primary key (s_id)
	);
## 创建Course 表
Create table Course(
	c_id varchar(50) primary key,
	c_name varchar(20) not null ,
	t_id varchar(50) not null,
	);
## 创建Teacher表
Create table Teacher(
	t_id varchar(50) primary key,
	t_name varchar(20) not null ,
	);
## 创建Score 表
Create table Score(
	s_id varchar(50) ,
	c_id varchar(50) not null ,
	s_score float not null,
	);

2.建好表，就要添加数据啦，也很简单，主要是insert into 的用法

## 在Student表插入数据
insert into Student values(\'01\',\'小王\',\'1995-01-01\',\'女\')
insert into Student values(\'02\',\'小郑\',\'1995-01-02\',\'男\')
insert into Student values(\'03\',\'小陈\',\'1994-03-01\',\'女\')
insert into Student values(\'04\',\'小树\',\'1992-06-01\',\'女\')
insert into Student values(\'05\',\'小新\',\'1996-02-01\',\'男\')
insert into Student values(\'06\',\'小李\',\'1994-05-01\',\'女\')
## 在Course表插入数据
insert into Course values(\'01\' , \'语文\' , \'02\')
insert into Course values(\'02\' , \'数学\' , \'01\')
insert into Course values(\'03\' , \'英语\' , \'03\')
## 在Teacher表插入数据
insert into Teacher values(\'01\' , \'陈真\')
insert into Teacher values(\'02\' , \'王九\')
insert into Teacher values(\'03\' , \'谢逊\')
## 在Score表插入数据
insert into Score values(\'01\' , \'01\' , \'70\')
insert into Score values(\'01\' , \'02\' , \'75\')
insert into Score values(\'01\' , \'03\' , \'85\')
insert into Score values(\'02\' , \'01\' , \'65\')
insert into Score values(\'02\' , \'02\' , \'80\')
insert into Score values(\'02\' , \'03\' , \'95\')
insert into Score values(\'03\' , \'01\' , \'95\')
insert into Score values(\'03\' , \'02\' , \'100\')
insert into Score values(\'03\' , \'03\' , \'89\')
insert into Score values(\'04\' , \'01\' , \'76\')
insert into Score values(\'04\' , \'02\' , \'88\')
insert into Score values(\'04\' , \'03\' , \'90\')
insert into Score values(\'05\' , \'01\' , \'56\')
insert into Score values(\'05\' , \'02\' , \'44\')
insert into Score values(\'05\' , \'03\' , \'59\')
insert into Score values(\'06\' , \'01\' , \'89\')
insert into Score values(\'06\' , \'02\' , \'56\')
insert into Score values(\'06\' , \'03\' , \'77\')

3.开始进入正题，想几个题目，接下来就是看题解题的过程

1、选出三门成绩都大于85分的同学

# 1、第一种解题思路：通过将成绩大于85分的转化为1，其他转化为0，如果都大于85，则结果为3
select s_name 
from 
Student where s_id in(
	select s_id 
	from Score
	group by s_id   --切记要使用group by 因为下面用到聚合
	having count(s_score) = sum(
	case 
	when s_score >85 then 1 
	when s_score <=85 then 0
	end
)
)
# 2、第二种思路：通过在每个学生的，三门成绩中，只要最低分的那一门成绩大于85，则三科成绩一定大于85
select s_name 
from 
Student where s_id in(
	select s_id 
	from Score
	group by s_id   --切记要使用group by 因为下面用到聚合
	having min(s_score) >85
)
#3、第三种思路：思路转化，求三门成绩大于85，那么只要一门低于85的学生，则不是我们的目标,group by 不能用where,要用having
select s_name 
from 
	Student where s_id not in(
	select s_id 
	from Score   --切记要使用group by 因为下面用到聚合
	where  s_score <85
)

2、查询01课程分数大于02课程的学生名字、性别和成绩

## 解题思路：首先将选出科目01 的成绩新建一列，然后挑出科目为02 的一列，然后将这两列的数据进行合并成一个表，这样就能对比这两列的数据了，这里比较关键的一步是对两个select语句进行left join ,当然两列的都有成绩时可以进行比较，当一列成绩为空的时候，也是我们要考虑的
select Score.s_id ,s_name ,c_id,s_sex,s_score
from Score , Student
where Score.s_id = Student.s_id
and Score.s_id  in 
	(
	select a.s_id from
		(select s_id,
		s_score as a_score 
		from Score 
		where c_id = 01) a
	left  join
		(select s_id,
		s_score as b_score
		from Score 
		where c_id = 02 ) b
	on b.s_id = a.s_id
	where a_score > b_score or a_score is not null and b_score is null
	)

## 如果是求课程01 小于课程02 ，的学生信息，则是用right join
select Score.s_id ,s_name ,c_id,s_sex,s_score
from Score , Student
where Score.s_id = Student.s_id
and Score.s_id  in 
	(
	select a.s_id from
		(select s_id,
		s_score as a_score 
		from Score 
		where c_id = 01) a
	left  join
		(select s_id,
		s_score as b_score
		from Score 
		where c_id = 02 ) b
	on b.s_id = a.s_id
	where a_score < b_score or b_score is not null and a_score is null
	)

3、求平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

# 1、解题思路：先找出平均成绩低于60分的同学的编号，再进行连接查询
select Score.s_id ,s_name ,
avg(s_score) as avg_score
from Score,Student
where Score.s_id = Student.s_id
	and Score.s_id in
		(
		select s_id from
		Score
		group by s_id
		having AVG(s_score) > 60
		)
group by Score.s_id,s_name

# 2、另一种方法：进行聚合的时候切记要进行group by
select Score.s_id ,s_name ,
avg(s_score) as avg_score
from Score,Student
where Score.s_id = Student.s_id
group by Score.s_id,s_name
having avg(s_score) > 60
#3、第三种方法：left join
select Score.s_id ,s_name ,
avg(s_score) as avg_score
from Student
left join 
Score
on Score.s_id = Student.s_id
group by s_name,Score.s_id
having avg(s_score) > 60

4、查询成绩和学生所有信息，并根据s_id 排序

select Score.s_id ,s_name ,
avg(s_score) as avg_score,
count(s_score) as cnt_score
from Student
left join 
Score
on Score.s_id = Student.s_id
group by Score.s_id,s_name
order by s_id

5、查询名字中带有王的学生的信息，包括各科成绩

select a.s_id,c_id,s_name,s_sex,s_birth,c_id,s_score
from
((select * 
from Student) a
left join
(select * 
from Score) b
on a.s_id = b.s_id)
where s_name like \'%王\'

6、根据课程总分对学生由高分到低分排名，添加排名列

select Student.s_id,s_name,s_sex,s_birth,
sum(s_score) as sum_score,
RANK() over (order by sum(s_score) ) as rank1
from 
Student,Score
Where Student.s_id = Score.s_id
group by Student.s_id,s_name,s_sex,s_birth
order by sum_score desc