回炉重塑SQL

一.共有四个表

学生表:

Student(s_id,s_name,s_birth,s_sex)-学号,学生姓名,出生年月,性别

成绩表:

Score(s_id,c_id,s_score)-学号课程编号,分数

课程表:

Course(c_id,c_name,t_id)-课程编号,课程名称,教师号

教师表:

Teacher(t_id,t_name)-教师号,教师姓名

二.建表

学生表:

create table Student(
s_id varchar(20),
s_name varchar(20) not null default'',
s_birth varchar(20) not null default'',
s_sex varchar(10) not null default'',
primary key(s_id)
);

成绩表:

create table Score(
s_id varchar(20),
c_id varchar(10),
s_score int(3),
primary key(s_id,c_id)
);

课程表:

create table Course(
c_id varchar(20),
c_name varchar(20) not null default'',
t_id varchar(20)not null,
primary key(c_id)
);

教师表:

create table Teacher(
t_id varchar(20),
t_name varchar(20)not null default'',
primary key(t_id)
);

三.插入数据(自行随便插吧,省略不写了)

四.查询   

1.查询课程编号‘01’的课程比‘02’的课程成绩高的所有学生的学号

(1)先查询课程编号为01的学生的学号和成绩

select s_id,c_id,s_score from Score where c_id='01'

(2)再查课程编号为02的学生的学号和成绩

select s_id,c_id,s_score from Score where c_id='02'

(3)把两张表连接起来

inner join(这个学生肯定01,02课程都有,所以是inner join)

(4)

select A.s_id from
(
select s_id,c_id,s_score from Score where c_id='01'
)as A
inner join
(
select s_id,c_id,s_score from Score where c_id='02'
)as B on A.s_id=B.s_id
where A.s_score>B.s_score;

2.查询平均成绩大于60分的学生的学号和平均成绩

(把相同 学号的分为一组,然后求平均值)

select s_id,avg(s_score)
   from Score
 group by s_id having avg(s_score)>60

3.查询所有学生的学号,姓名,选课数,总成绩

(学号,姓名,课程编号,这门课成绩

   然后将它们group by 一下就能得出来问题)

select a.s_id,a.s_name,count(b.c_id),
sum(case when b.s_score is null then 0 else b.s_score end)//把null置为0
   from Student as a
left join Score as b on a.s_id=b.s_id
group by s_id,a.s_name

4.查询姓史老师的个数

select count(t_id)
from Teacher
where t_name like'史%'

5.查询没学过杨硕老师课的学生的学号和姓名

方法一:

①找老师名字是杨硕的教师编号

select t_id from teacher where t_name='杨硕'

②找教师编号对应的课程编号

select c_id from course where t_id in(

select t_id from teacher where t_name='杨硕')

③找课程编号对应的学号

select s_id from score where c_id in(

select c_id from course where t_id in(

select t_id from teacher where t_name='杨硕')

)

④找学生当中不在学过杨硕的学生中

select s_id,s_name from student

where s_id not in(

  select s_id from score where c_id in(

      select c_id from course where t_id in(

     select t_id from teacher where t_name='杨硕'

                                                                     )

                                                                  )

)

方法二:

select s_id,s_name from Student
where s_id not in(
select s_id from Score as s
inner join Course as c on s.c_id=c.c_id
inner join Teacher as t on t.t_id=c.t_id
where t.t_name='杨硕')

6.查询学过刘俊老师所教的所有课的同学的学号和姓名

not exists:结果为真,不返回,结果为假,才返回

(1)查找刘俊老师的教师号

select t_id from Teacher where t_name='刘俊'

(2)根据教师号找到课程编号有哪些

select t_id from Course  where t_id=(
select t_id from Teacher where t_name='刘俊')

(3)假语句:

select t_id from Course  where t_id=(  
select t_id from Teacher where t_name='刘俊')
and c_id not in(
select s.c_id from Score as s where s.s_id=st.s_id
)

(4)

select st.s_id,st.s_name from Student as st where
not exists
(
select  t_id from Course  where t_id=(
select t_id from Teacher where t_name='刘俊')
and c_id not in(
select s.c_id from Score as s where s.s_id=st.s_id
)
)

