SQL筆試題:學生表、教師表、課程表、成績表

2021-02-16 馬同學的數據筆記

首先創建四張表

1.學生表
Student(SId,Sname,Sage,Ssex)

SId 學生編號,Sname 學生姓名,Sage 出生年月,Ssex 學生性別

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '趙雷' , '1990-01-01' , '男');
insert into Student values('02' , '錢電' , '1990-12-21' , '男');
insert into Student values('03' , '孫風' , '1990-12-20' , '男');
insert into Student values('04' , '李雲' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吳蘭' , '1992-01-01' , '女');
insert into Student values('07' , '鄭竹' , '1989-01-01' , '女');
insert into Student values('09' , '張三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '趙六' , '2013-06-13' , '女');
insert into Student values('13' , '孫七' , '2014-06-01' , '女');

2.課程表
Course(CId,Cname,TId)
CId 課程編號,Cname 課程名稱,TId 教師編號

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '語文' , '02');
insert into Course values('02' , '數學' , '01');
insert into Course values('03' , '英語' , '03');

3.教師表
Teacher(TId,Tname)
TId 教師編號,Tname 教師姓名

create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

4.成績表
SC(SId,CId,score)
SId 學生編號,CId 課程編號,score 分數

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
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);

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

-- 查詢出01課程所有學生的成績 */
SELECT *
FROM sc
WHERE cid ='01'

-- 查詢出02課程所有學生的成績
SELECT *
FROM sc
WHERE cid ='02'

-- 此處注意，from表中可以有兩張表，將兩張表連接在一起
SELECT *
FROM (SELECT *
FROM sc
WHERE cid ='01') AS a,
(SELECT *
FROM sc
WHERE cid ='02') AS b

-- 01課程成績大於02課程進行篩選
SELECT *
FROM (SELECT *
FROM sc
WHERE cid ='01') AS a,
(SELECT *
FROM sc
WHERE cid ='02') AS b
WHERE a.sid = b.sid
AND a.score > b.score

-- 有了一個新表結果，現在取出其中需要的列
SELECT a.sid,a.score as class1,b.score as class2
FROM (SELECT *
FROM sc
WHERE cid ='01') AS a,
(SELECT *
FROM sc
WHERE cid ='02') AS b
WHERE a.sid = b.sid
AND a.score > b.score

-- 最後一步，原始題目的要求是學生信息以及課程成績
SELECT *
FROM student
RIGHT JOIN
(SELECT a.sid,a.score as class1,b.score as class2
FROM (SELECT *
FROM sc
WHERE cid ='01') AS a,
(SELECT *
FROM sc
WHERE cid ='02') AS b
WHERE a.sid = b.sid
AND a.score > b.score)t1 -- 命名為新表t1
ON student.sid = t1.sid

-- 法二，更簡單的做法，其中先進行自聯結

SELECT *
FROM
(SELECT s1.sid, s1.score as class1,s2.score as class2
FROM sc s1
JOIN sc s2
ON s1.sid = s2.sid
WHERE s1.cid ='01' AND s2.cid ='02'
AND s1.score > s2.score) r
LEFT JOIN student
ON r.sid = student.sid

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

-- 翻譯成大白話：意思是選擇01和02課程的學生學號以及分數
SELECT a.sid,a.score,b.score
FROM (SELECT *
FROM sc
WHERE cid = '01') AS a,
(SELECT *
FROM sc
WHERE cid = '02') AS b
WHERE a.sid = b.sid

-- 法二

SELECT s1.sid, s1.score, s2.score
FROM sc s1
LEFT JOIN sc s2
ON s1.sid = s2.sid
WHERE s1.cid = '01' AND s2.cid = '02'

-- 1.2 查詢存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時顯示為 null )

/* 大白話:哪些同學選了01課程，但是可能沒有選02課程
顯示為null用left join 處理*/
SELECT *
FROM (SELECT *
FROM sc
WHERE cid = '01') AS a
LEFT JOIN(SELECT *
FROM sc
WHERE cid = '02') AS b
ON a.sid = b.sid

-- 1.3 查詢不存在" 01 "課程但存在" 02 "課程的情況

/*題目看似語言平實，實際上會產生歧義
此處我理解成選了02課程但是沒有選01課程的學生*/

-- 首先找出哪些人選了01課程
SELECT sid
FROM sc
WHERE cid = '01';

-- 以上這些學號排除再外
SELECT *
FROM sc
WHERE sid NOT IN
(SELECT sid
FROM sc
WHERE cid = '01')
AND cid = '02';

