SQL面試西潘，讓你的面試官無(wú)fu,ck可說(shuō)嚼沿，第17題難倒一片人

希望對(duì)大家面試有幫助,很經(jīng)典的幾道題,大家有興趣的話,歡迎大家加群討論,QQ群號(hào):295383988

問(wèn)題及描述：

1.學(xué)生表

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

2.課程表

Course(CID,Cname,TID) --CID --課程編號(hào),Cname 課程名稱(chēng),TID 教師編號(hào)

3.教師表

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

4.成績(jī)表

SC(SID,CID,score) --SID 學(xué)生編號(hào),CID 課程編號(hào),score 分?jǐn)?shù)

創(chuàng)建測(cè)試數(shù)據(jù)

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'韋達(dá)' , '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'鐘愛(ài)軍' , '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'閆衛(wèi)龍' , '1992-11-03' , N'男')

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

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

insert into Course values('02' , N'數(shù)學(xué)' , '01')

insert into Course values('03' , N'英語(yǔ)' , '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)

go

1、查詢"01"課程比"02"課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)

1.1车柠、查詢同時(shí)存在"01"課程和"02"課程的情況

select a.* , b.score [課程'01'的分?jǐn)?shù)],c.score [課程'02'的分?jǐn)?shù)] 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剔氏、查詢同時(shí)存在"01"課程和"02"課程的情況和存在"01"課程但可能不存在"02"課程的情況(不存在時(shí)顯示為null)(以下存在相同內(nèi)容時(shí)不再解釋)

select a.* , b.score [課程"01"的分?jǐn)?shù)],c.score [課程"02"的分?jǐn)?shù)] 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"課程成績(jī)低的學(xué)生的信息及課程分?jǐn)?shù)

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

select a.* , b.score [課程'01'的分?jǐn)?shù)],c.score [課程'02'的分?jǐn)?shù)] 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谈跛、查詢同時(shí)存在"01"課程和"02"課程的情況和不存在"01"課程但存在"02"課程的情況

select a.* , b.score [課程"01"的分?jǐn)?shù)],c.score [課程"02"的分?jǐn)?shù)] 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羊苟、查詢平均成績(jī)大于等于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)

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、查詢平均成績(jī)小于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)

4.1感憾、查詢?cè)趕c表存在成績(jī)的學(xué)生信息的SQL語(yǔ)句蜡励。

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.2、查詢?cè)趕c表中不存在成績(jī)的學(xué)生信息的SQL語(yǔ)句阻桅。

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凉倚、查詢所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名嫂沉、選課總數(shù)稽寒、所有課程的總成績(jī)

5.1、查詢所有有成績(jī)的SQL趟章。

select a.SID [學(xué)生編號(hào)], a.Sname [學(xué)生姓名], count(b.CID) 選課總數(shù), sum(score) [所有課程的總成績(jī)] from Student a , SC b where a.SID = b.SID group by a.SID,a.Sname order by a.SID --5.2瓦胎、查詢所有(包括有成績(jī)和無(wú)成績(jī))的SQL。

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

6尤揣、查詢"李"姓老師的數(shù)量

--方法1

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

--方法2

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

7搔啊、查詢學(xué)過(guò)"張三"老師授課的同學(xué)的信息

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、查詢沒(méi)學(xué)過(guò)"張三"老師授課的同學(xué)的信息

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北戏、查詢學(xué)過(guò)編號(hào)為"01"并且也學(xué)過(guò)編號(hào)為"02"的課程的同學(xué)的信息

--方法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负芋、查詢學(xué)過(guò)編號(hào)為"01"但是沒(méi)有學(xué)過(guò)編號(hào)為"02"的課程的同學(xué)的信息

--方法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、查詢沒(méi)有學(xué)全所有課程的同學(xué)的信息

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旧蛾、查詢至少有一門(mén)課與學(xué)號(hào)為"01"的同學(xué)所學(xué)相同的同學(xué)的信息

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"號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息

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蠕嫁、查詢沒(méi)學(xué)過(guò)"張三"老師講授的任一門(mén)課程的學(xué)生姓名

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锨天、查詢兩門(mén)及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)

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"課程分?jǐn)?shù)小于60病袄，按分?jǐn)?shù)降序排列的學(xué)生信息

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、按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)

17.1 靜態(tài)

select a.SID 學(xué)生編號(hào) , a.Sname 學(xué)生姓名 , max(case c.Cname when N'語(yǔ)文' then b.score else null end) [語(yǔ)文], max(case c.Cname when N'數(shù)學(xué)' then b.score else null end) [數(shù)學(xué)], max(case c.Cname when N'英語(yǔ)' then b.score else null end) [英語(yǔ)], 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 動(dòng)態(tài)

