sql经典练习50题交作业

试题链接：https://blog.csdn.net/flycat296/article/details/63681089

视频解析：https://www.bilibili.com/video/BV1q4411G7Lw?t=57&p=49 （视频解答我没有看，看文字答案不懂的小伙伴可以去看下视频，ps：视频解答不全）

花了大概四个多小时，把题目都做完了，总体下来感觉属于中低难度，但有些题目还是需要花脑经去想一想的，有些逻辑还真一时半会儿想不到。

其中大概有4道题我是一点思路都没有看了下答案才做出来的，其他部分基本上都能想到。

下面附上我的答案，差不多一半和原文答案不一样，但是运行结果是一样的，原答案中有部分不够健壮，我做了些改进，我的答案中有几条也不够健壮，有些情况下无法满足。

-- number1
select * from 
(select * from sc where C='01') a 
left join
(select * from sc where C='02') b
using(S)
where a.score>b.score
;

-- number1.1
select * from
(select * from sc where C='01') a 
join
(select * from sc where C='02') b
using(S);

--  number1.2 
select * from
(select * from sc where C='01') a 
left join
(select * from sc where C='02') b
using(S);

-- number1.3
select * from
(select * from sc where C='01') a 
right join
(select * from sc where C='02') b
using(S)
where a.C is null;

-- number2
select a.S,a.Sname, b.avg_score from student a
join(
 select S,avg(score) avg_score from sc group by S 
 having avg_score>=60) b using(S);

-- number3 
select * from student where S in (select distinct S from sc);

-- number4
select a.S,a.Sname,b.sum_course as '选课总数',b.sum_score as '总成绩'
from
student a
left join
( 
 select 
 S,
 count(*) as sum_course,
 sum(score) as sum_score
 FROM sc group by S
 ) b 
 using(S);
 
 -- number4.1 
 select a.S,a.Sname,b.sum_course as '选课总数',b.sum_score as '总成绩'
from
student a
join
( 
 select 
 S,
 count(*) as sum_course,
 sum(score) as sum_score
 FROM sc group by S
 ) b 
 using(S) 
;

-- number5
 select count(*) as '李姓老师数量'from teacher where Tname like '李%';
 
 -- number6
select * from student where S in(
select S from sc where C in (
select C from course where T in (
select T from teacher where Tname="张三"
)
)
);

-- number7
select * from student where S in(
 select S from (
 select * from sc 
 right join
 course 
 using(C)
 ) a group by S having count(*)<(select count(*) from course)
 );
 
 -- number8
 select * from student where S in(
 select distinct S from sc where C in (
 select C from sc where S='01')
 );
 
 --  number9
 select * from student where S in(
 select distinct S from (
 select S from sc where C in(
 select C from sc where S='01') and S!='01') a
 group by S having count(S)=(select count(*) from sc where S ='01')
 );
 
 -- number 10
 
 select * from student where S not in(
 select distinct S from sc where C in(
 select C from course where T=(
 select T from teacher where Tname='张三')));
 
 -- number 11
select S,Sname, b.avg_score
from student 
join(
 select 
 S,
 avg(score)  as avg_score
 from (
 select * from sc where score<60) a
 group by S having count(S)>=2
 ) b
 using(S);
 
 -- number 12
 select * from student 
 join
 (
 select S,score from sc where score<60 and C='01' order by score desc) a
 using(S);
 
 -- number13
 select * from sc join(
 select S,avg(score) as avg_score from sc  group by S) a
 using(S)
 order by avg_score desc;
 
 -- number 14
  select * from (
 select C,Cname,a.max_score,a.min_score,a.avg_score from course join (
 select C,max(score) max_score,min(score) min_score,avg(score) avg_score
 from sc group by C
 ) a using(C)
 ) a
 join
 (
 select C,
 sum(case when score>=60 then 1 else 0 end)/count(*) as '及格率',
 sum(case when score>=70 and score<80 then 1 else 0 end)/count(*) as '中等率',
 sum(case when score>=80 and score<90 then 1 else 0 end)/count(*) as '优良率',
 sum(case when score>=90 then 1 else 0 end)/count(*) as '优秀率'
 from sc group by C
) b 
using(C);
 
 
 -- number 15
 select *,rank()over(order by score desc) as '名次'from sc;
 
 -- number15.1
 select *,dense_rank()over(order by score desc) as '名次' from sc;
 
 -- number16
 select *,rank()over(order by a.sum_score desc ) as '名次' from (
 select S,sum(score) as sum_score from sc group by S) a;
 
 -- number 16.1
 select *,dense_rank()over(order by a.sum_score desc ) as '名次' from (
 select S,sum(score) as sum_score from sc group by S) a;
 
 -- numnber 17
 select c.C,c.cname,'[100-85]人数','[100-85]比率','[85-70]人数', '[85-70]比率','[70-60]人数','[70-60]比率','[60-0]人数','[60-0]比率'
 from course c join (
 select C,
 sum(case when score<=100 and score>85 then 1 else 0 end) as '[100-85]人数',
 sum(case when score<=100 and score>85 then 1 else 0 end)/count(*) as '[100-85]比率',
 sum(case when score<=85 and score>70 then 1 else 0 end) as '[85-70]人数',
 sum(case when score<=85 and score>70 then 1 else 0 end)/count(*) as '[85-70]比率',
 sum(case when score<=70 and score>60 then 1 else 0 end) as '[70-60]人数',
 sum(case when score<=70 and score>60 then 1 else 0 end)/count(*) as '[70-60]比率',
 sum(case when score<=60 and score>0 then 1 else 0 end) as '[60-0]人数',
 sum(case when score<=60 and score>0 then 1 else 0 end)/count(*) as '[60-0]比率'
 from sc group by C) b
 using(C);
 
 -- number 18
 select * from (
 select * ,rank()over(partition by C order by score desc ) as srank  from sc 
 ) a where a.srank<=3;
 
 -- number 19 
 select C, count(*) as '学生数' from sc group by C;
 
 -- number 20
 select a.S,a.Sname from student a join(
 select 
 S
 from sc 
 group by S having count(S) =2
 ) b using(S);
 
 -- number 21
 select Ssex,count(*) as '人数' 
 from student
 group by Ssex;
 
 -- number 22
 select * from student where Sname like '%风%';
 
 -- number 23
 select a.*, b.same_name as '同名人数' from student a join(
select 
Sname,Ssex,count(*) as same_name
from student 
group by Sname,Ssex
) b using(Sname,Ssex) where b.same_name>1;

