mysql經典面試題

標籤：mysql 面試題

Student(S#,Sname,Sage,Ssex) 學生表

Course(C#,Cname,T#) 課程表
SC(S#,C#,score) 成績表
Teacher(T#,Tname) 教師表 

問題：
1、查詢“001”課程比“002”課程成績高的所有學生的學號；

select a.S#from (select s#,score from SC where C#=’001′) a,(select s#,score from SC where C#=’002′) bwhere a.score>b.score and a.s#=b.s#;s

2、查詢平均成績大於60分的同學的學號和平均成績；

select S#,avg(score)from scgroup by S# having avg(score) >60;

3、查詢所有同學的學號、姓名、選課數、總成績；

select Student.S#,Student.Sname,count(SC.C#),sum(score)from Student left Outer join SC on Student.S#=SC.S#group by Student.S#,Sname

4、查詢姓“李”的老師的個數；

select count(distinct(Tname))from Teacherwhere Tname like ‘李%’;

5、查詢沒學過“葉平”老師課的同學的學號、姓名；

select Student.S#,Student.Snamefrom Studentwhere S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’葉平’);

6、查詢學過“001”並且也學過編號“002”課程的同學的學號、姓名；
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S# and SC.C#=’001′and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#=’002′);
7、查詢學過“葉平”老師所教的所有課的同學的學號、姓名；

select S#,Snamefrom Studentwhere S# in(select S#from SC ,Course ,Teacherwhere SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’葉平’ group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname=’葉平’));

8、查詢所有課程成績小於60分的同學的學號、姓名；

select S#,Snamefrom Studentwhere S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);

9、查詢沒有學全所有課的同學的學號、姓名；

select Student.S#,Student.Snamefrom Student,SCwhere Student.S#=SC.S#group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);

10、查詢至少有一門課與學號為“1001”的同學所學相同的同學的學號和姓名；
select S#,Sname
from Student,SC
where Student.S#=SC.S# and C# in （select C# from SC where S#=‘1001‘）;

11、刪除學習“葉平”老師課的SC表記錄；

Delect SCfrom course ,Teacherwhere Course.C#=SC.C# and Course.T#= Teacher.T# and Tname=‘葉平‘;

12、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分

SELECT L.C# 課程ID,L.score 最高分,R.score 最低分FROM SC L ,SC RWHERE L.C# = R.C#andL.score = (SELECT MAX(IL.score)FROM SC IL,Student IMWHERE IL.C# = L.C# and IM.S#=IL.S#GROUP BY IL.C#)andR.Score = (SELECT MIN(IR.score)FROM SC IRWHERE IR.C# = R.C#GROUP BY IR.C# );

13、查詢學生平均成績及其名次

SELECT 1+(SELECT COUNT( distinct 平均成績)FROM (SELECT S#,AVG(score) 平均成績FROM SCGROUP BY S# ) T1WHERE 平均成績 > T2.平均成績) 名次, S# 學生學號,平均成績FROM (SELECT S#,AVG(score) 平均成績 FROM SC GROUP BY S# ) T2ORDER BY 平均成績 desc;

14、查詢各科成績前三名的記錄:(不考慮成績並列情況)

SELECT t1.S# as 學生ID,t1.C# as 課程ID,Score as 分數FROM SC t1WHERE score IN (SELECT TOP 3 scoreFROM SCWHERE t1.C#= C#ORDER BY score DESC)ORDER BY t1.C#;

15、查詢每門功成績最好的前兩名

SELECT t1.S# as 學生ID,t1.C# as 課程ID,Score as 分數FROM SC t1WHERE score IN (SELECT TOP 2 scoreFROM SCWHERE t1.C#= C#ORDER BY score DESC )ORDER BY t1.C#;

補充：
已經知道原表
year salary
——————
2000 1000
2001 2000
2002 3000
2003 4000

解：

select b.year,sum(a.salary)from salary a,salary bwhere a.year<=b.yeargroup by b.yearorder by b.year;

在面試過程中多次碰到一道SQL查詢的題目，查詢A(ID,Name)表中第31至40條記錄，ID作為主鍵可能是不是連續增長的列，完整的查詢語句如下：
方法一：

select top 10 *from Awhere ID >(select max(ID) from (select top 30 ID from A order by ID ) T) order by ID

方法二：

select top 10 *from Awhere ID not In (select top 30 ID from A order by ID)order by ID;

mysql經典面試題