【184期】SQL數(shù)據(jù)庫(kù)面試題以及答案（50例題）

程序員的成長(zhǎng)之路

互聯(lián)網(wǎng)/程序員/技術(shù)/資料共享 

關(guān)注

• Sid：學(xué)號(hào)

• Sname：學(xué)生姓名

• Sage：學(xué)生年齡

• Ssex：學(xué)生性別

• Cid：課程編號(hào)

• Cname：課程名稱(chēng)

• Tid：教師編號(hào)

• Sid：學(xué)號(hào)

• Cid：課程編號(hào)

• score：成績(jī)

• Tid：教師編號(hào)：

• Tname：教師名字

問(wèn)題：

1、查詢(xún)“001”課程比“002”課程成績(jī)高的所有學(xué)生的學(xué)號(hào)

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、查詢(xún)平均成績(jī)大于60分的同學(xué)的學(xué)號(hào)和平均成績(jī)

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

3、查詢(xún)所有同學(xué)的學(xué)號(hào)、姓名、選課數(shù)、總成績(jī)

select s.sid,s.sname,count_cid as 選課數(shù), 
sum_score  as 總成績(jī)
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、查詢(xún)姓‘李’的老師的個(gè)數(shù)：

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

5、查詢(xún)沒(méi)有學(xué)過(guò)“葉平”老師可的同學(xué)的學(xué)號(hào)、姓名：

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、查詢(xún)學(xué)過(guò)“葉平”老師所教的所有課的同學(xué)的學(xué)號(hào)、姓名：

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、查詢(xún)學(xué)過(guò)“011”并且也學(xué)過(guò)編號(hào)“002”課程的同學(xué)的學(xué)號(hào)、姓名：

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、查詢(xún)課程編號(hào)“002”的成績(jī)比課程編號(hào)“001”課程低的所有同學(xué)的學(xué)號(hào)、姓名：

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<score;

9、查詢(xún)所有課程成績(jī)小于60的同學(xué)的學(xué)號(hào)、姓名：

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、查詢(xún)沒(méi)有學(xué)全所有課的同學(xué)的學(xué)號(hào)、姓名：

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、查詢(xún)至少有一門(mén)課與學(xué)號(hào)為“1001”同學(xué)所學(xué)相同的同學(xué)的學(xué)號(hào)和姓名：

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、查詢(xún)至少學(xué)過(guò)學(xué)號(hào)為“001”同學(xué)所有一門(mén)課的其他同學(xué)學(xué)號(hào)和姓名；

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

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、查詢(xún)和“1002”號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)學(xué)號(hào)和姓名：

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、刪除學(xué)習(xí)“葉平”老師課的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表中插入一些記錄，這些記錄要求符合以下條件：沒(méi)有上過(guò)編號(hào)“003”課程的同學(xué)學(xué)號(hào)、002號(hào)課的平均成績(jī)：

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、按平均成績(jī)從高到低顯示所有學(xué)生的“數(shù)據(jù)庫(kù)”、“企業(yè)管理”、“英語(yǔ)”三門(mén)的課程成績(jī)，按如下形式顯示：學(xué)生ID，數(shù)據(jù)庫(kù)，企業(yè)管理，英語(yǔ)，有效課程數(shù)，有效平均分：

select sid as 學(xué)生id,
(SELECT score from sc 
where sc.sid = t.sid and cid='004') as 數(shù)據(jù)庫(kù),
(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 英語(yǔ),
count(cid) as 有效課程數(shù), avg(t.score) as 平均成績(jī)
from sc as t 
group by sid
order by avg(t.score);

18、查詢(xún)各科成績(jī)最高和最低的分：以如下的形式顯示：課程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、按各科平均成績(jī)從低到高和及格率的百分?jǐn)?shù)從高到低順序：