另外一種思路，有人認為可以不存在01課程，但是除了01之外的其他課程都得呈現

SELECT s1.*
FROM sc s1
JOIN sc s2
ON s1.sid = s2.sid
WHERE s1.sid NOT IN
(SELECT sid
FROM sc s
WHERE s.cid = '01')
AND s2.cid = '02';

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

-- 按學號分組，查詢出每個學生的平均成績
SELECT sid，AVG（score)
FROM sc
GROUP BY sid
HAVING AVG(score) >= 60

-- 查找平均成績大於等於60的學生相關信息
SELECT t1.*,s.sname
FROM student s
RIGHT JOIN
(SELECT sid,avg(score)
FROM sc
GROUP BY sid
HAVING AVG(score) >= 60)t1
ON s.sid = t1.sid

-- 法二

SELECT s1.sid, s1.sname, avg(s2.score) as 平均成績
FROM student s1
JOIN sc s2
ON s1.sid = s2.sid
GROUP BY s1.sid
HAVING avg(s2.score) >= 60

-- 3. 查詢在 SC 表存在成績的學生信息

-- 首先查找出成績表中存在的學生學號
SELECT DISTINCT sid
FROM sc
WHERE score is not NULL

-- 根據上述查找出的學號，結合學生表，得出學生所有信息
SELECT *
FROM student
WHERE sid IN
(SELECT DISTINCT sid
FROM sc
WHERE score is not NULL)

-- 4. 查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績(沒成績的顯示為 null )

-- 大白話：學生編號和學生姓名在學生表中，選課總數用count在成績表中，總成績也在成績表中,實際將兩張表聯結即可
SELECT student.sid,sname, COUNT(cid) as 選課總數, SUM(score) as 總成績
FROM student
LEFT JOIN sc -- 因為需要顯示沒有成績的，所以要用左聯結
ON student.sid = sc.sid
GROUP BY sid

4.1 查有成績的學生信息

-- 首先查找出那些有成績的學生學號
SELECT sid
FROM sc
WHERE scor is not NULL

-- 根據以上學號，結合學生表查找出所有相關信息
SELECT *
FROM student
WHERE sid IN
(SELECT sid
FROM sc
WHERE score is not NULL)

5. 查詢「李」姓老師的數量

-- 5. 查詢「李」姓老師的數量
SELECT COUNT(tname)
FROM teacher
WHERE tname LIKE '李%'

6. 查詢學過「張三」老師授課的同學的信息

-- 首先根據教師表得出張三老師的教師編號
SELECT tid
FROM teacher
WHERE tname = '張三'

-- 根據張三老師的教師編號，結合課程表找出張三老師所授課程的課程編號
SELECT cid
FROM course
WHERE tid =
(SELECT tid
FROM teacher
WHERE tname = '張三')

-- 根據上述得出的課程編號得出那些上過張三老師課的學生編號
SELECT sid
FROM sc
WHERE cid =
(SELECT cid
FROM course
WHERE tid =
(SELECT tid
FROM teacher
WHERE tname = '張三'))

-- 再根據學生學號，結合學生表找出所有學生的信息
SELECT *
FROM student
WHERE sid IN
(SELECT sid
FROM sc
WHERE cid =
(SELECT cid
FROM course
WHERE tid =
(SELECT tid
FROM teacher
WHERE tname = '張三')))

-- 我習慣性拆解思路，一張一張表寫，可能會非常麻煩，如下的寫法效率很高

select distinct s.*
from student s
inner join sc sc on s.SId=sc.SId
inner join course c on sc.CId=c.CId
inner join teacher t on c.TId=t.TId
where t.Tname='張三'

7. 查詢沒有學全所有課程的同學的信息

-- 首先根據課程表計算出所有課程的數量
SELECT count(cid)
FROM course

-- 結合成績表，按照學號分組，只要選修的課程數量小於上述子查詢，得出學號
SELECT sid
FROM sc
GROUP BY sid
HAVING count(cid) <
(SELECT count(cid)
FROM course)

-- 根據上述得出的學生學號，結合學生表，得出所有學生信息
SELECT *
FROM student
WHERE sid IN
(SELECT sid
FROM sc
GROUP BY sid
HAVING count(cid) <
(SELECT count(cid)
FROM course))

-- 這道題目我看到別人寫出的答案，把學號9-13號的同學的信息也加進去了，我覺得沒必要，如果覺得應該這麼做的人可以在評論區寫答案，下面也有小部分題目也出現了類似情況，把其他學號加進去

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

