SQL查询语句练习(40道)
素材题目来源
50道SQL练习题及答案与详细分析 偶尔在网上看到了博主在挑战自己的博客,我想着自己也挑战一下自己,毕竟上学期才学完数据库,现在应该也是忘得差不多了,这里记录一下,如有记忆有点混乱的就做一下笔记,没有问题的就不去添加说明了(大胆的想自己已经是掌握好了的)。部分的比较偏一点的(之前数据库课本上没有讲到的就用了他的答案),这个练习的是链接博主的前40道,因为后面的50道题目是使用函数的就没有必要的,关键的是锻炼自己的思维能力。
题目一
- 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
使用exist语句直接查询,其实它可以和in语句相互替换
SELECT s1.Sid,Sname,Sage,Ssex,score
FROM student s1,sc sc1
WHERE s1.SId = sc1.SId AND sc1.CId='01' AND
EXISTS
(
SELECT *
FROM sc sc2
WHERE sc2.SId=s1.SId AND sc2.CId='02' AND sc2.score < sc1.score
)
1.1 查询同时存在" 01 “课程和” 02 "课程的成绩情况
SELECT t1.SId,t1.CId AS '课程1',t1.score,t2.CId AS '课程2',t2.score FROM
(SELECT * FROM sc WHERE sc.CId = '01') AS t1,
(SELECT * FROM sc WHERE sc.CId = '02') AS t2
WHERE t1.SId = t2.SId
1.2查询存在" 01 “课程但可能不存在” 02 "课程的成绩情况(不存在时显示为 null )
SELECT * FROM
(SELECT * FROM sc where CId='01') as s1
LEFT JOIN
(SELECT * FROM sc WHERE CId='02') as s2
ON s1.SId = s2.SId
1.3查询不存在" 01 “课程但存在” 02 "课程的成绩情况
SELECT * FROM sc WHERE CId='02'
AND SId NOT IN
(
SELECT SId FROM sc WHERE CId='01'
)
- 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT student.SId,sname,AVG(score)
FROM student,sc
WHERE student.SId=sc.SId
GROUP BY student.SId
HAVING AVG(score)>='60'
- 查询在 SC 表存在成绩的学生信息
SELECT DISTINCT student.* FROM student,sc WHERE student.SId=sc.SId
- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
SELECT student.SId, Sname,COUNT(*) AS '课程总数',SUM(score) AS '总成绩'
FROM student,sc
WHERE student.SId=sc.SId
GROUP BY student.SId
4.1 查有成绩的学生信息
SELECT * FROM student
WHERE Sid IN
(
SELECT SId FROM sc
)
- 查询「李」姓老师的数量
SELECT COUNT(*) FROM teacher WHERE Tname LIKE '李%'
- 查询学过「张三」老师授课的同学的信息
SELECT student.* FROM student,sc WHERE
student.SId=sc.SId AND CId IN
(