50道SQL练习题-Toy模板网

这篇具有很好参考价值的文章主要介绍了50道SQL练习题。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

参考

https://www.bilibili.com/video/BV14h411R7F6/?p=4&vd_source=76a21f3936db28f5e63b70544272d65e
https://www.cnblogs.com/Diyo/p/11424844.html
https://zhuanlan.zhihu.com/p/113173133

一、生成表

#–1.学生表 
#Student(s_id,s_name,s_brith,s_sex) –学生编号,学生姓名, 出生年月,学生性别
CREATE TABLE `Student` (
    `s_id` VARCHAR(20),
    s_name VARCHAR(20) NOT NULL DEFAULT '',
    s_brith VARCHAR(20) NOT NULL DEFAULT '',
    s_sex VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY(s_id)
);

#–2.课程表 
#Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 
create table Course(
    c_id varchar(20),
    c_name VARCHAR(20) not null DEFAULT '',
    t_id VARCHAR(20) NOT NULL,
    PRIMARY KEY(c_id)
);

/*
–3.教师表 
Teacher(t_id,t_name) –教师编号,教师姓名 
*/
CREATE TABLE Teacher(
    t_id VARCHAR(20),
    t_name VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(t_id)
);

/*
–4.成绩表 
Score(s_id,c_id,s_score) –学生编号,课程编号,分数
*/
Create table Score(
    s_id VARCHAR(20),
    c_id VARCHAR(20) not null default '',
    s_score INT(3),
    primary key(`s_id`,`c_id`)
);

二、插入数据

#--插入学生表测试数据
#('01' , '赵雷' , '1990-01-01' , '男')
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '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' , '女');
#--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
#--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
#--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

三、练习题和sql语句

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

方法1：自连接

思路：先使用自连接score表找到01"课程比"02"课程成绩高的的学生id,然后关联学生表获取学生信息

SELECT
	s.*,
	a.s_score AS 'score01',
	b.s_score AS 'score02' 
FROM
	Score a,
	Score b,
	Student s 
WHERE
	a.s_id = b.s_id 
	AND a.c_id = '01' 
	AND b.c_id = '02' 
	AND a.s_score > b.s_score 
	AND a.s_id = s.s_id

50道SQL练习题

方法二：长形数据变成宽型数据

思路：先使用MAX、case when和group by将长型数据变为宽型数据，然后筛选出01课程比02课程成绩高的的学生id,然后关联学生表获取学生信息

SELECT
	a.s_id,
	MAX(CASE WHEN a.c_id = '01' THEN a.s_score END ) score01,
	MAX(CASE WHEN a.c_id = '02' THEN a.s_score END ) score02
FROM
	Score a 
GROUP BY
	a.s_id
HAVING (score01 > score02)

或

SELECT
	s.*,
	t.score01,
	t.score02 
FROM
	(
	SELECT
		a.s_id,
		MAX( CASE WHEN a.c_id = '01' THEN a.s_score END ) score01,
		MAX( CASE WHEN a.c_id = '02' THEN a.s_score END ) score02 
	FROM
		Score a 
	GROUP BY
		a.s_id 
	) t,
	Student s 
WHERE
	t.s_id = s.s_id 
	AND ( t.score01 > t.score02 )

50道SQL练习题

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

SELECT
	s.*,
	s1.s_score as 01_score,
	s2.s_score as 02_score
FROM
	score s1,
	score s2,
	student s
WHERE
	s1.c_id = '01' 
	AND s2.c_id = '02' 
	AND s1.s_score < s2.s_score 
	AND s1.s_id = s2.s_id 
	AND s1.s_id = s.s_id

方法二（长形数据变成宽型数据）
思路：先根据score表将长型数据变为宽型数据，然后筛选出01课程比02课程成绩高的的学生id,然后关联学生表获取学生信息

SELECT
	stu.*,
	t.s01,
	t.s02 
FROM
	(
	SELECT
		s.s_id,
		MAX( CASE WHEN s.c_id = '01' THEN s.s_score ELSE NULL END ) s01,
		MAX( CASE WHEN s.c_id = '02' THEN s.s_score ELSE NULL END ) s02 
	FROM
		score s 
	GROUP BY
		s.s_id 
	HAVING
	( s01 < s02 )) t,
	student stu 
