sql语句练习50题(含解析)

大蒜儿ʕ๑•㉨•๑ʔ❀ 2023-04-21 原文

文章目录

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

SELECT
	st.*,
	sc1.s_score 01_score,
	sc2.s_score 02_score
FROM
	student st,
	score sc1,
	score sc2
WHERE
	st.s_id = sc1.s_id
AND sc1.s_id = sc2.s_id
AND sc1.c_id = '01'
AND sc2.c_id = '02'
AND sc1.s_score > sc2.s_score

通过score表自连接后，过滤出每位学生 “01”课程分数和 “02”课程分数的一条记录，最后将01”课程分数大于 “02”课程分数记录筛选出来

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

与第1题思路一样

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

SELECT
	st.s_id,
	s_name,
	avg(sc.s_score)
FROM
	student st,
	score sc
WHERE
	st.s_id = sc.s_id
GROUP BY
	st.s_id,
	st.s_name
HAVING
	avg(sc.s_score) >= 60

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

SELECT
	st.s_id,
	s_name,
	ROUND(avg(sc.s_score),2) avgsc
FROM
	student st LEFT JOIN
	score sc
on
	st.s_id = sc.s_id
GROUP BY
	st.s_id,
	st.s_name
HAVING
	avg(sc.s_score) < 60 or avgsc is null

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

SELECT
	st.s_id,
	st.s_name,
	count(sc.c_id) AS sum_course,
	sum(sc.s_score) AS sum_score
FROM
	student st
LEFT JOIN score sc ON st.s_id = sc.s_id
GROUP BY
	st.s_id,
	st.s_name

此题比较简单，使用分组即可。需要注意下在mysql中使用group by后,select后出现的字段要么是
分组的字段要么是聚集函数不然会报错

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

SELECT
	count(*)
FROM
	teacher
WHERE
	t_name LIKE '李%'

这一题就是考察 mysql中like关键字的使用

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

SELECT
	st.*
FROM
	student st
WHERE
	st.s_id 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 = '张三'
					)
			)
	)

这题我使用的是where型子查询需要使用到in关键字，使用连接查询也可以.根据表之间的连接关系:

首先在teacher表中通过t_name拿到t_id
在course表中通过t_id拿到c_id
在course表中通过c_id拿到s_id
最后在student表中通过s_id拿到学过‘张三’老师授课的学生信息

类似这种子查询都可以用连接查询替代

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

此题只需要把上一题中的in关键改成not in关键字即可

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

SELECT
	*
FROM
	student
WHERE
	s_id IN (
		SELECT
     sc1.s_id
		FROM
			score sc1
		JOIN score sc2 ON sc1.s_id = sc2.s_id
		WHERE
			sc1.c_id = '01'
		AND sc2.c_id = '02'
	)

这一题的方法与第一题类似，第一题搞懂了，这一题就很简单了。直接通过score表进行自连结运算然后筛选出即选修了’01’号课程又选修了’02’号课程的s_id

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

SELECT
	st.* 
FROM
	student st 
WHERE
	st.s_id IN ( SELECT s_id FROM score WHERE c_id = '01' ) 
	AND st.s_id NOT IN ( SELECT s_id FROM score WHERE c_id = '02' )

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

SELECT
	*
FROM
	student
WHERE
	s_id NOT IN (
		SELECT
			s_id
		FROM
			score
		GROUP BY
			s_id
		HAVING
			count(c_id) = (
				SELECT
					count(c_id)
				FROM
					course
			)
	)

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

SELECT DISTINCT
	st.*
FROM
	student st
LEFT JOIN score sc ON st.s_id = sc.s_id
GROUP BY
	s_id,
	c_id
HAVING
	c_id IN (
		SELECT
			c_id
		FROM
			score
		WHERE
			s_id = '01'
	)

SELECT DISTINCT st.* FROM student st LEFT JOIN score sc ON st.s_id=sc.s_id WHERE c_id IN (
SELECT c_id FROM score WHERE s_id='01')

select * from Student where s_id in (SELECT DISTINCT s_id from Score where c_id in (select c_id from Score where s_id='01'))

这题关键在于用其他每个学生所学课程号与"01"学生所学课程的课程号作对比

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

SELECT
	st.*
FROM
	student st
JOIN score sc ON st.s_id = sc.s_id
WHERE
	sc.c_id IN (
		SELECT
			c_id
		FROM
			score
		WHERE
			s_id = '01'
	)
