Mysql Sql 語句練習(xí)題mysql 數(shù)據(jù)庫練習(xí)題

一：表與數(shù)據(jù)

CREATE DATABASE db01;

USE db01;

-- 學(xué)生表 student

CREATE TABLE student(Sid VARCHAR(10),Sname NVARCHAR(10),Sage DATETIME,Ssex NVARCHAR(10))

INSERT INTO student VALUES('01' , N'趙雷' , '1990-01-01' , N'男'),

('02' , N'錢電' , '1990-12-21' , N'男'),

('03' , N'孫風(fēng)' , '1990-05-20' , N'男'),

('04' , N'李云' , '1990-08-06' , N'男'),

('05' , N'周梅' , '1991-12-01' , N'女'),

('06' , N'吳蘭' , '1992-03-01' , N'女'),

('07' , N'鄭竹' , '1989-07-01' , N'女'),

('08' , N'王菊' , '1990-01-20' , N'女');

-- 科目表 course

CREATE TABLE course(Cid VARCHAR(10),Cname NVARCHAR(10),Tid VARCHAR(10))

INSERT INTO course VALUES('01' , N'語文' , '02'),('02' , N'數(shù)學(xué)' , '01'),('03' , N'英語' , '03');

-- 教師表 teacher

CREATE TABLE teacher(Tid VARCHAR(10),Tname NVARCHAR(10))

INSERT INTO teacher VALUES('01' , N'張三'),('02' , N'李四'),('03' , N'王五');

-- 成績表 sc

CREATE TABLE sc(Sid VARCHAR(10),Cid VARCHAR(10),score DECIMAL(18,1))

INSERT INTO sc VALUES('01' , '01' , 80)

,('01' , '02' , 90)

,('01' , '03' , 99)

,('02' , '01' , 70)

,('02' , '02' , 60)

,('02' , '03' , 80)

,('03' , '01' , 80)

,('03' , '02' , 80)

,('03' , '03' , 80)

,('04' , '01' , 50)

,('04' , '02' , 30)

,('04' , '03' , 20)

,('05' , '01' , 76)

,('05' , '02' , 87)

,('06' , '01' , 31)

,('06' , '03' , 34)

,('07' , '02' , 89)

,('07' , '03' , 98);

二練習(xí)題：

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

SELECT a.Sid,a.score,student.* FROM (SELECT * FROM sc WHERE sc.`Cid`=01 ) a

LEFT JOIN (SELECT * FROM sc WHERE sc.cid=02)b ON a.Sid=b.Sid

LEFT JOIN student ON a.Sid=student.Sid

WHERE a.score>b.score;

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

SELECT * FROM (SELECT * FROM sc WHERE sc.`Cid`=01) a

INNER JOIN (SELECT * FROM sc WHERE sc.`Cid`=02) b

ON a.Sid=b.Sid;

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

SELECT * FROM

(SELECT * FROM sc WHERE sc.`Cid`=01) a

LEFT JOIN (SELECT * FROM sc WHERE sc.`Cid`=02) b

ON a.Sid=b.Sid;

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

SELECT b.* FROM

(SELECT * FROM sc WHERE sc.`Cid`=02) b

LEFT JOIN

(SELECT * FROM sc WHERE sc.`Cid`=01) a

ON b.Sid=a.Sid

WHERE a.Sid IS NULL;

-- 2. 查詢平均成績大于等于 60 分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績

SELECT * FROM student stu

INNER JOIN

(SELECT sc.Sid,AVG(score) AS avgscore FROM sc GROUP BY Sid) a

ON stu.`Sid`=a.Sid

WHERE a.avgscore > 60

-- 3. 查詢在 sc 表存在成績的學(xué)生信息

SELECT * FROM

sc LEFT JOIN

student stu

ON sc.`Sid`=stu.`Sid`

WHERE sc.`score` IS NOT NULL;

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

SELECT * FROM student stu

LEFT JOIN

(SELECT Sid,COUNT(Cid),SUM(score) FROM sc GROUP BY Sid) b

ON stu.`Sid`=b.Sid

-- 4.1 查有成績的學(xué)生信息