WHERE
	t.s_id = stu.s_id 
	AND t.s01 < t.s02

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

方法一：多表连接
注意：select语句先于having语句执行的

SELECT
	a.s_id,
	s.s_name,
	ROUND(AVG( a.s_score ), 2) avg_score 
FROM
	Score a,
	Student s -- inner join
WHERE
	a.s_id = s.s_id 
GROUP BY
	a.s_id 
HAVING
	avg_score >= 60

方法二：关联子查询

SELECT
	a.s_id,
	( SELECT s_name FROM Student s WHERE s.s_id = a.s_id ) s_name, -- 关联子查询
	ROUND(AVG( a.s_score ), 2) avg_score 
FROM
	Score a 
GROUP BY
	a.s_id 
HAVING
	AVG( a.s_score ) >= 60

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)

SELECT
	s.s_id,
	s.s_name,
	ROUND(IFNULL( AVG( a.s_score ), 0 ), 2) avg_score 
FROM
	Score a
	RIGHT JOIN Student s ON a.s_id = s.s_id --右连接
GROUP BY
	s.s_id 
HAVING
	avg_score <= 60

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩（包含没有选课的）

SELECT
	s.s_id,
	s.s_name,
	COUNT( a.c_id ) count_c,
	IFNULL( SUM( a.s_score ), 0 ) sum_a 
FROM
	Student s
	LEFT JOIN Score a ON s.s_id = a.s_id -- 左连接
GROUP BY
	s.s_id,
	s.s_name

50道SQL练习题

6、查询"李"姓老师的数量

SELECT
	count( t_id )
FROM
	teacher t 
WHERE
	t.t_name LIKE '李%'

7、查询学过"张三"老师授课的同学的信息

方法一（多表关联）：

思路：首先利用teacher和course表找到张三老师教授的课程，然后关联score表找到选择张三老师教授课程的s_id,最后再关联student表获取详细信息

SELECT
	stu.* 
FROM
	teacher t,
	course c,
	score s,
	student stu 
WHERE
	t.t_id = c.t_id 
	AND c.c_id = s.c_id 
	AND s.s_id = stu.s_id
	AND t.t_name = '张三'

方法二（嵌套IN）

SELECT
	stu.* 
FROM
	student stu
	JOIN score s ON stu.s_id = s.s_id 
WHERE
	s.c_id IN (
	SELECT
		c.c_id 
	FROM
		course c 
	WHERE
		c.t_id = ( SELECT t.t_id FROM teacher t WHERE t.t_name = '张三' ) 
	)

8、查询没学过"张三"老师授课的同学的信息

思路：首先利用teacher和course表找到张三老师教授的课程，然后关联score表找到选择张三老师教授课程的s_id,最后再关联student表查询没学过"张三"老师授课的同学的信息

SELECT
	stu.* 
FROM
	student stu 
WHERE
	stu.s_id NOT IN (
	SELECT
		s.s_id 
	FROM
		score s
		JOIN (
		SELECT
			c.c_id 
		FROM
			course c 
		WHERE
		c.t_id = ( SELECT t.t_id FROM teacher t WHERE t.t_name = '张三' ) 
	) a ON s.c_id = a.c_id)

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

思路：先利用score表查询学过编号为"01"的sid,然后再从中删选出score表查询学过编号为"02"的sid，最后关联student表查询具体的同学的信息
方法一（嵌套查询）

SELECT
	s.* 
FROM
	student s 
WHERE
	s.s_id IN (
	SELECT
		s2.s_id 
	FROM
		score s2 
	WHERE
		s2.c_id = '02' 
	AND s2.s_id IN ( SELECT s1.s_id FROM score s1 WHERE s1.c_id = '01' ))

方法二（连表查询）

SELECT
	s1.* 
FROM
	student s1,
	score s2,
	score s3 
WHERE
	s1.s_id = s2.s_id 
	AND s2.s_id = s3.s_id 
	AND s2.c_id = '01' 
	AND s3.c_id = '02'

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

思路：先利用score表查出学过编号为"01"但是没有学过编号为"02"的课程的同学，再关联student表查询详细信息

