前言:为帮助大家学习SQL,在此提供SQL必练50题供大家练习
本篇延用第二章所建数据库,在此提供建表语句。
-- 创建学生表并插入数据
DROP TABLE student;
CREATE TABLE student (
s_id INT NOT NULL PRIMARY KEY,
s_name VARCHAR ( 10 ) NOT NULL,
s_sex VARCHAR ( 5 ) NOT NULL,
s_age INT NOT NULL
);
DELETE FROM student;
INSERT INTO student VALUES(1,'胡图图','男',6);
INSERT INTO student VALUES(2,'牛爷爷','男',55);
INSERT INTO student VALUES(3,'壮壮妈','女',33);
INSERT INTO student VALUES(4,'胡英俊','男',32);
INSERT INTO student VALUES(5,'壮壮','男',7);
INSERT INTO student VALUES(6,'张小丽','女',31);
INSERT INTO student VALUES(7,'小怪','男',2);
-- 创建教师表并插入数据
DROP TABLE teacher;
CREATE TABLE teacher (
t_id INT,
t_name VARCHAR ( 10 ));
DELETE FROM teacher;
INSERT INTO teacher VALUES(1,'图图妈');
INSERT INTO teacher VALUES(2,'神厨小福贵');
INSERT INTO teacher VALUES(3,'健康哥哥');
-- 创建成绩表并插入数据
DROP TABLE score;
CREATE TABLE score ( s_id INT, c_id INT, s_score INT );
DELETE FROM score;
INSERT INTO score VALUES(1,2,90);
INSERT INTO score VALUES(1,3,50);
INSERT INTO score VALUES(2,1,60);
INSERT INTO score VALUES(2,3,80);
INSERT INTO score VALUES(3,1,80);
INSERT INTO score VALUES(3,2,50);
INSERT INTO score VALUES(3,3,80);
INSERT INTO score VALUES(4,1,70);
INSERT INTO score VALUES(4,2,60);
INSERT INTO score VALUES(4,3,70);
INSERT INTO score VALUES(5,2,85);
INSERT INTO score VALUES(5,3,55);
INSERT INTO score VALUES(6,1,95);
INSERT INTO score VALUES(6,2,65);
INSERT INTO score VALUES(6,3,100);
-- 创建课程表并插入数据
DROP TABLE course;
CREATE TABLE course (
c_id INT PRIMARY KEY,
c_name VARCHAR ( 10 ) NOT NULL,
t_id INT NOT NULL );
DELETE FROM course;
INSERT INTO course VALUES(1,'厨艺',2);
INSERT INTO course VALUES(2,'体育',3);
INSERT INTO course VALUES(3,'生活',1);
知识拓展:
UNION 操作符:用于合并两个或多个 SELECT 语句的结果。使用 UNION 时,每个 SELECT 语句必须具有相同数量的列,且对应列的数据类型必须相似。UNION 操作符默认会去除重复的记录,如果需要保留所有重复记录,可以使用 UNION ALL 操作符。
SQL UNION 语法
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
在SQL中,ROW_NUMBER() 是一个窗口函数(Window Function),它用于为结果集中的每一行分配一个唯一的连续整数。这个整数是根据OVER()子句中指定的排序顺序来分配的。PARTITION BY(可选):指定分区列。如果指定了分区列,ROW_NUMBER()将在每个分区内独立计算。如果不指定分区列,则整个结果集被视为一个单一的分区。ORDER BY:指定用于分配行号的排序顺序。这是必须的,因为ROW_NUMBER()需要知道如何对行进行排序以便分配唯一的连续整数。
基本语法
ROW_NUMBER() OVER (
PARTITION BY column1, column2, ...
ORDER BY columnA, columnB, ... )
题目:
- 查询课程编号为1的课程比2的课程成绩高的所有学生的学号(子查询+连接)
-
查询平均成绩大于60分的学生的学号和平均成绩
-
查询所有学生的学号、姓名、选课数、总成绩
-
查询姓“刘”的老师的个数
-
查询没学过“图图妈”老师课的学生的学号、姓名
-
查询学过“健康哥哥”老师所教的所有课的同学的学号、姓名
-
查询学过编号为1的课程并且也学过编号为2的课程的学生的学号、姓名
-
查询课程编号为2的课程总成绩
-
查询所有课程成绩小于60分的学生的学号、姓名()未选课程则成绩视为0,最大值小于60则均小于60,此外考虑无成绩学生。
-
查询至少有一门课与学号为1的学生所学课程相同的学生的学号和姓名 (难)
-
查询和1号同学所学课程完全相同的其他同学的学号(难)(NOT IN与COUNT)
-
查询没学过"神厨小福贵"老师讲授的任一门课程的学生姓名
-
查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩(连接区块化完成任务,COUNT内不可加查询)
- 检索课程1分数小于60,按分数降序排列的学生信息
-
按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(难)
-
查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率(按课程分组,逻辑)
-
按各科成绩进行排序,并显示排名(难)(1)按照每个科目进行一次排名 (2)对所有分数进行一个总的排名
-
查询学生的总成绩并进行排名
-
查询不同老师所教不同课程平均分从高到低显示
-
查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
- 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计各分数段人数:课程ID和课程名称
- 查询学生平均成绩及其名次。考察点:row_number 的用法,记得里面可以指定是升序还是降序排列
-
查询各科成绩前三名的记录(不考虑成绩并列情况)
-
查询每门课程被选修的学生数
-
查询出只有两门课程的全部学生的学号和姓名
-
查询男生、女生人数
-
查询名字中含有"状"字的学生信息
-
查询1990年出生的学生名单
-- 考察点:Year()、Month()、Day()
-- 变形题:查询2月出生的学生名单,查询2号出生的学生名单
-
查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
-
查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列
-
查询课程名称为"生活",且分数低于60的学生姓名和分数
-
查询所有学生的课程及分数情况
-
查询任何一门课程成绩在70分以上的姓名、课程名称和分数
-
查询不及格的课程并按课程号从大到小排列
-
查询课程编号为3且课程成绩在80分以上的学生的学号和姓名
-
求每门课程的学生人数
-
查询选修“图图妈”老师所授课程的学生中成绩最高的学生姓名及其成绩
-
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 (难)
这个学生至少修了两门课以上,且这些课程成绩都相同
-
查询每门功成绩最好的前两名
-
统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
-
检索至少选修两门课程的学生学号
-
查询选修了全部课程的学生信息
- 查询各学生的年龄
-
查询两门以上不及格课程的同学的学号及其平均成绩
-
查询本月过生日的学生
-
查询下一个月过生日的学生
答案:
1.查询课程编号为1的课程比2的课程成绩高的所有学生的学号(子查询+连接)
方法一:分离题干限定条件课程号得两小表与结果对应表联立(之前我们已经讲过AS命名表别名的用法,实际上AS还可以命名列别名,列名称要加英文双引号,上述两种方法均可省略AS)
SELECT st.*,a.s_score AS "课程1成绩",b.s_score AS "课程2成绩"
FROM student as st
INNER JOIN ( SELECT s_id,s_score FROM score WHERE c_id = 1 ) AS a ON st.s_id = a.s_id
INNER JOIN ( SELECT s_id,s_score FROM score WHERE c_id = 2 ) AS b ON st.s_id = b.s_id
WHERE a.s_score > b.s_score;
方法二:分离score表得单课程成绩表联立学生号比较成绩。
SELECT a.s_id
FROM ( SELECT s_id, s_score FROM score WHERE c_id = 1 ) AS a
INNER JOIN ( SELECT s_id, s_score FROM score WHERE c_id = 2 ) AS b ON a.s_id = b.s_id
WHERE a.s_score > b.s_score;
SELECT *
FROM ( SELECT s_id, s_score FROM score WHERE c_id = 1 ) AS a
INNER JOIN ( SELECT s_id, s_score FROM score WHERE c_id = 2 ) AS b ON a.s_id = b.s_id
WHERE a.s_score > b.s_score;
2.查询平均成绩大于60分的学生的学号和平均成绩
SELECT s_id, AVG(s_score) AS avg_score FROM score GROUP BY s_id HAVING AVG(s_score) > 60;
3.查询所有学生的学号、姓名、选课数、总成绩
SELECT
st.s_id,st.s_name,COUNT(s.c_id) AS "选课数",SUM(case WHEN s.s_score is NULL THEN 0 ELSE s.s_score END) AS "总成绩"
FROM
student AS st
LEFT JOIN score AS s ON st.s_id = s.s_id
GROUP BY st.s_id;
GROUP BY与函数结合;case WHEN用法。
4.查询姓“刘”的老师的个数
SELECT COUNT(1) FROM teacher WHERE t_name LIKE '刘%';
5.查询没学过“图图妈”老师课的学生的学号、姓名
SELECT s_id,s_name FROM student
WHERE s_id NOT IN ( SELECT st.s_id FROM student AS st
INNER JOIN score AS sc ON st.s_id = sc.s_id
INNER JOIN course AS c ON c.c_id = sc.c_id
INNER JOIN teacher AS t ON t.t_id = c.t_id WHERE t.t_name = '图图妈');
6.查询学过“健康哥哥”老师所教的所有课的同学的学号、姓名
SELECT s_id,s_name FROM student WHERE s_id IN (
SELECT st.s_id FROM student AS st
INNER JOIN score AS sc ON st.s_id = sc.s_id
INNER JOIN course AS c ON c.c_id = sc.c_id
INNER JOIN teacher AS t ON t.t_id = c.t_id WHERE t_name = '健康哥哥'
);
7.查询学过编号为1的课程并且也学过编号为2的课程的学生的学号、姓名
内连接作并且用。
SELECT st.s_id,st.s_name
FROM student as st
INNER JOIN ( SELECT s_id,s_score FROM score WHERE c_id = 1 ) AS a ON st.s_id = a.s_id
INNER JOIN ( SELECT s_id,s_score FROM score WHERE c_id = 2 ) AS b ON st.s_id = b.s_id;
8、查询课程编号为2的课程总成绩
SELECT c_id,SUM(s_score) FROM score WHERE c_id = 2;
9、查询所有课程成绩小于60分的学生的学号、姓名()未选课程则成绩视为0
最大值小于60则均小于60,此外考虑无成绩学生。
方法一:INNER JOIN 后OR用法
SELECT
DISTINCT st.s_id,
st.s_name
FROM
student AS st
INNER JOIN ( SELECT s_id FROM score GROUP BY s_id HAVING MAX( s_score ) < 60 ) AS t ON st.s_id = t.s_id
OR st.s_id not in (SELECT DISTINCT s_id FROM score);
方法二:子查询(未出现重复以及score表缺失项错补,效果更优?)
SELECT
a.s_id,
a.s_name
FROM
student AS a
WHERE
( SELECT max( s_score ) FROM score GROUP BY s_id HAVING s_id = a.s_id ) < 60
OR a.s_id NOT IN ( SELECT DISTINCT s_id FROM score );
10、查询至少有一门课与学号为1的学生所学课程相同的学生的学号和姓名 (难)
SELECT DISTINCT st.s_id,st.s_name FROM student AS st
INNER JOIN score as sc ON st.s_id = sc.s_id
WHERE sc.c_id IN(
SELECT c_id FROM score WHERE s_id = 1
) AND st.s_id != 1;
11、查询和1号同学所学课程完全相同的其他同学的学号(难)(NOT IN与COUNT)
SELECT
s_id,
s_name
FROM
student
WHERE
s_id IN (
SELECT s_id FROM score WHERE s_id != 1 GROUP BY s_id
HAVING COUNT( * ) = ( SELECT COUNT( c_id ) FROM score WHERE s_id = 1 )
)
AND s_id NOT IN ( SELECT DISTINCT s_id FROM score WHERE c_id NOT IN ( SELECT c_id FROM score WHERE s_id = 1 ) );
12、查询没学过"神厨小福贵"老师讲授的任一门课程的学生姓名
SELECT
s_id,
s_name
FROM
student
WHERE
s_id NOT IN (
SELECT
s_id
FROM
score AS sc
INNER JOIN course AS c ON sc.c_id = c.c_id
INNER JOIN teacher AS t ON t.t_id = c.t_id
WHERE
t.t_name = '神厨小福贵'
);
13、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩(连接区块化完成任务,COUNT内不可加查询)
SELECT
t3.s_id,
avg( t4.s_score )
FROM
(
SELECT
t1.s_id
FROM
student t1
INNER JOIN ( SELECT * FROM score WHERE s_score < 60 ) t2 ON t1.s_id = t2.s_id
GROUP BY
t1.s_id
HAVING
count( 1 )>= 2
) t3
INNER JOIN score t4 ON t3.s_id = t4.s_id
GROUP BY
t3.s_id;
14、检索课程1分数小于60,按分数降序排列的学生信息
SELECT
st.*
FROM
student AS st,
score AS sc
WHERE
st.s_id = sc.s_id
AND sc.c_id = 1
AND sc.s_score < 60
ORDER BY
sc.s_score DESC;
15、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(难)
SELECT s_id,s_name,
(select s_score from score where s_id = st.s_id and c_id = 1) as '1_score',
(select s_score from score where s_id = st.s_id and c_id = 2) as '2_score',
(select s_score from score where s_id = st.s_id and c_id = 3) as '3_score',
(select AVG(s_score) from score where s_id = st.s_id) as 'avg_score'
FROM student AS st
ORDER BY avg_score DESC;
16、查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率(按课程分组,逻辑)
SELECT
sc.c_id '课程ID',
co.c_name '课程name',
MAX(sc.s_score) '最高分',
MIN(sc.s_score) '最低分',
AVG(sc.s_score) '平均分',
(SELECT COUNT(*) FROM score WHERE c_id = sc.c_id AND s_score >= 60)/COUNT(*) '及格率',
(SELECT COUNT(*) FROM score WHERE c_id = sc.c_id AND s_score BETWEEN 70 AND 80)/COUNT(*) '中等率',
(SELECT COUNT(*) FROM score WHERE c_id = sc.c_id AND s_score BETWEEN 80 AND 90)/COUNT(*) '优良率',
(SELECT COUNT(*) FROM score WHERE c_id = sc.c_id AND s_score >= 90 )/COUNT(*) '优秀率'
FROM score as sc,course AS co
WHERE sc.c_id = co.c_id
GROUP BY sc.c_id;
17、按各科成绩进行排序,并显示排名(难)
(1)按照每个科目进行一次排名
SELECT
s_id,
c_id,
s_score,
row_number() over ( PARTITION BY c_id ORDER BY s_score DESC ) AS 'm_rank'
FROM
score
ORDER BY
c_id;
- 对所有分数进行一个总的排名
select s_id,c_id,s_score,row_number() over (order by s_score desc) as 'm_rank'
from score
order by m_rank;
18、查询学生的总成绩并进行排名
SELECT st.s_id,st.s_name,
(case when sum(s_score) is Null then 0 else sum(s_score) end) as "总分",
rank() over (ORDER BY sum(s_score) DESC) AS 'rank'
FROM student AS st LEFT JOIN score as sc
ON st.s_id = sc.s_id
GROUP BY st.s_id;
19、查询不同老师所教不同课程平均分从高到低显示
三表联立GROUP BY使用中间表字段
方法一:
SELECT t.t_id,t.t_name,co.c_id,co.c_name,AVG(sc.s_score) AS 's_avg'
FROM teacher AS t
INNER JOIN course AS co ON t.t_id = co.t_id
INNER JOIN score AS sc ON co.c_id = sc.c_id
GROUP BY sc.c_id
ORDER BY s_avg DESC;
方法二:
SELECT t.t_id,t.t_name,co.c_id,co.c_name,AVG(sc.s_score) AS 's_avg'
FROM teacher AS t,course AS co,score AS sc
WHERE t.t_id = co.t_id AND co.c_id = sc.c_id
GROUP BY sc.c_id
ORDER BY s_avg DESC;
20、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩(重要 25类似)
方法一:
(select c_id,a.s_id,s_name,s_score,row_number() over (order by s_score desc) as 'rank'
from score as a right join student as b
on b.s_id = a.s_id
where c_id = 1
order by c_id,s_score desc
limit 1,2)
union all
(select c_id,a.s_id,s_name,s_score,row_number() over (order by s_score desc) as 'rank'
from score as a right join student as b
on b.s_id = a.s_id
where c_id = 2
order by c_id,s_score desc
limit 1,2)
union all
(select c_id,a.s_id,s_name,s_score,row_number() over (order by s_score desc) as 'rank'
from score as a right join student as b
on b.s_id = a.s_id
where c_id = 3
order by c_id,s_score desc
limit 1,2);
方法二:
SELECT * FROM
(SELECT sc.c_id,st.s_name,sc.s_score,row_number() over (PARTITION BY c_id ORDER BY s_score DESC) AS 'm_rank'
FROM score AS sc INNER JOIN student AS st
ON sc.s_id = st.s_id) AS c
WHERE m_rank IN (2,3);
- 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计各分数段人数:课程ID和课程名称
select
distinct sc.c_id,
co.c_name AS "课程名称",
(select count(*) from score where c_id = sc.c_id) "总数",
(select count(*) from score where c_id = sc.c_id and s_score between 85 and 100) AS "100-85",
(select count(*) from score where c_id = sc.c_id and s_score between 70 and 85) AS "85-70",
(select count(*) from score where c_id = sc.c_id and s_score between 60 and 70) AS "70-60",
(select count(*) from score where c_id = sc.c_id and s_score < 60) AS "<60"
from score as sc, course as co
where sc.c_id = co.c_id;
- 查询学生平均成绩及其名次
考察点:row_number 的用法,记得里面可以指定是升序还是降序排列
SELECT sc.s_id,st.s_name,AVG(sc.s_score),row_number() over (ORDER BY AVG(sc.s_score) DESC) AS 'm_rank'
FROM score AS sc,student AS st
WHERE sc.s_id = st.s_id
GROUP BY sc.s_id
ORDER BY m_rank ;
- 查询各科成绩前三名的记录(不考虑成绩并列情况)
为达到“各科”,故使用“IN”并外附select查询。(对各组分组后无法使用limit)
SELECT * FROM(
SELECT c_id,s_id,s_score,row_number() over (PARTITION by c_id ORDER BY s_score DESC) AS 'm_rank'
FROM score
) AS t
WHERE m_rank IN (1,2,3);
24、查询每门课程被选修的学生数
SELECT c_id,COUNT(1) AS '学生数'
FROM score GROUP BY c_id;
25、查询出只有两门课程的全部学生的学号和姓名
方法一:
SELECT sc.s_id,st.s_name FROM score AS sc
INNER JOIN student AS st ON sc.s_id = st.s_id
GROUP BY sc.s_id HAVING COUNT(*) =2;
方法二:
select s_id,s_name
from student where s_id in(
select s_id
from score
group by s_id
having count(*) = 2);
26、查询男生、女生人数
SELECT s_sex,COUNT(*) FROM student
GROUP BY s_sex;
27、查询名字中含有"状"字的学生信息
SELECT * FROM student
WHERE s_name LIKE '%壮%';
28、查询1990年出生的学生名单
-- 考察点:Year()、Month()、Day()
-- 变形题:查询2月出生的学生名单,查询2号出生的学生名单
SELECT * FROM student
WHERE YEAR(s_birth) = 1990
SELECT * FROM student
WHERE MONTH(s_birth) = 1
SELECT * FROM student
WHERE DAY(s_birth) = 1
29、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT st.s_id,st.s_name,AVG(sc.s_score) AS '平均成绩'
FROM student AS st
INNER JOIN score AS sc
ON st.s_id = sc.s_id
GROUP BY sc.s_id HAVING AVG(sc.s_score) >= 85;
30、查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列
SELECT c_id,AVG(s_score) AS avg_score FROM score
GROUP BY c_id
ORDER BY avg_score ASC,c_id DESC ;
- 查询课程名称为"生活",且分数低于60的学生姓名和分数
SELECT st.s_id,st.s_name,sc.s_score FROM student AS st,score AS sc,course AS co
WHERE st.s_id = sc.s_id AND sc.c_id = co.c_id
AND co.c_name = '生活' AND sc.s_score < 60
32、查询所有学生的课程及分数情况
思路同17题
方法一:
select
distinct c.s_id,
c.s_name,
(select (case when s_score is NULL then 0 else s_score end) from score as a,course as b where a.c_id = b.c_id and a.s_id = c.s_id and c_name = '厨艺') "厨艺",
(select (case when s_score is NULL then 0 else s_score end) from score as a,course as b where a.c_id = b.c_id and a.s_id = c.s_id and c_name = '体育') "体育",
(select (case when s_score is NULL then 0 else s_score end) from score as a,course as b where a.c_id = b.c_id and a.s_id = c.s_id and c_name = '生活') "生活"
from student as c,score as d
where c.s_id = d.s_id;
子查询设计course表与score表用于得到课程分数列,这里用不到student列因此无需三表联查,后续条件为三表建立联系避免冗余(本质为两表连接时重名字段系统自动编号作为不同字段),而后为得到学生id与name,再联合。
方法二:
select sc.s_id "id",
st.s_name "姓名",
MAX(case when sc.c_id='01' then s_score else null end) "01_score",
MAX(case when sc.c_id='02' then s_score else null end) "02_score",
MAX(case when sc.c_id='03' then s_score else null end) "03_score",
avg(sc.s_score) "平均成绩"
from score as sc,student as st
where sc.s_id = st.s_id
group by sc.s_id
order by "平均成绩";
33、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
SELECT st.s_id,st.s_name,co.c_name,sc.s_score FROM student AS st,score AS sc,course AS co
WHERE st.s_id = sc.s_id AND sc.c_id = co.c_id AND sc.s_score > 70;
34、查询不及格的课程并按课程号从大到小排列
SELECT s_id,c_id
FROM score
WHERE s_score<60
ORDER BY c_id DESC;
35、查询课程编号为3且课程成绩在80分以上的学生的学号和姓名
SELECT st.s_id,st.s_name,sc.c_id,sc.s_score FROM student AS st,score AS sc
WHERE st.s_id = sc.s_id AND sc.s_score > 80 AND sc.c_id = '03';
36、求每门课程的学生人数
SELECT c_id,COUNT(*) AS total FROM score
GROUP BY c_id;
37、查询选修“图图妈”老师所授课程的学生中成绩最高的学生姓名及其成绩
SELECT
st.s_id,
st.s_name,
sc.s_score
FROM
student AS st,
score AS sc,
course AS co,
teacher AS t
WHERE
st.s_id = sc.s_id
AND sc.c_id = co.c_id
AND co.t_id = t.t_id
AND t.t_name = '图图妈'
ORDER BY
sc.s_score DESC
LIMIT 1;
38、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 (难)
这个学生至少修了两门课以上,且这些课程成绩都相同
select a.s_id,st.s_name,a.s_score
from(
SELECT s_id,s_score FROM score
WHERE s_id in (SELECT s_id FROM score GROUP BY s_id HAVING COUNT(*) >= 2)
GROUP BY c_id,s_score
) AS a
INNER JOIN student AS st on st.s_id = a.s_id
GROUP BY s_id,s_score
HAVING COUNT(*) = 1;
SELECT
st.s_id,
st.s_name,
a.s_score
FROM
( SELECT s_id, s_score FROM score WHERE s_id IN ( SELECT s_id FROM score GROUP BY s_id HAVING COUNT(*) >= 2 ) ) AS a
INNER JOIN student AS st ON st.s_id = a.s_id
GROUP BY
s_id,
s_score
HAVING
COUNT(*) >= 3;
39、查询每门功成绩最好的前两名
SELECT c.* FROM (
SELECT so.c_id,st.s_name,so.s_score,row_number() over (PARTITION by so.c_id ORDER BY so.s_score DESC) AS 'm_rank'
FROM score AS so INNER JOIN student as st ON st.s_id = so.s_id
) AS c
WHERE m_rank in(1,2);
40、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT c_id,COUNT(*) FROM score
GROUP BY c_id HAVING COUNT(*) > 5
ORDER BY COUNT(*) DESC,c_id ASC;
41、检索至少选修两门课程的学生学号
SELECT s_id FROM score
GROUP BY s_id HAVING COUNT(*) >= 2;
42、查询选修了全部课程的学生信息
SELECT *
FROM student
WHERE s_id IN(SELECT
s_id
FROM
score
GROUP BY
s_id
HAVING
count(*)=(
SELECT
COUNT(*)
FROM
course));
43、查询各学生的年龄
方法一:仅年份相减,不精准,忽略未到生日,出现偏大,改进:比较月份与日期
SELECT
s_name,(
SELECT YEAR
(
NOW()) - YEAR ( s_birth )) AS '年龄'
FROM
student;
方法二:取整可能会偏大,改进:round(*,1)保留一位小数,但不美观
select st.*, round(datediff(now(),st.s_birth)/365) as "年龄"
from student as st;
方法三:向下取整,无误差且美观,可以根据需要选择方法
select st.*, floor(datediff(now(),st.s_birth)/365) as "年龄"
from student as st;
44、查询两门以上不及格课程的同学的学号及其平均成绩
SELECT s_id AS '学号',AVG(s_score) AS "平均成绩"
FROM score
WHERE s_score < 60
GROUP BY s_id HAVING COUNT(*) > 2;
45、查询本月过生日的学生
SELECT * FROM student
WHERE MONTH(NOW()) = MONTH(s_birth);
46、查询下一个月过生日的学生
select * from student
where month(now()) + 1 ) % 12 = month(s_birth);