SELECT DISTINCT stu.Sname,stu.`Sage`,stu.`Ssex`

FROM sc

LEFT JOIN student stu ON stu.`Sid`=sc.`Sid`

WHERE sc.`score` IS NOT NULL;

-- 5. 查詢「李」姓老師的數(shù)量

SELECT COUNT(*) '「李」姓老師的數(shù)量'

FROM teacher

WHERE Tname LIKE '李四%'

-- 6. 查詢學(xué)過「張三」老師授課的同學(xué)的信息

SELECT * FROM student stu

WHERE stu.Sid IN

(SELECT Sid FROM course

LEFT JOIN sc

ON course.`Cid`=sc.`Cid`

WHERE course.Tid

IN(SELECT Tid FROM teacher

WHERE teacher.`Tname`='張三'))

-- 7. 查詢沒有學(xué)全所有課程的同學(xué)的信息

SELECT * FROM student stu

? ? ? ? WHERE stu.Sid IN (SELECT stu.Sid? FROM student stu

LEFT JOIN sc

ON stu.Sid=sc.Sid

WHERE sc.`Cid` IS NULL)

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

#1

SELECT * FROM student stu

WHERE stu.Sid IN (SELECT DISTINCT sc.Sid FROM sc

WHERE sc.`Cid` IN (SELECT course.Cid FROM course

WHERE course.`Cid` IN (SELECT sc.`Cid` FROM sc

WHERE sc.Sid=01)))

#2

SELECT * FROM student WHERE Sid IN (SELECT Sid FROM sc WHERE Cid IN (SELECT Cid FROM sc WHERE Sid = '01'));

-- 9. 查詢和" 01 "號的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息

#01號同學(xué)的課程

SELECT sc.`Cid` FROM sc

WHERE sc.`Sid`=01

#02不在以上課程中的同學(xué)

SELECT sc.`Sid` FROM sc

WHERE sc.`Cid` NOT IN (SELECT sc.`Cid` FROM sc

WHERE sc.`Sid`=01)

#數(shù)據(jù)02子集的同學(xué)

SELECT * FROM sc

WHERE sc.`Sid` NOT IN(

SELECT sc.`Sid` FROM sc

WHERE sc.`Cid` NOT IN (SELECT sc.`Cid` FROM sc

WHERE sc.`Sid`=01)

);

#痛過grouby將所有數(shù)據(jù)按照sid進行分組，并計算出每組的sid數(shù)量

SELECT DISTINCT sc.Sid FROM sc

WHERE sc.Sid IN(

SELECT sc.Sid FROM sc

WHERE sc.`Sid` NOT IN(

SELECT sc.`Sid` FROM sc

WHERE sc.`Cid` NOT IN (SELECT sc.`Cid` FROM sc

WHERE sc.`Sid`=01))

GROUP BY Sid

HAVING COUNT(*)=(SELECT COUNT(*) FROM sc WHERE sc.`Sid`=01) AND sc.Sid!=01);

#查上面所查到的sid的信息

SELECT * FROM student

WHERE student.`Sid` IN (

SELECT DISTINCT sc.Sid FROM sc

WHERE sc.Sid IN(

SELECT sc.Sid FROM sc

WHERE sc.`Sid` NOT IN(

SELECT sc.`Sid` FROM sc

WHERE sc.`Cid` NOT IN (SELECT sc.`Cid` FROM sc

WHERE sc.`Sid`=01))

GROUP BY Sid

HAVING COUNT(*)=(SELECT COUNT(*) FROM sc WHERE sc.`Sid`=01) AND sc.Sid!=01));

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

EXPLAIN SELECT * FROM (SELECT DISTINCT Sid FROM sc

WHERE sc.`Sid` NOT IN (SELECT Cid FROM course c

LEFT JOIN teacher t

ON c.`Tid`=t.`Tid`

WHERE t.`Tname`='張三')) mx LEFT JOIN student

ON? mx.Sid=student.`Sid`

EXPLAIN SELECT SQL_NO_CACHE * FROM student

WHERE student.Sid IN (SELECT DISTINCT Sid FROM sc

WHERE sc.`Sid` NOT IN (SELECT Cid FROM course c

LEFT JOIN teacher t

ON c.`Tid`=t.`Tid`

WHERE t.`Tname`='張三'));