SELECT
	s1.* 
FROM
	student s1,
	score s2 
WHERE
	s1.s_id = s2.s_id 
	AND s2.c_id = '01' 
	AND s2.s_id NOT IN (
	SELECT
		s3.s_id 
	FROM
		score s3 
	WHERE
		s3.c_id = '02' 
	)

11、查询没有学全所有课程的同学的信息

思路：先查询学全所有课程的同学，然后查询student表中不包含这些同学的其余同学

方法一：

SELECT
	s.* 
FROM
	student s 
WHERE
	s.s_id NOT IN (
	SELECT
		s1.s_id 
	FROM
		score s1,
		score s2,
		score s3 
	WHERE
		s1.s_id = s2.s_id 
		AND s2.s_id = s3.s_id 
		AND s1.c_id = '01' 
		AND s2.c_id = '02' 
	AND s3.c_id = '03')

方法二：

SELECT
	stu.* 
FROM
	student stu 
WHERE
	stu.s_id NOT IN (
	SELECT
		s.s_id 
	FROM
		score s 
	GROUP BY
		s.s_id 
	HAVING
	count( s.c_id ) = (select count(c.c_id) from course c))

方法三：
连表查询后分组，统计没有学全所有课程的同学

SELECT
	stu.* 
FROM
	student stu
	left join score s on stu.s_id = s.s_id  # 注意这里要用左连接，因为score里并不是所有人都有选课
GROUP BY
	stu.s_id 
HAVING
	count( s.c_id )  < (select count(c.c_id) from course c)

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

思路：首先查询“01”同学所学的课程，然后查询score表中所学课程在“01”同学所学课程之中的同学s_id(注意去重)

SELECT
	c.* 
FROM
	student c 
WHERE
	c.s_id IN (
	SELECT DISTINCT
		b.s_id 
	FROM
		score b 
	WHERE
		b.s_id != '01' 
	AND b.c_id IN ( SELECT a.c_id FROM score a WHERE a.s_id = '01' ))

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

这道题有点难度，需要用到group_concat函数,但是需要注意选择的课程c_id排序方式需相同，然后利用字符串相等判断所选课程完全相同。

思路：
1.首先查询"01"号同学学习的全部课程，然后按照升序排序的方式使用group_concat函数合并所有课程
2.然后查询score表，按照s_id分组，使用having筛选（将各同学的课程按照升序方式排列并使用group_concat函数合并）出所选课程和01号同学完全相同的其他同学
3.最后关联student表获取同学的详细信息

方法一：

SELECT
	* 
FROM
	student c 
WHERE
	c.s_id IN (
	SELECT
		b.s_id 
	FROM
		score b 
	GROUP BY
		b.s_id 
	HAVING
		group_concat( b.c_id ORDER BY b.c_id ) = ( SELECT group_concat( a.c_id ) FROM score a WHERE a.s_id = '01' ORDER BY a.c_id ) 
		AND b.s_id != '01' 
	)

方法二：

思路：保证所选课程在‘01’号学生所选课程范围内，且所选课程数和‘01’号学生所选课程数相等

SELECT
	s.s_id 
FROM
	score s 
WHERE
	s.s_id != '01' 
GROUP BY
	s.s_id 
HAVING
	sum(s.c_id IN ( SELECT c_id FROM score WHERE s_id = '01' )) = ( SELECT count( c_id ) FROM score WHERE s_id = '01' ) 
	AND count( s.c_id ) = ( SELECT count( c_id ) FROM score WHERE s_id = '01' );

14、查询没学过"张三"老师讲授的任一门课程的学生姓名

思路：首先查询学过张山老师教授课程的同学，然后再排除这些同学即所需

首先利用teacher和course表查询张三老师讲授的所有课程

然后利用score表查询学过张三老师教授课程的学生

最后利用student表查询没学过张三老师教授课程的学生

SELECT
	d.s_name 
FROM
	student d 
WHERE
	d.s_id NOT IN (
	SELECT
		c.s_id 
	FROM
		score c 
	WHERE
		c.c_id = (
		SELECT
			b.c_id 
		FROM
			course b 
		WHERE
		b.t_id = ( SELECT a.t_id FROM teacher a WHERE a.t_name = '张三' )))

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（有争议）

