数据库SQL面试题：查询学生张三没有选修的课程号有哪些

发表于2021年7月19日2021年7月19日作者 laozizhu

本文是一道网上的面试题目，查询某位名字叫“张三"的学生，有哪些课程没有选修。要考虑到姓名重复的可能性。

目录导航

背景

我们构造了4个学生，2门课程，其中学生张三，只选修了1门课程。

学生表(student)

学生表S(SNO,SNAME,AGE,SEX)，其属性分别表示学号、学生姓名、年龄、性别；

gbase> create table student(sno varchar(20),sname varchar(20),age int, sex int);
Query OK, 0 rows affected (Elapsed: 00:00:00.12)

gbase> insert into student values ('20210001','张三','18',1);
Query OK, 1 row affected (Elapsed: 00:00:00.07)

gbase> insert into student values ('20210002','李四','19',1);
Query OK, 1 row affected (Elapsed: 00:00:00.05)

gbase> insert into student values ('20210003','王五','18',0);
Query OK, 1 row affected (Elapsed: 00:00:00.06)

gbase> insert into student values ('20210004','赵六','18',0);
Query OK, 1 row affected (Elapsed: 00:00:00.06)

gbase> select * from student;
+----------+--------+------+------+
| sno      | sname  | age  | sex  |
+----------+--------+------+------+
| 20210001 | 张三   |   18 |    1 |
| 20210002 | 李四   |   19 |    1 |
| 20210003 | 王五   |   18 |    0 |
| 20210004 | 赵六   |   18 |    0 |
+----------+--------+------+------+
4 rows in set (Elapsed: 00:00:00.01)

课程表(course)

课程表C(CNO,CNAME,TEACHER)，其属性分别表示课程号、课程名、上课教师名；

gbase> create table course(cno  varchar(10),cname varchar(20),teacher varchar(20));
Query OK, 0 rows affected (Elapsed: 00:00:00.10)

gbase> insert into course values('990001','大数','张老师');
Query OK, 1 row affected (Elapsed: 00:00:00.07)

gbase> insert into course values('990002','大英','李老师');
Query OK, 1 row affected (Elapsed: 00:00:00.04)

gbase> select * from course;
+--------+--------+-----------+
| cno    | cname  | teacher   |
+--------+--------+-----------+
| 990001 | 大数   | 张老师    |
| 990002 | 大英   | 李老师    |
+--------+--------+-----------+
2 rows in set (Elapsed: 00:00:00.01)

选修表(student_course)

选修表SC(SNO,CNO,GRADE)，其属性分别表示学号、课程号、成绩。

gbase> create table student_course(sno varchar(20),cno varchar(10),grade int);
Query OK, 0 rows affected (Elapsed: 00:00:00.11)

gbase> insert into student_course values ('20210001','990001',1);
Query OK, 1 row affected (Elapsed: 00:00:00.07)

gbase> insert into student_course values ('20210002','990001',1);
Query OK, 1 row affected (Elapsed: 00:00:00.05)

gbase> insert into student_course values ('20210003','990001',1);
Query OK, 1 row affected (Elapsed: 00:00:00.05)

gbase> insert into student_course values ('20210004','990001',1);
Query OK, 1 row affected (Elapsed: 00:00:00.07)

gbase> insert into student_course values ('20210002','990002',1);
Query OK, 1 row affected (Elapsed: 00:00:00.08)

gbase> insert into student_course values ('20210003','990002',1);
Query OK, 1 row affected (Elapsed: 00:00:00.04)

gbase> insert into student_course values ('20210004','990002',1);
Query OK, 1 row affected (Elapsed: 00:00:00.05)

gbase> select * from student_course;
+----------+--------+-------+
| sno      | cno    | grade |
+----------+--------+-------+
| 20210001 | 990001 |     1 |
| 20210002 | 990001 |     1 |
| 20210003 | 990001 |     1 |
| 20210004 | 990001 |     1 |
| 20210002 | 990002 |     1 |
| 20210003 | 990002 |     1 |
| 20210004 | 990002 |     1 |
+----------+--------+-------+
7 rows in set (Elapsed: 00:00:00.01)

查询张三同学没有选修的课程的课程号

分析

张三没有选修 = 所有课程 - 张三已经选修的。

SQL实现

张三选修的课程

通过名字，从学生表(student)，可以查询到其学生编号，再从学生编号，从选修课程表里，获得其已经选修的课程列表。本处使用了一个子查询。

gbase> select * from student_course where sno=(select sno from student where sname='张三');
+----------+--------+-------+
| sno      | cno    | grade |
+----------+--------+-------+
| 20210001 | 990001 |     1 |
+----------+--------+-------+
1 row in set (Elapsed: 00:00:00.05)

张三没有选修的课程

通过课程表，查询 cno not in 已经选修的课程信息，拿到没有选修的课程。

gbase> select * from course where cno not in (select cno from student_course where sno=(select sno from student where sname='张三'));
+--------+--------+-----------+
| cno    | cname  | teacher   |
+--------+--------+-----------+
| 990002 | 大英   | 李老师    |
+--------+--------+-----------+
1 row in set (Elapsed: 00:00:00.05)