CREATE INDEX sc_index IN sc

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

#1查詢兩門以上不及格的sid和平均值

SELECT sc.`Sid`,AVG(score) FROM sc

WHERE sc.`score` < 60

GROUP BY sc.`Sid`

HAVING COUNT(*) >=2

#2聯(lián)表student

SELECT a.score,a.Sid,stu.Sname FROM (SELECT sc.`Sid`,AVG(score) score FROM sc

WHERE sc.`score` < 60

GROUP BY sc.`Sid`

HAVING COUNT(*) >=2) a

LEFT JOIN student stu

ON a.Sid = stu.Sid

-- 12. 檢索" 01 "課程分?jǐn)?shù)小于 60，按分?jǐn)?shù)降序排列的學(xué)生信息

SELECT * FROM (SELECT * FROM sc

WHERE sc.`Cid`=01 AND sc.`score` < 60) a

LEFT JOIN student stu

ON a.`Sid`=stu.Sid

ORDER BY a.score DESC;

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

SELECT DISTINCT sc.`Sid`,a.av FROM sc

LEFT JOIN (SELECT Sid,AVG(sc.`score`) av FROM sc

GROUP BY sc.`Sid`) a

ON sc.`Sid`=a.Sid

ORDER BY a.av

-- 14. 查詢各科成績最高分、最低分和平均分：

SELECT * FROM course cou

LEFT JOIN (SELECT sc.`Cid`,MAX(score),MIN(score),AVG(score) FROM sc

GROUP BY sc.`Cid`) b

ON cou.Cid=b.Cid

-- 15. 以如下形式顯示：課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率

-- 及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90

#01將各分?jǐn)?shù)線分類

SELECT

CASE WHEN score >= 60 AND score < 70 THEN '及格'

WHEN score >= 70 AND score < 80 THEN '中等'

WHEN score >= 80 AND score < 90 THEN '優(yōu)良'

WHEN score >= 90 THEN '優(yōu)秀'

ELSE? '不及格'

END grade

FROM sc

#02進入主題

SELECT * FROM course a

LEFT JOIN(SELECT Cid,MAX(score) '最高分',MIN(score) '最低分',AVG(score) '平均分',

SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END ) / COUNT(DISTINCT Sid) AS '及格率',

SUM(CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END ) / COUNT(DISTINCT Sid) '中等率',

SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END ) / COUNT(DISTINCT Sid) '優(yōu)良率',

SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END ) / COUNT(DISTINCT Sid) '優(yōu)秀率'

FROM sc

GROUP BY Cid) b

ON a.Cid=b.Cid

-- 16. 要求輸出課程號和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列

SELECT sc.`Cid`,COUNT(*) FROM sc

GROUP BY sc.`Cid`

ORDER BY COUNT(*) DESC,sc.`Cid` ASC;

-- 15. 按各科成績進行排序，并顯示排名， score 重復(fù)時保留名次空缺

#同score為null

SET @rk=0;

SET @last_Cid=0;

SET @score=0;

SELECT sc.`Cid`,sc.`score`,sc.`Sid`,IF(@last_Cid=sc.`Cid`,

IF(@score=sc.`score`,NULL,@rk:=@rk+1),

@rk:=1) AS rk,

@last_Cid:=sc.`Cid` AS last_Cid,

@score:=sc.`score` AS last_score

FROM sc

ORDER BY sc.`Cid`,sc.`score` DESC

-- 15.1 按各科成績進行排序，并顯示排名， score 重復(fù)時合并名次(1,1,2,3,4...)

#同分?jǐn)?shù)并列排名，所有自定義變量都要打印，否則結(jié)果會是錯的

SET @rk=0;

SET @last_Cid=0;

SET @score=0;

SELECT sc.`Cid`,sc.`score`,sc.`Sid`,IF(@id=sc.`Cid`,

IF(@score=sc.`score`,@rk:=@rk,@rk:=@rk+1),

@rk:=1) AS rk,

@id:=sc.`Cid` AS id,

@score:=sc.`score` AS last_score

