SQL面試筆試經典題(Part 1)_ZenDei技術網路在線

本文是在Cat Qi的原貼的基礎之上，經本人逐題分別在MySql資料庫中實現的筆記，持續更新... 參考原貼：http://www.cnblogs.com/qixuejia/p/3637735.html 01 表結構 Student(Sno,Sname,Sage,Ssex) 學生表 Course(C ...

本文是在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 ;