7.查询学过编号为01的课程并且也学过编号为02的课程的学生的学号和姓名

(1)求出编号为01的课程

(2)求出编号为02的课程

(3)作交集然后求学生的学号和姓名

select st.s_id,st.s_name from Student as st 
where st.s_id in(
select a.s_id from
(select s_id from Score where c_id='01')as a
inner join
(select s_id from Score where c_id='02') as b on a.s_id=b.s_id
)

8.查询课程编号为02的总成绩

select sum(s_score)
from Score where c_id='02'

9.查询所有课程成绩小于60分的学号和姓名

(1)得出同学课程成绩小于60的课程数

(2)统计同学总共学了几门课

(3)如果总共的课程数等于<60的课程数,那么就是所有课都<60的课程了

select a.s_id,st.s_name from
(
select s_id,count(c_id) as cnt from Score where s_score<60
group by s_id
)as a
inner join
(
select s_id,count(c_id) as cnt from Score
group by s_id
)as b on a.s_id=b.s_id
inner join Student as st on st.s_id=a.s_id
where a.cnt=b.cnt

10.查询没有学全所有课的学生的学号、姓名

注意:可能有一门课都没有学的学生!score表中可能没有他的信息,所以不能直接从score表中找。

学生和成绩是一对多的关系,学生一定都会出现,成绩不一定。所以用left join连接.

left join:假设【1,2,3,4】和【2,3,4,5】作了左连接,如下:

【1,null】【2,2】【3,3】【4,4】

找学生选课数<课程总数的学生信息就行

select st.s_id,st.s_name
from student as st
left join score as sc on sc.s_id=st.s_id
group by st.s_id
having count(sc.c_id)<(select count(c_id) from course)

11.查询    至少有一门课与学号是01的学生所学课程     相同的    学生的学号和姓名

①先要知道学号是01的学生学了哪些课

select *
from score
where s_id='01'

②至少有一门课与学号是01的学生所学课程相同的

select  distinct s_id
from score where c_id in(
select c_id from score where s_id='01')

③去掉01同学本身

select distinct s_id
from score where c_id in(
select c_id from score where s_id='01')
and s_id!='01'

select s_id,s_name from student
where s_id in(
select distinct s_id
from score where c_id in(
select c_id from score where s_id='01')
and s_id!='01'
)

用inner join的答案:

select a.s_id,a.s_name from student as a
inner join(
select distinct s_id
from score where c_id in(
select c_id from score where s_id='01')
and s_id!='01'
)as b on a.s_id=b.s_id

12.查询和‘01’号同学所学的课程完全相同的其他同学的学号

  反向思考,有一门课和01号同学学的不同肯定不满足条件,筛去。

①01号同学学的课程号

select c_id from score where s_id='01'

②和01号同学学的不一样的学生的学号

select distinct s_id from score
where c_id not in(
select c_id from score where s_id='01'
)

③找到学的课程数一样的学生学号(不能包括01,去掉01)

select s_id from score
where s_id!='01'
group by s_id having count(distinct c_id)=
(select count(distinct c_id) from score where s_id='01')

select s_id from student
where s_id in(
select s_id from score
where s_id!='01'
group by s_id having count(distinct c_id)=
(select count(distinct c_id) from score where s_id='01')
)
and s_id not in(
select distinct s_id from score
where c_id not in(
select c_id from score where s_id='01')
)
15.查询两门及以上不及格课程的同学的学号,姓名,和平均成绩

①找成绩不合格同学的学号

select s_id from score where s_score<60

②再加上两门及以上不合格

select s_id from score where s_score<60
group by s_id
having count(distinct c_id)>=2

③姓名在student表,成绩在score表,作个join

select a.s_id,a.s_name,avg(s_score)from student as a
inner join score as b on a.s_id=b.s_id
where a.s_id in(
select s_id from score where s_score<60
group by s_id
having count(distinct c_id)>=2
)
group by s_id,s_name