SELECT DISTINCT s.*,s.sid
FROM student s, sc s2
WHERE cid IN
(SELECT cid
FROM sc
WHERE sid = '01')
AND s.sid = s2.sid
AND s2.sid <> '01'

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

-- 首先查找出01同學學習的課程
SELECT cid
FROM sc
WHERE sid ='01'

-- 哪些學生學習的課程和01號同學相似
SELECT DISTINCT sid
FROM sc
WHERE cid IN
(SELECT cid
FROM sc
WHERE sid ='01')
AND sid <> '01'
GROUP BY sid -- 這裡一定要按學號分組，否則查詢結果只會返回第一個符合條件的學號
HAVING COUNT(cid) >=3

-- 根據上述查詢的學號，結合學生表，找到相關信息
SELECT student.*
FROM student
WHERE sid IN
(SELECT DISTINCT sid
FROM sc
WHERE cid IN
(SELECT cid
FROM sc
WHERE sid ='01')
AND sid <> '01'
GROUP BY sid
HAVING COUNT(cid) >=3

-- 法二

有時需要幾張表的信息時可以同時卸載FROM 子句中

SELECT DISTINCT s2.*
FROM sc s1, student s2,
(SELECT cid
FROM sc
WHERE sid ='01')r
WHERE s1.sid = s2.sid
AND s1.cid = r.cid
AND s2.sid <> '01'
GROUP BY s2.sid
HAVING COUNT(s1.cid) >2

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

-- 首先查詢出張三老師所授課程的教師號
SELECT tid
FROM teacher
WHERE tname = '張三'

-- 結合課程表，根據上述教師號找到相應的課程號
SELECT cid
FROM course
WHERE tid =
(SELECT tid
FROM teacher
WHERE tname = '張三')

-- 結合成績表，查詢哪些學生上過張三老師的課
SELECT sid
FROM sc
WHERE cid IN
(SELECT cid
FROM course
WHERE tid =
(SELECT tid
FROM teacher
WHERE tname = '張三'))-- 換個思維，因為找沒上過的人學號，結果會把學號和課程號當成一條記錄來判斷

-- 查找有哪些學號沒有在上述子查詢中出現
SELECT DISTINCT sid
FROM sc
WHERE sid not in
(SELECT sid
FROM sc
WHERE cid IN
(SELECT cid
FROM course
WHERE tid =
(SELECT tid
FROM teacher
WHERE tname = '張三')))

-- 最後根據上述查詢學號找到相應的學生姓名
SELECT sname
FROM student
WHERE sid IN
(SELECT DISTINCT sid
FROM sc
WHERE sid not in
(SELECT sid
FROM sc
WHERE cid IN
(SELECT cid
FROM course
WHERE tid =
(SELECT tid
FROM teacher
WHERE tname = '張三'))))

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

-- 查詢兩門以上不及格的同學學號及其平均成績
SELECT sid,avg(score) as avg_score
FROM sc
WHERE score <60
GROUP BY sid
HAVING COUNT(cid) >= 2

-- 根據上述查詢的學號，結合學生表得出相關姓名
SELECT s.sid,sname,t1.avg_score
FROM student s
RIGHT JOIN
(SELECT sc.sid,avg(score) as avg_score
FROM sc
WHERE score <60
GROUP BY sc.sid
HAVING COUNT(sc.cid) >= 2)t1
ON s.sid = t1.sid

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

-- 找出01課程分數小於60的學生學號
SELECT sid,score
FROM sc
WHERE cid ='01'
AND score <60

-- 根據上述查詢出的學號，結合學生表找到相關信息
SELECT *,score
FROM student s
RIGHT JOIN
(SELECT sid,score
FROM sc
WHERE cid ='01'
AND score <60)t1
ON s.sid = t1.sid
ORDER BY score DESC

-- 法二 非常精煉的做法

select student.*
from student,sc
where sc.CId ='01'
and sc.score<60
and student.SId=sc.SId

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

-- 首先按照學號分類，查詢每個學生的平均成績
SELECT sid, AVG(score) as 平均成績
FROM sc
GROUP BY sid
ORDER BY AVG(score) DESC

-- 再添加學生的每門課程的成績
SELECT sc.sid, sc.cid, sc.score,t1.平均成績
FROM sc
LEFT JOIN
(SELECT sid, AVG(score) as 平均成績
FROM sc
GROUP BY sid)t1
ON sc.sid = t1.sid
ORDER BY t1.平均成績 DESC

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

-- 根據教師表，查詢張三老師的教師編號
SELECT tid
FROM teacher
WHERE tname = '張三'

-- 結合課程表，查詢張三老師的課程編號
SELECT cid
FROM course
WHERE tid =
(SELECT tid
FROM teacher
WHERE tname = '張三')

-- 結合成績表，查詢上過張三老師課程的學生學號
SELECT sid
FROM sc
WHERE cid =
(SELECT cid
FROM course
WHERE tid =
(SELECT tid
FROM teacher
WHERE tname = '張三'))

-- 根據上述學號結果，查詢沒上過張三老師課程的學生學號
SELECT DISTINCT sid
FROM sc
WHERE sid NOT IN
(SELECT sid
FROM sc
WHERE cid =
(SELECT cid
FROM course
WHERE tid =
(SELECT tid
FROM teacher
WHERE tname = '張三')))

-- 結合學生表，查詢上述學號的學生姓名
SELECT sname
FROM student
WHERE sid =
(SELECT DISTINCT sid
FROM sc
WHERE sid NOT IN
(SELECT sid
FROM sc
WHERE cid =
(SELECT cid
FROM course
WHERE tid =
(SELECT tid
FROM teacher
WHERE tname = '張三'))))

-- 14查詢各科成績最高分、最低分和平均分，要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列

結果如下圖所示：

SELECT sc.cid,course.cname,COUNT(DISTINCT sc.SId) 課程人數,
AVG(sc.score) 平均分,
MAX(sc.score) 最高分,
MIN(sc.score) 最低分,
COUNT(CASE
WHEN sc.score >= 60 THEN 1
ELSE NULL
END) / COUNT(DISTINCT sc.SId) 及格率,
COUNT(CASE
WHEN sc.score >= 70 AND sc.score < 80 THEN 1
ELSE NULL
END) / COUNT(DISTINCT sc.SId) 中等率,
COUNT(CASE
WHEN sc.score >= 80 AND sc.score < 90 THEN 1
ELSE NULL
END) / COUNT(DISTINCT sc.SId) 優良率,
COUNT(CASE
WHEN sc.score >= 90 AND sc.score <=100 THEN 1
ELSE NULL
END) / COUNT(DISTINCT sc.SId) 優秀率
FROM course
JOIN sc
ON course.cid = sc.cid
GROUP BY sc.cid
ORDER BY 課程人數 DESC, sc.cid;

-- 15 按各科成績進行排序，並顯示排名， Score 重複時保留名次空缺

這題有點難度，首先一方面涉及到組內排名的問題

SELECT s1.cid, s1.sid, s1.score, count(s2.score) + 1 as 排名
FROM sc s1
LEFT JOIN sc s2
ON s1.cid = s2.cid AND s1.score < s2.score
GROUP BY s1.cid,s1.sid, s1.score
ORDER BY s1.cid, count(s2.score) + 1 ASC;

大家可以再百度內搜索：mysql 組內排名，我覺得比較好的一個回答連結是：

【原】MySQL分組排序（包含組內排名、求中位數） - MERRU - 博客園www.cnblogs.com

第二就是排序問題，給大家提供一個學習連結，主要學習「賦值」這個概念，以下是我覺得講的非常清楚詳細的回答，但是此回答不是關於組內排序獲取序列號：

https://blog.csdn.net/gao763024185/article/details/79638052?from=singlemessage&isappinstalled=0blog.csdn.net

-- 按各科成績進行排序，並顯示排名， Score 重複時合併名次

此題比上一問難度增加，參考了其他人的答案

SELECT sc.SId, sc.CId, sc.score, tp.ranks
FROM sc
LEFT JOIN
(SELECT SId, CId,
(SELECT COUNT(DISTINCT sc2.score) + 1
FROM sc sc2
WHERE sc1.CId = sc2.CId
AND sc2.score > sc1.score) ranks
FROM sc sc1) tp
ON sc.SId = tp.SId AND sc.CId = tp.CId
ORDER BY sc.CId , ranks;

如果哪位大神能夠解答，或者有其他更加簡單的方法，歡迎在評論區貼出

-- 查詢學生的總成績，並進行排名，總分重複時保留名次空缺

SELECT a.*, @rank := @rank + 1 as rank_no
FROM
(SELECT sid, sum(score) AS 總成績
FROM sc
GROUP BY sid
ORDER BY sum(score) DESC)a,
(SELECT @rank := 0) b;

-- 法二
set @rank=0;
SELECT a.*, @rank := @rank + 1 as rank_no
FROM
(SELECT sid, sum(score) AS 總成績
FROM sc
GROUP BY sid
ORDER BY sum(score) DESC)a;

-- 查詢學生的總成績，並進行排名，總分重複時，相同值序號相同

SET @rank := 0 ,@rowtotal := NULL;
SELECT a.*,
CASE WHEN @rowtotal = a.sumscore THEN @rank
WHEN @rowtotal := a.sumscore THEN @rank := @rank + 1
END AS rank_no
FROM
(select sc.SId, sum(score) as sumscore
from sc
GROUP BY sc.SId
ORDER BY sum(score) desc)a;

-- 17 統計各科成績各分數段人數：課程編號，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所佔百分比

SELECT c.cname,t1.*
FROM course c
JOIN
(SELECT cid,
SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) as '85-100',
ROUND(SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END)/count(score),2) as '[85-100]佔比',
SUM(CASE WHEN score BETWEEN 70 AND 84 THEN 1 ELSE 0 END) as '70-84',
ROUND(SUM(CASE WHEN score BETWEEN 70 AND 84 THEN 1 ELSE 0 END)/count(score),2) as '[70-84]佔比',
SUM(CASE WHEN score BETWEEN 60 AND 69 THEN 1 ELSE 0 END) as '60-69',
ROUND(SUM(CASE WHEN score BETWEEN 60 AND 69 THEN 1 ELSE 0 END)/count(score),2) as '[60-69]佔比',
SUM(CASE WHEN score BETWEEN 0 AND 59 THEN 1 ELSE 0 END) as '0-59',
ROUND(SUM(CASE WHEN score BETWEEN 0 AND 59 THEN 1 ELSE 0 END)/count(score),2) as '[0-59]佔比'
FROM sc
GROUP BY cid)t1
ON c.cid = t1.cid;

