经典MySQL50题

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

最近花了三四天把入门经典《SQL必知必会》看完了，然后开始做网上的50道经典练习题，部分习题比较难，大部分比较简单。下面，我po出自己的解答思路与结果：（如有错误或改进的地方还望留言指正~）

#1.1查询01课程比02课程成绩高的学生信息及课程分数 01 02都存在
select student.*,
max(case when sc.cid='01' then score end) as C1,
max(case when sc.cid='02' then score  end)as C2
from student left join sc
on student.sid=sc.sid
group by sid
having c1 is not null
and c2 is not null
and c1>c2;

select * from student right join(
      select t1.sid, C1,C2 from
            (select sid,score as C1 from sc where sc.cid='01') as t1,
             (select sid,score as C2 from sc where sc.cid='02') as t2
             where t1.sid=t2.sid and t1.c1>t2.c2
)r
on student.sid=r.sid;
#1.2查询01课程比02课程成绩高的学生信息及课程分数 01存在 02可能不存在
select student.*,
max(case when cid='01' then score end) as C1,
max(case when cid='02' then score end) as C2
from student left join sc
on student.sid=sc.sid
group by sid
having c1 is not null
and c1>c2
or c2 is null;

#1.3查询01课程分数不存在但02存在
select student.*,
max(case when cid='01' then score end) as C1,
max(case when cid='02' then score end) as C2
from student left join sc
on student.sid=sc.sid
group by sid
having c1 is null
and c2 is not null;

##2.查询平均成绩大于等于60的同学的学生编号和学生姓名和平均成绩
select Sname,SId,(select avg(score)
                  from sc
                  where sc.SId=student.SId
                  group by SId
                  having AVG(score)>=60)as avg_score
from student;

##3.查询SC表存在成绩的学生信息
select Sname,Sage,Ssex
from student
where SId IN(select SId from sc where score IS NOT NULL);

#4 查询所有学生的学生编号、学生姓名、选课总数、所有课程的总成绩
select student.SId,student.Sname,count(*) as num_course,sum(score) as total_score
from student left join sc
on sc.SId=student.SId
group by SId;

#4.1 查询所有学生的学生编号、学生姓名、选课总数、所有课程的总成绩(有成绩)
select student.SId,student.Sname,count(*) as num_course,sum(score) as total_score
from student inner join sc
on sc.SId=student.SId
group by SId;

#5.查询李姓老师的数量
select count(*) as num_li
from teacher
where Tname like '李%';

#6.查询学过张三老师课程的学生的信息
select *
from student
where SId in(
select SId from sc where CId in(select CId
from course
where TId=(select TId
from teacher
where Tname='张三'))); #子查询

select student.SId,Sname,Sage,Ssex
from student,sc,course,teacher
where student.SId=sc.SId
and sc.CId=course.CId
and teacher.TId=course.TId
and teacher.Tname='张三'; #多表联结

#7.查询没有学全所有课程的同学的信息
select student.*,count(sc.SId) as num_course
from student left join sc
on student.SId=sc.SId
group by student.SId
having num_course<(select count(CId) from course); #分组后筛选用having

#8.查询至少一门课与学号为‘01’的同学所学相同的同学的信息
select *
from student
where SId in (
select distinct SId from sc
where CId in (select CId from sc where SId='01'));#课程总数

#9.查询和01同学课程完全相同的学生的信息
select SId,Count(SId) as num_course
from sc  
where CId in('01','02','03')
group by SId
having num_course=3;

select *
from student
where SId in(select SId
                    from sc  
                    where CId in(select CId from sc where SId='01')
                    group by SId
                    having Count(SId)=(select count(CId)from sc where SId='01'));

#10.查询没学过‘张三’老师教授的任一门课程的学生姓名
select CId,teacher.TId,Tname
from course inner join teacher
on course.Tid=teacher.tid
where Tname='张三'; #教授的课程编号

select student.*
from student
where SId not in (select distinct student.SId
                  from student left join sc
                  on student.sid=sc.sid
                  where cid  in (select CId
                                 from course inner join teacher
                                 on course.Tid=teacher.tid
                                 where Tname='张三')); #学过张三老师任一门课的同学)
select student.*
from student
where SId not in (select distinct student.SId
                  from student,sc,course,teacher
                  where student.sid=sc.sid
                  and course.cid=sc.cid
                  and course.Tid=teacher.tid
                  and  Tname='张三');#学过张三老师任一门课的同学                  

