50道SQL练习题及答案与分析（1-10）多种解法

数据表介绍

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

学生表 Student
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');

科目表 Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

教师表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

成绩表 SC
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

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

SELECT Student.SId, Student.Sname, SC.score
FROM SC
INNER JOIN Student ON SC.SId = Student.SId
WHERE SC.CId = '01' AND SC.score > (
  SELECT score FROM SC WHERE SC.CId = '02' AND SC.SId = Student.SId
)

-- SELECT Student.*,SC.*
-- FROM SC
-- INNER JOIN Student ON SC.SId = Student.SId

1.1 查询某个学生同时存在学过" 01 "课程和" 02 "课程的情况

-- 在连表查询的时候就已经做了筛选
SELECT
	*
FROM
	(SELECT * FROM SC WHERE Cid = '01') A
LEFT JOIN (SELECT * FROM SC WHERE Cid = '02') B ON A.Sid = B.Sid
WHERE
	B.Sid IS NOT NULL

-- 子查询做法
SELECT DISTINCT s.SId, s.Sname
FROM Student s
WHERE s.SId IN (
  SELECT sc1.SId
  FROM SC sc1
  WHERE sc1.CId = '01'
)
AND s.SId IN (
  SELECT sc2.SId
  FROM SC sc2
  WHERE sc2.CId = '02'
);

-- SELECT DISTINCT s.SId, s.Sname
-- FROM Student s
-- WHERE s.SId IN (
--   SELECT s1.SId
--   FROM SC sc1 JOIN Course c1 ON c1.CId = sc1.CId
--               JOIN Student s1 ON s1.SId = sc1.SId
--   WHERE c1.CId = '01'
-- )
-- AND s.SId IN (
--   SELECT s2.SId
--   FROM SC sc2 JOIN Course c2 ON c2.CId = sc2.CId
--               JOIN Student s2 ON s2.SId = sc2.SId
--   WHERE c2.CId = '02'
-- );

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

SELECT
	*
FROM
	(SELECT * FROM SC WHERE Cid = '01') A
LEFT JOIN (SELECT * FROM SC WHERE Cid = '02') B ON A.Sid = B.Sid
WHERE
	A.Sid IS NOT NULL


SELECT s.Sid, s.Sname, s.Ssex, sc1.Score AS 'C01 Score', sc2.Score AS 'C02 Score'
FROM Student s
INNER JOIN SC sc1 ON s.Sid = sc1.Sid AND sc1.Cid = '01'
LEFT JOIN SC sc2 ON s.Sid = sc2.Sid AND sc2.Cid = '02'

1.3 查询某个学生不存在" 01 "课程但存在" 02 "课程的情况

select * from SC where Cid='02'and Sid not in(select Sid from SC where Cid='01')


SELECT s.Sid, s.Sname, s.Ssex, sc.Score AS 'C02 Score'
FROM Student s
INNER JOIN SC sc ON s.Sid = sc.Sid AND sc.Cid = '02'
WHERE NOT EXISTS(
    SELECT 1
    FROM SC
    WHERE Sid = s.Sid AND Cid = '01'
)
-- 这条SQL语句先使用INNER JOIN将学生表(Student)和选课表(SC)连接起来，并且根据条件sc.Cid = '02'筛选出选了" 02 "课程的记录。
-- 然后它再通过一个子查询判断哪些学生没有选修" 01 "课程，即在选课表(SC)中不存在对应的记录，
-- 如果满足条件则将其返回。具体来说，子查询中的SELECT语句返回一个常数1，
-- 然后根据条件Sid = s.Sid AND Cid = '01'筛选出所有选了" 01 "课程的记录。
-- 最后使用NOT EXISTS关键字判断当前查询结果中的学生ID是否在该子查询中出现过，
-- 如果没有出现过，则表示该学生不存在" 01 "课程但存在" 02 "课程，符合要求，需要将其返回。
-- 最终返回的结果包括学生ID、姓名、性别以及选修" 02 "课程的成绩。

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