注意：就算是没参加考试，成绩也会被记为零分，因此也应该算是不及格

思路：使用UNION ALL，首先查出参加考试且有两门及以上不及格课程的同学，然后查询未选课的同学

SELECT
	stu.s_id,
	stu.s_name,
	t.avg_score 
FROM
	student stu,
	(
	SELECT
		s.s_id,
		ROUND(
		avg( s.s_score )) avg_score 
	FROM
		score s 
	WHERE
		s_score < 60 GROUP BY s_id HAVING count( s.s_score ) >= 2 
	) t 
WHERE
	stu.s_id = t.s_id 
UNION ALL
SELECT
	stu.s_id,
	stu.s_name,
	IFNULL( s.s_score, 0 ) AS avg_score 
FROM
	student stu
	LEFT JOIN score s ON stu.s_id = s.s_id 
WHERE
	s.s_score IS NULL

16、检索"01"课程分数小于60，按分数降序排列的学生信息

SELECT
	b.*,
	a.s_score 
FROM
	score a,
	student b 
WHERE
	a.s_id = b.s_id 
	AND a.c_id = '01' 
	AND a.s_score < 60 
ORDER BY
	a.s_score

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

注意：未参加考试的学生成绩为0

思路：
1.利用case when, max 和 group by 实现行转列，查询所有学生的所有课程成绩
2.利用group by, avg计算每个学生的平均成绩

SELECT
	a.s_id,
	a.s_name,
	ROUND(
	IFNULL( sum( b.s_score ) / ( SELECT count(*) FROM course ), 0 )) avg_score,
	IFNULL( max( CASE WHEN c_id = '01' THEN s_score ELSE NULL END ), 0 ) 01_score,
	IFNULL( max( CASE WHEN c_id = '02' THEN s_score ELSE NULL END ), 0 ) 02_score,
	IFNULL( max( CASE WHEN c_id = '03' THEN s_score ELSE NULL END ), 0 ) 03_score 
FROM
	student a
	LEFT JOIN score b ON a.s_id = b.s_id 
GROUP BY
	a.s_id 
ORDER BY
	avg_score DESC

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率–及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

方法一（关联子查询）

SELECT
	a.c_id,
	b.c_name,
	max( a.s_score ) AS '最高分',
	min( a.s_score ) AS '最低分',
	ROUND(sum( a.s_score ) / ( SELECT count( 1 ) FROM student ), 2) AS '平均分' ,
	ROUND ( 100 * ((select count(1) from score s where s.c_id = a.c_id and s.s_score >= 60) / ( SELECT count( 1 ) FROM student )), 2) as '及格率',
	ROUND ( 100 * ((select count(1) from score s where s.c_id = a.c_id and s.s_score >= 70 and s.s_score < 80) / ( SELECT count( 1 ) FROM student )), 2) as '中等率',
	ROUND ( 100 * ((select count(1) from score s where s.c_id = a.c_id and s.s_score >= 80 and s.s_score < 90) / ( SELECT count( 1 ) FROM student )), 2) as '优良率',
	ROUND ( 100 * ((select count(1) from score s where s.c_id = a.c_id and s.s_score >= 90) / ( SELECT count( 1 ) FROM student )), 2) as '优秀率'
FROM
	score a,
	course b 
WHERE
	a.c_id = b.c_id 
GROUP BY
	a.c_id

方法二（case when）

SELECT
	a.c_id,
	b.c_name,
	max( a.s_score ) AS '最高分',
	min( a.s_score ) AS '最低分',
	ROUND(sum( a.s_score ) / ( SELECT count( 1 ) FROM student ), 2) AS '平均分' ,
	ROUND ( 100 * ((sum(case when a.s_score >= 60 then 1 else 0 end )) / ( SELECT count( 1 ) FROM student )), 2) as '及格率',
	ROUND ( 100 * ((sum(case when a.s_score >= 70 and a.s_score <= 80 then 1 else 0 end)) / ( SELECT count( 1 ) FROM student )), 2) as '中等率',
	ROUND ( 100 * ((sum(case when a.s_score >= 80 and a.s_score < 90 then 1 else 0 end)) / ( SELECT count( 1 ) FROM student )), 2) as '优良率',
	ROUND ( 100 * ((sum(case when a.s_score >= 90 then 1 else 0 end )) / ( SELECT count( 1 ) FROM student )), 2) as '优秀率'
