2023年6月21日发(作者：)
SQL数据库⾯试题以及答案（50题）Student(Sid,Sname,Sage,Ssex)学⽣表Sid：学号Sname：学⽣姓名Sage：学⽣年龄Ssex：学⽣性别Course(Cid,Cname,T#)课程表Cid：课程编号Cname：课程名称Tid：教师编号SC(Sid,Cid,score)成绩表Sid：学号Cid：课程编号score：成绩Teacher(Tid,Tname)教师表Tid：教师编号：Tname：教师名字1、查询“001”课程⽐“002”课程成绩⾼的所有学⽣的学号select from
(select sid,score from sc where cid='001')a,(select sid,score from sc where cid='002')b
where = and >;2、查询平均成绩⼤于60分的同学的学号和平均成绩select sid,avg(score) from scgroup by sid
having avg(score)>60;3、查询所有同学的学号、姓名、选课数、总成绩select ,,count_cid as 选课数,
sum_score as 总成绩from student sleft join
(select sid,count(cid) as count_cid,sum(score) as sum_score
from sc group by sid )scon = ;4、查询姓‘李’的⽼师的个数：select count(tname)from teacher
where tname like '李%';5、查询没有学过“叶平”⽼师可的同学的学号、姓名：select ,
from student as s
where not in ( select DISTINCT sid
from sc as sc
where in ( select cid
from course as c
left join teacher as t on =
where = '叶平'));6、查询学过“叶平”⽼师所教的所有课的同学的学号、姓名：select ,
from student as s
where in ( select distinct
from sc as sc
where in ( select cid
from course as c
left join teacher as t on =
where = '叶平') group by
HAVING count(cid)= (select count(cid)
from course as c left join teacher as t on =
where = '叶平'));7、查询学过“011”并且也学过编号“002”课程的同学的学号、姓名：SELECT ,
from student as s
left join sc as sc on = re = '001'and EXISTS( select * from sc as sc_2
where = sc_
and sc_='002');select ,rom student as s
left join sc as sc
on = re = '001'and in ( select sid from sc as sc_2
where sc_='002'
and sc_ = );8、查询课程编号“002”的成绩⽐课程编号“001”课程低的所有同学的学号、姓名：select sid,snamefrom (select ,,score, (select score from sc as sc_2
where sc_ =
and sc_ = '002') as score2
from student,sc
where = and cid = '001') s_2where score2 (select from student s,sc where = and score>60 );select sid,snamefrom student swhere not EXISTS (select from sc where = and >60);10、查询没有学全所有课的同学的学号、姓名：select ,rom student s ,sc sc where = up by ,aving count()<(select count(cid) from course);select ,rom student s right join sc sc on = up by ,aving count()<(select count(cid) from course);11、查询⾄少有⼀门课与学号为“1001”同学所学相同的同学的学号和姓名：select ,snamefrom student,sc where = cid in (select cid from sc where sid='1001');select ,rom sc sc left join student as son = re in (select cid from sc where sid='1001');select sc_,rom sc sc_1 left join student as son sc_ = re exists (select sc_ from sc as sc_2 where sc_ = sc_ and sc_ = '1001');12、查询⾄少学过学号为“001”同学所有⼀门课的其他同学学号和姓名；13、把“SC”表中“叶平”⽼师教的课的成绩都更改为此课程的平均成绩：update sc set score = (select avg(sc_) from sc sc_2 where sc_ = )where cid in (select from course c left join teacher t on = where = '叶平');14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名：select sc_ from (select cid from sc where sid='1002')aleft join sc sc_1 on = sc_re sc_<>'1002' group by sc_ having count(sc_) = (select count(cid) from sc where sid='1002');select , from (select sid,GROUP_CONCAT(cid order by cid separator ',') as cid_str from sc where sid='1002')b,(select sid,GROUP_CONCAT(cid order by cid separator ',') as cid_str from sc group by sid)aleft join student s on = re _str = _str and <>'1002';15、删除学习“叶平”⽼师课的SC表记录：delete from sc WHEREcid in (select from course c LEFT JOIN teacher t on = where = '叶平');insert into sc select sid,'002',(select avg(score) from sc where cid='0022')from student where sid not in (select sid from sc where cid='002');17、按平均成绩从⾼到低显⽰所有学⽣的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显⽰：学⽣ID，数据库，企业管理，英语，有效课程数，有效平均分：select sid as 学⽣id,(SELECT score from sc where = and cid='004') as 数据库,(select score from sc where = and cid='001') as 企业管理,(select score from sc where = and cid='015') as 英语,count(cid) as 有效课程数, avg() as 平均成绩from sc as t group by sidorder by avg();18、查询各科成绩最⾼和最低的分：以如下的形式显⽰：课程ID，最⾼分，最低分select as 课程id, as 最⾼分, as 最低分from sc l,sc rwhere = = (select max() from sc t where = group by )and = (select min() from sc t where = group by )order by ;select cid as 课程id,max(score) as 最⾼分,min(score) as 最低分from sc group by cid;19、按各科平均成绩从低到⾼和及格率的百分数从⾼到低顺序：SELECT as 课程号, as 课程名,COALESCE(avg(score),0) as 平均成绩,100*sum(case when COALESCE(score,0)>=60 then 1 else 0 END)/count(*) as 及格百分数from sc tleft join course c on = up by er by 100*sum(case when COALESCE(score,0)>=60 then 1 else 0 END)/count(*);20、查询如下课程平均成绩和及格率的百分数(⽤”1⾏”显⽰): 企业管理（001），马克思（002），OO&UML （003），数据库（004）：21、查询不同⽼师所教不同课程平均分从⾼到低显⽰：select as 教师id, as 教师姓名, as 课程id,avg(score) as 平均成绩from sc as scLEFT JOIN course c on = t join teacher t on = up by order by avg() desc;22、查询如下课程成绩第3名到第6名的学⽣成绩单：企业管理(001)，马克思(002)，UML(003)，数据库(004)：23、统计下列各科成绩，各分数段⼈数：课程ID，课程名称，[100-85],[85-70],[70-60],[ ⼩于60] ：select as 课程id,cname as 课程名称,sum(case when score between 85 and 100 then 1 else 0 end) as '[100-85]',sum(case when score between 70 and 85 then 1 else 0 end) as '[85-70]',sum(case when score between 60 and 70 then 1 else 0 end) as '[70-60]',sum(case when score<60 then 1 else 0 end) as '[60-0]'from sc as sc left join course as con = up by ;24、查询学⽣平均成绩及其名次：select 1+(select count(distinct 平均成绩) from (select sid,avg(score) as 平均成绩 from sc group by sid)t1 where 平均成绩>t2.平均成绩) as 名次,sid as 学⽣学号,平均成绩 from (select sid,avg(score) 平均成绩 from sc group by sid) as t2order by 平均成绩 desc;25、查询各科成绩前三名的记录（不考虑成绩并列情况）：select sid,cid,scorefrom sc sc_1where (select count(3) from sc sc_2 where sc_ = sc_ and sc_>=sc_)<=2 order by sc_);26、查询每门课程被选修的学⽣数：select cid, count(sid)from sc group by cid;27、查询出只选修⼀门课程的全部学⽣的学号和姓名：select ,,count() as 课程数from sc as scLEFT JOIN student as son = up by ing count()=1;28、查询男⽣、⼥⽣⼈数：select count(ssex) as 男⽣⼈数from studentgroup by ssexhaving ssex = '男';select count(2) from studentwhere ssex = '⼥';29、查询姓“张”的学⽣名单：select sid,snamefrom student where sname like '张%';30、查询同名同姓的学⽣名单，并统计同名⼈数：select sname,count(8)from student group by snamehaving count(8)>1;31、1981年出⽣的学⽣名单（注：student表中sage列的类型是datetime）:关注公众号Java技术栈回复⾯试获取更多⾯试题。32、查询平均成绩⼤于85的所有学⽣的学号、姓名和平均成绩：select ,,avg() as 平均成绩from sc as scleft join student as s on = up by having avg()>85;33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列：select cid,avg(score)from sc group by cidorder by avg(score),cid desc;34、查询课程名称为“数据库”，且分数低于60的学⽣名字和分数：select ,,,rom course cleft join sc on = T JOIN student s on = re = '数据库' and <60;35、查询所有学⽣的选课情况：select ,,,rom sc LEFT JOIN course c on = t join student s on = ;36、查询任何⼀门课程成绩在70分以上的姓名、课程名称和分数：select distinct ,,,rom sc left join student s on = t join course c on = re >70;37、查询不及格的课程，并按课程号从⼤到⼩的排列：select cidfrom sc where score<60ORDER BY cid;38、查询课程编号为“003”且课程成绩在80分以上的学⽣的学号和姓名：select , from sc left join student s on = re = '003' and >80;39、求选了课程的学⽣⼈数：select count(2) from (select distinct sid from sc)a;40、查询选修“叶平”⽼师所授课程的学⽣中，成绩最⾼的学⽣姓名及其成绩：select ,rom sc sc left join student s on = t join course c on = t join teacher t on = re = '叶平'and = (select max(score) from sc sc_1 where = sc_);41、查询各个课程及相应的选修⼈数：select cid,count(*) from sc group by cid;42、查询不同课程成绩相同的学⽣和学号、课程号、学⽣成绩：select DISTINCT ,,rom sc as a ,sc as b where = nd <> ;43、查询每门课程成绩最好的前两名：关注公众号Java技术栈回复⾯试获取更多⾯试题。44、统计每门课程的学⽣选修⼈数(超过10⼈的课程才统计)。要求输出课程号和选修⼈数，查询结果按⼈数降序排序，若⼈数相同，按课程号升序排序：select cid as 课程号,count(8) as 选修⼈数from scgroup by cidHAVING count(sid)>10order by count(8) desc,cid;45、检索⾄少选修两门课程的学⽣学号：select sidfrom scgroup by sidhaving count(8)>=2;46、查询全部学⽣选修的课程和课程号和课程名：select cid,cnamefrom course where cid in (select cid from sc group by cid);47、查询没学过”叶平”⽼师讲授的任⼀门课程的学⽣姓名：select sname from student where sid not in ( select sid from sc,course,teacher where = and = and ='叶平');48、查询两门以上不及格课程的同学的学号以及其平均成绩：select sid,avg(COALESCE(score,0))from scwhere sid in ( select sid from sc where score<60 group by sid having count(8)>2)group by sid;49、检索“004”课程分数⼩于60，按分数降序排列的同学学号：select sid,scorefrom scwhere cid='004'and score<60order by score desc;50、删除“002”同学的“001”课程的成绩：delete from scwhere sid = '002'and cid = '001';
2023年6月21日发(作者：)
SQL数据库⾯试题以及答案（50题）Student(Sid,Sname,Sage,Ssex)学⽣表Sid：学号Sname：学⽣姓名Sage：学⽣年龄Ssex：学⽣性别Course(Cid,Cname,T#)课程表Cid：课程编号Cname：课程名称Tid：教师编号SC(Sid,Cid,score)成绩表Sid：学号Cid：课程编号score：成绩Teacher(Tid,Tname)教师表Tid：教师编号：Tname：教师名字1、查询“001”课程⽐“002”课程成绩⾼的所有学⽣的学号select from
(select sid,score from sc where cid='001')a,(select sid,score from sc where cid='002')b
where = and >;2、查询平均成绩⼤于60分的同学的学号和平均成绩select sid,avg(score) from scgroup by sid
having avg(score)>60;3、查询所有同学的学号、姓名、选课数、总成绩select ,,count_cid as 选课数,
sum_score as 总成绩from student sleft join
(select sid,count(cid) as count_cid,sum(score) as sum_score
from sc group by sid )scon = ;4、查询姓‘李’的⽼师的个数：select count(tname)from teacher
where tname like '李%';5、查询没有学过“叶平”⽼师可的同学的学号、姓名：select ,
from student as s
where not in ( select DISTINCT sid
from sc as sc
where in ( select cid
from course as c
left join teacher as t on =
where = '叶平'));6、查询学过“叶平”⽼师所教的所有课的同学的学号、姓名：select ,
from student as s
where in ( select distinct
from sc as sc
where in ( select cid
from course as c
left join teacher as t on =
where = '叶平') group by
HAVING count(cid)= (select count(cid)
from course as c left join teacher as t on =
where = '叶平'));7、查询学过“011”并且也学过编号“002”课程的同学的学号、姓名：SELECT ,
from student as s
left join sc as sc on = re = '001'and EXISTS( select * from sc as sc_2
where = sc_
and sc_='002');select ,rom student as s
left join sc as sc
on = re = '001'and in ( select sid from sc as sc_2
where sc_='002'
and sc_ = );8、查询课程编号“002”的成绩⽐课程编号“001”课程低的所有同学的学号、姓名：select sid,snamefrom (select ,,score, (select score from sc as sc_2
where sc_ =
and sc_ = '002') as score2
from student,sc
where = and cid = '001') s_2where score2 (select from student s,sc where = and score>60 );select sid,snamefrom student swhere not EXISTS (select from sc where = and >60);10、查询没有学全所有课的同学的学号、姓名：select ,rom student s ,sc sc where = up by ,aving count()<(select count(cid) from course);select ,rom student s right join sc sc on = up by ,aving count()<(select count(cid) from course);11、查询⾄少有⼀门课与学号为“1001”同学所学相同的同学的学号和姓名：select ,snamefrom student,sc where = cid in (select cid from sc where sid='1001');select ,rom sc sc left join student as son = re in (select cid from sc where sid='1001');select sc_,rom sc sc_1 left join student as son sc_ = re exists (select sc_ from sc as sc_2 where sc_ = sc_ and sc_ = '1001');12、查询⾄少学过学号为“001”同学所有⼀门课的其他同学学号和姓名；13、把“SC”表中“叶平”⽼师教的课的成绩都更改为此课程的平均成绩：update sc set score = (select avg(sc_) from sc sc_2 where sc_ = )where cid in (select from course c left join teacher t on = where = '叶平');14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名：select sc_ from (select cid from sc where sid='1002')aleft join sc sc_1 on = sc_re sc_<>'1002' group by sc_ having count(sc_) = (select count(cid) from sc where sid='1002');select , from (select sid,GROUP_CONCAT(cid order by cid separator ',') as cid_str from sc where sid='1002')b,(select sid,GROUP_CONCAT(cid order by cid separator ',') as cid_str from sc group by sid)aleft join student s on = re _str = _str and <>'1002';15、删除学习“叶平”⽼师课的SC表记录：delete from sc WHEREcid in (select from course c LEFT JOIN teacher t on = where = '叶平');insert into sc select sid,'002',(select avg(score) from sc where cid='0022')from student where sid not in (select sid from sc where cid='002');17、按平均成绩从⾼到低显⽰所有学⽣的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显⽰：学⽣ID，数据库，企业管理，英语，有效课程数，有效平均分：select sid as 学⽣id,(SELECT score from sc where = and cid='004') as 数据库,(select score from sc where = and cid='001') as 企业管理,(select score from sc where = and cid='015') as 英语,count(cid) as 有效课程数, avg() as 平均成绩from sc as t group by sidorder by avg();18、查询各科成绩最⾼和最低的分：以如下的形式显⽰：课程ID，最⾼分，最低分select as 课程id, as 最⾼分, as 最低分from sc l,sc rwhere = = (select max() from sc t where = group by )and = (select min() from sc t where = group by )order by ;select cid as 课程id,max(score) as 最⾼分,min(score) as 最低分from sc group by cid;19、按各科平均成绩从低到⾼和及格率的百分数从⾼到低顺序：SELECT as 课程号, as 课程名,COALESCE(avg(score),0) as 平均成绩,100*sum(case when COALESCE(score,0)>=60 then 1 else 0 END)/count(*) as 及格百分数from sc tleft join course c on = up by er by 100*sum(case when COALESCE(score,0)>=60 then 1 else 0 END)/count(*);20、查询如下课程平均成绩和及格率的百分数(⽤”1⾏”显⽰): 企业管理（001），马克思（002），OO&UML （003），数据库（004）：21、查询不同⽼师所教不同课程平均分从⾼到低显⽰：select as 教师id, as 教师姓名, as 课程id,avg(score) as 平均成绩from sc as scLEFT JOIN course c on = t join teacher t on = up by order by avg() desc;22、查询如下课程成绩第3名到第6名的学⽣成绩单：企业管理(001)，马克思(002)，UML(003)，数据库(004)：23、统计下列各科成绩，各分数段⼈数：课程ID，课程名称，[100-85],[85-70],[70-60],[ ⼩于60] ：select as 课程id,cname as 课程名称,sum(case when score between 85 and 100 then 1 else 0 end) as '[100-85]',sum(case when score between 70 and 85 then 1 else 0 end) as '[85-70]',sum(case when score between 60 and 70 then 1 else 0 end) as '[70-60]',sum(case when score<60 then 1 else 0 end) as '[60-0]'from sc as sc left join course as con = up by ;24、查询学⽣平均成绩及其名次：select 1+(select count(distinct 平均成绩) from (select sid,avg(score) as 平均成绩 from sc group by sid)t1 where 平均成绩>t2.平均成绩) as 名次,sid as 学⽣学号,平均成绩 from (select sid,avg(score) 平均成绩 from sc group by sid) as t2order by 平均成绩 desc;25、查询各科成绩前三名的记录（不考虑成绩并列情况）：select sid,cid,scorefrom sc sc_1where (select count(3) from sc sc_2 where sc_ = sc_ and sc_>=sc_)<=2 order by sc_);26、查询每门课程被选修的学⽣数：select cid, count(sid)from sc group by cid;27、查询出只选修⼀门课程的全部学⽣的学号和姓名：select ,,count() as 课程数from sc as scLEFT JOIN student as son = up by ing count()=1;28、查询男⽣、⼥⽣⼈数：select count(ssex) as 男⽣⼈数from studentgroup by ssexhaving ssex = '男';select count(2) from studentwhere ssex = '⼥';29、查询姓“张”的学⽣名单：select sid,snamefrom student where sname like '张%';30、查询同名同姓的学⽣名单，并统计同名⼈数：select sname,count(8)from student group by snamehaving count(8)>1;31、1981年出⽣的学⽣名单（注：student表中sage列的类型是datetime）:关注公众号Java技术栈回复⾯试获取更多⾯试题。32、查询平均成绩⼤于85的所有学⽣的学号、姓名和平均成绩：select ,,avg() as 平均成绩from sc as scleft join student as s on = up by having avg()>85;33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列：select cid,avg(score)from sc group by cidorder by avg(score),cid desc;34、查询课程名称为“数据库”，且分数低于60的学⽣名字和分数：select ,,,rom course cleft join sc on = T JOIN student s on = re = '数据库' and <60;35、查询所有学⽣的选课情况：select ,,,rom sc LEFT JOIN course c on = t join student s on = ;36、查询任何⼀门课程成绩在70分以上的姓名、课程名称和分数：select distinct ,,,rom sc left join student s on = t join course c on = re >70;37、查询不及格的课程，并按课程号从⼤到⼩的排列：select cidfrom sc where score<60ORDER BY cid;38、查询课程编号为“003”且课程成绩在80分以上的学⽣的学号和姓名：select , from sc left join student s on = re = '003' and >80;39、求选了课程的学⽣⼈数：select count(2) from (select distinct sid from sc)a;40、查询选修“叶平”⽼师所授课程的学⽣中，成绩最⾼的学⽣姓名及其成绩：select ,rom sc sc left join student s on = t join course c on = t join teacher t on = re = '叶平'and = (select max(score) from sc sc_1 where = sc_);41、查询各个课程及相应的选修⼈数：select cid,count(*) from sc group by cid;42、查询不同课程成绩相同的学⽣和学号、课程号、学⽣成绩：select DISTINCT ,,rom sc as a ,sc as b where = nd <> ;43、查询每门课程成绩最好的前两名：关注公众号Java技术栈回复⾯试获取更多⾯试题。44、统计每门课程的学⽣选修⼈数(超过10⼈的课程才统计)。要求输出课程号和选修⼈数，查询结果按⼈数降序排序，若⼈数相同，按课程号升序排序：select cid as 课程号,count(8) as 选修⼈数from scgroup by cidHAVING count(sid)>10order by count(8) desc,cid;45、检索⾄少选修两门课程的学⽣学号：select sidfrom scgroup by sidhaving count(8)>=2;46、查询全部学⽣选修的课程和课程号和课程名：select cid,cnamefrom course where cid in (select cid from sc group by cid);47、查询没学过”叶平”⽼师讲授的任⼀门课程的学⽣姓名：select sname from student where sid not in ( select sid from sc,course,teacher where = and = and ='叶平');48、查询两门以上不及格课程的同学的学号以及其平均成绩：select sid,avg(COALESCE(score,0))from scwhere sid in ( select sid from sc where score<60 group by sid having count(8)>2)group by sid;49、检索“004”课程分数⼩于60，按分数降序排列的同学学号：select sid,scorefrom scwhere cid='004'and score<60order by score desc;50、删除“002”同学的“001”课程的成绩：delete from scwhere sid = '002'and cid = '001';
发布评论