SQL面試筆試經典題(Part 1)，sqlpart
本文是在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;
View Code
【注】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 all
10 select 3,'楊艷' union all
11 select 4,'周磊';
12
13 insert into Course select 1,'語文',1 union all
14 select 2,'數學',2 union all
15 select 3,'英語',3 union all
16 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;
View Code
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 ;