FROM
	score a,
	course b 
WHERE
	a.c_id = b.c_id 
GROUP BY
	a.c_id

19、按各科成绩进行排序，并显示排名

方法一(用函数实现排名)：

SELECT
  m.s_name,
  m.c_id,
  m.s_score,
  m.rank 
FROM
  (
  SELECT
    t.s_name,
    t.c_id,
    t.s_score,
  IF
    ( @p = t.c_id, @r := @r + 1, @r := 1 ) rank,
    @p := t.c_id 
  FROM
    ( SELECT a.*, b.c_id, b.s_score FROM student a INNER JOIN score b ON a.s_id = b.s_id ) t,
    ( SELECT @p := NULL, @r := 0 ) r 
  ORDER BY
    t.c_id,
    t.s_score DESC 
  ) m

方法二（窗口函数，mysql 8.0支持）：

SELECT
  a.s_id,
  a.c_id,
  b.s_score,
  ROW_NUMBER() over ( PARTITION BY b.c_id ORDER BY b.s_score ) as rank
FROM
  score a

20、查询学生的总成绩并进行排名

方法一(用函数实现排名)：

SELECT
  @r := @r + 1 AS rank,
  t.s_id,
  t.s_name,
  t.total_score 
FROM
  ( SELECT @r := 0 ) r,
  (
  SELECT
    a.s_id,
    a.s_name,
    IFNULL( sum( b.s_score ), 0 ) AS total_score 
  FROM
    student a
    LEFT JOIN score b ON a.s_id = b.s_id 
  GROUP BY
    a.s_id 
  ORDER BY
    total_score DESC 
  ) t

方法二（窗口函数）：

SELECT
  a.s_id,
  a.s_name,
  IFNULL( sum( b.s_score ), 0 ) AS total_score,
  ROW_NUMBER() over ( PARTITION BY a.s_id ORDER BY sum( b.score ) DESC ) AS rank 
FROM
  student a
  LEFT JOIN score b ON a.s_id = b.s_id

21、查询不同老师所教不同课程平均分从高到低显示

SELECT
  c.t_id,
  c.t_name,
  a.c_id,
  b.c_name,
  ROUND(IFNULL(avg( a.s_score ), 0), 2)AS avg_score 
FROM
  score a,
  course b,
  teacher c 
WHERE
  a.c_id = b.c_id 
  AND b.t_id = c.t_id 
GROUP BY
  a.c_id 
ORDER BY
  avg_score DESC

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（有争议）

思路：
1.首先先按照各课程的成绩排名
2.然后关联student表查询每个课程成绩第2名到第3名的学生信息及该课程成绩

SELECT
  b.*,
  m.c_id,
  m.s_score,
  m.rank 
FROM
  student b,
  (
  SELECT
    t.s_id,
    t.c_id,
    t.s_score,
  IF
    ( @p = t.c_id, @r := @r + 1, @r := 1 ) AS rank,
    @p := t.c_id 
  FROM
    ( SELECT @p := NULL, @r := 0 ) r,
    ( SELECT * FROM score a ORDER BY a.c_id, a.s_score DESC ) t 
  ) m 
WHERE
  b.s_id = m.s_id 
  AND m.rank BETWEEN 2 
AND 3

方法二（开窗函数）：

SELECT
  stu.s_id,
  stu.s_name,
  t.c_id,
  t.rank 
FROM
  student stu,
  (
  SELECT
    a.s_id,
    a.c_id,
    ROW_NUMBER() over ( PARTITION BY a.c_id ORDER BY a.s_score ) AS rank 
  FROM
    score a 
  ) t 
WHERE
  stu.s_id = t.s_id 
  AND t.rank BETWEEN 2 and 3

23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

思路：在score表中直接使用sum, case when, group by进行统计

