SQL數(shù)據(jù)庫面試題以及答案（50題）

Student(Sid,Sname,Sage,Ssex)學生表

• Sid：學號
• Sname：學生姓名
• Sage：學生年齡
• Ssex：學生性別

Course(Cid,Cname,T#)課程表

• Cid：課程編號
• Cname：課程名稱
• Tid：教師編號

SC(Sid,Cid,score)成績表

• Sid：學號
• Cid：課程編號
• score：成績

Teacher(Tid,Tname)教師表

• Tid：教師編號：
• Tname：教師名字

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

select?a.sid?from?
(select?sid,score?from?sc?where?cid='001')a,
(select?sid,score?from?sc?where?cid='002')b?
where?a.sid?=?b.sid?and?a.score>b.score;

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

select?sid,avg(score)?from?sc
group?by?sid?
having?avg(score)>60;

3、查詢所有同學的學號、姓名、選課數(shù)、總成績

select?s.sid,s.sname,count_cid?as?選課數(shù),?
sum_score??as?總成績
from?student?s
left?join?
(select?sid,count(cid)?as?count_cid,sum(score)?as?sum_score?
from?sc?group?by?sid?)sc
on?s.sid?=?sc.sid;

4、查詢姓‘李’的老師的個數(shù)：

select?count(tname)
from?teacher?
where?tname?like?'李%';

5、查詢沒有學過“葉平”老師可的同學的學號、姓名：

select?s.sid,s.sname?
from?student?as?s?
where?s.sid?not?in?(
????select?DISTINCT?sid?
????from?sc?as?sc?
????where?sc.cid?in?(
????????select?cid?
????????from?course?as?c?
????????left?join?teacher?as?t?on?c.tid?=?t.tid?
????????where?t.tname?=?'葉平')
);

6、查詢學過“葉平”老師所教的所有課的同學的學號、姓名：

select?s.sid,s.sname?
from?student?as?s?
where?s.sid?in?(
????select?distinct?sc.sid?
??from?sc?as?sc?
??where?sc.cid?in?(
????select?cid?
????from?course?as?c?
????left?join?teacher?as?t?on?c.tid?=?t.tid?
????where?t.tname?=?'葉平')
????????group?by?sc.sid?
????HAVING?count(cid)=
????(select?count(cid)?
?????from?course?as?c?left?join?teacher?as?t?on?c.tid?=?t.tid?
?????where?t.tname?=?'葉平')
);

7、查詢學過“011”并且也學過編號“002”課程的同學的學號、姓名：

SELECT?s.sid,s.sname?
from?student?as?s?
left?join?sc?as?sc?on?s.sid?=?sc.sid
where?sc.cid?=?'001'
and?EXISTS(
??select?*?from?sc?as?sc_2?
??where?sc.sid?=?sc_2.sid?
??and?sc_2.cid='002');

select?s.sid,s.sname
from?student?as?s?
left?join?sc?as?sc?
on?sc.sid?=?s.sid
where?sc.cid?=?'001'
and?s.sid?in?(
??select?sid?from?sc?as?sc_2?
??where?sc_2.cid='002'?
??and?sc_2.sid?=?sc.sid);

8、查詢課程編號“002”的成績比課程編號“001”課程低的所有同學的學號、姓名：

select?sid,sname
from?(select?student.sid,student.sname,score,
?????(select?score?from?sc?as?sc_2?
??????where?sc_2.sid?=?student.sid?
??????and?sc_2.cid?=?'002')?as?score2?
??????from?student,sc?
??????where?student.sid=sc.sid?and?cid?=?'001')?s_2
where?score2

9、查詢所有課程成績小于60的同學的學號、姓名：

select?sid,sname
from?student
where?sid?not?in?
(select?s.sid?
from?student?s,sc?
where?s.sid=sc.sid?and?score>60?);

select?sid,sname
from?student?s
where?not?EXISTS?(
select?s.sid?from?sc?
where?sc.sid?=?s.sid?and?sc.score>60);

10、查詢沒有學全所有課的同學的學號、姓名：

select?s.sid,s.sname
from?student?s?,sc?sc?
where?s.sid?=?sc.sid
group?by?s.sid,s.sname
having?count(sc.cid)<(
select?count(cid)?
from?course);

select?s.sid,s.sname
from?student?s?
right?join?sc?sc?on?s.sid?=?sc.sid
group?by?s.sid,s.sname
having?count(sc.cid)<
(select?count(cid)?from?course);

11、查詢至少有一門課與學號為“1001”同學所學相同的同學的學號和姓名：

select?student.sid,sname
from?student,sc?
where?student.sid?=?sc.sid
and?cid?in?
(select?cid?from?sc?where?sid='1001');

select?s.sid,s.sname
from?sc?sc?left?join?student?as?s
on?sc.sid?=?s.sid
where?sc.cid?in?(select?cid?from?sc?where?sid='1001');

select?sc_1.sid,s.sname
from?sc?sc_1?left?join?student?as?s
on?sc_1.sid?=?s.sid
where?
exists?(select?sc_2.cid?from?sc?as?sc_2?
where?sc_1.cid?=?sc_2.cid?
and?sc_2.sid?=?'1001');

12、查詢至少學過學號為“001”同學所有一門課的其他同學學號和姓名；

13、把“SC”表中“葉平”老師教的課的成績都更改為此課程的平均成績：

update?sc?set?score?=?
(select?avg(sc_2.score)?from?sc?sc_2??
where?sc_2.cid?=?sc.cid)
where?cid?in?
(select?c.cid?from?course?c?
left?join?teacher?t?on?t.tid?=?c.tid?
where?t.tname?=?'葉平');

14、查詢和“1002”號的同學學習的課程完全相同的其他同學學號和姓名：

select?sc_1.sid?
from?(select?cid?from?sc?where?sid='1002')a
left?join?sc?sc_1?on?a.cid?=?sc_1.cid
where?sc_1.sid<>'1002'?
group?by?sc_1.sid?
having?count(sc_1.cid)?=?
(select?count(cid)?from?sc?where?sid='1002');

select?a.sid,s.sname?from?
(select?sid,GROUP_CONCAT(cid?order?by?cid?separator?',')?as?cid_str?
from?sc?where?sid='1002')b,
(select?sid,GROUP_CONCAT(cid?order?by?cid?separator?',')?as?cid_str?
from?sc?group?by?sid)a
left?join?student?s?
on?a.sid?=?s.sid
where?a.cid_str?=?b.cid_str?and?a.sid<>'1002';

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

delete?from?sc?WHERE
cid?in?(
select?c.cid?from?course?c?
LEFT?JOIN?teacher?t?on?c.tid=t.tid?
where?t.tname?=?'葉平');

16、向SC表中插入一些記錄，這些記錄要求符合以下條件：沒有上過編號“003”課程的同學學號、002號課的平均成績：

insert?into?sc?select?sid,'002',
(select?avg(score)?from?sc?where?cid='0022')
from?student?
where?sid?not?in?(select?sid?from?sc?where?cid='002');

17、按平均成績從高到低顯示所有學生的“數(shù)據(jù)庫”、“企業(yè)管理”、“英語”三門的課程成績，按如下形式顯示：學生ID，數(shù)據(jù)庫，企業(yè)管理，英語，有效課程數(shù)，有效平均分：

select?sid?as?學生id,
(SELECT?score?from?sc?
where?sc.sid?=?t.sid?and?cid='004')?as?數(shù)據(jù)庫,
(select?score?from?sc?
where?sc.sid?=?t.sid?and?cid='001')?as?企業(yè)管理,
(select?score?from?sc?
where?sc.sid?=?t.sid?and?cid='015')?as?英語,
count(cid)?as?有效課程數(shù),?avg(t.score)?as?平均成績
from?sc?as?t?
group?by?sid
order?by?avg(t.score);

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

select?l.cid?as?課程id,l.score?as?最高分,
r.score?as?最低分
from?sc?l,sc?r
where?l.cid?=?r.cid
and?l.score?=?
(select?max(t.score)?from?sc?t?
where?l.cid?=?t.cid?group?by?t.cid)
and?r.score?=?(select?min(t.score)?from?sc?t?
where?r.cid?=?t.cid?group?by?t.cid)
order?by?l.cid;

select?cid?as?課程id,max(score)?as?最高分,
min(score)?as?最低分
from?sc?
group?by?cid;

19、按各科平均成績從低到高和及格率的百分數(shù)從高到低順序：

SELECT?t.cid?as?課程號,
c.cname?as?課程名,
COALESCE(avg(score),0)?as?平均成績,
100*sum(case?
when?COALESCE(score,0)>=60?
then?1?else?0?END)/count(*)?as?及格百分數(shù)
from?sc?t
left?join?course?c?
on?t.cid?=?c.cid
group?by?t.cid
order?by?100*sum(case?
when?COALESCE(score,0)>=60?
then?1?else?0?END)/count(*);

20、查詢?nèi)缦抡n程平均成績和及格率的百分數(shù)(用”1行”顯示): 企業(yè)管理（001），馬克思（002），OO&UML （003），數(shù)據(jù)庫（004）：

