關於Sql server 的 幾道面試題

一、簡介

　　今天在網上搜到幾道數據庫題還不錯，是關於數據庫表的操作的題目，這類題目經常出現在面試的筆試題目中。 做了一遍，特在這裡記錄下來做個總結。 

二、題目及答案

　　數據庫中共有四張表，下面將以截圖的方式說明：

老師信息表：                                   學生信息表：

課程表：                                        分數表：

1、查詢"1"課程比"2"課程成績高的所有學生的學號；

select a.S# from (
select  * 
from sc where C#=1 ) a 
inner join  (select  * 
from sc where C#=2)  b on a.S#=b.S#
where a.score>b.Score

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

select  S#,avg(score) as score
from sc 
group by s#
having avg(score)>60 

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

select  a.S#,Sname,
case when b.Num is null then 0 else b.Num end Num ,
case when  b.SumScore is null then 0 else  b.SumScore end SumScore
from student a left join 
(select S#,count(*) Num,sum(score) SumScore from SC group by S# )b on a.S#=b.S#

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

select  count(*) Num
from Teacher  
where Tname like '李%'

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

select S#,Sname 
from student 
where S# not in (
select  distinct S# 
from SC c 
where exists  (
select  C# 
from Course a left join teacher b on a.T#=b.T# 
where  b.Tname='葉平' and c.C#=a.C#) )

6、查詢學過"1"並且也學過編號"2"課程的同學的學號、姓名；

select a.S#,b.Sname from (
select a.S# 
from (
select  * 
from sc 
where C# in (1,2)) a
group by a.S# 
having count(*)=2) a  
left join Student b on a.S#=b.S#

或

select a.S#,C.Sname from (
select  * 
from sc where C#=1 ) a 
inner join  (select  * 
from sc where C#=2)  b on a.S#=b.S#
left join student c on a.S#=c.S#

7、查詢學過"葉平"老師所交的所有課程的同學的學號、姓名；

select a.S#,b.Sname from (
select S# from (
select sc.* from sc  where C# in (
select  C# from Course a inner join 
Teacher b on a.T#=b.T# where b.tname='葉平' )     
) a group by S# 
having  count(*)=(select count(*) from Course a inner join 
Teacher b on a.T#=b.T# where b.tname='葉平')
)  a left join Student b on a.S#=b.S#

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

select distinct a.S#,c.Sname 
from sc  a left join Student c on a.S#=c.S# 
where  not exists (
select  S# 
from sc b where Score>60 and a.S#=b.S#) 

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

select a.* 
from  student  a 
where a.S# not in (select  S# 
from sc 
group by S# 
having count(*)=(
select count(*)
from Course)) 

10、查詢至少有一門課與"張三"的同學相同的同學的學號和姓名；

select  distinct a.S#,b.Sname  
from sc a left join Student b on a.S#=b.S#
where C#  in (
select a.C# 
from Sc a left join student b on a.S#=b.S#
where b.Sname='張三')                                       
and a.S#<> (select  S# from Student where Sname='張三')    

11、查詢學過學號為"1"同學所有課的其他同學學號和姓名；

select a.S#,b.Sname  from (
select a.S# 
from (
select * 
from sc  a
where S#<>1 and exists (select  * 
from sc  b where S#=1 and a.C#=b.C#  ) )  a
group by   a.S#
having count(*)= (select  count(*) 
from sc  b where S#=1)  ) a left join Student b on a.S#=b.S#