FROM sc

ORDER BY sc.`Cid`,sc.`score` DESC

-- 16. 查詢學(xué)生的總成績，并進行排名，總分重復(fù)時保留名次空缺

SELECT sc.`Sid`,SUM(sc.`score`) sumscore FROM sc

GROUP BY sc.`Sid`

ORDER BY sumscore

-- 16.1 查詢學(xué)生的總成績，并進行排名，總分重復(fù)時不保留名次空缺

-- 17. 統(tǒng)計各科成績各分?jǐn)?shù)段人數(shù)：課程編號，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

#01

SELECT sc.`Cid`,

SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) / COUNT(DISTINCT Sid) '[60-0]',

SUM(CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END) / COUNT(DISTINCT Sid) '[70-60]',

SUM(CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END) / COUNT(DISTINCT Sid) '[85-70]',

SUM(CASE WHEN score >= 85 AND score < 100 THEN 1 ELSE 0 END) / COUNT(DISTINCT Sid) '[100-85]'

FROM sc

GROUP BY sc.`Cid`;

#02聯(lián)表查詢

SELECT * FROM course a

LEFT JOIN (SELECT sc.`Cid`,

SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) / COUNT(DISTINCT Sid) '[60-0]',

SUM(CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END) / COUNT(DISTINCT Sid) '[70-60]',

SUM(CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END) / COUNT(DISTINCT Sid) '[85-70]',

SUM(CASE WHEN score >= 85 AND score < 100 THEN 1 ELSE 0 END) / COUNT(DISTINCT Sid) '[100-85]'

FROM sc

GROUP BY sc.`Cid`) b

ON a.Cid=b.Cid;

-- 18. 查詢各科成績前三名的記錄 使用自定義函數(shù)

SET @rank=0;

SET @last_Cid=0;

SELECT a.Cid,a.Sid,a.score,a.rk FROM (SELECT Cid,Sid,score,

IF(@last_Cid=Cid,@rank:=@rank+1,@rank:=1) AS rk,

@last_Cid:=Cid

FROM sc

ORDER BY sc.`Cid`,sc.`score` DESC) a

WHERE a.rk <=3

-- 19. 查詢每門課程被選修的學(xué)生數(shù)

SELECT sc.`Cid`,COUNT(sc.`Sid`) FROM sc

GROUP BY sc.`Cid`

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

SELECT * FROM student a

WHERE a.Sid IN (SELECT sc.`Sid` FROM sc

GROUP BY sc.`Sid`

HAVING COUNT(DISTINCT sc.`Cid`)=2)

-- 21. 查詢男生、女生人數(shù)

#1

SELECT

SUM(CASE WHEN Ssex='男' THEN 1 ELSE 0 END) '男',

SUM(CASE WHEN Ssex='女' THEN 1 ELSE 0 END) '女'

FROM student

#2

SELECT st.Ssex,COUNT(*) FROM student st

GROUP BY st.Ssex

-- 22. 查詢名字中含有「風(fēng)」字的學(xué)生信息

SELECT * FROM student stu

WHERE stu.Sname LIKE '%風(fēng)%'

-- 23. 查詢同名同性學(xué)生名單，并統(tǒng)計同名人數(shù)

#1

SELECT a.Sname, a.Ssex,COUNT(*) FROM student a

GROUP BY a.Sname, a.Ssex

HAVING COUNT(*)>= 2

#2

SELECT c.Sname,c.Ssex, COUNT(DISTINCT c.Sid) AS counts FROM (

SELECT a.Sid, a.Sname, a.Ssex FROM student a

INNER JOIN student b

ON a.Sname = b.Sname

AND a.Ssex = b.Ssex

) c

GROUP BY c.Sname, c.Ssex

HAVING COUNT(c.Sid) >= 2;

-- 24. 查詢 1990 年出生的學(xué)生名單

SELECT * FROM student stu

WHERE YEAR(stu.`Sage`)='1990' AND MONTH(stu.Sage)='05'

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

SELECT sc.`Cid`,AVG(score) FROM sc

GROUP BY sc.`Cid`

ORDER BY AVG(score) DESC,sc.`Cid` ASC;