21、查詢不同老師所教不同課程平均分從高到低顯示：

select?t.tid?as?教師id,
t.tname?as?教師姓名,
sc.cid?as?課程id,
avg(score)?as?平均成績
from?sc?as?sc
LEFT?JOIN?course?c?on?sc.cid?=?c.cid
left?join?teacher?t?on?c.tid?=?t.tid
group?by?sc.cid?
order?by?avg(sc.score)?desc;

22、查詢?nèi)缦抡n程成績第3名到第6名的學生成績單：企業(yè)管理(001)，馬克思(002)，UML(003)，數(shù)據(jù)庫(004)：

23、統(tǒng)計下列各科成績，各分數(shù)段人數(shù)：課程ID，課程名稱，[100-85],[85-70],[70-60],[ 小于60] ：

select?sc.cid?as?課程id,cname?as?課程名稱,
sum(case?when?score?between?85?and?100?then?1?else?0?end)?as?'[100-85]',
sum(case?when?score?between?70?and?85?then?1?else?0?end)?as?'[85-70]',
sum(case?when?score?between?60?and?70?then?1?else?0?end)?as?'[70-60]',
sum(case?when?score<60?then?1?else?0?end)?as?'[60-0]'
from?sc?as?sc?
left?join?course?as?c
on?sc.cid?=?c.cid
group?by?sc.cid;

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

