SQL面試，讓你的面試官無fu,ck可說，第17題難倒一片人

04-23

問題及描述：

1.學生表

Student(SID,Sname,Sage,Ssex) --SID 學生編號,Sname 學生姓名,Sage 出生年月,Ssex 學生性別

2.課程表

Course(CID,Cname,TID) --CID --課程編號,Cname 課程名稱,TID 教師編號

3.教師表

Teacher(TID,Tname) --TID 教師編號,Tname 教師姓名

4.成績表

SC(SID,CID,score) --SID 學生編號,CID 課程編號,score 分數

創建測試數據

create table Student(Sid varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))

insert into Student values(01 , N化懷彬 , 1990-01-01 , N男)

insert into Student values(02 , N肖易 , 1990-12-21 , N男)

insert into Student values(03 , N韋達 , 1990-05-20 , N男)

insert into Student values(04 , N姜雪 , 1990-08-06 , N女)

insert into Student values(05 , N秦蓉 , 1991-12-01 , N女)

insert into Student values(06 , N陳丹紅 , 1992-03-01 , N女)

insert into Student values(07 , N韋瀚瑜 , 1989-07-01 , N男)

insert into Student values(08 , N鍾愛軍 , 1990-01-20 , N男)

insert into Student values(09 , N王小凡 , 1985-02-20 , N男)

insert into Student values(10 , N于海洲 , 1989-04-13 , N男)

insert into Student values(11 , N姜曉亮 , 1992-11-03 , N男)

insert into Student values(12 , N王炳洲 , 1989-04-13 , N男)

insert into Student values(13 , N閆衛龍 , 1992-11-03 , N男)

create table Course(Cid varchar(10),Cname nvarchar(10),Tid varchar(10))

insert into Course values(01 , N語文 , 02)

insert into Course values(02 , N數學 , 01)

insert into Course values(03 , N英語 , 03)

create table Teacher(Tid varchar(10),Tname nvarchar(10))

insert into Teacher values(01 , N小徐老師)

insert into Teacher values(02 , N歡歡老師)

insert into Teacher values(03 , N小姜老師)

create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,2))

insert into SC values(01 , 01 , 80)

insert into SC values(01 , 02 , 90)

insert into SC values(01 , 03 , 99)

insert into SC values(02 , 01 , 70)

insert into SC values(02 , 02 , 60)

insert into SC values(02 , 03 , 80)

insert into SC values(03 , 01 , 80)

insert into SC values(03 , 02 , 80)

insert into SC values(03 , 03 , 80)

insert into SC values(04 , 01 , 50)

insert into SC values(04 , 02 , 30)

insert into SC values(04 , 03 , 20)

insert into SC values(05 , 01 , 76)

insert into SC values(05 , 02 , 87)

insert into SC values(06 , 01 , 31)

insert into SC values(06 , 03 , 34)

insert into SC values(07 , 02 , 89)

insert into SC values(07 , 03 , 98)

insert into SC values(08 , 01 , 92)

insert into SC values(08 , 02 , 90)

insert into SC values(08 , 03 , 89)

insert into SC values(09 , 01 , 28)

insert into SC values(09 , 02 , 19)

insert into SC values(09 , 03 , 32)

insert into SC values(10 , 01 , 82)

insert into SC values(10 , 02 , 76)

insert into SC values(10 , 03 , 89)

insert into SC values(11 , 01 , 98)

insert into SC values(11 , 02 , 83)

insert into SC values(11 , 03 , 99)

insert into SC values(12 , 01 , 82)

insert into SC values(12 , 02 , 80)

insert into SC values(12 , 03 , 83)

insert into SC values(13 , 01 , 80)

insert into SC values(13 , 02 , 83)

insert into SC values(13 , 03 , 82)

1、查詢"01"課程比"02"課程成績高的學生的信息及課程分數

1.1、查詢同時存在"01"課程和"02"課程的情況

select a.* , b.score [課程01的分數],c.score [課程02的分數] from Student a , SC b , SC c where a.SID = b.SID and a.SID = c.SID and b.CID = 01 and c.CID = 02 and b.score > c.score

1.2、查詢同時存在"01"課程和"02"課程的情況和存在"01"課程但可能不存在"02"課程的情況(不存在時顯示為null)(以下存在相同內容時不再解釋)

select a.* , b.score [課程"01"的分數],c.score [課程"02"的分數] from Student a left join SC b on a.SID = b.SID and b.CID = 01 left join SC c on a.SID = c.SID and c.CID = 02 where b.score > isnull(c.score,0)

2、查詢"01"課程比"02"課程成績低的學生的信息及課程分數

2.1、查詢同時存在"01"課程和"02"課程的情況

select a.* , b.score [課程01的分數],c.score [課程02的分數] from Student a , SC b , SC c where a.SID = b.SID and a.SID = c.SID and b.CID = 01 and c.CID = 02 and b.score < c.score

2.2、查詢同時存在"01"課程和"02"課程的情況和不存在"01"課程但存在"02"課程的情況

select a.* , b.score [課程"01"的分數],c.score [課程"02"的分數] from Student a left join SC b on a.SID = b.SID and b.CID = 01 left join SC c on a.SID = c.SID and c.CID = 02 where isnull(b.score,0) < c.score

3、查詢平均成績大於等於60分的同學的學生編號和學生姓名和平均成績

select a.SID , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score from Student a , sc b where a.SID = b.SID group by a.SID , a.Sname having cast(avg(b.score) as decimal(18,2)) >= 60 order by a.SID