-- 26. 查詢平均成績大于等于 85 的所有學(xué)生的學(xué)號、姓名和平均成績

#01查詢每名同學(xué)的平均成績

SELECT Sid,AVG(sc.`score`) FROM sc

GROUP BY sc.`Sid`

#02進入正題

SELECT * FROM student a

INNER JOIN(SELECT Sid,AVG(sc.`score`) FROM sc

GROUP BY sc.`Sid`

HAVING AVG(sc.`score`) > 85 ) b

ON a.`Sid`=b.Sid

-- 27. 查詢課程名稱為「數(shù)學(xué)」，且分?jǐn)?shù)低于 60 的學(xué)生姓名和分?jǐn)?shù)

SELECT b.Sname,sc.`score` FROM course a

LEFT JOIN sc

ON a.Cid=sc.`Cid`

LEFT JOIN student b

ON SC.`Sid`=b.Sid

WHERE a.Cname='數(shù)學(xué)' AND sc.`score`<60

-- 28. 查詢所有學(xué)生的課程及分?jǐn)?shù)情況（存在學(xué)生沒成績，沒選課的情況）

SELECT a.Sname,COUNT(*) '課程數(shù)量',IF(AVG(b.`score`),AVG(b.`score`),0) FROM student a

LEFT JOIN sc b

ON a.Sid=b.Sid

GROUP BY a.Sname

SELECT a.*, b.* FROM student a

LEFT JOIN sc b

ON a.Sid = b.Sid;

-- 29. 查詢?nèi)魏我婚T課程成績在 70 分以上的姓名、課程名稱和分?jǐn)?shù)

SELECT b.`Sname`,c.`Cname`,a.score FROM

(SELECT * FROM sc

WHERE sc.`score` > 70) a

LEFT JOIN student b

ON a.`Sid`=b.Sid

LEFT JOIN course c

ON a.Cid=c.`Cid`

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

SELECT a.Sid,c.Sname,b.Cname,a.Score FROM sc a

LEFT JOIN course b

ON a.Cid=b.Cid

LEFT JOIN student c

ON a.Sid=c.Sid

WHERE a.score < 60

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

SELECT a.Sid,b.Sname FROM student a

LEFT JOIN sc b

ON a.Sid=b.`Sid`

WHERE b.`Cid`=01 AND b.`score`>80

-- 32. 求每門課程的學(xué)生人數(shù)

SELECT Cid,COUNT(DISTINCT Sid) FROM sc a

GROUP BY a.`Cid`;

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

#1

SELECT d.*,e.score FROM student d

LEFT JOIN(SELECT DISTINCT c.Sid,c.`score` FROM course a

LEFT JOIN teacher b

ON a.`Tid`=b.`Tid`

LEFT JOIN sc c

ON a.Cid=c.Cid

WHERE b.`Tname`='張三') e

ON d.Sid=e.Sid

ORDER BY e.score DESC

LIMIT 1

#2

SELECT * FROM (

SELECT a.Sid, MAX(a.score) AS maxscore, b.Sname, rank() over(ORDER BY a.score DESC) AS rk FROM sc a

LEFT JOIN student b

ON a.Sid = b.Sid

WHERE Cid = (

SELECT Cid FROM course WHERE Tid = (SELECT Tid FROM teacher WHERE Tname = '張三'))

GROUP BY Sid)c

WHERE c.rk = 1;

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

SELECT * FROM (

SELECT a.Sid, MAX(a.score) AS maxscore, b.Sname,dense_rank() over(ORDER BY a.score DESC) AS rk FROM sc a

LEFT JOIN student b

ON a.Sid = b.Sid

WHERE Cid = (

SELECT Cid FROM course WHERE Tid = (SELECT Tid FROM teacher WHERE Tname = '張三'))

GROUP BY Sid)c

WHERE c.rk = 1;

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

SELECT DISTINCT d.`Sname`,c.`Cname`,a.`score` FROM sc a

INNER JOIN sc b

ON a.`score`=b.`score` AND a.`Cid`!=b.`Cid`

LEFT JOIN course c

ON b.`Cid`=c.`Cid`

INNER JOIN student d

ON b.Sid=d.Sid

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