select?1+(select?count(distinct?平均成績)?
from?(select?sid,avg(score)?as?平均成績?
from?sc?group?by?sid)t1?
where?平均成績>t2.平均成績)?as?名次,
sid?as?學生學號,平均成績?
from?(select?sid,avg(score)?平均成績?from?sc?group?by?sid)?as?t2
order?by?平均成績?desc;

25、查詢各科成績前三名的記錄（不考慮成績并列情況）：

select?sid,cid,score
from?sc?sc_1
where?(
select?count(3)?from?sc?sc_2?
where?sc_1.cid?=?sc_2.cid?
and?sc_2.score>=sc_1.score)<=2?
order?by?sc_1.cid
);

26、查詢每門課程被選修的學生數(shù)：

select?cid,?count(sid)
from?sc?
group?by?cid;

27、查詢出只選修一門課程的全部學生的學號和姓名：

select?sc.sid,s.sname,
count(sc.cid)?as?課程數(shù)
from?sc?as?sc
LEFT?JOIN?student?as?s
on?sc.sid?=?s.sid
group?by?sc.sid
having?count(sc.cid)=1;

28、查詢男生、女生人數(shù)：

select?count(ssex)?as?男生人數(shù)
from?student
group?by?ssex
having?ssex?=?'男';
select?count(2)?from?student
where?ssex?=?'女';

29、查詢姓“張”的學生名單：

select?sid,sname
from?student?
where?sname?like?'張%';

30、查詢同名同姓的學生名單，并統(tǒng)計同名人數(shù)：

select?sname,count(8)
from?student?
group?by?sname
having?count(8)>1;

31、1981年出生的學生名單（注：student表中sage列的類型是datetime）:

關(guān)注公眾號Java技術(shù)?；貜兔嬖嚝@取更多面試題。

32、查詢平均成績大于85的所有學生的學號、姓名和平均成績：

select?s.sname,sc.sid,avg(sc.score)?as?平均成績
from?sc?as?sc
left?join?student?as?s?
on?sc.sid?=?s.sid
group?by?sc.sid?
having?avg(sc.score)>85;

33、查詢每門課程的平均成績，結(jié)果按平均成績升序排序，平均成績相同時，按課程號降序排列：

select?cid,avg(score)
from?sc?
group?by?cid
order?by?avg(score),cid?desc;

34、查詢課程名稱為“數(shù)據(jù)庫”，且分數(shù)低于60的學生名字和分數(shù)：