AND st.s_id != '01'
GROUP BY
	st.s_id
HAVING
	count(st.s_id) = (
		SELECT
			count(*)
		FROM
			score
		WHERE
			s_id = '01'
	)

select  Student.* from Student left JOIN Score on Student.s_id = Score.s_id where Score.c_id in (select c_id from Score where s_id='01') and Score.s_id <> '01' GROUP BY Student.s_id HAVING COUNT(*)=(select count(*) from Score where s_id='01')

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

SELECT
	*
FROM
	student
WHERE
	s_id NOT IN (
		SELECT
			st.s_id
		FROM
			student st,
			score sc
		WHERE
			st.s_id = sc.s_id
		AND c_id IN (
			SELECT
				c_id
			FROM
				teacher te
			LEFT JOIN course c ON te.t_id = c.t_id
			WHERE
				t_name = '张三'
		)
	)

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

SELECT
	st.s_id,
	st.s_name,
	round(avg(sc.s_score))
FROM
	student st,
	score sc
WHERE
	st.s_id = sc.s_id
AND s_score < 60
GROUP BY
	s_id
HAVING
	count(*) >= 2

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

SELECT
	st.*,
	sc.c_id,
	sc.s_score
FROM
	student st,
	score sc
WHERE
	st.s_id = sc.s_id
AND s_score < 60
AND c_id = '01'
ORDER BY
	s_score DESC

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

select st.s_id,
(select s_score from score where c_id='01' and s_id=st.s_id) as '语文' ,
(select s_score from score where c_id='02' and s_id=st.s_id) as '数学',
(select s_score from score where c_id='03' and s_id=st.s_id) as  '英语',
round((select avg(s_score) from score where s_id=st.s_id group by s_id),2) as '平均分'
from student st order by  平均分 desc

这一题用到了子查询的另一种方式即创建计算字段，该子查询对检索出的每个student执行一次，在此例中，该查询共执行了8次，因为检索出8名学生，另外当在group by 或 order by后面使用中文时，不要加单引号，否则不生效

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

explain select sc.c_id,c_name,max(s_score),min(s_score),ROUND(avg(s_score),2) '平均分',
ROUND(100*(SUM(case when sc.s_score>=60 then 1 else 0 end)/SUM(case when sc.s_score then 1 else 0 end)),2) as '及格率',
ROUND(100*(SUM(case when sc.s_score>=70 and sc.s_score<80 then 1 else 0 end)/SUM(case when sc.s_score then 1 else 0 end)),2) as '中等率',
ROUND(100*(SUM(case when sc.s_score>=80 and sc.s_score<90 then 1 else 0 end)/SUM(case when sc.s_score then 1 else 0 end)),2) as '优良率',
ROUND(100*(SUM(case when sc.s_score>=90 then 1 else 0 end)/SUM(case when sc.s_score then 1 else 0 end)),2) as '优秀率'
from course c,score sc where c.c_id=sc.c_id group by sc.c_id

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

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

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

SELECT c.t_id,t.t_name,s.c_id,ROUND(AVG(s_score),2) avgsc FROM teacher t,course c,score s WHERE t.t_id=c.t_id AND c.c_id=s.c_id GROUP BY s.c_id,c.t_id,t.t_name ORDER BY avgsc DESC

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

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

24、查询学生平均成绩及其名次

25、查询各科成绩前三名的记录

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

select c_id ,count(*) from score group by c_id

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

select st.s_id,st.s_name,count(*) '课程数' from student st left join score sc on st.s_id=sc.s_id group by 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)

28、查询男生、女生人数

select s_sex,(select count(s_sex) from student st2 where s_sex = st1.s_sex) '人数' from student st1 group by s_sex

select  DISTINCT s_sex,(select count(s_sex) from Student st2 where s_sex = st1.s_sex) '人数' from Student st1

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

 select * from student where s_name like '%风%'

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

select s_name,count(*)-1 '同名人数' from student group by s_name having count(*)>=2

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

select s_name from student where s_birth like '1990%'

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

select c_id, ROUND(avg(s_score),2) '平均成绩' from score group by c_id order by 平均成绩 desc ,c_id

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

select st.s_id,s_name,ROUND(avg(s_score),2) '平均成绩' from student st,score sc where st.s_id=sc.s_id group by st.s_id , s_name having avg(s_score)>=85