select
	b.c_name, a.c_id,
	sum(case when a.s_score <= 60 and a.s_score >= 0 then 1 else 0 end) as '[0-60]',
	round(100 * sum(case when a.s_score <= 60 and a.s_score >= 0 then 1 else 0 end) / count(a.s_score), 2) as '[0-60] 百分比',
	sum(case when a.s_score > 60 and a.s_score <= 70 then 1 else 0 end) as '[60-70]',
	round(100 * sum(case when a.s_score > 60 and a.s_score <= 70 then 1 else 0 end) / count(a.s_score), 2) as '[60-70] 百分比',
	sum(case when a.s_score > 70 and a.s_score <= 85 then 1 else 0 end) as '[70-85]',
	round(100 * sum(case when a.s_score > 70 and a.s_score <= 85 then 1 else 0 end) / count(a.s_score), 2) as '[70-85] 百分比',
	sum(case when a.s_score > 85 and a.s_score <= 100 then 1 else 0 end) as '[85-100]',
	round(100 * sum(case when a.s_score > 85 and a.s_score <= 100 then 1 else 0 end) / count(a.s_score), 2) as '[85-100] 百分比'
from 
	score a, course b
where 
	a.c_id = b.c_id
group by 
	a.c_id

24、查询学生平均成绩及其名次（与20题一样）

方法一（用变量排序）：
思路：
1. 首先以student作为主表，score作为从表，以s_id作为分组查询学生的平均成绩，并实现按照平均成绩的倒叙排序
2. 然后再利用变量实现排序

SELECT
  t.s_id,
  t.s_name,
  t.avg_score,
  ( SELECT @r := @r + 1 ) AS rank 
FROM
  ( SELECT @r := 0 ) r,(
  SELECT
    a.s_id,
    a.s_name,
    IFNULL( round( avg( b.s_score ), 2 ), 0 ) AS avg_score 
  FROM
    student a
    LEFT JOIN score b ON a.s_id = b.s_id 
  GROUP BY
    a.s_id 
  ORDER BY
    avg_score DESC 
  ) t

方法二（开窗函数）：

SELECT
  a.s_id,
  a.s_name,
  IFNULL( round( avg( b.s_score ), 2 ), 0 ) AS avg_score,
  ROW_NUMBER() over (PARTITION BY a.s_id ORDER BY avg( b.s_score )) AS rank 
FROM
  student a
  LEFT JOIN score b ON a.s_id = b.s_id

25、查询各科成绩前三名的记录（与22题一样）

方法一（用变量排序）：
思路：
1. 首先按照各科成绩进行排名
2. 然后选择前3名的记录
不考虑并列

SELECT
    m.rank,
    b.s_id,
    b.s_name,
    m.c_id,
    s_score 
  FROM
    student b,
    (
    SELECT
    IF
      ( @p = t.c_id, @r := @r + 1, @r := 1 ) AS rank,
      t.s_id,
      @p := t.c_id AS c_id,
      t.s_score 
    FROM
      ( SELECT a.s_id, a.c_id, a.s_score FROM score a ORDER BY a.c_id, a.s_score DESC ) t,
      ( SELECT @p := NULL, @r := 0 ) r 
    ) m 
  WHERE
    b.s_id = m.s_id 
    AND m.rank BETWEEN 1 AND 3

方法二（开窗函数）：

SELECT
  b.s_id,
  b.s_name,
  b.c_id,
  t.rank 
FROM
  student b,
  (
  SELECT
    a.s_id,
    a.c_id,
    a.s_score,
    ROW_NUMBER() over ( PARTITION BY a.c_id ORDER BY a.s_score ) AS rank 
  FROM
    score a 
  ) t 
WHERE
  b.s_id = t.s_id 
  AND rank <= 3

26、查询每门课程被选修的学生数

SELECT
  a.c_id,
  count( a.s_id ) AS '人数' 
FROM
  score a 
GROUP BY
  a.c_id

27、查询出只有两门课程的全部学生的学号和姓名

SELECT
  a.s_id,
  b.s_name 
FROM
  score a,
  student b 
WHERE
  a.s_id = b.s_id 
GROUP BY
  a.s_id 
HAVING
  count( a.c_id ) = 2

28、查询男生、女生人数

SELECT
  sum(case when a.s_sex = '男' then 1 else 0 end) as '男生人数',
  sum(case when a.s_sex = '女' then 1 else 0 end) as '女生人数'