#1排序錯的，未查找到原因

SET @rank=0;

SET @last_Cid=0;

SELECT *,

IF (@last_Cid=b.`Cid`,@rank:=@rank+1,@rank:=1) AS rk,

@last_Cid:=b.`Cid` AS last_Cid

FROM course2 a

LEFT JOIN sc2 b

ON a.`Cid`=b.`Cid`

ORDER BY b.Cid,b.`score` DESC

#正確的方式

SELECT a.Sid,b.Sname,a.`Cid` FROM(SELECT sc.`Cid`,sc.`score`,sc.`Sid`,

IF(@id=sc.`Cid`,@rk:=@rk+1,@rk:=1) AS rk,

@id:=sc.`Cid` AS id

FROM sc

ORDER BY sc.`Cid`,sc.`score` DESC) a

LEFT JOIN student b

ON a.Sid=b.Sid

WHERE a.rk <=2

#下面同score為null

SET @rk=0;

SET @last_Cid=0;

SET @score=0;

SELECT sc.`Cid`,sc.`score`,sc.`Sid`,IF(@last_Cid=sc.`Cid`,

IF(@score=sc.`score`,null,@rk:=@rk+1),

@rk:=1) AS rk,

@last_Cid:=sc.`Cid` AS last_Cid,

@score:=sc.`score` AS last_score

FROM sc

ORDER BY sc.`Cid`,sc.`score` DESC

#同分?jǐn)?shù)并列排名，所有自定義變量都要打印，否則結(jié)果會是錯的

SET @rk=0;

SET @last_Cid=0;

SET @score=0;

SELECT sc.`Cid`,sc.`score`,sc.`Sid`,IF(@id=sc.`Cid`,

IF(@score=sc.`score`,@rk:=@rk,@rk:=@rk+1),

@rk:=1) AS rk,

@id:=sc.`Cid` AS id,

@score:=sc.`score` AS last_score

FROM sc

ORDER BY sc.`Cid`,sc.`score` DESC

-- 37. 統(tǒng)計每門課程的學(xué)生選修人數(shù)（超過 5 人的課程才統(tǒng)計）。

SELECT sc.`Cid`,COUNT(*) FROM sc

GROUP BY sc.`Cid`

HAVING COUNT(sc.`Sid`)>5

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

SELECT? sc.`Sid`,COUNT(DISTINCT sc.`Cid`) FROM sc

GROUP BY sc.`Sid`

HAVING COUNT(DISTINCT sc.`Cid`) >= 2

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

SELECT * FROM sc a

LEFT JOIN student b

ON a.Sid=b.`Sid`

GROUP BY a.`Sid`

HAVING COUNT(DISTINCT a.`Cid`)=(SELECT COUNT(DISTINCT Cid) FROM sc )

-- 40. 查詢各學(xué)生的年齡，只按年份來算

SELECT (YEAR(CURDATE()) - YEAR(student.Sage) + 1) FROM student;

-- 41. 按照出生日期來算，當(dāng)前月日 < 出生年月的月日則年齡減一

SELECT student.`Sid`,IF(YEAR(CURDATE()) < YEAR(student.Sage),(YEAR(CURDATE()) - YEAR(student.Sage) - 1),(YEAR(CURDATE()) - YEAR(student.Sage) + 1)) AS '年齡' FROM student;

-- 42. 查詢本周過生日的學(xué)生

SELECT stu.`Sid`,stu.`Sname` FROM student stu

WHERE WEEK(stu.`Sage`)=WEEK(NOW());

-- 43. 查詢下周過生日的學(xué)生

SELECT stu.`Sid`,stu.`Sname` FROM student stu

WHERE WEEK(stu.`Sage`)=WEEK(NOW())+1;

-- 44. 查詢本月過生日的學(xué)生

SELECT stu.`Sid`,stu.`Sname` FROM student stu

WHERE MONTH(stu.`Sage`)=MONTH(NOW());

-- 45. 查詢下月過生日的學(xué)生

SELECT stu.`Sid`,stu.`Sname` FROM student stu

WHERE MONTH(stu.`Sage`)=MONTH(NOW())+1;

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容