经典-sql-练习

最新推荐文章于 2022-08-08 10:26:11 发布

fengzyf

最新推荐文章于 2022-08-08 10:26:11 发布

阅读量245

点赞数 1

CC 4.0 BY-SA版权

分类专栏： sql 文章标签： sql 练习

原文链接：https://blog.youkuaiyun.com/paul0127/article/details/82529216

sql 专栏收录该内容

6 篇文章

订阅专栏

本文解析了50道SQL练习题，涵盖了group by、having、join等高级SQL语法，旨在帮助读者提升SQL技能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

由于自己的sql 比较弱，于是决定每天练习slq
这里感谢原作者无私提供练习题目
经典联系题目 https://blog.youkuaiyun.com/paul0127/article/details/82529216

用到group by 和 having 
如果 HAVING 中包含多个条件，那么这些条件将通过 AND、OR 或 NOT 组合在一起。
SELECT * from course GROUP BY type = 0 HAVING MIN(price) > 100

转sql语句练习50题(Mysql版)https://blog.youkuaiyun.com/fashion2014/article/details/78826299

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

自己写的是
SELECT t3.*, t2.* from (
SELECT t1.s_id, AVG(t1.s_score) from Score t1 GROUP BY t1.s_id HAVING AVG(t1.s_score) > 60
) t2 LEFT JOIN Student t3 on t2.s_id = t3.s_id 

答案给的
SELECT	b.s_id, b.s_name, ROUND(AVG(a.s_score), 2) AS avg_score FROM Student b JOIN Score a ON b.s_id = a.s_id  GROUP BY b.s_id, 	b.s_name HAVING 	avg_score >= 60;

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

总结4题：自己忽略了为null 的问题。加上 IFNULL(t2.s_score,0) 就解决了。答案给的也是对的，只不过他用了union

自己写的错误
SELECT * from Student t1 LEFT JOIN Score t2 on t1.s_id = t2.s_id GROUP BY t2.s_id HAVING ROUND(AVG(t2.s_score)) < 60

答案给的
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from 
	Student b 
	left join Score a on b.s_id = a.s_id
	GROUP BY b.s_id,b.s_name HAVING avg_score <60
	union
select a.s_id,a.s_name,0 as avg_score from 
	Student a 
	where a.s_id not in (
				select distinct s_id from Score);

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

总结5题：自己错在没有理清表关系，理清表关系至关重要，什么都不做，也要把表关系理清了。自己错在把Course表当成了选课表，测试题中没有选课表。答案给的还不是严谨，应该在最后加上IFNULL(t2.s_score,0)

自己写的错误
SELECT t1.s_id, t1.s_name, COUNT(t2.c_id) as courseCount, SUM(IFNULL(t3.s_score, 0)) as totalScore FROM	Student t1
LEFT JOIN Course t2 ON t1.s_id = t2.t_id
LEFT JOIN Score t3 ON t1.s_id = t3.s_id
GROUP BY	t1.s_id,	t1.s_name

答案给的
SELECT	a.s_id,	a.s_name,	count(b.c_id) AS sum_course,	sum(b.s_score) AS sum_score FROM	Student a
LEFT JOIN Score b ON a.s_id = b.s_id
GROUP BY	a.s_id,	a.s_name;

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

总结6题：真是大马虎，需求给的是计算老师中姓李的老师的数量，只需要在在教师表中计算就可以，但是自己却关联课程表，这样关联是错误的

自己写的错误
SELECT COUNT(*) from Course t1 LEFT JOIN Teacher t2 on t1.t_id = t2.t_id where t2.t_name LIKE '李%'

答案给的
select count(t_id) from Teacher where t_name like '李%';

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

自己写的正确
SELECT * from Score t5 LEFT JOIN Student t6 on t5.s_id = t6.s_id where t5.c_id in (
SELECT t4.c_id from Teacher t3 INNER JOIN Course t4 on t3.t_id = t4.t_id where t3.t_name = "张三" 
)
 
 答案给的
 select a.* from 	Student a join Score b on a.s_id=b.s_id where b.c_id in( select c_id from Course where t_id =( select t_id from Teacher where t_name = '张三'));

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

总结8题：自己又写错了，可见自己的sql 有多差劲。自己将成绩表和学生表联合了，一个学生选了多门课程，自己只过滤了课程等于02的记录，其另外的记录没有过滤。导致了出错。下面是自己写的另一种正确的。