FROM
  student a

29、查询名字中含有"风"字的学生信息

SELECT
  * 
FROM
  student a 
WHERE
  a.s_name LIKE '%风%'

30、查询同名同性学生名单，并统计同名人数

SELECT
  a.s_name,
  a.s_sex,
  count( a.s_name ) as '同名人数'
FROM
  student a
  inner JOIN student b 
  ON a.s_name = b.s_name 
  AND a.s_sex = b.s_sex 
  AND a.s_id != b.s_id 
GROUP BY
  a.s_name

31、查询1990年出生的学生名单

SELECT
  * 
FROM
  student a 
WHERE
  YEAR ( a.s_brith ) = '1990'

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

SELECT
  a.c_id,
  ROUND( avg( a.s_score ), 2 ) AS avg_score 
FROM
  score a 
GROUP BY
  a.c_id 
ORDER BY
  avg_score DESC,
  a.c_id ASC

33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT
  a.s_id,
  a.s_name,
  round( avg( b.s_score ), 2 ) AS avg_score 
FROM
  student a
  LEFT JOIN score b ON a.s_id = b.s_id 
GROUP BY
  a.s_id 
HAVING
  avg_score >= 85

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

SELECT
  a.s_name,
  b.s_score 
FROM
  student a,
  score b,
  course c 
WHERE
  a.s_id = b.s_id 
  AND b.c_id = c.c_id 
  AND c.c_name = '数学' 
AND b.s_score < 60

35、查询所有学生的课程及分数情况；

SELECT
  a.s_id,
  a.s_name,
  IFNULL(MAX(case when c.c_name = '语文' then b.s_score end), 0) as '语文',
  IFNULL(MAX(case when c.c_name = '数学' then b.s_score end), 0) as '数学',
  IFNULL(MAX(case when c.c_name = '英语' then b.s_score end), 0) as '英语'
FROM
  student a
  LEFT JOIN score b ON a.s_id = b.s_id
  JOIN course c ON b.c_id = c.c_id 
group by 
  a.s_id

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；(有争议)

注意：该题不是很严谨，只限定最低分>70，选出来的可能是只修了2门课的，但题目应该是是让选修了所有课且每门>70的学生信息，我认为应该再加一个限定课程数目=所有课程数目，我的做法是先选出满足这两个条件的sid然后用这个表和其他表去联立。

SELECT
  a.s_id,
  b.s_name,
  c.c_name,
  count( a.s_id ) AS count_course,
  min( a.s_score ) AS min_score 
FROM
  score a,
  student b,
  course c 
WHERE
  a.s_id = b.s_id 
  AND a.c_id = c.c_id 
GROUP BY
  a.s_id 
HAVING
  count_course = ( SELECT count( 1 ) FROM course ) 
  AND min_score > 70

37、查询不及格的课程

SELECT
  a.s_name,
  c.c_name,
  b.s_score 
FROM
  student a
  LEFT JOIN score b ON a.s_id = b.s_id
  JOIN course c ON b.c_id = c.c_id 
WHERE
  b.s_score < 60

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

SELECT
  a.s_id,
  a.s_name 
FROM
  student a
  LEFT JOIN score b ON a.s_id = b.s_id 
WHERE
  b.c_id = '01' 
  AND b.s_score > 80

39、求每门课程的学生人数

SELECT
  a.c_id,
  count( a.c_id ) AS '课程人数' 
FROM
  score a 
GROUP BY
  a.c_id

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT
  a.*,
  c.c_name,
  b.s_score 
FROM
  student a,
  score b,
  course c,
  teacher d 
WHERE
  a.s_id = b.s_id 
  AND b.c_id = c.c_id 
  AND c.t_id = d.t_id 
  AND d.t_name = '张三' 
ORDER BY
  b.s_score DESC 
  LIMIT 1

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT 
  DISTINCT s1.* 
FROM
  Score AS s1
  INNER JOIN Score AS s2 ON s1.s_id = s2.s_id 
  AND s1.c_id <> s2.c_id 
  AND s1.s_score = s2.s_score;

42、查询每门功成绩最好的前两名(类题25)