16.检索'01'课程分数<60,按分数降序排序的学生信息

按分数排序,但分数在score表,作join

select * from student as a
inner join score as b on a.s_id=b.s_id
where b.c_id='01' and b.s_score<60
order by b.s_score desc

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

如果直接求,只会出现一个学生的一个成绩  

一个学生对应多个课程的成绩,inner join

select a.* ,avg_s_score from score as a
inner join(
select s_id,avg(s_score) as avg_s_score from score group by s_id
)as b on a.s_id=b.s_id
order by b.avg_s_score desc

18.查询各科成绩最高分,最低分和平均分:如下显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率,及格为>=60,中等为70-80,优良为80-90,优秀为>=90

①不加后面一系列操作,只求各科最高,最低,平均分

select max(s_score),min(s_score),avg(s_score) from score
group by c_id

select a.c_id '课程ID'
,b.c_name '课程name'
,max(a.s_score)'最高分'
,min(a.s_score)'最低分'
,avg(a.s_score)'平均分'
,sum(case when a.s_score>=60 then 1 else 0 end)/count(s_id)'及格率'
,sum(case when a.s_score>=70 and a.s_score<80 then 1 else 0 end)/count(s_id)'中等率'
,sum(case when a.s_score>=80 and a.s_score<90 then 1 else 0 end)/count(s_id)'优良率'
,sum(case when a.s_score>=90 then 1 else 0 end)/count(s_id)'优秀率'
from score as a
inner join course as b on a.c_id=b.c_id
group by a.c_id

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

求和sum,肯定用到分组统计group by

select s_id,sum(s_score) as sum_score, row_number() over(order by sum_score desc)'排名'from score
group by c_id
order by sum_score desc

21.查询不同老师所教不同课程平均分从高到低

先吐槽一下这题目啥意思。。。。。

以老师为主体,求平均分

一个老师教多门课,求这个老师所教的课的平均分

select c.t_id,c.t_name,avg(a.s_score) as avg_score from score as a
inner join course as b on a.c_id=b.c_id
inner join teacher as c on b.t_id=c.t_id
group by c.t_id,c.t_name
order by avg_score desc

23.使用分段[100,85),[85,70),[70,60),[<60]来统计各科成绩,分别统计各分数段人数:课程ID和课程名称

select b.c_id,b.c_name
,sum(case when a.s_score<60 then 1 else 0 end)"[0,60]"
,sum(case when a.s_score>=70 and a.s_score<60 then 1 else 0 end)"(60,70]"
,sum(case when a.s_score>=85 and a.s_score<70 then 1 else 0 end)"(70,85]"
,sum(case when a.s_score>=100 and a.s_score<85 then 1 else 0 end)"(85,100]"
from score as a
inner join course as b on a.c_id=b.c_id
group by b.c_id,b.c_name
 

24.查询学生平均成绩及名次

select avg(s_score),row_number() over(order by avg(s_score)desc) 
from score
group by s_id
 

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

 select a.c_id,b.c_name,count(a.c_id) from score as a
inner join course as b on a.c_id=b.c_id
group by b.c_id,b.c_name

 

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

①先找score表里只学了两门课程的学生的学号

select s_id from score
group by s_id
having count(c_id)=2 

②再在student表里找匹配的

select s_id,s_name from student
where s_id in(
select s_id from score
group by s_id
having count(c_id)=2 )

另一种做法:

select a.s_id,a.s_name from student as a
inner join score as b on a.s_id=b.s_id
group by a.s_id having count(c_id)=2

28.查询男生女生人数

select sum(case when s_sex='男' then 1 else 0 end)"男生人数",
sum(case when s_sex='女' then 1 else 0 end)"女生人数"
from student

另一种写法:

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

29.查询名字中含有“风”字的学生信息

select * from student
where s_name like'%风%'

31.查询1990年出生的学生名单

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

32.查询平均成绩>=85的所有学生的学号姓名和平均成绩

select a.s_id,a.s_name,avg_score from student as a
inner join(
select s_id,avg(s_score) as avg_score from score
group by s_id having avg_score >=85)as b on a.s_id=b.s_id

 

