自己作为一个sql初学者,练习sql练习题所得答案,答案仅供参考~

1
select student.S_id,student.Sname,student.Sage,student.Ssex, C.A_S ‘语文’,C.B_S ‘数学’
from (select A.S_id,A.score as A_S,B.score as B_S
from (select S_id,C_id,score
from sc
where C_id=‘01’) as A left join
(select S_id,C_id,score
from sc
where C_id=‘02’) as B on A.S_id=B.S_id
where A.score>B.score) as C inner join student
on student.S_id=C.S_id

1.1
select A.S_id,A.C_id,A.score,B.C_id,B.score
from (select * from sc where C_id=‘01’) A
inner join (select *from sc where C_id=‘02’) B
on A.S_id=B.S_id

1.2
select * from(select * from sc where C_id=‘01’) A
left join (select * from sc where C_id=‘02’) B
on A.S_id=B.S_id
where A.C_id=‘01’

1.3
select * from(select * from sc where C_id=‘02’) A
left join (select * from sc where C_id=‘01’) B
on B.S_id=A.S_id
where B.S_id is null

2
select sc.S_ID,student.Sname,avg(score) from sc inner join student
on sc.S_id=student.S_id
group by S_id
having avg(score)>60

3
select sc.S_id,Sname,Sage,Ssex
from sc left join student
on sc.S_id=student.S_id
group by sc.S_id

4
select student.S_id,student.Sname,count(sc.C_id),sum(sc.score)
from student left join sc
on student.s_id=sc.s_id
group by student.S_id

5
select count(Tname)
from teacher
where Tname like ‘李%’

6
select * from student right join sc
on sc.s_id=student.s_id
where c_id=(select C_id from course where
t_id=(select T_id from teacher where tname=‘张三’));

7
select * from student
where s_id not in(
select s_id
from sc
group by s_id
having count(c_id)=3)

select * from student
where s_id in (select s_id from sc
group by s_id
having count(s_id)>=1)
and s_id<>“01”

9
select * from student
where s_id in (select s_id from sc
group by s_id
having count(s_id)=3)
and s_id<>‘01’

10
select Sname from student
where s_id not in (select s_id from sc where
c_id=(select c_id from course where
T_id=(select t_id from teacher where Tname=‘张三’)));

11
select sc.S_id,student.Sname,avg(sc.score)
from sc join student
on sc.s_id=student.s_id
group by s_id
having s_id in (select s_id from sc
where score<60
group by s_id
having count(c_id)>=2);

12
select * from student inner join sc
on student.s_id=sc.s_id
where c_id=‘01’
and score<60
order by score desc

13
select Sname,avg(score) from student right join sc
on student.s_id=sc.s_id
group by sc.s_id
order by avg(score) desc

14
select sc.C_id,count(s_id),Cname,max(score),min(score),avg(score),a.a/count(s_id),b.b/count(s_id),c.c/count(s_id),d.d/count(s_id)
from sc left join course
on course.c_id=sc.c_id
left join (select c_id,count(s_id) a
from sc
where score>=60
group by c_id) a
on a.c_id=sc.c_id
left join (select c_id,count(s_id) b
from sc
where score>=70 and score<80
group by c_id) b
on b.c_id=sc.c_id
left join (select c_id,count(s_id) c
from sc
where score>=80 and score<90
group by c_id) c
on c.c_id=sc.c_id
left join (select c_id,count(s_id) d
from sc
where score>=60
group by c_id) d
on d.c_id=sc.c_id
group by sc.c_id
order by count(s_id) desc,sc.C_id

15
select a.s_id,a.c_id,count(b.score) from
sc a
left join
(select * from sc
group by score,c_id) b
on a.c_id=b.c_id
and a.score>=b.score
group by a.s_id,a.c_id
order by a.c_id,count(b.score)

16
select a.s_id,a.ascore,count(ascore)
from
(select s_id,sum(score) ascore from sc group by s_id) a
cross join (select * from
(select s_id,sum(score) bscore from sc group by s_id) b group by bscore) c
where a.ascore <=c.bscore
group by a.s_id
order by count(ascore)