#11.查询两门及以上不及格课程的同学的学号，姓名和平均成绩
select student.SId,Sname,avg(score) as avg_score
from sc inner join student
on sc.SId=student.SId
group by sc.SId
having sc.SId in (select sid
                 from sc
                 where score<60
                 group by SId
                 having count(CId)>=2);    #2门及以上课程不及格同学的ID

#12.检索01课程分数小于60，按分数降序排列的学生信息
select student.*
from student,sc
where student.sid=sc.sid
and cid='01'
and score<60
order by score desc;

#13.查询平均成绩由高到低显示所有学生的所有课程的成绩及平均成绩
select * from sc
left join (select sid,avg(score) as avg_score from sc
           group by sid)r
on r.sid=sc.sid
order by avg_score desc;

#14.查询各科成绩的最高分、最低分和平均分
/*create view course_score as
select course.CId,course.Cname,sc.score
from course left join sc
on course.cid=sc.cid;*/

select cid,cname,max(score) as max_score,
min(score) as min_score,
avg(score) as avg_score,
sum(case when score>=60 then 1 else 0 end)/count(*) as 及格率,
sum(case when 80>score&score>=70 then 1 else 0 end)/count(*) as 中等率,
sum(case when 90>score&score>=80 then 1 else 0 end)/count(*) as 优良率,
sum(case when score>=90 then 1 else 0 end)/count(*) as 优秀率,
count(*) as num
from course_score
group by cid
order by num desc,cid asc;

#15.按各科成绩进行排序，并显示排名，成绩重复时保留名次空缺
select A.CID,A.sid,A.score,count(B.score)+1 as rank
from sc as A
left join sc as B
on B.score>A.score and B.cid=A.cid
group by A.cid,A.sid
order by A.cid,rank asc;
#15.1 按各科成绩进行排序，并显示排名，成绩重复时合并名次
select a.*,count(distinct b.score)+1 as rank
from sc as a
left join sc as b
on b.score>a.score and b.cid=a.cid
group by a.cid,a.sid
order by a.cid,rank;

#16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select a.sid,a.total,count(b.total)+1 as rank
from (select sid,sum(score) as total
                        from sc
                        group by sid )a
left join (select sid,sum(score) as total
                        from sc
                        group by sid )b
on b.total>a.total
group by a.sid;

#16.1查询学生的总成绩，并进行排名，总分重复时合并名次
select a.sid,a.total,count(distinct b.total)+1 as rank
from (select sid,sum(score) as total
                        from sc
                        group by sid )a
left join (select sid,sum(score) as total
                        from sc
                        group by sid )b
on b.total>a.total
group by a.sid;

#17.统计各科成绩各分数段的人数：课程编号，课程名称，[100-85],[85-70],[70-60],[60-0]
select sc.cid,cname,
sum(case when score<=100 and score>=85 then 1 else 0 end)/count(*) as s1,
sum(case when score<85 and score>=70 then 1 else 0 end)/count(*) as s2,
sum(case when score<70 and score>=60 then 1 else 0 end)/count(*) as s3,
sum(case when score<60 then 1 else 0 end)/count(*) as s4
from sc,course
where sc.cid=course.cid
group by sc.cid;

#18.查询各科成绩前三名的记录
select a.*,count(b.sid)+1 as rank
from sc as a
left join sc as b
on a.cid=b.cid and b.score>a.score
group by a.cid,a.sid
having rank<=3
order by a.cid,a.score desc;

#19.查询每门课选修的学生人数
select cid,count(sid)
from sc
group by cid;

#20.查询出只修两门课程的学生学号和姓名
select student.sid,student.Sname
from student inner join sc
on sc.sid=student.sid
group by sc.sid
having count(cid)=2;

#21.查询男生、女生人数
select Ssex,count(*) as num
from student
group by Ssex;

#22.查询名字中带有风字的学生信息
select *
from student
where Sname like '%风%';

#24.查询1990年出生的学生名单
select *
from student
where year(sage)=1990;

#25.查询每门课的平均成绩，结果按平均成绩降序排列，若平均成绩相同时，则按课程号升序排列
select cid,avg(score) as avg_score
from sc
group by cid
order by avg_score desc,cid asc;

#26.查询平均成绩≥85的所有学生的学号、姓名和平均成绩
select student.sid,student.sname,avg(score) as avg_score
from student,sc
where student.sid=sc.sid
group by student.sid
having avg_score>=85;

#27.查询课程名称为数学，且分数低于60的学生姓名和分数
select student.sname,sc.score
from student,sc,course
where student.sid=sc.sid
and course.cid=sc.cid
and course.Cname='数学'
and sc.score<60;

