50道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 分数

---

题目

1~10

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

解答：

SELECT * 
FROM (SELECT SId, score from SC where sc.CId = '01') as t1 JOIN (SELECT SId, score from SC where sc.CId = '02') as t2 JOIN Student
ON t1.SId = t2.SId AND t1.SId = Student.SId
WHERE t1.score > t2.score;

结果：

1.1 查询同时存在" 01 “课程和” 02 "课程的情况

解答：

SELECT * 
FROM (SELECT SId, score from SC where sc.CId = '01') as t1 JOIN (SELECT SId, score from SC where sc.CId = '02') as t2
ON t1.SId = t2.SId ;

结果：

1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

解答：

SELECT * 
FROM (SELECT SId, score from SC where sc.CId = '01') as t1 LEFT JOIN (SELECT SId, score from SC where sc.CId = '02') as t2
ON t1.SId = t2.SId ;

结果：

1.3 查询不存在" 01 “课程但存在” 02 "课程的情况

解答：

SELECT *
FROM SC
WHERE CId='02' AND SId NOT IN (SELECT SId FROM SC WHERE CId='01');

结果：

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

解答：

SELECT s.SId, s.Sname, t.av
FROM STUDENT AS s
JOIN
(SELECT SId, AVG(score) av
FROM SC
GROUP BY SId
HAVING AVG(score) >= 60) as t
ON s.SId = t.SId;

结果：

3. 查询在 SC 表存在成绩的学生信息

解答：

SELECT *
FROM STUDENT
WHERE SId IN (SELECT DISTINCT(SId) FROM SC);

结果：

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

解答：

SELECT S.SId, S.Sname, t.counts, t.sums
FROM STUDENT as S
LEFT JOIN 
(SELECT SId, COUNT(CId) as counts, SUM(score) as sums
FROM SC
GROUP BY SId) as t
ON S.SId = t.SId;

结果：

4.1 查有成绩的学生信息

解答：

SELECT *
FROM STUDENT
WHERE SId IN (SELECT DISTINCT(SId) FROM SC);

结果：

5. 查询「李」姓老师的数量

解答：

SELECT COUNT(*)
FROM Teacher
WHERE Tname LIKE "李%";

结果：

6. 查询学过「张三」老师授课的同学的信息

解答：

SELECT * 
FROM STUDENT
WHERE SId IN
(SELECT SC.SId
FROM SC JOIN
	(SELECT C.CId, C.TId 
	FROM Course as C JOIN Teacher
	ON C.TId = Teacher.TId
	WHERE Teacher.Tname = "张三") as a
ON SC.CId = a.CId) ;

结果：

7. 查询没有学全所有课程的同学的信息

解答：

SELECT * 
FROM STUDENT
WHERE SId NOT IN
(SELECT SId
FROM SC
GROUP BY SId
HAVING COUNT(CId) = (SELECT COUNT(CId) FROM Course));

答案：

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

---

11~20

---

21~30

---

31~40

---

41~50