-- 18.查詢各科成績前三名的記錄

-- 首先查詢出有哪些組
SELECT cid, max(score) AS 最大值
FROM sc
GROUP BY cid;

查詢結果顯示有三門課程

-- 接著使用order by 子句按成績降序排序（DESC) ，然後用limit子句返回排名前三的學生學號
SELECT *
FROM sc
WHERE cid = '01'
ORDER BY score DESC
LIMIT 3;

-- 以此類推，其他兩門課程也這麼做，用union進行聯結
(SELECT * FROM sc WHERE cid = '01' ORDER BY score DESC LIMIT 3)
union all
(SELECT * FROM sc WHERE cid = '02' ORDER BY score DESC LIMIT 3)
union all
(SELECT * FROM sc WHERE cid = '03' ORDER BY score DESC LIMIT 3);

-- 以上方法比較繁瑣，並且萬一如果有多個分組結果，用起來會非常麻煩，下面推薦一個簡單高效做法，運用關聯子查詢

SELECT *
FROM sc
WHERE (SELECT COUNT(*)
FROM sc a
WHERE sc.cid = a.cid AND sc.score < a.score )<3
ORDER BY cid ASC, sc.score DESC;

-- 19.查詢每門課程被選修的學生數

SELECT cid, count(score) as 學生人數
FROM sc
GROUP BY cid；