#28.查询所有学生的课程及分数情况
select student.sid,student.sname,cid,score
from student left join sc
on student.sid=sc.sid;

#29.查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数
select student.sname,course.cname,sc.score
from student,sc,course
where student.sid=sc.sid
and course.cid=sc.cid
and student.sid not in (select sid from sc group by sid having max(score)<=70);

#30.查询存在不及格的课程
select distinct course.cname,sc.cid
from course,sc
where course.cid=sc.cid
having sc.cid not in (select sid from sc group by cid having min(score)>=60);

#31.查询课程编号为01且课程成绩在80分以上的学生学号和姓名
select student.sid,sname
from student,sc
where sc.sid=student.sid
and sc.score>80
and sc.cid='01';

#32.求每门课程的学生人数
select cid,count(sid) as num
from sc
group by cid;

#33.成绩不重复，查询选张三老师授课的学生中，成绩最高的学生信息及其成绩
select student.*,score
from sc,student,course,teacher
where sc.sid=student.sid
and course.cid=sc.cid
and course.tid=teacher.tid
and tname='张三'
order by score desc
limit 0,1; #降序排列取第一名

#34.成绩重复，查询选李四老师授课的学生中，成绩最高的学生信息及其成绩
select student.*,score
from sc,student,course,teacher
where sc.sid=student.sid
and course.cid=sc.cid
and course.tid=teacher.tid
and tname='李四'
and sc.score=(select max(score)from sc,student,course,teacher
               where sc.sid=student.sid
               and course.cid=sc.cid
               and course.tid=teacher.tid
               and tname='李四') #找出最高分;

#35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select *
from sc as a
left join sc as b
on a.sid=b.sid and b.cid<>a.cid
where a.score=b.score
group by a.cid;

#36.查询每门课程成绩最好的前两名
select a.*,count(b.sid)+1 as rank
from sc as a
left join sc as b
on b.score>a.score and a.cid=b.cid #左连结b表
group by a.cid,a.sid
having rank<=2
order by cid;

#37.统计每门课程的选修人数，超过5人
select cid,count(sid) as num
from sc
group by cid
having num>5;

#38.查询至少选修了两门课程的学生学号
select sid
from sc
group by sid
having count(sid)>=2;

#39.查询选修了全部课程的学生信息
select student.*
from student,sc
where student.sid=sc.sid
and sc.cid in (select cid from course)
group by sc.sid
having count(sc.cid)=(select count(distinct cid) from course);

#40.查询各学生的年龄，只按年份计算
select sname,year(now())-year(sage) as Age
from student;

#41.按出生日期来算，当前月日<出生月日，则年龄减1
select sid,case when month(now())<month(sage) or
         ( month(now())=month(sage) and day(now())<day(sage)  )
       then year(now())-year(sage)-1  else  year(now())-year(sage) end as Age
from student;

#42.查询本周过生日的同学
select date_format(now(),'%w'); #查询今天星期几
set @mon=(select subdate(curdate(),date_format(curdate(),'%w')-1)); #本周一的日期
set @sun=(select adddate(curdate(),7-date_format(now(),'%w'))); #本周日的日期
select sid
from student
where
date_format(sage,'%m-%d')<=date_format(@sun,'%m-%d')
and date_format(sage,'%m-%d')>=date_format(@mon,'%m-%d');

#43.查询下周过生日的同学
set @Nextmon=(select adddate(curdate(),7-date_format(curdate(),'%w')+1)); #下周一的日期
set @Nextsun=(select adddate(curdate(),7-date_format(now(),'%w')+7)); #下周日的日期
select sid
from student
where
date_format(sage,'%m-%d')<=date_format(@Nextsun,'%m-%d')
and date_format(sage,'%m-%d')>=date_format(@Nextmon,'%m-%d');

#44.查询本月过生日的同学
select sid
from student
where month(sage)=month(now());

#45.查询下月过生日的同学
select sid
from student
where month(sage)=month(now())+1;

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏9 回帖

关键词：学生信息 课程成绩 课程名称 平均成绩 学生人数

楼主勤奋

cheetahfly 发表于 2019-4-5 16:03
楼主勤奋

保研狗无聊到学习:）

你好楼主想问一下  练习题的题目原数据库有吗

玉瑾然 发表于 2019-4-8 13:22
你好楼主想问一下  练习题的题目原数据库有吗

题目就在网上搜的，把前面几个create table还有insert values命令，运行后就生成题目对应的数据库了。如果你说数据库软件的话，我下次再分享。

顶一下！！！！！