首页 > 代码库 > SQL面试笔试经典题(Part 1)
SQL面试笔试经典题(Part 1)
本文是在Cat Qi的原贴的基础之上，经本人逐题分别在MySql数据库中实现的笔记，持续更新...
参考原贴：http://www.cnblogs.com/qixuejia/p/3637735.html
01 表结构
Student(Sno,Sname,Sage,Ssex) 学生表
Course(Cno,Cname,Tno) 课程表
SC(Sno,Cno,score) 成绩表
Teacher(Tno,Tname) 教师表
02 建表及插入测试数据
（1） 建表：
1 DROP TABLE IF EXISTS student ; 2 DROP TABLE IF EXISTS course ; 3 DROP TABLE IF EXISTS sc ; 4 DROP TABLE IF EXISTS teacher ; 5 6 CREATE TABLE Student 7 ( 8 Sno int, 9 Sname varchar(32),10 Sage int,11 Ssex varchar(8)12 )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;13 14 CREATE TABLE Course 15 ( 16 Cno INT, 17 Cname varchar(32), 18 Tno INT 19 )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;20 21 CREATE TABLE Sc 22 ( 23 Sno INT, 24 Cno INT,25 score INT 26 )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;27 28 CREATE TABLE Teacher 29 ( 30 Tno INT, 31 Tname varchar(16) 32 )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
【注】MySQL数据库建表时需要添加“ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci”命令，否则中文会发生乱码。
（2） 插入测试数据：
1 insert into Student select 1,‘刘一‘,18,‘男‘ union all 2 select 2,‘钱二‘,19,‘女‘ union all 3 select 3,‘张三‘,17,‘男‘ union all 4 select 4,‘李四‘,18,‘女‘ union all 5 select 5,‘王五‘,17,‘男‘ union all 6 select 6,‘赵六‘,19,‘女‘ 7 8 insert into Teacher select 1,‘叶平‘ union all 9 select 2,‘贺高‘ union all10 select 3,‘杨艳‘ union all11 select 4,‘周磊‘;12 13 insert into Course select 1,‘语文‘,1 union all14 select 2,‘数学‘,2 union all15 select 3,‘英语‘,3 union all16 select 4,‘物理‘,4;17 18 insert into SC 19 select 1,1,56 union all 20 select 1,2,78 union all 21 select 1,3,67 union all 22 select 1,4,58 union all 23 select 2,1,79 union all 24 select 2,2,81 union all 25 select 2,3,92 union all 26 select 2,4,68 union all 27 select 3,1,91 union all 28 select 3,2,47 union all 29 select 3,3,88 union all 30 select 3,4,56 union all 31 select 4,2,88 union all 32 select 4,3,90 union all 33 select 4,4,93 union all 34 select 5,1,46 union all 35 select 5,3,78 union all 36 select 5,4,53 union all 37 select 6,1,35 union all 38 select 6,2,68 union all 39 select 6,4,71;
03 问题及实现代码
（1）查询“1”课程比“2”课程成绩高的所有学生的学号；
select a.sno from(select sno,score from sc where cno=1) a,(select sno,score from sc where cno=2) bwhere a.sno=b.sno and a.score>b.score;
（2）查询平均成绩大于60分的同学的学号和平均成绩；
select Sno,AVG(Score) as AvgScore from SC group by Sno having AVG(Score)>60
（3）查询所有同学的学号、姓名、选课数、总成绩；
select student.sno,student.sname,count(sc.cno),sum(sc.score) from student left outer join sc on student.sno = sc.sno group by student.sno order by student.sno;
（4）查询姓“李”的老师的个数；
select count(distinct tname) as count from teacher where tname like ‘李%‘;
（5）查询没学过“叶平”老师课的同学的学号、姓名；
select s.sno,s.sname from student s where s.sno not in ( select distinct(sc.sno) from sc ,course c,teacher t where sc.cno = c.cno and c.tno = t.tno and t.tname = ‘叶平‘ )
（6）查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
select s.sno,s.sname from student s, (select sno from sc where cno=1) a, (select sno from sc where cno=2) b where s.sno = a.sno and a.sno = b.sno;
方法二 用exist函数
select s.Sno,s.Sname from Student s,SC sc where s.Sno=sc.Sno and sc.Cno=1 and exists ( select * from SC sc2 where sc.Sno=sc2.Sno and sc2.Cno=2 )
（7）查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select s.sno,s.sname from student s,teacher t, course c left outer join sc on c.cno = sc.cno where t.tname="叶平" and t.tno = c.cno and s.sno = sc.sno ;
或者：
select s.sno,s.sname from student s where s.sno in ( select sc.sno from sc,course c,teacher t where c.cno=sc.cno and c.tno=t.tno and t.tname ="叶平" group by sc.sno having count(sc.cno)= ( select count(c1.cno) from course c1,teacher t1 where c1.tno=t1.tno and t1,tname ="叶平" ) );
（8）查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
select s.sno,s.sname from student s where s.sno in ( select a.sno from (select sno,score from sc where cno=2) a, (select sno,score from sc where cno=1) b where a.sno = b.sno and a.score < b.score );
（9）查询有课程成绩小于60分的同学的学号、姓名；
select s.sno,s.sname from student s,sc where sc.score<60 and s.sno=sc.sno group by s.sno;
（10）查询没有学全所有课的同学的学号、姓名；
select s.sno,s.sname from student s where s.sno not in ( select sc.sno from sc group by sc.sno having count(distinct sc.cno)= ( select count(distinct c.cno) from course c ) );
（11）查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
select distinct(s.sno),s.sname from student s,sc where s.sno=sc.sno and sc.cno in ( select distinct(cno) from sc where sno=1 );
（12）查询至少学过学号为“1”同学所有一门课的其他同学学号和姓名；
select distinct(s.sno),s.sname from student s,sc where s.sno=sc.sno and s.sno != 1 and sc.cno in ( select distinct(cno) from sc where sno=1 );
（13）把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；
update sc set score = ( select avg(sc1.score) from sc sc1,course c,teacher t where sc1.cno = c.cno and c.tno = t.tno and t.tname="叶平" ) where cno in ( select cno from course c,teacher t where c.tno = t.tno and t.tname="叶平" );
（14）查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名；
select s.sno,s.sname from student s where s.sno != 2 and s.sno in ( select distinct(sno) from sc where cno in (select cno from sc where sno=2) group by sno having count(distinct cno)= ( select count(distinct cno) from sc where sno=2 ) );
（15）删除学习“叶平”老师课的SC表记录；
delete from sc where cno in ( select c.cno from course c,teacher t where c.tno = t.tno and t.tname="叶平" );
（16）向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号作为学号；②将“2”号课程的平均成绩作为其成绩；
insert into sc select s.sno,2,(select avg(score) from sc where cno=2) from student s where s.sno not in (select distinct(sno) from sc where cno=2);
（17）按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分； 【此处已补回15题中被删除的数据】
select sc0.sno as "学生ID", (select score from sc where sno=sc0.sno and cno =1) as "语文" , (select score from sc where sno=sc0.sno and cno =2) as "数学" , (select score from sc where sno=sc0.sno and cno =3) as "英语" , count(sc0.cno) as "有效课程数", avg(sc0.score) as "有效平均分" from sc sc0 group by sc0.sno order by avg(sc0.score);
（18）查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select cno as "课程ID",max(score) as "最高分",min(score) as "最低分" from sc group by cno;
（19）按各科平均成绩从低到高和及格率的百分数从高到低顺序；
select sc.cno as "课程ID", c.cname as "课程名称", avg(sc.score) as "平均分", 100*sum(case when sc.score >= 60 then 1 else 0 end)/count(sc.score) as "Percent(%)" from sc ,course c where sc.cno = c.cno group by sc.cno order by avg(sc.score) desc ;
（20）查询如下课程平均成绩和及格率的百分数(备注：需要在1行内显示): 企业管理（2），OO&UML （3），数据库（4）
select sum(case when cno=2 then score else 0 end)/sum(case when cno=2 then 1 else 0 end) as "企业管理平均成绩", 100*sum(case when cno=2 and score >= 60 then 1 else 0 end)/sum(case when cno=2 then 1 else 0 end) as "企业管理及格率(%)", sum(case when cno=3 then score else 0 end)/sum(case when cno=3 then 1 else 0 end) as "OO&UML平均成绩", 100*sum(case when cno=3 and score >= 60 then 1 else 0 end)/sum(case when cno=3 then 1 else 0 end) as "OO&UML及格率(%)", sum(case when cno=4 then score else 0 end)/sum(case when cno=4 then 1 else 0 end) as "数据库平均成绩", 100*sum(case when cno=4 and score >= 60 then 1 else 0 end)/sum(case when cno=4 then 1 else 0 end) as "数据库及格率(%)" from sc;
（21）查询不同老师所教不同课程平均分从高到低显示；
select t.tname as "老师姓名", c.cname as "课程名称", avg(sc.score) as "平均分" from sc,teacher t,course c where t.tno=c.tno and c.cno=sc.cno group by t.tno order by avg(sc.score) desc;
（22）查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（1），马克思（2），UML （3），数据库（4）
select distinct SC.Sno As "学生学号", Student.Sname as "学生姓名" , T1.score as "企业管理", T2.score as "马克思", T3.score as "UML", T4.score as "数据库", ifnull(T1.score,0) + ifnull(T2.score,0) + ifnull(T3.score,0) + ifnull(T4.score,0) as "总分" from Student,SC left join SC as T1 on SC.Sno = T1.Sno and T1.Cno = 1 left join SC as T2 on SC.Sno = T2.Sno and T2.Cno = 2 left join SC as T3 on SC.Sno = T3.Sno and T3.Cno = 3 left join SC as T4 on SC.Sno = T4.Sno and T4.Cno = 4 where student.Sno=SC.Sno order by ifnull(T1.score,0) + ifnull(T2.score,0) + ifnull(T3.score,0) + ifnull(T4.score,0) desc ;
SQL面试笔试经典题(Part 1)
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。