SELECT
  m.s_id,
  m.c_id,
  m.s_score,
  m.rank 
FROM
  (
  SELECT
    t.s_id,
    t.c_id,
    t.s_score,
  IF
    ( @p = t.c_id, @r := @r + 1, @r := 1 ) rank,
    @p := t.c_id 
  FROM
    ( SELECT @p := NULL, @r := 0 ) r,
    ( SELECT * FROM score a ORDER BY a.c_id, a.s_score DESC ) t 
  ) m 
WHERE
  m.rank BETWEEN 1 AND 2

还可以使用开窗函数

43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT
  a.c_id,
  count( a.s_id ) AS count_person 
FROM
  score a 
GROUP BY
  a.c_id 
HAVING
  count_person > 5 
ORDER BY
  count_person DESC,
  a.c_id ASC

44、检索至少选修两门课程的学生学号

SELECT
  a.s_id 
FROM
  score a 
GROUP BY
  a.s_id 
HAVING
  count( a.c_id ) >= 2

45、查询选修了全部课程的学生信息

SELECT
  a.* 
FROM
  student a,
  score b 
WHERE
  a.s_id = b.s_id 
GROUP BY
  a.s_id 
HAVING
  count( b.c_id ) = (
  SELECT
    count( 1 ) 
  FROM
  course c)

46、查询各学生的年龄(有争议)

SELECT
  s_id,
  s_name,
  YEAR (
  CURDATE()) - YEAR ( s_brith ) AS s_age 
FROM
  student

47、查询本周过生日的学生

WEEKOFYEAR(): 返回日期的星期数文章来源地址https://www.toymoban.com/news/detail-485253.html

SELECT
  * 
FROM
  student 
WHERE
  WEEKOFYEAR(CURDATE()) = WEEKOFYEAR(s_brith)

48、查询下周过生日的学生

SELECT
  * 
FROM
  student 
WHERE
  WEEKOFYEAR( s_brith ) = WEEKOFYEAR(date_add(curdate(),interval 1 week))

49、查询本月过生日的学生

SELECT
  * 
FROM
  student 
WHERE
  MONTH (
  DATE_FORMAT( NOW(), '%Y%m%d' )) = MONTH ( s_brith )

50、查询下月过生日的学生

SELECT
  * 
FROM
  student 
WHERE
  MONTH (
  DATE_FORMAT( NOW(), '%Y%m%d' ))+ 1 = MONTH (s_brith)

到了这里，关于50道SQL练习题的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

50道SQL练习题

参考

一、生成表

二、插入数据

三、练习题和sql语句

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

2、 查询"01"课程比"02"课程成绩低的学生的信息及课程分数

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩（包含没有选课的）

6、查询"李"姓老师的数量

7、查询学过"张三"老师授课的同学的信息

8、查询没学过"张三"老师授课的同学的信息

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

11、查询没有学全所有课程的同学的信息

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

14、查询没学过"张三"老师讲授的任一门课程的学生姓名

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 （有争议）

16、检索"01"课程分数小于60，按分数降序排列的学生信息

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率–及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

19、按各科成绩进行排序，并显示排名

20、查询学生的总成绩并进行排名

21、查询不同老师所教不同课程平均分从高到低显示

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（有争议）

23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

24、查询学生平均成绩及其名次（与20题一样）

25、查询各科成绩前三名的记录（与22题一样）

26、查询每门课程被选修的学生数

27、查询出只有两门课程的全部学生的学号和姓名

28、查询男生、女生人数

29、查询名字中含有"风"字的学生信息

30、查询同名同性学生名单，并统计同名人数

31、查询1990年出生的学生名单

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

35、查询所有学生的课程及分数情况；

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；(有争议)

37、查询不及格的课程

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

39、求每门课程的学生人数

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

42、查询每门功成绩最好的前两名(类题25)

43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

44、检索至少选修两门课程的学生学号

45、查询选修了全部课程的学生信息

46、查询各学生的年龄(有争议)

47、查询本周过生日的学生

48、查询下周过生日的学生

49、查询本月过生日的学生

50、查询下月过生日的学生

相关文章

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

支付宝扫一扫领取红包，优惠每天领

二维码1

二维码2

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（有争议）