-- 连表查询 
SELECT SC.SId, Student.Sname, AVG(SC.score) AS avg_score
FROM SC
INNER JOIN Student ON SC.SId = Student.SId
GROUP BY SC.SId, Student.Sname
HAVING AVG(SC.score) >= 60

-- 内连接  
select student.SId,sname,ss from student,(
    select SId, AVG(score) as ss from sc  
    GROUP BY SId 
    HAVING AVG(score)> 60
    )r
where student.sid = r.sid;

这种SQL查询语句是使用内连接的一种形式，也被称为等值连接（Equi Join）或自然连接（Natural Join）。

具体来说，这个查询语句中，两张表student和子查询r通过where子句中的连接条件student.sid = r.sid进行连接，
只返回满足条件的行。其中，子查询r返回每个学生的平均分数，并且只返回平均分数大于60分的学生ID。
外部查询会使用这个结果集来与学生表连接（通过学生ID），并且返回每个符合条件的学生的ID、姓名和平均分数。

由于这里的连接条件是基于学生ID相等而实现的，因此这种连接方式通常被称为等值连接。
这个查询语句使用了一种不使用INNER JOIN的内连接方式，也被称为隐式内连接（Implicit Inner Join）。
具体来说，查询语句中在FROM子句中列出两张表student和子查询r，但没有使用INNER JOIN关键字进行连接。
相反，它使用逗号分隔符（,）将两个表名放在FROM子句中，这被称为隐式内连接。
在隐式内连接中，WHERE子句中指定了用于连接两个表的条件，即student.sid = r.sid。
这个条件限制了只有那些在两个表中都存在对应记录的数据才会被返回，所以这个查询实现的是内连接。
值得注意的是，尽管隐式内连接常用于早期版本的SQL，但在现代SQL代码中，通常使用明确的INNER JOIN语法来完成内连接操作。
因为隐式内连接不仅可读性较低，而且容易引起一些错误和歧义。

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

SELECT DISTINCT t2.SId, t2.Sname, t2.Sage, t2.Ssex
FROM SC t1
INNER JOIN
Student t2
ON
t1.SId = t2.SId
WHERE score IS NOT NULL;


select * from Student where Sid in (select distinct Sid from SC)

第一种 SQL 查询使用了 INNER JOIN 操作符，将 SC 表与 Student 表连接起来，
并在 WHERE 子句中过滤掉分数为空的记录。使用 DISTINCT 关键字去除重复记录，
最终返回的结果包含了每个学生的 ID、姓名、年龄和性别。
这种写法可以保证结果集中只有符合条件的记录，并且可以通过优化查询计划来提高查询效率。
但是，需要注意的是，如果 SC 表中存在多个相同的 SId，那么会返回多条记录，这可能不是我们想要的结果。

第二种 SQL 查询使用了子查询，首先在子查询中查询出所有在 SC 表中存在成绩的学生 ID，
然后在主查询中通过 IN 操作符将这些学生的详细信息查询出来。
这种写法比较简单直观，但是可能会存在性能问题，因为子查询需要先执行并返回结果集，
然后再作为主查询的子集查询数据，而且当数据量较大时，IN 操作符的性能也可能会受到影响。

总的来说，第一种 SQL 查询更加高效和稳定，但是可能需要花费一些时间来理解 INNER JOIN 操作符的使用。
而第二种 SQL 查询则比较简单，但是可能会存在一些性能问题。根据具体情况，我们可以选择适合自己的写法。

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

SELECT 
Student.SId, 
Student.Sname, 
COUNT(DISTINCT SC.CId) AS course_count,
SUM(SC.score) AS total_score
FROM Student
LEFT JOIN SC ON Student.SId = SC.SId
GROUP BY 
Student.SId, 
Student.Sname


-- select B.Sid,B.Sname,A.选课总数,A.总成绩 from
-- (select Sid,COUNT(Cid)选课总数,sum(score)总成绩 from SC group by Sid)A
-- right join Student B on A.Sid=B.Sid

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

---子查询
SELECT * from student WHERE sid in (SELECT sid from sc WHERE Cid in (SELECT Cid FROM course WHERE Tid in (
SELECT Tid FROM teacher WHERE Tname = '张三')))