gbase> select cno from course where cno not in (select cno from student_course where sno=(select sno from student where sname='张三'));
+--------+
| cno    |
+--------+
| 990002 |
+--------+
1 row in set (Elapsed: 00:00:00.04)

如果张三存在重名呢？

数据变动

我们增加了一个学生，也叫张三，其cno=20210099, 同时该学生和前一个张三不同：选修了第一个课程，而没有选择第二个课程。

gbase> insert into student values ('20210099','张三','18',0);
Query OK, 1 row affected (Elapsed: 00:00:00.07)

gbase> insert into student_course values ('20210099','990002',1);
Query OK, 1 row affected (Elapsed: 00:00:00.05)

gbase> select * from student;
+----------+--------+------+------+
| sno      | sname  | age  | sex  |
+----------+--------+------+------+
| 20210001 | 张三   |   18 |    1 |
| 20210002 | 李四   |   19 |    1 |
| 20210003 | 王五   |   18 |    0 |
| 20210004 | 赵六   |   18 |    0 |
| 20210099 | 张三   |   18 |    0 |
+----------+--------+------+------+
5 rows in set (Elapsed: 00:00:00.00)

gbase> select * from course;
+--------+--------+-----------+
| cno    | cname  | teacher   |
+--------+--------+-----------+
| 990001 | 大数   | 张老师    |
| 990002 | 大英   | 李老师    |
+--------+--------+-----------+
2 rows in set (Elapsed: 00:00:00.01)

gbase> select * from student_course;
+----------+--------+-------+
| sno      | cno    | grade |
+----------+--------+-------+
| 20210001 | 990001 |     1 |
| 20210002 | 990001 |     1 |
| 20210003 | 990001 |     1 |
| 20210004 | 990001 |     1 |
| 20210002 | 990002 |     1 |
| 20210003 | 990002 |     1 |
| 20210004 | 990002 |     1 |
| 20210099 | 990002 |     1 |
+----------+--------+-------+
8 rows in set (Elapsed: 00:00:00.01)

直接查询报错

由于叫张三的有多个，所以前一个实现SQL里面的cno=子查询会因为1对多，而报错。在GBase 8a数据库报错样例如下

gbase> select cno from course where cno not in (select cno from student_course where sno=(select sno from student where sname='张三'));
ERROR 1708 (HY000): [10.0.2.101:5050](GBA-02AD-0005)Failed to query in gnode:
DETAIL: (GBA-01EX-0002) Subquery return more than 1 row
SQL: SELECT /

等于改用in，结果错误

由于2人分别选择了不同的选修，导致结果为空。

gbase> select cno from course where cno not in (select cno from student_course where sno in(select sno from student where sname='张三'));
Empty set (Elapsed: 00:00:00.04)

改进方案

虽然都叫张三，但其学号肯定不同，所以最终结果，必然是根据学号，而不是根据姓名来区分。

拿到张三能选修的课程列表

通过笛卡尔，无条件的join,得到张三能选修的所有课程对照表。其中包含了所有叫张三的人。

gbase> select * from student s,course c where s.sname='张三';
+----------+--------+------+------+--------+--------+-----------+
| sno      | sname  | age  | sex  | cno    | cname  | teacher   |
+----------+--------+------+------+--------+--------+-----------+
| 20210001 | 张三   |   18 |    1 | 990001 | 大数   | 张老师    |
| 20210001 | 张三   |   18 |    1 | 990002 | 大英   | 李老师    |
| 20210099 | 张三   |   18 |    0 | 990001 | 大数   | 张老师    |
| 20210099 | 张三   |   18 |    0 | 990002 | 大英   | 李老师    |
+----------+--------+------+------+--------+--------+-----------+

关联课程表

左关联课程表，可以发现有没有关联上的。

gbase> select * from (select s.sno,c.cno from student s,course c where s.sname='张三')t left join student_course sc on sc.sno=t.sno and sc.cno=t.cno;
+----------+--------+----------+--------+-------+
| sno      | cno    | sno      | cno    | grade |
+----------+--------+----------+--------+-------+
| 20210001 | 990001 | 20210001 | 990001 |     1 |
| 20210099 | 990002 | 20210099 | 990002 |     1 |
| 20210001 | 990002 | NULL     | NULL   |  NULL |
| 20210099 | 990001 | NULL     | NULL   |  NULL |
+----------+--------+----------+--------+-------+
4 rows in set (Elapsed: 00:00:00.04)

张三没有选修的课程

通过过滤课程表里null的数据，就是没有选修的课程了。

gbase> select t.sno,t.cno from (select s.sno,c.cno from student s,course c where s.sname='张三')t left join student_course sc on sc.sno=t.sno and sc.cno=t.cno where sc.sno is null;
+----------+--------+
| sno      | cno    |
+----------+--------+
| 20210001 | 990002 |
| 20210099 | 990001 |
+----------+--------+
2 rows in set (Elapsed: 00:00:00.06)

Post Views: 3,690

2024年11月
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

背景

学生表(student)

课程表(course)

选修表(student_course)

查询张三同学没有选修的课程的课程号

分析

SQL实现

张三选修的课程

张三没有选修的课程

如果张三存在重名呢？

数据变动

直接查询报错

等于改用in，结果错误

改进方案

拿到张三能选修的课程列表

关联课程表

张三没有选修的课程

相关文章: