50道SQL练习题

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

参考

  1. https://www.bilibili.com/video/BV14h411R7F6/?p=4&vd_source=76a21f3936db28f5e63b70544272d65e
  2. https://www.cnblogs.com/Diyo/p/11424844.html
  3. 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);

Snipaste_2023-06-14_15-57-23.png

三、练习题和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

image.png

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

image.png

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

image.png

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 = '张三' ) 
	)

image.png

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)

image.png

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'

image.png

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' 
	)

image.png

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)

image.png

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' ))

image.png

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' );

image.png

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

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

  1. 首先利用teacher和course表查询张三老师讲授的所有课程
  2. 然后利用score表查询学过张三老师教授课程的学生
  3. 最后利用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 = '张三' )))

image.png

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

image.png

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	

image.png

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

image.png

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

image.png

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

image.png

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

image.png

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

image.png

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

image.png

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 

image.png

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

image.png

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

image.png

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

image.png

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 

image.png

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

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

image.png

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'

image.png

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

image.png

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

image.png

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

image.png

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

image.png

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

image.png

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

image.png

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

image.png

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

image.png

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;

image.png

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

还可以使用开窗函数
image.png

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

image.png

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

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

image.png

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)

image.png

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

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

image.png

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模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • Hive考试练习题(参考题解)

      前置环境: 请打开【VMware Workstation Pro】中的【linux1】环境,登录账号【root】密码【root】。  一、搭建MySQL运行环境(25分,每项5分) 1、删除MySQL依赖的libs包 2、安装MySQL的服务器与安装MySQL的客户端 3、登录MySQL并修改MySQL密码 4、给与MySQL的master权限 5、刷新MySQL权限并退出

    2024年02月09日
    浏览(20)
  • SQL经典练习题

    1.学生表 Student(SId,Sname,Sage,Ssex) SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 2.课程表 Course(CId,Cname,TId) CId 课程编号,Cname 课程名称,TId 教师编号 3.教师表 Teacher(TId,Tname) TId 教师编号,Tname 教师姓名 4.成绩表 SC(SId,CId,score) SId 学生编号,CId 课程编号,score 分数 学生表 Studen

    2024年01月20日
    浏览(54)
  • sql函数练习题

    1. 计算用户8月每天的练题数量 题目:现在运营想要计算出 2021年8月每天用户练习题目的数量 ,请取出相应数据。 示例:question_practice_detail id device_id question_id result date 1 2138 111 wrong 2021-05-03 2 3214 112 wrong 2021-05-09 3 3214 113 wrong 2021-06-15 4 6543 111 right 2021-08-13 5 2315 115 right 2021-08-1

    2024年02月07日
    浏览(15)
  • sql练习题

    DQL练习1-学生表     创建如下学生表      create table student(     id int,     name varchar(20),     gender varchar(20),     chinese int,     math int,     english int     );                   insert into student values      (1,\\\'张明\\\',\\\'男\\\',89,78,90),     (2,\\\'李进\\\',\\\'男\\\',67,53,95),     (3,\\\'王五

    2024年02月16日
    浏览(19)
  • 【MySQL】- 05 sql 语句练习题

    查询结果就不放了,语句是否成功运行,结果是否正确都容易自行判断 –1. 学生表 Student(s_id,s_name,s_birth,s_sex) --学生编号,学生姓名, 出生年月,学生性别 –2. 课程表 Course(c_id,c_name,t_id) – --课程编号, 课程名称, 教师编号 –3. 教师表 Teacher(t_id,t_name) --教师编号,教师姓名 –4. 成

    2024年02月08日
    浏览(30)
  • 【SQL每日一练】分组过滤练习题

    题目:现在运营想查看每个学校用户的平均发贴和回帖情况,寻找低活跃度学校进行重点运营,请取出平均发贴数低于5的学校或平均回帖数小于20的学校。 代码如下: WHERE 无法与合计函数一起使用; SQL语句执行顺序 (8) SELECT (9) DISTINCT (1) FROM (3) JOIN (2) ON (4) WHERE (5) GR

    2024年04月23日
    浏览(10)
  • Hive SQL必刷练习题:留存率问题(*****)

    首次登录算作当天新增,第二天也登录了算作一日留存。可以理解为,在10月1号登陆了。在10月2号也登陆了,那这个人就可以算是在1号留存 今日留存率 = (今日登录且明天也登录的用户数) / 今日登录的总用户数 * 100% ​ 这类问题主要 借助left join ,根据原表的数据,先去找

    2024年04月24日
    浏览(6)
  • 【MySQL】- 05 sql 语句练习题(第二部分)

    20、 查询学生的总成绩并进行排名 21、 查询不同老师所教不同课程平均分从高到低显示 22、 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩 23、 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比 24、 查询学生平均成绩及其名次

    2024年02月09日
    浏览(16)
  • Hive SQL必刷练习题:连续问题 & 间断连续(*****)

    1) 连续问题:找出连续三天(或者连续几天的啥啥啥)。 2) 间断连续:统计各用户连续登录最长天数,间断一天也算连续,比如1、3、4、6也算登陆了6天 这两类问题,都是涉及到连续,只不过连续类问题只要判断出来相邻的时间是和当前行的时间挨着即可,而间断连续,

    2024年03月27日
    浏览(25)
  • 10 SQL进阶 -- 综合练习题 -- 10道经典SQL题目,配套数据与解答

    点击下方链接直接下载 创建数据表脚本:http://tianchi-media.oss-cn-beijing.aliyuncs.com/dragonball/SQL/create_table.sql 执行建表语句 执行成功 查看创建的表 点击下方链接直接下载 插入数据脚本:https://tianchi-media.oss-cn-beijing.aliyuncs.com/dragonball/SQL/data.zip 大家下载好脚本后,先在MySQL环境中运

    2024年04月27日
    浏览(16)

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

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

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

二维码1

领取红包

二维码2

领红包