declare @sql nvarchar(4000) set @sql = 'select a.SID ' + N'學(xué)生編號(hào)' + ' , a.Sname ' + N'學(xué)生姓名' 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)

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 人面猴
    序言：七十年代末赘阀，一起剝皮案震驚了整個(gè)濱河市益缠，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌基公，老刑警劉巖幅慌，帶你破解...
    沈念sama閱讀 206,839評(píng)論 6贊 482
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場(chǎng)離奇詭異轰豆，居然都是意外死亡胰伍，警方通過(guò)查閱死者的電腦和手機(jī)齿诞，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 88,543評(píng)論 2贊 382
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門(mén)，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)骂租，“玉大人掌挚，你說(shuō)我怎么就攤上這事∑凶桑” “怎么了吠式？”我有些...
    開(kāi)封第一講書(shū)人閱讀 153,116評(píng)論 0贊 344
  • 道士緝兇錄：失蹤的賣(mài)姜人
    文/不壞的土叔 我叫張陵，是天一觀的道長(zhǎng)抽米。 經(jīng)常有香客問(wèn)我特占，道長(zhǎng)，這世上最難降的妖魔是什么云茸？ 我笑而不...
    開(kāi)封第一講書(shū)人閱讀 55,371評(píng)論 1贊 279
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任是目，我火速辦了婚禮，結(jié)果婚禮上标捺，老公的妹妹穿的比我還像新娘懊纳。我一直安慰自己，他們只是感情好亡容，可當(dāng)我...
    茶點(diǎn)故事閱讀 64,384評(píng)論 5贊 374
  • 惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
    文/花漫 我一把揭開(kāi)白布嗤疯。 她就那樣靜靜地躺著，像睡著了一般闺兢。 火紅的嫁衣襯著肌膚如雪茂缚。 梳的紋絲不亂的頭發(fā)上，一...
    開(kāi)封第一講書(shū)人閱讀 49,111評(píng)論 1贊 285
  • 城市分裂傳說(shuō)
    那天屋谭，我揣著相機(jī)與錄音脚囊，去河邊找鬼。 笑死桐磁，一個(gè)胖子當(dāng)著我的面吹牛悔耘，可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播我擂，決...
    沈念sama閱讀 38,416評(píng)論 3贊 400
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開(kāi)眼衬以，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼！你這毒婦竟也來(lái)了扶踊？” 一聲冷哼從身側(cè)響起靶橱，我...
    開(kāi)封第一講書(shū)人閱讀 37,053評(píng)論 0贊 259
  • 萬(wàn)榮殺人案實(shí)錄
    序言：老撾萬(wàn)榮一對(duì)情侶失蹤馋没，失蹤者是張志新（化名）和其女友劉穎，沒(méi)想到半個(gè)月后庐完，有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體舶治，經(jīng)...
    沈念sama閱讀 43,558評(píng)論 1贊 300
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡分井，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,007評(píng)論 2贊 325
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年车猬，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片尺锚。...
    茶點(diǎn)故事閱讀 38,117評(píng)論 1贊 334
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡珠闰，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出瘫辩，到底是詐尸還是另有隱情伏嗜，我是刑警寧澤，帶...
    沈念sama閱讀 33,756評(píng)論 4贊 324
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布伐厌，位于F島的核電站承绸，受9級(jí)特大地震影響，放射性物質(zhì)發(fā)生泄漏挣轨。R本人自食惡果不足惜军熏，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,324評(píng)論 3贊 307
  • 男人毒藥：我在死后第九天來(lái)索命
    文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望卷扮。 院中可真熱鬧荡澎，春花似錦、人聲如沸晤锹。這莊子的主人今日做“春日...
    開(kāi)封第一講書(shū)人閱讀 30,315評(píng)論 0贊 19
  • 一樁弒父案，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)鞭铆。三九已至热鞍，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間衔彻，已是汗流浹背薇宠。 一陣腳步聲響...
    開(kāi)封第一講書(shū)人閱讀 31,539評(píng)論 1贊 262
  • 情欲美人皮
    我被黑心中介騙來(lái)泰國(guó)打工， 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留艰额，地道東北人澄港。 一個(gè)月前我還...
    沈念sama閱讀 45,578評(píng)論 2贊 355
  • 代替公主和親
    正文 我出身青樓，卻偏偏與公主長(zhǎng)得像柄沮，于是被迫代替她去往敵國(guó)和親回梧。 傳聞我的和親對(duì)象是個(gè)殘疾皇子，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 42,877評(píng)論 2贊 345

推薦閱讀更多精彩內(nèi)容

  • Student(Sid,Sname,Sage,Ssex) 學(xué)生表Course(Cid,Cname,Tid) 課程表...
    小爐爐閱讀 1,438評(píng)論 0贊 0
  • 50個(gè)常用的sql語(yǔ)句Student(S#,Sname,Sage,Ssex) 學(xué)生表Course(C#,Cname...
    哈哈海閱讀 1,225評(píng)論 0贊 7
  • 說(shuō)明：以下五十個(gè)語(yǔ)句都按照測(cè)試數(shù)據(jù)進(jìn)行過(guò)測(cè)試祖搓，最好每次只單獨(dú)運(yùn)行一個(gè)語(yǔ)句狱意。 問(wèn)題及描述： --1.學(xué)生表 Stud...
    lijun_m閱讀 1,289評(píng)論 0贊 1
  • 50個(gè)常用的sql語(yǔ)句 Student(S#,Sname,Sage,Ssex) 學(xué)生表 Course(C#,Cna...
    最美的太陽(yáng)WW閱讀 3,158評(píng)論 0贊 23
  • 已經(jīng)進(jìn)入期末月的醫(yī)學(xué)狗，仍然過(guò)著滿課的日子藏姐。病理課第三節(jié)隆箩，臨近中午十二點(diǎn)，想著食堂噴噴香的飯菜羔杨，唾液腺和胃腸道的各...
    愛(ài)喝旺仔的小柴火閱讀 393評(píng)論 0贊 1