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） 建表：

DROP TABLE IF EXISTS student ;
DROP TABLE IF EXISTS course ;
DROP TABLE IF EXISTS sc ;
DROP TABLE IF EXISTS teacher ;

CREATE TABLE Student
(
Sno int,
Sname varchar(32),
Sage int,
Ssex varchar(8)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE Course 
  ( 
     Cno    INT, 
     Cname varchar(32), 
     Tno    INT 
  )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE Sc 
( 
Sno    INT, 
Cno   INT,
score INT 
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE Teacher 
  ( 
     Tno    INT, 
     Tname varchar(16) 
  )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

　　【注】MySQL数据库建表时需要添加“ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci”命令，否则中文会发生乱码。

　　（2） 插入测试数据：

 insert into Student select 1,'刘一',18,'男' union all
 select 2,'钱二',19,'女' union all
 select 3,'张三',17,'男' union all
 select 4,'李四',18,'女' union all
 select 5,'王五',17,'男' union all
 select 6,'赵六',19,'女' 
 
 insert into Teacher select 1,'叶平' union all
 select 2,'贺高' union all
 select 3,'杨艳' union all
 select 4,'周磊';
 
 insert into Course select 1,'语文',1 union all
 select 2,'数学',2 union all
 select 3,'英语',3 union all
 select 4,'物理',4;
 
 insert into SC 
 select 1,1,56 union all 
 select 1,2,78 union all 
 select 1,3,67 union all 
 select 1,4,58 union all 
 select 2,1,79 union all 
 select 2,2,81 union all 
 select 2,3,92 union all 
 select 2,4,68 union all 
 select 3,1,91 union all 
 select 3,2,47 union all 
 select 3,3,88 union all 
 select 3,4,56 union all 
 select 4,2,88 union all 
 select 4,3,90 union all 
 select 4,4,93 union all 
 select 5,1,46 union all 
 select 5,3,78 union all 
 select 5,4,53 union all 
 select 6,1,35 union all 
 select 6,2,68 union all 
 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) b
where 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 ;