不知道这样写对不对,反正得到的答案是一样的QAQ

select a.s_id,a.s_name, avg(b.s_score)from student as a
inner join score as b on a.s_id=b.s_id
group by b.s_id having avg(b.s_score)>=85

33.查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排序。

select c_id,avg(s_score) as avg_score from score
group by c_id
order by avg_score asc,c_id desc

34.查询课程名称为“数学”,且分数低于60的学生姓名和分数

select a.s_id,a.s_score,c.s_name from score as a
inner join course as b on a.c_id=b.c_id
inner join student as c on c.s_id=a.s_id
where a.s_score<60 and b.c_name="数学"
 

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

select st.s_id,st.s_name,sc.s_score,sc.c_id,c.c_name from student as st
left join score as sc on st.s_id=sc.s_id
left join course as c on c.c_id=sc.c_id
 

36.查询课程成绩在70分以上的课程名称,分数和学生姓名

select c.c_name,sc.s_score,st.s_name from student as st
inner join score as sc on sc.s_id=st.s_id
inner join course as c on c.c_id=sc.c_id
where sc.s_score>70

 

37.查询不及格的课程并按课程号从大到小排序

select s.c_id,c.c_name from score as s
inner join course as c on s.c_id=c.c_id
where s.s_score<60
group by s.c_id
order by s.c_id desc

 

38.查询课程编号为03,且课程成绩在80分以上的学生的学号和姓名

select st.s_id,st.s_name from score as sc
inner join student as st on sc.s_id=st.s_id
where sc.c_id='03' and sc.s_score>80
 

39.求每门课程的学生人数

select sc.c_id,c.c_name,count(s_id) from score as sc
inner join course as c on c.c_id=sc.c_id
group by sc.c_id

 

40.查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩

select st.s_name,sc.s_score from student as st
inner join score as sc on st.s_id=sc.s_id
inner join course as c on c.c_id=sc.c_id
inner join teacher as t on t.t_id=c.t_id
where t.t_name='张三'
order by sc.s_score desc
limit 0,1

 

41.查询不同课程成绩相同的学生的学生编号,课程编号,学生成绩

它要查的是有这么一个学生,这个学生学的课程不同,但是这些 不同课程的成绩却相等

那么肯定是c_id不同但s_score相同的学生信息

select a.* from score as a
inner join score as b on a.s_id=b.s_id
where a.s_score=b.s_score and a.c_id!=b.c_id
group by a.s_id,a.s_score,a.c_id

 

43.统计每门课程的学生选修人数(超过5人才统计),要求输出课程号,选修人数,查询结果按人数降序排序,若人数相同,按课程号升序排序

select c_id,count(s_id) as people from score
group by c_id having count(s_id)>5
order by people desc,c_id asc

 

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

select s_id from score
group by s_id having count(c_id)>=2

 

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

select * from student 
where s_id in(
select s_id from score
group by s_id having count(c_id)=(
select count(c_id) from course))

 

46.查询各学生的年龄

select s_id,s_name,floor(datediff('2020-5-19',s_birth)/365) as "年龄"
from student

round()取整:四舍五入

floor()取整:只保留整数

datediff(date1,date2):返回的是date1-date2的天数

 

47.查询没学过“张三”老师讲授的任一门课程的学生姓名

从反面想:先把学过张三老师教的课的学生 选出来,再把他们排除掉,剩下的就是答案

select s_name from student where s_id not in(
select s_id from score where c_id in(
select c_id from course where t_id in(
select t_id from teacher
where t_name="张三")))

另一种写法:

select s_name from student
where s_id not in(
select s_id from score as a
inner join course as b on a.c_id=b.c_id
inner join teacher as c on c.t_id=b.t_id
where c.t_name="张三")

 

48.查询下周过生日的学生

select * from student where week(s_birth,1)=week(date(now()),1)

 

49.查询本月过生日的人

select * from student 
where month(s_birth)=month(date(now()))

 

50.下个月过生日的同学

select * from student
where month(s_birth)=month(date(now()))+1

 

 

 

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值