17
select sc.c_id,cname,a.a,a.a/count(a.s_id),b.b,b.b/count(b.s_id),c.c,c.c/count(c.s_id),d.d,d.d/count(d.s_id)
from sc
left join course
on sc.c_id=course.c_id
left join (select *,count(s_id) a from sc where score>=85 group by c_id) a
on sc.c_id=a.c_id
left join (select *,count(s_id) b from sc where score>=70 and score<85 group by c_id) b
on sc.c_id=b.c_id
left join (select *,count(s_id) c from sc where score>=60 and score<70 group by c_id) c
on sc.c_id=c.c_id
left join (select *,count(s_id) d from sc where score>=0 and score<60 group by c_id) d
on sc.c_id=d.c_id
group by c_id

18
select a.s_id,a.c_id,count(b.score) from
sc a
left join
(select * from sc
group by score,c_id) b
on a.c_id=b.c_id
and a.score>=b.score
group by a.s_id,a.c_id
having count(b.score)<=3
order by a.c_id,count(b.score)

19
select c_id,count(c_id)
from sc
group count(c_id)

20
select sc.s_id,sname
from student right join sc
on student.s_id=sc.s_id
group by sc.s_id
having count(c_id)=2

.21
select ssex,count(ssex) from student
group by ssex

22
select * from student
where sname like ‘%风%’

23
select sname,ssex from student
group by sname,ssex
having count(sname)>=2

24
select * from student
where year(sage)=1990

25
select c_id,avg(score) from sc
group by c_id
order by avg(score) desc,c_id

26
select sc.s_id,s.sname,avg(score)
from student s join sc
on s.s_id=sc.s_id
group by sc.s_id
having avg(score)>=85

27
select cname,sname,score
from student s left join sc
on s.s_id=sc.s_id
left join course
on sc.c_id=course.c_id
where cname=‘数学’
and score<60

28
select s.s_id,sname,c_id,score
from sc right join student s
on sc.s_id=s.s_id

29
select sname,cname,score
from sc left join student s
on sc.s_id=s.s_id
left join course
on sc.c_id=course.c_id
where score>70

30
select sname,cname,score
from sc left join student s
on sc.s_id=s.s_id
left join course
on sc.c_id=course.c_id
where score<60

31
select student.s_id,sname,c_id,score
from student right join sc
on student.s_id=sc.s_id
where c_id=‘01’
and score>80

32
select c_id,count(s_id)
from sc
group by c_id

33 34
select student.*,sc.score,count(sc.score)
from sc left join
(select * from sc group by score) a
on sc.c_id=a.c_id
left join student
on sc.s_id=student.s_id
left join course
on sc.c_id=course.c_id
left join teacher
on course.t_id=teacher.t_id
where tname=‘张三’
and sc.score<=a.score
group by sc.s_id,sc.c_id
having count(sc.score)=1

35
select sc.* from sc
right join (
select sc.*, count(sc.score) from sc
left join (select * from sc group by score) a
on sc.c_id=a.c_id
and sc.score=a.score
group by sc.c_id,sc.score
having count(sc.score)>=2) b
on b.score=sc.score and b.c_id=sc.c_id

select c_id,score from sc
group by c_id,score
having count(s_id)>1 可以替换上面表b,更简单

36
select sc.*,count(a.score) from sc
left join (select * from sc group by c_id,score) a
on sc.c_id=a.c_id
and sc.score<=a.score
group by sc.s_id,sc.c_id
having count(a.score)<=2
order by sc.c_id ,count(a.score)

37
select c_id ,count(s_id)
from sc
group by c_id
having count(s_id)>5

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

39
select * from student a
right join sc
on a.s_id=sc.s_id
group by sc.s_id
having count(c_id)=3

40 41
select sname, year(now())-year(sage) as age from student

42
select * from (select *,concat(‘2020’, right(sage,15)) a from student) a
where week(a)=week(now())

43
select * from (select *,concat(‘2020’, right(sage,15)) a from student) a
where week(a)=week(now())+1

44
select sname from student
where month(sage)=month(now())

45
select sname from student
where month(sage)=month(now())+1

题目以及数据来自

https://blog.csdn.net/flycat296/article/details/63681089

后面也有博客原作者所写答案,答案仅供参考~