select s.s_id,s.s_name,ROUND(AVG(sc.s_score)) '平均分' from Student s LEFT JOIN Score sc ON s.s_id=sc.s_id GROUP BY s.s_id,s.s_name HAVING AVG(sc.s_score)>=85

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

select s_name,s_score from student st,course c,score sc where st.s_id=sc.s_id and sc.c_id=c.c_id and c_name='数学' and s_score<60

select  distinct s_name,s_score from Student s LEFT JOIN Score sc on s.s_id=sc.s_id LEFT JOIN Course c on c.c_id=sc.c_id where c_name='数学' and s_score<60

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

select st.s_id,s_name,c_name,s_score from student st,course c,score sc where st.s_id=sc.s_id and sc.c_id=c.c_id

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数

select s_name,c_name,s_score from student st,course c,score sc where st.s_id = sc.s_id and c.c_id =sc.c_id and s_score>70

37、查询不及格的课程

select st.*,c_name,s_score from student st,score sc,course c where st.s_id=sc.s_id and sc.c_id=c.c_id and s_score<60

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

select st.s_id,st.s_name from student st,score sc where st.s_id=sc.s_id and c_id='01' and s_score>=80

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

select c.c_name '课程名', count(*) '人数' from Course c,Score s where c.c_id=s.c_id GROUP BY s.c_id,c.c_name

select c_name '课程名' ,(select count(*) from Score s1 where c_id=c.c_id) '人数' from Course c

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

select st.*,s_score from student st,score sc where st.s_id=sc.s_id and c_id=(select c_id from teacher te left join  course c on te.t_id=c.t_id where t_name='张三') order by s_score desc limit 1

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

select distinct s1.* FROM score s1 ,score s2 where s1.c_id !=s2.c_id and s1.s_score=s2.s_score

42、查询每门功成绩最好的前两名(偏难)

select sc1.s_id,sc1.c_id,sc1.s_score from score sc1
		where (select COUNT(1) from score sc2 where sc2.c_id=sc1.c_id and sc2.s_score>=sc1.s_score)<=2 ORDER BY sc1.c_id

首先，select count(1)表示查询出表中符合条件的行数；
sc2.c_id=sc1.c_id and sc2.s_score>=sc1.s_score表示查询条件；
select COUNT(1) from score sc2 where sc2.c_id=sc1.c_id and sc2.s_score>=sc1.s_score总体的意思就是从表sc2中查询出满足sc2.c_id=sc1.c_id and sc2.s_score>=sc1.s_score条件的行数；
结合完整的sql语句来看，这个查询出的行数要<=2,所以"行数<=2"是作为前一个查询语句的查询条件的。
这样这个语句简单点理解就是：从sc1表查询sc1.s_id,sc1.c_id,sc1.s_score这三列，查询条件是"行数<=2"。

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

select c_id,count(*) '选修人数' from score group by c_id having count(*)>5 order by 选修人数 desc ,c_id

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

select s_id from score group by s_id having count(*)>=2

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

select st.* from student st,score sc where st.s_id=sc.s_id  group by sc.s_id having count(*) = (select count(*) from course)

练习 sql span class token 数据库 mysql

有关sql语句练习50题(含解析)的更多相关文章