---连表查询
SELECT * from student LEFT JOIN
sc on student.sid = sc.sid
LEFT JOIN course
ON sc.cid = course.cid
LEFT JOIN teacher
ON course.tid = teacher.tid
WHERE Tname = '张三'

6 查询没有学全所有课程的同学的信息(在有成绩名单里，不包括所有学生)

select * from Student where Sid in
(select Sid from SC group by Sid 
having COUNT(Cid)<(select count(cid) from course))

7 查询没有学全所有课程的同学的信息(包括所有学生)

select * from student
where student.sid not in (
  select sc.sid from sc
  group by sc.sid
  having count(sc.cid)= (select count(cid) from course)
);

7.1 查询学了所有课程的学生信息

SELECT * from student JOIN (SELECT Sid,COUNT(Cid) as count from (SELECT sc.Cid,sc.Sid,sc.score from sc 
LEFT JOIN 
(SELECT Cid FROM course) t
ON sc.Cid = t.Cid) as test
GROUP BY Sid
HAVING COUNT(Cid) = (SELECT count(Cid) FROM course)) AS qs
ON student.sid = qs.sid

SELECT * FROM Student
WHERE NOT EXISTS (
  SELECT CId
  FROM Course
  WHERE NOT EXISTS (
    SELECT *
    FROM SC
    WHERE Student.SId = SC.SId AND Course.CId = SC.CId
  )
);

第一个查询使用了 "LEFT JOIN" 和子查询的组合来查找已选课程数等于总课程数的学生信息。
然后，使用 "WHERE" 子句过滤掉已学习所有课程的学生。
这种查询方法适用于数据量较小的情况，因为它涉及到多次表连接和子查询，可能会导致性能下降。

第二个查询使用子查询和 "NOT EXISTS" 操作符来查找未学完所有课程的学生信息。
具体地说，它首先查询课程表中所有的课程，然后检查每个学生是否都选了这些课程，
如果没有，则保留该学生的信息。这种查询方法更加简洁且易于理解，而且执行效率更高。

因此，第二个查询是更好的选择，尤其是在处理大型数据集时，
因为它只需要进行一次子查询，并且使用了 "NOT EXISTS" 操作符，
避免了多余的连接操作。同时，它也更容易优化和调试。

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

SELECT * from student where Sid in 
(SELECT DISTINCT Sid FROM sc where 
cid in (SELECT DISTINCT Cid from sc WHERE Sid = '01') and Sid != '01')

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

SELECT SId,CourseList FROM(SELECT SId, GROUP_CONCAT(CId ORDER BY CId) AS CourseList
FROM SC
WHERE SId != '01'
GROUP BY SId) as t
WHERE CourseList = (SELECT GROUP_CONCAT(Cid SEPARATOR ',') AS CourseList
FROM SC
WHERE Sid = '01')

-- SELECT SId, GROUP_CONCAT(CId) AS CourseList
-- FROM SC
-- WHERE SId != '01'
-- GROUP BY SId

SELECT *
FROM Student S1
WHERE NOT EXISTS (
  SELECT *
  FROM SC
  WHERE SId = '01' AND CId NOT IN (
    SELECT CId
    FROM SC
    WHERE SId = S1.SId
  )
) AND S1.SId <> '01';

--  首先，内部的子查询 SELECT CId FROM SC WHERE SId = S1.SId 返回了跟学号为 01 的学生所选课程完全相同的其他学生所选的所有课程 ID。
-- 然后，外层的 NOT EXISTS 判断当前查询的学生是否有其他课程不在上述集合中，
-- 如果不存在，则表示当前学生与学号为 01 的学生所选的课程完全相同，满足条件，
-- 将该学生的信息返回。最后，加上 S1.SId <> '01' 是为了排除学号为 01 的学生本身。

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

SELECT * FROM student WHERE Sid NOT IN 
(SELECT DISTINCT Sid FROM sc WHERE Cid IN 
(SELECT Cid FROM course WHERE tid = (SELECT Tid FROM teacher WHERE Tname = "张三")))