-- number 24
select *
from student
where year(Sage)='1990';

-- number 25
select C,
avg(score) as avg_score
from sc 
group by C
order by avg_score desc,C;

-- number 26
select S ,Sname,avg_score from student join(
select S,
avg(score) as avg_score
from sc
group by S having avg_score>=85) a
using(S);

-- number 27
select Sname,score from student join(
select S,score from sc where C in(
select C from course where Cname='数学'
) and score <60) a
using(S);

-- number 28
select S,Sname,C,score from student
left join
sc
using(S);

-- number 29
select Sname,Cname,score from student join(
select S,C,score from sc where score >70) a
using(S)
join course
using(C);

-- number 30
select C from sc where score <60;

-- number 31
select S,Sname from student join(
select S from sc where C='01' and score >80) a
using(S);

-- number 32
select C,count(*) as '人数' from sc 
group by C;

-- number 33
select *,max_score from student join(
select S,max(score) as max_score from sc where C in(
select C from course where T in(
select T from teacher where Tname ='张三')
) )a
using(S);


-- number 34
select * from student join(
select S,score,dense_rank()over(order by score desc) as srank 
from sc where C in(
select C from course where T in(
select T from teacher where Tname ='张三')
) )a
using(S) where srank=1;

-- number 35
select * from sc group by S,score having count(*) >1;

-- number 36
select S,C,Sname from student join(
select S,C, row_number()over(partition by C order by score) as srank
from sc) a
using(S)
where a.srank<3;

-- number 37
select C,count(*) as '选修人数'
from sc
group by C having count(*)>5;

-- number 38
select S,count(*) from sc
group by S 
having count(*) >=2;

-- number 39
select * from student where S in(
select S from sc group by S having count(*) =(select count(*) from course));

-- number 40
select S,Sname,year(Sage) as age,Ssex from student;

-- number 41
select 
(
case 
when convert(int,'1'+substring(CONVERT(varchar(10),Sage,112),5,8))
<
convert(int,'1'+substring(CONVERT(varchar(10),GETDATE(),112),5,8)) 
then datediff(yy,Sage,GETDATE()) 
else datediff(yy,Sage,GETDATE())-1 
end
) age 
from Student;

-- number 42
select *,
(case 
when datename(wk,
convert(datetime,
(convert(
varchar(10),year(GETDATE()))+
substring(convert(
varchar(10),Sage,112),5,8)
)))=DATENAME(WK,GETDATE()) 
then 1 else 0 end) birthnock
from Student;

-- number 43
select *,(case when datename(wk,convert(datetime,(convert(varchar(10),year(GETDATE()))+
substring(convert(varchar(10),Sage,112),5,8))))=DATENAME(WK,GETDATE())+1 
then 1 else 0 end) birthnock
from Student;

-- number 44
select *,(case when month(convert(datetime,(convert(varchar(10),year(GETDATE()))+substring(convert(varchar(10),Sage,112),5,8))))=month(GETDATE())
then 1 else 0 end) birthnock
from Student;

-- number 45
select *,(case when month(convert(datetime,(convert(varchar(10),year(GETDATE()))+substring(convert(varchar(10),Sage,112),5,8))))=month(GETDATE())+1
then 1 else 0 end) birthnock
from Student;
           

做完这些题目的话，我觉得可能还是差点火候，建议大家可以上牛客网或者leetcode上再看看难度高一点的，毕竟面试你的HR都希望你能造原子弹，虽然进去后可能只是拧螺丝，但是如果换你是面试官，同样的价钱，你选择会拧螺丝的还是会造原子弹的呢？

skr，真实！！！我太难了……