Ruby 解析字符串 - 2
我有一个字符串input="maybe(thisis|thatwas)some((nice|ugly)(day|night)|(strange(weather|time)))"Ruby中解析该字符串的最佳方法是什么？我的意思是脚本应该能够像这样构建句子:maybethisissomeuglynightmaybethatwassomenicenightmaybethiswassomestrangetime等等，你明白了......我应该一个字符一个字符地读取字符串并构建一个带有堆栈的状态机来存储括号值以供以后计算，还是有更好的方法？也许为此目的准备了一个开箱即用的库？
ruby - 解析 RDFa、微数据等的最佳方式是什么，使用统一的模式/词汇(例如 schema.org)存储和显示信息 - 2
我主要使用Ruby来执行此操作，但到目前为止我的攻击计划如下:使用gemsrdf、rdf-rdfa和rdf-microdata或mida来解析给定任何URI的数据。我认为最好映射到像schema.org这样的统一模式，例如使用这个yaml文件，它试图描述数据词汇表和opengraph到schema.org之间的转换:#SchemaXtoschema.orgconversion#data-vocabularyDV:name:namestreet-address:streetAddressregion:addressRegionlocality:addressLocalityphoto:i
ruby - 用逗号、双引号和编码解析 csv - 2
我正在使用ruby1.9解析以下带有MacRoman字符的csv文件#encoding:ISO-8859-1#csv_parse.csvName,main-dialogue"Marceu","Giveittohimóhe,hiswife."我做了以下解析。require'csv'input_string=File.read("../csv_parse.rb").force_encoding("ISO-8859-1").encode("UTF-8")#=>"Name,main-dialogue\r\n\"Marceu\",\"Giveittohim\x97he,hiswife.\"\
ruby-on-rails - 我更新了 ruby gems，现在到处都收到解析树错误和弃用警告! - 2
简而言之错误:NOTE:Gem::SourceIndex#add_specisdeprecated,useSpecification.add_spec.Itwillberemovedonorafter2011-11-01.Gem::SourceIndex#add_speccalledfrom/opt/local/lib/ruby/site_ruby/1.8/rubygems/source_index.rb:91./opt/local/lib/ruby/gems/1.8/gems/rails-2.3.8/lib/rails/gem_dependency.rb:275:in`==':und
ruby - 如何在 Ruby 中向现有方法定义添加语句 - 2
我注意到类定义，如果我打开classMyClass，并在不覆盖的情况下添加一些东西我仍然得到了之前定义的原始方法。添加的新语句扩充了现有语句。但是对于方法定义，我仍然想要与类定义相同的行为，但是当我打开defmy_method时似乎,def中的现有语句和end被覆盖了，我需要重写一遍。那么有什么方法可以使方法定义的行为与定义相同，类似于super，但不一定是子类？最佳答案我想您正在寻找alias_method:classAalias_method:old_func,:funcdeffuncold_func#similartoca
Hive SQL 五大经典面试题 - 2
目录第1题连续问题分析：解法：第2题分组问题分析：解法：第3题间隔连续问题分析：解法：第4题打折日期交叉问题分析：解法：第5题同时在线问题分析：解法：第1题连续问题如下数据为蚂蚁森林中用户领取的减少碳排放量iddtlowcarbon10012021-12-1212310022021-12-124510012021-12-134310012021-12-134510012021-12-132310022021-12-144510012021-12-1423010022021-12-154510012021-12-1523.......找出连续3天及以上减少碳排放量在100以上的用户分析：遇到这类
牛客网专项练习30天Pytnon篇第02天 - 2
1.在Python3中，下列关于数学运算结果正确的是:（B）a=10b=3print(a//b)print(a%b)print(a/b)A.3，3，3.3333...B.3，1，3.3333...C.3.3333...，3.3333...，3D.3.3333...，1，3.3333...解析：在Python中，//表示地板除（向下取整），%表示取余，/表示除（Python2向下取整返回3）2.如下程序Python2会打印多少个数:（D）k=1000whilek>1: print(k)k=k/2A.1000 B.10C.11D.9解析：按照题意每次循环K/2，直到K值小于等
sql - 查询忽略时间戳日期的时间范围 - 2
我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。最佳答案您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时
ruby - 用 YAML.load 解析 json 安全吗？ - 2
我正在使用ruby2.1.0我有一个json文件。例如:test.json{"item":[{"apple":1},{"banana":2}]}用YAML.load加载这个文件安全吗？YAML.load(File.read('test.json'))我正在尝试加载一个json或yaml格式的文件。最佳答案 YAML可以加载JSONYAML.load('{"something":"test","other":4}')=>{"something"=>"test","other"=>4}JSON将无法加载YAML。JSON.load("
ruby - ruby 乘法语句中星号中断语法前的空格 - 2
在添加一些空格以使代码更具可读性时(与上面的代码对齐)，我遇到了这个:classCdefx42endendm=C.new现在这将给出“错误数量的参数”:m.x*m.x这将给出“语法错误，意外的tSTAR，期待$end”:2/m.x*m.x这里的解析器到底发生了什么？我使用Ruby1.9.2和2.1.5进行了测试。最佳答案 *用于运算符(42*42)和参数解包(myfun*[42,42])。当你这样做时:m.x*m.x2/m.x*m.xRuby将此解释为参数解包，而不是*运算符(即乘法)。如果您不熟悉它，参数解包(有时也称为“spl