-- 20.查詢出只選修兩門課程的學生學號和姓名

SELECT sid, sname
FROM student
WHERE sid IN
(SELECT sid
FROM sc
GROUP BY sid
HAVING COUNT(cid) =2);

-- 21.查詢男生、女生人數

SELECT ssex,count(sid) as 人數
FROM student
GROUP BY ssex;

-- 22.查詢名字中含有「風」字的學生信息

SELECT *
FROM student
WHERE sname LIKE '%風%';

-- 23.查詢同名學生名單，並統計同名人數

SELECT sname,count(sname)
FROM student
GROUP BY sname
HAVING count(sname) >= 2;

-- 24.查詢 1990 年出生的學生名單

SELECT *
FROM student
WHERE sage BETWEEN '1990-01-01' AND '1990-12-31';

-- 法二
SELECT *
FROM student
WHERE EXTRACT(year FROM sage) = '1990'

-- EXTRACT函數——截取日期元素，只能用在mysql和postgresql中

EXTRACT(日期元素 FROM 日期）

-- 25.查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號升序排列

SELECT cid, avg(score) as avg_sc
FROM sc
GROUP BY cid
ORDER BY avg_sc DESC, cid ASC;

-- 26.查詢平均成績大於等於 85 的所有學生的學號、姓名和平均成績

SELECT s1.sid,sname,t1.平均成績
FROM student s1
JOIN
(SELECT sid,avg(score) as 平均成績
FROM sc
GROUP BY sid
HAVING avg(score) >= 85)t1
ON s1.sid = t1.sid;

-- 法二
SELECT s1.sid, sname, avg(score) as 平均成績
FROM sc s1,
student s2
WHERE s1.sid = s2.sid
GROUP BY s1.sid
HAVING avg(score) >=85;

其實上面很多題多可以不用子查詢的方法，直接在FROM 上面寫兩張表，然後聯結，這個是ORACLE的寫法，但是如果條件一旦很多的時候就容易疏漏；

子查詢的方法，感覺更有層次和邏輯，但是如果做多層嵌套的子查詢會降低SQL的性能

各有優劣，大家自行選擇啦

-- 27.查詢課程名稱為「數學」，且分數低於 60 的學生姓名和分數

SELECT sname,t1.score
FROM student s1
JOIN
(SELECT sid,score
FROM sc
WHERE cid =
(SELECT cid
FROM course
WHERE cname = '數學')
AND score < 60)t1
ON s1.sid = t1.sid

-- 法二
SELECT c.cname, s1.sname, s2.score
FROM course c,
student s1,
sc s2
WHERE c.cid = s2.cid
AND s1.sid = s2.sid
AND c.cname = '數學'
AND s2.score < 60;

以上這個方法，就是把所有的條件羅列在WHERE 子句中 ，其實感覺這樣做好像也很簡單……

-- 28.查詢所有學生的課程及分數情況（存在學生沒成績，沒選課的情況

SELECT s1.*,s2.cid,s2.score
FROM student s1
LEFT JOIN sc s2
ON s1.sid = s2.sid

-- 29.查詢任何一門課程成績在 70 分以上的姓名、課程名稱和分數

SELECT sname, cname, score
FROM student s1,
course c,
sc s2
WHERE s1.sid = s2.sid
AND c.cid = s2.cid
AND score >70

-- 30.查詢存在不及格的課程

SELECT t1.cid,cname, t1.score
FROM course c
JOIN
(SELECT cid,score
FROM sc
WHERE score <60)t1
ON c.cid = t1.cid

-- 31.查詢課程編號為 01 且課程成績在 80 分及以上的學生的學號和姓名

SELECT sid,sname
FROM student
WHERE sid IN
(SELECT sid
FROM sc
WHERE score >= 80
AND cid ='01')

-- 32.求每門課程的學生人數

SELECT cid, count(sid) AS 學生人數
FROM sc
GROUP BY cid

-- 33. 成績不重複，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績

SELECT DISTINCT s.*,t1.最高分
FROM student s
JOIN
(SELECT sid,max(score) as 最高分
FROM sc
WHERE cid =
(SELECT cid
FROM course
WHERE tid =
(SELECT tid
FROM teacher
WHERE tname = '張三')))t1
ON s.sid = t1.sid

-- 34.成績有重複的情況下，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績

SELECT s.*,t1.最高分
FROM student s
JOIN
(SELECT sid,max(score) as 最高分
FROM sc
WHERE cid =
(SELECT cid
FROM course
WHERE tid =
(SELECT tid
FROM teacher
WHERE tname = '張三')))t1
ON s.sid = t1.sid

-- 35.查詢不同課程成績相同的學生的學生編號、課程編號、學生成績

SELECT s1.sid, s1.cid, s1.score
FROM sc s1
JOIN sc s2
ON s1.sid = s2.sid AND s1.cid <> s2.cid AND s1.score = s2.score
GROUP BY s1.sid, s1.cid

-- 36.查詢每門功成績最好的前兩名

SELECT a.CId, a.SId, a.score
FROM sc a
LEFT JOIN sc b
ON a.CId = b.CId
AND a.score < b.score
GROUP BY a.CId , a.SId
HAVING COUNT(a.CId) < 2
ORDER BY CId , score DESC

-- 37.統計每門課程的學生選修人數（超過 5 人的課程才統計）

SELECT cid,count(sid)
FROM sc
GROUP BY cid
HAVING count(sid) >5

-- 38.檢索至少選修兩門課程的學生學號

SELECT sid,COUNT(cid)
FROM sc
GROUP BY sid
HAVING COUNT(cid) >=2

-- 39.查詢選修了全部課程的學生信息

SELECT *
FROM student
WHERE sid IN
(SELECT sid
FROM sc
GROUP BY sid
HAVING count(cid) =
(SELECT count(cid)
FROM course))

-- 40-41.查詢各學生的年齡，只按年份來算

SELECT sid, sname, ssex, extract(year from CURRENT_DATE) - extract(year from sage) as age
from student

SELECT current_date; 可以獲得當前日期， 日期合適是yyyy--mm--dd

-- 42.查詢本周過生日的學生

SELECT sid
FROM student
WHERE WEEKOFYEAR(current_date) = WEEKOFYEAR(sage)

關於mysql中weekofyear相關函數的解釋，以下連結中有解釋

https://blog.csdn.net/csdn_0_001/article/details/79502366blog.csdn.net

--43. 查詢下周過生日的學生

SELECT sid
FROM student
WHERE WEEKOFYEAR(current_date) + 1 = WEEKOFYEAR(sage)

-- 44.查詢本月過生日的學生

SELECT *
from student
WHERE extract(month from CURRENT_DATE) = extract(month from sage)

-- 法二
SELECT *
FROM student
WHERE MONTH(Sage) = MONTH(current_date);

-- 45.查詢下月過生日的學生

SELECT *
from student
WHERE extract(month from CURRENT_DATE) + 1 = extract(month from sage)

相關焦點

  • 這些SQL技能你都會嗎? 經典SQL面試題送給你(附答案)
    在面試數據分析工作時，資料庫是必考技能，與此同時SQL面試題也越來越難了，今天就給大家總結一些經典的SQL面試題。首先熟悉一下資料庫相關專業名詞, 比如DB、 DBMS和SQL,這些概念常常讓人傻傻分不清楚。資料庫(DataBase)是長期存儲在計算機內、有組織的、統一管理的相關數據的集合。
  • 產品經理學SQL(二)一天學會用SQL解決業務查詢問題
    如果沒看過上篇文章或想回顧的可以點擊：產品經理學SQL（一）一個小時上手SQL本文用到的資料庫表為方便學習，我們仍然使用上篇文章用到的學生表student和成績表sc，為了模擬業務中複雜的查詢任務，我們再引入課程表course和教師表teacher。
  • SQL常見面試題目(多表聯查)
    (學生id，姓名，性別，分數)student（s_id，name,sex,score）班級表（班級id，班級名稱）class（c_id，c_name）學生班級表（班級id，學生id）student_class(s_id,c_id)(考察三表聯查)1.查詢一班得分在80分以上的學生。
  • 手撕SQL | 必知必會5道SQL面試題
    如下為某公司8月份的員工薪資表。【行列轉換】以下為兩張表，一張是考生成績表、一張為考生信息表，請用一條SQL 語句實現，以下查詢結果顯示每個考生的單科成績和總分成績表列名含義如下：name：國家名稱continent ：該國家屬於哪個洲area：⾯積population：⼈⼝gdp：國內⽣產總值請用sql實現：列出歐洲每個國家的⼈均gdp，其中⼈均gdp要⾼於英國(United Kingdom
  • 50道SQL經典面試題(下)
    由於篇幅較長，上次已經給小夥伴們分享了25道面試題，還沒看的可以戳這裡：50道SQL經典面試題(上)今天繼續給小夥伴們補上剩下的25道。為便於閱讀理解，我們還是把表結構和測試數據給大家補上。一、表結構1、學生表Student(Sid,Sname,Sage,Ssex)學生編號，學生姓名，出生年月，學生性別2、課程表Course(Cid,Cname,Tid) 課程編號，課程名稱，教師編號3、教師表Teacher(Tid,Tname)
  • SQL面試題洩露!做完這50道練習題你就過關了!
    做完這些題，恭喜你！你的 SQL 就算過關了！1. 學生表Student(S#,Sname,Sage,Ssex) S# 學生編號,Sname 學生姓名,Sage 出生年月,Ssex 學生性別2. 查詢平均成績大於等於 60 分的同學的學生編號和學生姓名和平均成績3. 查詢在 SC 表存在成績的學生信息4. 查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績(沒成績的顯示為 null )5.
  • MySQL怎麼刪除#sql開頭的臨時表
    原因如果MySQL在一個 ALTER TABLE操作(ALGORITHM=INPLACE)的中間退出，那麼可能會留下一個佔用系統空間的臨時表。例如，在對一張表(大表)添加索引時中途中斷、磁碟不足導致異常或正在添加索引時實例被kill等等情況所致。
  • SparkSQL 50道練習題
    5.查詢Score表中的最高分的學生學號和課程號。（子查詢或者排序）6.查詢每門課的平均成績。7.查詢Score表中至少有5名學生選修的並以3開頭的課程的平均分數。8.查詢分數大於70，小於90的Sno列。9.查詢所有學生的Sname、Cno和Degree列。10.查詢所有學生的Sno、Cname和Degree列。11.查詢所有學生的Sname、Cname和Degree列。
  • mysql經典面試題(2019年整理)
    mysql經典面試題(2019年整理)MySQL資料庫開發規範MYSQL如何優化？mysql經典面試題(2019年整理)mysql的sql語句優化方法面試題總結Mysql的引擎有哪些？支持事物麼？DB儲存引擎有哪些？
  • SQL經典面試50題 | 附答案
    p=1數據表介紹學生表： Student(s_id,s_name,s_birth,s_sex) 學生編號,學生姓名, 出生年月,學生性別課程表：Course(c_id,c_name,t_id) 課程編號
  • PL/SQL中如何導出表結構和表數據
    在資料庫操作中，很多時候會發現資料庫不兼容，但原來的資料庫中又有很多數據，表結構也很複雜，這時候就會想到將這個表中的數據和表結構都移植到另一個資料庫中，在PL/SQL的工具欄中，可以進行資料庫表移植和數據轉移。
  • 50道SQL經典面試題(上)
    最近在收集SQL每日一題時，找到這套比較經典的SQL面試題。我根據題目重新梳理了一遍，包括表結構，表之間的關係，測試數據，題目，參考答案等。其中大部分參考答案在各種資料庫平臺上通用。由於題目數量較多（足足50道題），小夥伴們可能不容易消化理解，於是將內容分為上下兩篇，希望對你有所幫助。
  • SQL面試經典50題:帶你從建表開始!
    大家好，相信很多學習數據分析的小夥伴在面試前都經歷過刷題，本系列小編將帶大家一起來刷一刷SQL面試必會的經典50題。當然本系列文章不單單是刷題，小編會帶著大家梳理一下解題時用到的知識點，所以基礎比較差的小夥伴也完全不必擔心，小編會帶著大家由淺入深，一步一步實現解題過程。你準備好了嗎？相信讀完本系列文章之後，你會覺得：哇！SQL原來如此簡單和有意思。
  • 國家公務員考試筆試時間安排表
    國家公務員考試筆試時間安排表 　　2021國家公務員考試準考證列印開始時間是2020年11月23日0:00，此次國考筆試包括公共科目和專業科目，公共科目筆試時間為2020年11月29日。
  • 國家公務員考試網2021年筆試成績已公布-附國考入面名單課程表
    【導讀】華圖國家公務員考試網同步國家公務員招聘網發布：國家公務員考試網2021年筆試成績已公布－附國考入面名單課程表,更多關於國家公務員,廣西公務員,公務員考試的內容請關注廣西國家公務員考試網,以及廣西華圖微信公眾號(gxhuatu)和國考交流群:928698228）,獲取更多招考信息和備考資料。
  • 國考筆試成績時間公布_國家公務員局職位表
    國考筆試成績時間公布_國家公務員局職位表由國家公務員考試網考試快訊欄目由提供，更多關於國家公務員考試成績查詢,國家公務員考試考試快訊的內容，請關注國家公務員考試網/廣東公務員考試網！
  • 2021國考筆試成績發布_國家公務員局職位表
    2021國考筆試成績發布_國家公務員局職位表由國家公務員考試網考試快訊欄目由提供，更多關於國考成績查詢時間,國家公務員考試考試快訊的內容，請關注國家公務員考試網/廣東公務員考試網！
  • 阿里規定超過三張表禁止JOIN,為啥呢?
    四、我概述下我的實驗 有4張表，student學生表，teacher老師表，course課程表，sc中間關係表，記錄了學生選修課程以及分數。 具體sql腳本，看文章結尾，我附上。中間我自己寫了造數據的腳本，也在結尾。
  • 高中課改課程表排定
    普高新課程實驗方案 課程選擇　　每個人的課程表不一樣「學會選擇」將是今年進入高一的學生面臨的第一個問題。　　新課程分為必修和選修後，學生的課程表個性化，這樣在一個班級甚至一所學校中，找到兩張完全相同的課程表將很困難。
  • 小愛課程表
    小愛課程表是一款基於小米的智能AI小愛同學打造的輔助學習教育類軟體，通過建立智能的AI課程表來幫助學生高效管理課程。特別適合給大學生來使用，可以通過拍照來快速和教務系統來快速導入課表，實時查詢課程、教室位置、老師信息，學習安排盡在掌中。並且最新版本中還加入了分享功能，一人編輯完課程表後可以一鍵分享給全班同學。從此再也不怕記錯課程了，並且可以讓你隨時查看全天的學習安排，方便制定學習計劃。