select?c.cname,s.sid,s.sname,sc.score
from?course?c
left?join?sc?on?sc.cid?=?c.cid
LEFT?JOIN?student?s?on?s.sid?=?sc.sid
where?c.cname?=?'數(shù)據(jù)庫'?and?sc.score<60;

35、查詢所有學生的選課情況：

select?sc.sid,sc.cid,s.sname,c.cname
from?sc?
LEFT?JOIN?course?c?on?sc.cid?=?c.cid
left?join?student?s?on?sc.sid?=?s.sid;

36、查詢?nèi)魏我婚T課程成績在70分以上的姓名、課程名稱和分數(shù)：

select?distinct?s.sid,s.sname,sc.cid,sc.score
from?sc?
left?join?student?s?on?sc.sid?=?s.sid
left?join?course?c?on?sc.cid?=?c.cid
where?sc.score>70;

37、查詢不及格的課程，并按課程號從大到小的排列：

select?cid
from?sc?
where?score<60
ORDER?BY?cid;

38、查詢課程編號為“003”且課程成績在80分以上的學生的學號和姓名：

select?sc.sid,s.sname?
from?sc?
left?join?student?s?on?sc.sid?=?s.sid
where?sc.cid?=?'003'?and?sc.score>80;

39、求選了課程的學生人數(shù)：

select?count(2)?from?
(select?distinct?sid?from?sc)a;

40、查詢選修“葉平”老師所授課程的學生中，成績最高的學生姓名及其成績：

select?s.sname,sc.score
from?sc?sc?
left?join?student?s?on?sc.sid?=?s.sid
left?join?course?c?on?sc.cid?=?c.cid
left?join?teacher?t?on?c.tid?=?t.tid
where?t.tname?=?'葉平'
and?sc.score?=?(
select?max(score)?
from?sc?sc_1?
where?sc.cid?=?sc_1.cid);

41、查詢各個課程及相應的選修人數(shù)：

select?cid,count(*)?from?sc?group?by?cid;

42、查詢不同課程成績相同的學生和學號、課程號、學生成績：

select?DISTINCT?a.sid,a.cid,a.score
from?sc?as?a?,sc?as?b?
where?a.score?=?b.score
and?a.cid?<>?b.cid;

43、查詢每門課程成績最好的前兩名：

關(guān)注公眾號Java技術(shù)?；貜兔嬖嚝@取更多面試題。

44、統(tǒng)計每門課程的學生選修人數(shù)(超過10人的課程才統(tǒng)計)。要求輸出課程號和選修人數(shù)，查詢結(jié)果按人數(shù)降序排序，若人數(shù)相同，按課程號升序排序：

select?cid?as?課程號,count(8)?as?選修人數(shù)
from?sc
group?by?cid
HAVING?count(sid)>10
order?by?count(8)?desc,cid;

45、檢索至少選修兩門課程的學生學號：

select?sid
from?sc
group?by?sid
having?count(8)>=2;

46、查詢?nèi)繉W生選修的課程和課程號和課程名：

select?cid,cname
from?course?
where?cid?in?(select?cid?from?sc?group?by?cid);

47、查詢沒學過”葉平”老師講授的任一門課程的學生姓名：

select?sname?
from?student?
where?sid?not?in?(
????select?sid?
????from?sc,course,teacher?
????where?course.tid?=?teacher.tid?and?sc.cid?=?course.cid?
????and?teacher.tname='葉平'
);

48、查詢兩門以上不及格課程的同學的學號以及其平均成績：

select?sid,avg(COALESCE(score,0))
from?sc
where?sid?in?(
????select?sid?
????from?sc?
????where?score<60?
????group?by?sid?
????having?count(8)>2
)
group?by?sid;

49、檢索“004”課程分數(shù)小于60，按分數(shù)降序排列的同學學號：

select?sid,score
from?sc
where?cid='004'
and?score<60
order?by?score?desc;

50、刪除“002”同學的“001”課程的成績：

delete?from?sc
where?sid?=?'002'
and?cid?=?'001';

版權(quán)聲明：本文為博主原創(chuàng)文章，遵循 CC 4.0 BY-SA 版權(quán)協(xié)議，轉(zhuǎn)載請附上原文出處鏈接和本聲明。本文鏈接：https://blog.csdn.net/hundan_520520/article/details/54881208

戳原文，獲取精選面試題！