欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

最全50個(gè)Mysql數(shù)據(jù)庫(kù)查詢(xún)練習(xí)題

 更新時(shí)間：2020年12月29日 12:55:40   作者：新宿一次狼  
這篇文章主要介紹了最全50個(gè)數(shù)據(jù)庫(kù)查詢(xún)練習(xí)題，Mysql數(shù)據(jù)庫(kù)版本，全部都驗(yàn)證過(guò)

此數(shù)據(jù)庫(kù)查詢(xún)語(yǔ)句是網(wǎng)絡(luò)上50個(gè)數(shù)據(jù)庫(kù)查詢(xún)練習(xí)題目，網(wǎng)上有些版本是oracle語(yǔ)句寫(xiě)的，大多數(shù)公司還是用免費(fèi)的mysql數(shù)據(jù)庫(kù)，以下都是mysql版本，全部都有驗(yàn)證過(guò)。

表名和字段

–1.學(xué)生表
Student(s#, sname, sage,ssex) –學(xué)生編號(hào),學(xué)生姓名, 出生年月,學(xué)生性別
–2.課程表
Course(c#,cname,t#) – –課程編號(hào), 課程名稱(chēng), 教師編號(hào)
–3.教師表
Teacher(t#,tname) –教師編號(hào),教師姓名
–4.成績(jī)表
Sc(s#,c#,score) –學(xué)生編號(hào),課程編號(hào),分?jǐn)?shù)

測(cè)試數(shù)據(jù)

用數(shù)據(jù)庫(kù)可視化工具做練習(xí)非常方便，推薦使用sqlyog，軟件圖標(biāo)是一只海豚。

在新連接種填上本機(jī)地址，用戶(hù)名，密碼和端口就直接連上mysql。

所有測(cè)試數(shù)據(jù)如下：

# --插入學(xué)生表測(cè)試數(shù)據(jù)
INSERT INTO student VALUES('01' , '趙雷' , '1990-01-01' , '男');
INSERT INTO student VALUES('02' , '錢(qián)電' , '1990-12-21' , '男');
INSERT INTO student VALUES('03' , '孫風(fēng)' , '1990-05-20' , '男');
INSERT INTO student VALUES('04' , '李云' , '1990-08-06' , '男');
INSERT INTO student VALUES('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO student VALUES('06' , '吳蘭' , '1992-03-01' , '女');
INSERT INTO student VALUES('07' , '鄭竹' , '1989-07-01' , '女');
INSERT INTO student VALUES('08' , '王菊' , '1990-01-20' , '女');
# --插入課程表測(cè)試數(shù)據(jù)
INSERT INTO course VALUES('01' , '語(yǔ)文' , '02');
INSERT INTO course VALUES('02' , '數(shù)學(xué)' , '01');
INSERT INTO course VALUES('03' , '英語(yǔ)' , '03');
 
# --插入教師表測(cè)試數(shù)據(jù)
INSERT INTO teacher VALUES('01' , '張三');
INSERT INTO teacher VALUES('02' , '李四');
INSERT INTO teacher VALUES('03' , '王五');
 
# --插入成績(jī)表測(cè)試數(shù)據(jù)
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);

最后是50個(gè)數(shù)據(jù)庫(kù)查詢(xún)練習(xí)，已經(jīng)驗(yàn)證過(guò)，是mysql版本的。

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

SELECT * FROM
  (SELECT `s#` AS sno1, `c#`AS cno1, score FROM sc WHERE `c#`=01) a
    LEFT JOIN
  (SELECT `s#` AS sno2, `c#`AS cno2, score FROM sc WHERE `c#`=02) b
    ON a.sno1 = b.sno2
    WHERE a.score > b.score

1.1 查詢(xún)同時(shí)存在" 01 "課程和" 02 "課程的情況

SELECT * FROM
  (SELECT `s#` AS sno1, `c#`AS cno1, score FROM sc WHERE `c#`=01) a
    LEFT JOIN
  (SELECT `s#` AS sno2, `c#`AS cno2, score FROM sc WHERE `c#`=02) b
    ON a.sno1 = b.sno2
    WHERE sno2 IS NOT NULL

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

SELECT * FROM
  (SELECT `s#` AS sno1, `c#`AS cno1, score FROM sc WHERE `c#`=01) a
    LEFT JOIN
  (SELECT `s#` AS sno2, `c#`AS cno2, score FROM sc WHERE `c#`=02) b
    ON a.sno1 = b.sno2

1.3 查詢(xún)不存在" 01 "課程但存在" 02 "課程的情況

SELECT * FROM
    sc WHERE `c#`='02' AND `s#` NOT IN (SELECT `s#` FROM sc WHERE `c#`='01')

2. 查詢(xún)平均成績(jī)大于等于 60 分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)

SELECT a.`s#`,b.`sname`, a.avg_score FROM 
  (SELECT `s#` ,AVG(score) AS avg_score FROM sc GROUP BY `s#`) AS a
    LEFT JOIN student AS b
    ON a.`s#` = b.`s#`
    WHERE a.avg_score >=60

3. 查詢(xún)?cè)?SC 表存在成績(jī)的學(xué)生信息

SELECT * FROM student WHERE `s#` IN (SELECT DISTINCT `s#` FROM sc)

4. 查詢(xún)所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名、選課總數(shù)、所有課程的總成績(jī)(沒(méi)成績(jī)的顯示為 NULL )

SELECT `s#` ,sname , course_num , score_sum FROM 
  (SELECT `s#`, sname FROM student ) AS a
    LEFT JOIN
  (SELECT `s#` AS sno ,COUNT(`c#`) AS course_num ,SUM(score) AS score_sum FROM sc GROUP BY sno) AS b
    ON a.`s#` = b.sno

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

# 在最外面一層select的時(shí)候，不可以用函數(shù)
# 如果兩張表連接之后，有相同的字段，這時(shí)候select就需要把其中一個(gè)字段改名

SELECT `s#` ,sname , course_num , score_sum FROM 
  (SELECT `s#`, sname FROM student ) AS a
    LEFT JOIN
  (SELECT `s#` AS sno ,COUNT(`c#`) AS course_num ,SUM(score) AS score_sum FROM sc GROUP BY sno) AS b
    ON a.`s#` = b.sno
    WHERE course_num IS NOT NULL

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

SELECT COUNT(*) FROM teacher WHERE tname LIKE '李%'

6. 查詢(xún)學(xué)過(guò)「張三」老師授課的同學(xué)的信息

# 張三老師是01號(hào)
SELECT * FROM student WHERE `s#` IN 
  (SELECT `s#` FROM sc WHERE `c#` =
    (SELECT `c#` FROM course WHERE `t#` = 
      (SELECT `t#` FROM teacher WHERE tname='張三')))

# 7. 查詢(xún)沒(méi)有學(xué)全所有課程的同學(xué)的信息

SELECT `s#`,COUNT(`c#`) AS course_num FROM sc GROUP BY `s#`
    HAVING course_num < (SELECT COUNT(*) FROM course)

# 8. 查詢(xún)至少有一門(mén)課與學(xué)號(hào)為"01"的同學(xué)所學(xué)相同的同學(xué)的信息

SELECT * FROM student WHERE `s#` IN 
  (SELECT DISTINCT `s#` FROM sc WHERE `c#` IN
    (SELECT `c#` FROM sc WHERE `s#`=01))
  AND `s#`!= 01

# 9. 查詢(xún)和"01"號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息

SELECT `s#` FROM 
  (SELECT * FROM sc 
    LEFT JOIN 
  (SELECT `c#` AS cno FROM sc WHERE `s#` =01) a
    ON sc.`c#` = a.cno) AS b
GROUP BY `s#`    
HAVING COUNT(b.`s#`) = (SELECT COUNT(`c#`) AS cno FROM sc WHERE `s#` =01)

# 10. 查詢(xún)沒(méi)學(xué)過(guò)"張三"老師講授的任一門(mén)課程的學(xué)生姓名

# 張三是01
# 01老師是教數(shù)學(xué)，c#是02
SELECT * FROM student WHERE `s#` NOT IN 
  (SELECT DISTINCT `s#` FROM sc WHERE `c#` IN 
    (SELECT `c#` FROM course WHERE `t#` IN 
      (SELECT `t#` FROM teacher WHERE tname = '張三')))

# 11. 查詢(xún)兩門(mén)及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)

SELECT `s#`, sname, avg_score FROM 
  (SELECT `s#`, sname FROM student WHERE `s#` IN
    (SELECT a.`s#` FROM 
      (SELECT `s#`,COUNT(`c#`) AS num FROM sc WHERE score <60 GROUP BY `s#`) a
      WHERE num >=2)) AS b
    LEFT JOIN
  (SELECT `s#` AS sno ,AVG(score) AS avg_score FROM sc GROUP BY `s#`) AS c
    ON b.`s#` = c.sno

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

SELECT `s#`, sname, score FROM 
   student AS a
    LEFT JOIN 
  (SELECT `s#` AS sno,`c#`,score FROM sc WHERE `c#`= 01 AND score <60 )b
    ON a.`s#`= b.sno
  WHERE score IS NOT NULL
  ORDER BY score DESC

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

SELECT `s#` ,AVG(score) AS avg_score FROM sc GROUP BY `s#` ORDER BY avg_score DESC

# 14. 查詢(xún)各科成績(jī)最高分、最低分和平均分：
# 以如下形式顯示：課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率
# 及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90
# 要求輸出課程號(hào)和選修人數(shù)，查詢(xún)結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列

SELECT DISTINCT a.`c#`,cname,最高分,最低分,平均分,及格率,中等率,優(yōu)良率,優(yōu)秀率 FROM sc a
LEFT JOIN course ON a.`c#`=course.`c#`
LEFT JOIN (SELECT `c#`, MAX(score)最高分, MIN(score)最低分, AVG(score)平均分 FROM sc GROUP BY `c#`)b ON a.`c#`=b.`c#`
LEFT JOIN (SELECT `c#`, ROUND( r1 /cnt * 100, 2 ) AS 及格率 FROM
  (SELECT `c#`, (SUM(CASE WHEN score >=60 THEN 1 ELSE 0 END)*1.00) AS r1 , COUNT(*) AS cnt FROM sc GROUP BY `c#`) c1) c ON a.`c#`=c.`c#`
LEFT JOIN (SELECT `c#`, ROUND( r2 /cnt * 100, 2 ) AS 中等率 FROM
  (SELECT `c#`, (SUM(CASE WHEN score >=70 AND score<80 THEN 1 ELSE 0 END)*1.00) AS r2 , COUNT(*) AS cnt FROM sc GROUP BY `c#`) d1) d ON a.`c#`=d.`c#`  
LEFT JOIN (SELECT `c#`, ROUND( r3 /cnt * 100, 2 ) AS 優(yōu)良率 FROM
  (SELECT `c#`, (SUM(CASE WHEN score >=80 AND score<90 THEN 1 ELSE 0 END)*1.00) AS r3 , COUNT(*) AS cnt FROM sc GROUP BY `c#`) e1) e ON a.`c#`=e.`c#`
LEFT JOIN (SELECT `c#`, ROUND( r4 /cnt * 100, 2 ) AS 優(yōu)秀率 FROM
  (SELECT `c#`, (SUM(CASE WHEN score >=90 THEN 1 ELSE 0 END)*1.00) AS r4 , COUNT(*) AS cnt FROM sc GROUP BY `c#`) f1) f ON a.`c#`=f.`c#`

# 15. 按各科成績(jī)進(jìn)行排序，并顯示排名， Score 重復(fù)時(shí)保留名次空缺

# mysql中沒(méi)有rank()函數(shù)
# 這種是重復(fù)時(shí)候保留名次，所以最后名次和人數(shù)是一樣的
SELECT `s#`, `c#`, score, rank FROM
(SELECT `s#`, `c#`, score,
@currank := IF(@prevrank = score, @currank, @incrank) AS rank, 
@incrank := @incrank + 1, 
@prevrank := score
FROM sc , (
SELECT @currank :=0, @prevrank := NULL, @incrank := 1
) r 
ORDER BY score DESC) s

# 15.1 按各科成績(jī)進(jìn)行排序，并顯示排名， Score 重復(fù)時(shí)合并名次

# 這種是當(dāng)有重復(fù)名次的時(shí)候變成只有一個(gè)名次，所以排名的數(shù)量會(huì)變少
SELECT `s#`, `c#`, score, 
CASE 
WHEN @prevrank = score THEN @currank 
WHEN @prevrank := score THEN @currank := @currank + 1
END AS rank
FROM sc, 
(SELECT @currank :=0, @prevrank := NULL) r
ORDER BY score DESC

# 16.  查詢(xún)學(xué)生的總成績(jī)，并進(jìn)行排名，總分重復(fù)時(shí)保留名次空缺

# from后面不需要加表的別名
SELECT `s#`, sum_score, rank FROM
(SELECT `s#`, sum_score,
@currank := IF(@prevrank = sum_score, @currank, @incrank) AS rank, 
@incrank := @incrank + 1, 
@prevrank := sum_score
FROM 
(SELECT `s#`, SUM(score) AS sum_score FROM sc GROUP BY `s#`) c , 
(SELECT @currank :=0, @prevrank := NULL, @incrank := 1) r 
ORDER BY sum_score DESC) s

# 16.1 查詢(xún)學(xué)生的總成績(jī)，并進(jìn)行排名，總分重復(fù)時(shí)不保留名次空缺

SELECT c.*,
CASE 
WHEN @prevrank = c.sum_score THEN @currank 
WHEN @prevrank := c.sum_score THEN @currank := @currank + 1
END AS rank
FROM 
(SELECT a.`s#`,a.sname,SUM(score) AS sum_score
FROM (student AS a RIGHT JOIN sc AS b ON a.`s#` = b.`s#`) 
GROUP BY a.`s#` ) c , 
(SELECT @currank := 0 , @prevrank :=NULL ) d 
ORDER BY sum_score DESC

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

SELECT a.`c#` , b.cname, 
  SUM(CASE WHEN score >=85 AND score <=100 THEN 1 ELSE 0 END ) '[100-85]',
  SUM(CASE WHEN score >=85 AND score <=100 THEN 1 ELSE 0 END )*1.00/COUNT(*) AS '[100-85]percent',
    SUM(CASE WHEN score < 85 AND score >= 70 THEN 1 ELSE 0 END ) '(85-70]',
    SUM(CASE WHEN score < 85 AND score >= 70 THEN 1 ELSE 0 END )*1.00/COUNT(*) AS '(85-70]percent',
    SUM(CASE WHEN score < 70 AND score >= 60 THEN 1 ELSE 0 END ) '(70-60]',
    SUM(CASE WHEN score < 70 AND score >= 60 THEN 1 ELSE 0 END )*1.00/COUNT(*) AS '(85-70]percent',
    SUM(CASE WHEN score < 60 AND score >= 0 THEN 1 ELSE 0 END ) '(60-0]',
    SUM(CASE WHEN score < 60 AND score >= 0 THEN 1 ELSE 0 END )*1.00/COUNT(*) AS '(85-70]percent',
    COUNT(*) AS counts
FROM sc a LEFT JOIN course b ON a.`c#` = b.`c#`
GROUP BY `c#`

# 18. 查詢(xún)各科成績(jī)前三名的記錄

SELECT * FROM sc a WHERE 
  (SELECT COUNT(*) FROM sc WHERE `c#`=a.`c#` AND score>a.score)<3 
  ORDER BY a.`c#`, a.score DESC;

# 19. 查詢(xún)每門(mén)課程被選修的學(xué)生數(shù)

SELECT `c#`, COUNT(`s#`) FROM 
(SELECT `s#`,`c#` FROM sc ORDER BY `c#`)a
GROUP BY `c#` 

SELECT  a.`c#` , b.cname ,COUNT(*) AS num FROM sc a LEFT JOIN course b ON a.`c#` = b.`c#`
GROUP BY a.`c#`;

# 20. 查詢(xún)出只選修兩門(mén)課程的學(xué)生學(xué)號(hào)和姓名

SELECT a.`s#`, a.sname ,cnt FROM 
student a
LEFT JOIN 
(SELECT `s#`,COUNT(`c#`) AS cnt FROM sc GROUP BY `s#`) b
ON a.`s#`=b.`s#`
WHERE cnt=2

# 21. 查詢(xún)男生、女生人數(shù)

SELECT ssex,COUNT(ssex) FROM student GROUP BY ssex

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

SELECT * FROM student WHERE sname LIKE '%風(fēng)%'

# 23. 查詢(xún)同名同性學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)

SELECT a.*,b.同名人數(shù) FROM student a
LEFT JOIN (SELECT sname,ssex,COUNT(*) AS 同名人數(shù) FROM student GROUP BY sname,ssex)b 
ON a.sname=b.sname AND a.ssex=b.ssex
WHERE b.同名人數(shù)>1

# 24. 查詢(xún) 1990 年出生的學(xué)生名單

SELECT * FROM student WHERE YEAR(sage) = 1990

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

SELECT `c#`, ROUND(AVG(score),2) AS avg_score FROM sc GROUP BY `c#` ORDER BY `c#` ASC

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

SELECT c.`s#`,sname ,avg_score FROM
(student c 
LEFT JOIN
(SELECT `s#`, avg_score FROM 
(SELECT `s#` ,ROUND(AVG(score),2) AS avg_score FROM sc 
GROUP BY `s#` ORDER BY avg_score DESC)a 
WHERE avg_score >=85) b
ON c.`s#` =b.`s#`)
WHERE avg_score IS NOT NULL

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

SELECT a.`s#`,a.sname,b.math, b.score FROM
student a
LEFT JOIN
(SELECT `s#`,`c#` AS math ,score FROM sc WHERE `c#` IN 
  (SELECT `c#` FROM course WHERE cname = '數(shù)學(xué)')
  AND sc.score <60) b
ON a.`s#`=b.`s#`
WHERE b.score IS NOT NULL

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

SELECT a.`s#`,a.`sname`,a.`sage`,a.`ssex`,b.`c#`,b.score FROM 
student a LEFT JOIN sc b ON a.`s#` = b.`s#`
LEFT JOIN course c ON c.`c#` = b.`c#`

# 29. 查詢(xún)?nèi)魏我婚T(mén)課程成績(jī)?cè)?70 分以上的姓名、課程名稱(chēng)和分?jǐn)?shù)

SELECT a.`s#`,a.`sname`,a.`sage`,a.`ssex`,b.`c#`,b.score FROM 
student a 
LEFT JOIN 
(SELECT `s#`,`c#`,score FROM sc WHERE score >70) b ON a.`s#`=b.`s#`
LEFT JOIN course c 
ON c.`c#`=b.`c#`
WHERE score IS NOT NULL

# 30. 查詢(xún)不及格的課程

SELECT * FROM sc WHERE score < 60

# 31. 查詢(xún)課程編號(hào)為 01 且課程成績(jī)?cè)?80 分以上的學(xué)生的學(xué)號(hào)和姓名

SELECT a.`s#`, a.sname ,b.score FROM 
  student a
    LEFT JOIN
  (SELECT * FROM sc WHERE `c#`='01' AND score >= 80) b
    ON a.`s#` = b.`s#`
  WHERE score IS NOT NULL

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

SELECT `c#`,COUNT(`c#`) FROM sc GROUP BY `c#`

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

SELECT a.`s#`, a.`sname` ,b.`c#`, b.max_score FROM
student a
LEFT JOIN
(SELECT `s#` AS sid,`c#` ,MAX(score) AS max_score FROM sc WHERE `c#` IN 
  (SELECT `c#` FROM course WHERE `t#` IN 
    (SELECT `t#` FROM teacher WHERE tname = '張三'))) b
ON a.`s#`=b.sid
WHERE max_score IS NOT NULL

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

SELECT * FROM
(SELECT dd.*,
CASE 
WHEN @prevrank = dd.score THEN @currank 
WHEN @prevrank := dd.score THEN @currank := @currank + 1
END AS rank
 FROM (SELECT a.*,b.score FROM
student a 
LEFT JOIN sc b ON a.`s#` = b.`s#`
LEFT JOIN course c ON b.`c#` = c.`c#`
LEFT JOIN teacher d ON c.`t#` = d.`t#` WHERE d.tname = '張三' ) dd,(SELECT @currank := 0 , @prevrank :=NULL ) ff 
ORDER BY score DESC) AS dddddddd
WHERE rank = 1;

# 35. 查詢(xún)不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào)、課程編號(hào)、學(xué)生成績(jī)

SELECT DISTINCT a.`s#`, a.`c#`, a.score FROM sc AS a JOIN sc AS b 
WHERE a.`c#` != b.`c#` AND a.score = b.score AND a.`s#` != b.`s#`
ORDER BY a.`s#`, a.`c#`, a.score

# 36. 查詢(xún)每門(mén)功課成績(jī)最好的前兩名

# 此題和18題相同
SELECT * FROM sc a WHERE 
  (SELECT COUNT(*) FROM sc WHERE `c#`=a.`c#` AND score>a.score)<2 
  ORDER BY a.`c#`, a.score DESC;

# 37. 統(tǒng)計(jì)每門(mén)課程的學(xué)生選修人數(shù)（超過(guò) 5 人的課程才統(tǒng)計(jì)）

# 要求輸出課程號(hào)和選修人數(shù)，查詢(xún)結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列 
SELECT a.`c#`, COUNT(*) AS num FROM 
course a LEFT JOIN sc b ON a.`c#` = b.`c#`
GROUP BY a.`c#` HAVING num > 5
ORDER BY num,a.`c#`

# 38. 檢索至少選修兩門(mén)課程的學(xué)生學(xué)號(hào)

SELECT DISTINCT`s#`,COUNT(`c#`) AS num FROM sc GROUP BY `s#` HAVING num >=2

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

SELECT * FROM 
  (SELECT `s#`,COUNT(*) AS num FROM sc GROUP BY `s#` ) b
  WHERE num = (SELECT COUNT(*) FROM course)

# 40. 查詢(xún)各學(xué)生的年齡，只按年份來(lái)算

SELECT *, YEAR(NOW()) - YEAR(sage) AS age FROM student

# 41. 查詢(xún)本周過(guò)生日的學(xué)生

SELECT * FROM
(SELECT * , WEEK(sage), MONTH(sage),DAY(sage),
WEEK(STR_TO_DATE(CONCAT_WS(',',YEAR(NOW()),MONTH(sage),DAY(sage)),'%y,%m,%d')) AS w FROM student) a
WHERE w = WEEK(NOW())

# 42. 查詢(xún)下周過(guò)生日的學(xué)生

SELECT * FROM
(SELECT * , WEEK(sage), MONTH(sage),DAY(sage),WEEK(NOW()),
WEEK(STR_TO_DATE(CONCAT_WS(',',YEAR(NOW()),MONTH(sage),DAY(sage)),'%y,%m,%d')) AS w FROM student) a
WHERE w + 2 = WEEK(NOW())

# 43. 查詢(xún)本月過(guò)生日的學(xué)生

SELECT * , MONTH(sage),MONTH(NOW()) FROM student
WHERE MONTH(sage) = MONTH(NOW())

# 44. 查詢(xún)下月過(guò)生日的學(xué)生

SELECT * , MONTH(sage),MONTH(NOW()) FROM student
WHERE MONTH(sage) = MONTH(NOW()) + 1

到此這篇關(guān)于最全50個(gè)Mysql數(shù)據(jù)庫(kù)查詢(xún)練習(xí)題的文章就介紹到這了,更多相關(guān)Mysql數(shù)據(jù)庫(kù)查詢(xún)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

最新評(píng)論