自己写的正确
SELECT * from Score t5 LEFT JOIN Student t6 on t5.s_id = t6.s_id where t5.c_id not in (
SELECT t4.c_id from Teacher t3 INNER JOIN Course t4 on t3.t_id = t4.t_id where t3.t_name = "张三" 
);
SELECT * from Student t1 where t1.s_id NOT in (
SELECT  t2.s_id from Score t2 where t2.c_id in (SELECT t4.c_id from Teacher t3 INNER JOIN Course t4 on t3.t_id = t4.t_id where t3.t_name = "张三" )
)
答案给的
select * from  Student c  where c.s_id not in(
select a.s_id from Student a join Score b on a.s_id=b.s_id where b.c_id in(
select a.c_id from Course a join Teacher b on a.t_id = b.t_id where t_name ='张三'));

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

总结9题：自己写的为什么这么麻烦，主要就是自己对多张表联结还是不熟。其实没必要那么多的and 和or ,因为两张表是完全一样的。

自己写的
SELECT * from Student t4 where t4.s_id in (
SELECT  t3.s_id from (
SELECT t1.s_id from Score t1 INNER JOIN Score t2 on t1.s_id = t2.s_id WHERE (t1.c_id = '01' or t1.c_id = '02' ) and (t2.c_id = '01' or t2.c_id = '02' )and t1.c_id <> t2.c_id
) t3
GROUP BY t3.s_id
)

答案给的
select a.* from 
Student a, Score b, Score c 
where a.s_id = b.s_id  and a.s_id = c.s_id and b.c_id='01' and c.c_id='02';

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

总结10题：两个表join，有很多交叉的记录，自己用where过滤的不对，所以用子查询，更适合。

自己写的错误
SELECT * from (
SELECT t3.s_id FROM (
SELECT t1.s_id from Score t1, Score t2 where  t1.c_id = '01' and t2.c_id <> '02' and t1.s_id = t2.s_id and t1.c_id <> t2.c_id  
) t3 GROUP BY t3.s_id
) t4 LEFT JOIN Student t5 on t4.s_id = t5.s_id ;

答案给的
select a.* from 
Student a 
where a.s_id in (select s_id from Score where c_id='01' ) and a.s_id not in(select s_id from Score where c_id='02');

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

SELECT * FROM (SELECT t1.s_id from Score t1 GROUP BY t1.s_id HAVING COUNT(*) < (SELECT COUNT(*) from Course t) ) t2 
LEFT JOIN Student t3 ON t2.s_id = t3.s_id

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

对于这些包含关系的题目，使用in是最方便的。

自己写的麻烦了
SELECT  t3.s_id, t3.s_birth, t3.s_name, t3.s_sex from Score t2 LEFT JOIN Student t3 ON t2.s_id = t3.s_id WHERE  t2.s_id <> '01'and t2.c_id in (
SELECT t.c_id from Score t where t.s_id = '01'
)GROUP BY t3.s_id, t3.s_birth, t3.s_name, t3.s_sex

答案给的
select * from Student where s_id in(
	select distinct a.s_id from Score a where a.c_id in(select a.c_id from Score a where a.s_id='01')
	);

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

总结：在有group by的查询语句中，select指定的字段要么就包含在group by语句的后面，作为分组的依据，要么就包含在聚合函数中。（有关group by的知识请戳：浅析SQL中Group By的使用）。
使用group_concat 将学生的课程编号连接到一块。然后就行连接，连接就是相等。
自己犯的错误在于两次取反，导致意义不一样了。
注意：每一步，都要非常清楚其中的逻辑，和结果。中间一步不正确，导致全盘皆输。

自己写的错误
SELECT * from Student t3 where t3.s_id not in (
SELECT t2.s_id from Score t2 where t2.c_id NOT IN(
SELECT  t1.c_id from Score t1 where t1.s_id = '01'
))

答案给的
SELECT
 DISTINCT t3.s_id, t3.s_name, t3.s_birth, t3.s_sex
FROM
 (
  SELECT
   s_id,
   group_concat(c_id ORDER BY c_id) group1
  FROM
   Score
  WHERE
   s_id = '01'
  GROUP BY
   s_id
 ) t1