SELECT t.cid as 課程號(hào),
c.cname as 課程名,
COALESCE(avg(score),0) as 平均成績(jī),
100*sum(case 
when COALESCE(score,0)>=60 
then 1 else 0 END)/count(*) as 及格百分?jǐn)?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、查詢(xún)?nèi)缦抡n程平均成績(jī)和及格率的百分?jǐn)?shù)(用”1行”顯示): 企業(yè)管理（001），馬克思（002），OO&UML （003），數(shù)據(jù)庫(kù)（004）：

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

select t.tid as 教師id,
t.tname as 教師姓名,
sc.cid as 課程id,
avg(score) as 平均成績(jī)
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、查詢(xún)?nèi)缦抡n程成績(jī)第3名到第6名的學(xué)生成績(jī)單：企業(yè)管理(001)，馬克思(002)，UML(003)，數(shù)據(jù)庫(kù)(004)：

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

select sc.cid as 課程id,cname as 課程名稱(chēng),
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、查詢(xún)學(xué)生平均成績(jī)及其名次：

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

25、查詢(xún)各科成績(jī)前三名的記錄（不考慮成績(jī)并列情況）：

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、查詢(xún)每門(mén)課程被選修的學(xué)生數(shù)：

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

27、查詢(xún)出只選修一門(mén)課程的全部學(xué)生的學(xué)號(hào)和姓名：

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、查詢(xún)男生、女生人數(shù)：

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

29、查詢(xún)姓“張”的學(xué)生名單：

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

30、查詢(xún)同名同姓的學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)：

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

31、1981年出生的學(xué)生名單（注：student表中sage列的類(lèi)型是datetime）:

32、查詢(xún)平均成績(jī)大于85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)：

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

33、查詢(xún)每門(mén)課程的平均成績(jī)，結(jié)果按平均成績(jī)升序排序，平均成績(jī)相同時(shí)，按課程號(hào)降序排列：

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

34、查詢(xún)課程名稱(chēng)為“數(shù)據(jù)庫(kù)”，且分?jǐn)?shù)低于60的學(xué)生名字和分?jǐn)?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ù)庫(kù)' and sc.score<60;

35、查詢(xún)所有學(xué)生的選課情況：

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、查詢(xún)?nèi)魏我婚T(mén)課程成績(jī)?cè)?0分以上的姓名、課程名稱(chēng)和分?jǐn)?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、查詢(xún)不及格的課程，并按課程號(hào)從大到小的排列：

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

38、查詢(xún)課程編號(hào)為“003”且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名：

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、求選了課程的學(xué)生人數(shù)：

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

40、查詢(xún)選修“葉平”老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生姓名及其成績(jī)：

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、查詢(xún)各個(gè)課程及相應(yīng)的選修人數(shù)：

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

42、查詢(xún)不同課程成績(jī)相同的學(xué)生和學(xué)號(hào)、課程號(hào)、學(xué)生成績(jī)：

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、查詢(xún)每門(mén)課程成績(jī)最好的前兩名：

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

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

45、檢索至少選修兩門(mén)課程的學(xué)生學(xué)號(hào)：

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

46、查詢(xún)?nèi)繉W(xué)生選修的課程和課程號(hào)和課程名：

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

47、查詢(xún)沒(méi)學(xué)過(guò)”葉平”老師講授的任一門(mén)課程的學(xué)生姓名：

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

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”課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的同學(xué)學(xué)號(hào)：

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

50、刪除“002”同學(xué)的“001”課程的成績(jī)：

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

抽兩個(gè)干飯紅包，每個(gè)25元，點(diǎn)擊小卡片，回復(fù)“抽獎(jiǎng)”即可參與，周二晚上20點(diǎn)開(kāi)獎(jiǎng)。

推薦閱讀：

【182期】SpringCloud常見(jiàn)面試題（2020最新版）

【181期】HashMap 面試二十一問(wèn)！

【179期】這些最常用的Linux命令都不會(huì)，你怎么敢去面試？

微信掃描二維碼，關(guān)注我的公眾號(hào)

朕已閱