4、查詢平均成績小於60分的同學的學生編號和學生姓名和平均成績

4.1、查詢在sc表存在成績的學生信息的SQL語句。

4.2、查詢在sc表中不存在成績的學生信息的SQL語句。

select a.SID , a.Sname , isnull(cast(avg(b.score) as decimal(18,2)),0) avg_score from Student a left join sc b on a.SID = b.SID group by a.SID , a.Sname having isnull(cast(avg(b.score) as decimal(18,2)),0) < 60 order by a.SID

5、查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績

5.1、查詢所有有成績的SQL。

select a.SID [學生編號], a.Sname [學生姓名], count(b.CID) 選課總數, sum(score) [所有課程的總成績] from Student a , SC b where a.SID = b.SID group by a.SID,a.Sname order by a.SID --5.2、查詢所有(包括有成績和無成績)的SQL。

select a.SID [學生編號], a.Sname [學生姓名], count(b.CID) 選課總數, sum(score) [所有課程的總成績] from Student a left join SC b on a.SID = b.SID group by a.SID,a.Sname order by a.SID

6、查詢"李"姓老師的數量

--方法1

select count(Tname) ["李"姓老師的數量] from Teacher where Tname like N李%

--方法2

select count(Tname) ["李"姓老師的數量] from Teacher where left(Tname,1) = N李 /* "李"姓老師的數量 ----------- 1 */

7、查詢學過"張三"老師授課的同學的信息

select distinct Student.* from Student , SC , Course , Teacher where Student.SID = SC.SID and SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = N張三 order by Student.SID

8、查詢沒學過"張三"老師授課的同學的信息

select m.* from Student m where SID not in (select distinct SC.SID from SC , Course , Teacher where SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = N張三) order by m.SID

9、查詢學過編號為"01"並且也學過編號為"02"的課程的同學的信息

--方法1

select Student.* from Student , SC where Student.SID = SC.SID and SC.CID = 01 and exists (Select 1 from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = 02) order by Student.SID

--方法2

select Student.* from Student , SC where Student.SID = SC.SID and SC.CID = 02 and exists (Select 1 from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = 01) order by Student.SID

--方法3

select m.* from Student m where SID in ( select SID from ( select distinct SID from SC where CID = 01 union all select distinct SID from SC where CID = 02 ) t group by SID having count(1) = 2 ) order by m.SID

10、查詢學過編號為"01"但是沒有學過編號為"02"的課程的同學的信息

--方法1

select Student.* from Student , SC where Student.SID = SC.SID and SC.CID = 01 and not exists (Select 1 from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = 02) order by Student.SID

--方法2 select Student.* from Student , SC where Student.SID = SC.SID and SC.CID = 01 and Student.SID not in (Select SC_2.SID from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = 02) order by Student.SID

11、查詢沒有學全所有課程的同學的信息

11.1、

select Student.* from Student , SC where Student.SID = SC.SID group by Student.SID , Student.Sname , Student.Sage , Student.Ssex having count(CID) < (select count(CID) from Course)

11.2

select Student.* from Student left join SC on Student.SID = SC.SID group by Student.SID , Student.Sname , Student.Sage , Student.Ssex having count(CID) < (select count(CID) from Course)

12、查詢至少有一門課與學號為"01"的同學所學相同的同學的信息

select distinct Student.* from Student , SC where Student.SID = SC.SID and SC.CID in (select CID from SC where SID = 01) and Student.SID <> 01

13、查詢和"01"號的同學學習的課程完全相同的其他同學的信息

select Student.* from Student where SID in (select distinct SC.SID from SC where SID <> 01 and SC.CID in (select distinct CID from SC where SID = 01) group by SC.SID having count(1) = (select count(1) from SC where SID=01))

14、查詢沒學過"張三"老師講授的任一門課程的學生姓名

select student.* from student where student.SID not in (select distinct sc.SID from sc , course , teacher where sc.CID = course.CID and course.TID = teacher.TID and teacher.tname = N張三) order by student.SID

15、查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績

select student.SID , student.sname , cast(avg(score) as decimal(18,2)) avg_score from student , sc where student.SID = SC.SID and student.SID in (select SID from SC where score < 60 group by SID having count(1) >= 2) group by student.SID , student.sname

16、檢索"01"課程分數小於60，按分數降序排列的學生信息

select student.* , sc.CID , sc.score from student , sc where student.SID = SC.SID and sc.score < 60 and sc.CID = 01 order by sc.score desc

17、按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績

17.1 靜態

select a.SID 學生編號 , a.Sname 學生姓名 , max(case c.Cname when N語文 then b.score else null end) [語文], max(case c.Cname when N數學 then b.score else null end) [數學], max(case c.Cname when N英語 then b.score else null end) [英語], cast(avg(b.score) as decimal(18,2)) 平均分 from Student a left join SC b on a.SID = b.SID left join Course c on b.CID = c.CID group by a.SID , a.Sname order by 平均分 desc

17.2 動態

declare @sql nvarchar(4000) set @sql = select a.SID + N學生編號 + , a.Sname + N學生姓名 select @sql = @sql + ,max(case c.Cname when N+Cname+ then b.score else null end) [+Cname+] from (select distinct Cname from Course) as t set @sql = @sql + , cast(avg(b.score) as decimal(18,2)) + N平均分 + from Student a left join SC b on a.SID = b.SID left join Course c on b.CID = c.CID group by a.SID , a.Sname order by + N平均分 + desc exec(@sql)

希望對大家面試有幫助,很經典的幾道題,大家有興趣的話,歡迎大家加群討論,QQ群號:295383988