INNER JOIN (
 SELECT
  group_concat(c_id ORDER BY c_id) group2
 FROM
  Score
 WHERE
  s_id <> '01'
 GROUP BY
  s_id
) t2 ON t1.group1 = t2.group2
INNER JOIN Student t3 ON t1.s_id = t3.s_id
where t3.s_id <> '01'

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

自己写的答案前提是老师和课程的关系是一对多的关系

自己写的
SELECT t4.* from Student t4 where t4.s_id not IN (
SELECT t3.s_id  from Score t3 where t3.c_id in ( 
SELECT t2.c_id from Teacher t1 LEFT JOIN Course t2 on t1.t_id = t2.t_id where t1.t_name = '张三'
))

答案给的
select a.s_name from Student a where a.s_id not in (
	select s_id from Score where c_id = 
				(select c_id from Course where t_id =(
					select t_id from Teacher where t_name = '张三')));

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

SELECT * FROM (
SELECT t.s_id, ROUND(AVG(DISTINCT t.s_score))from Score t where t.s_score < 60 GROUP BY t.s_id HAVING COUNT(*) >= 2
) t2 LEFT JOIN Student t3 ON t2.s_id = t3.s_id

答案给的
select a.s_id,a.s_name,ROUND(AVG(b.s_score)) from 
	Student a 
	left join Score b on a.s_id = b.s_id
	where a.s_id in(
			select s_id from Score where s_score<60 GROUP BY  s_id having count(1)>=2)
	GROUP BY a.s_id,a.s_name

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

SELECT t1.* from Score t, Student t1  where t.c_id = '01' and t.s_score < 60 and t.s_id = t1.s_id ORDER BY t.s_score DESC

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

总结：mysql，select后面查询出来的字段，是列。而不是行！！！

第一种写法
select a.s_id,(select s_score from Score where s_id=a.s_id and c_id='01') as 语文,
				(select s_score from Score where s_id=a.s_id and c_id='02') as 数学,
				(select s_score from Score where s_id=a.s_id and c_id='03') as 英语,
			round(avg(s_score),2) as 平均分 from Score a  GROUP BY a.s_id ORDER BY 平均分 DESC;

第二种写法
SELECT a.s_id, MAX(CASE a.c_id WHEN '01' THEN a.s_score END ) 语文, 
MAX(CASE a.c_id WHEN '02' THEN a.s_score END ) 数学, 
MAX(CASE a.c_id WHEN '03' THEN a.s_score END ) 英语, 
avg(a.s_score),b.s_name FROM Score a JOIN Student b ON a.s_id=b.s_id GROUP BY a.s_id ORDER BY 5 DESC

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

自己写的
SELECT
	t.c_id AS 课程ID,
	MAX(t.s_score) AS 最高分,
	MIN(t.s_score) AS 最低分,
	ROUND(AVG(t.s_score), 2) AS 平均分,
    CONCAT(ROUND((SUM((CASE WHEN t.s_score >= 60 THEN 1 ELSE 0 END)) / COUNT(*)) * 100  , 0), '%') as 及格率,
    CONCAT(ROUND((SUM((CASE WHEN t.s_score >= 70 AND t.s_score < 80 THEN 1 ELSE 0 END)) / COUNT(*)) * 100  , 0), '%') as 中等率,
    CONCAT(ROUND((SUM((CASE WHEN t.s_score >= 80 AND t.s_score < 90  THEN 1 ELSE 0 END)) / COUNT(*)) * 100  , 0), '%') as 优良率,
    CONCAT(ROUND((SUM((CASE WHEN t.s_score >= 90 THEN 1 ELSE 0 END)) / COUNT(*)) * 100  , 0), '%') as 优秀率
FROM
	Score t
GROUP BY
	t.c_id
答案给的
select a.c_id,b.c_name,MAX(s_score),MIN(s_score),ROUND(AVG(s_score),2),
	ROUND(100*(SUM(case when a.s_score>=60 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 及格率,
	ROUND(100*(SUM(case when a.s_score>=70 and a.s_score<=80 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 中等率,
	ROUND(100*(SUM(case when a.s_score>=80 and a.s_score<=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优良率,
	ROUND(100*(SUM(case when a.s_score>=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优秀率
from Score a left join Course b on a.c_id = b.c_id GROUP BY a.c_id,b.c_name