扫码加入数据分析学习群

单选题：有关系S(SNO,SNAME,SDEPT,SAGE),C(CNO,CNAME)，Sc(SNO,CNO,GRADE)。其中SNO是学生号，SNAME是学生姓名，SDEPT是系别,SAGE是学生年龄,CNO是号,CNAME是课程名称，GRADE是成绩。要查询“计算机”系的没有不及格成绩的学生学号和姓名 （ ）

A.SELECT Sno, Sname FROM S WHERE Sdept='计算机' INTERSECT SELECT Sno, Sname FROM SC WHERE GRADE>=60;

B.SELECT Sno, Sname FROM S WHERE Sdept='计算机' and Sno not in (SELECT SNO FROM SC WHERE GRADE<60);

C.SELECT Sno, Sname FROM S WHERE Sdept='计算机' EXCEPT SELECT SNOFROM SC WHERE GRADE<60;

D.SELECT Sno, Sname FROM S WHERE Sdept='计算机' and SNO in (SELECT SNO FROM SC WHERE GRADE>=60);

答案:选B

解析:

首先提示一点，一个学生会对应多个课程，会存在有的课程及格有的课程不及格,如下表所示:

学号 课程 成绩

学生A 课程1 66

学生A 课程2 55

学生A 课程3 44

其次没有不及格成绩的学生学号和姓名,意味着这个学生的所有课程成绩都要及格,上表中的学生A这类有的课程及格有的课程不及格的学生不符合条件

小伙伴大多疑问D选项，对于SELECT SNO FROM SC WHERE GRADE>=60这个子查询，学生A这类有的课程及格有的课程不及格的学生会出现在子查询结果中，进而最后结果中也会出现学生A这类有的课程及格有的课程不及格的学生，因此D不是正确的答案。

详细探讨：

A. SELECT Sno, Sname FROM S WHERE Sdept='计算机’INTERSECT SELECT Sno, Sname FROM SC WHERE GRADE>=60;

首先 其中SELECT Sno, Sname FROM SC WHERE GRADE>=60错误，Sname 不在SC中，这个子查询无法执行。

其次这个查询语句使用了INTERSECT操作符来获取同时满足两个条件的学生学号和姓名。首先，在第一个子查询中，我们选择S表中Sno和Sname列，限定条件是Sdept为“计算机”。然后，在第二个子查询中，我们选择SC表中Sno和Sname列，限定条件是GRADE大于等于60, 学生A这类有的课程及格有的课程不及格的学生会被查询到,。最后，使用INTERSECT操作符来获取两个子查询的交集。然而，学生A这类有的课程及格有的课程不及格的学生会出现在最后结果中，因此不是正确的答案。

B. SELECT Sno, Sname FROM S WHERE Sdept='计算机' and Sno not in (SELECT SNO FROM SC WHERE GRADE<60);

这个查询语句使用了NOT IN关键字来排除主查询中的学生学号在子查询中出现的情况。首先，在主查询中，我们从S表中选择Sno和Sname列，限定条件是Sdept为“计算机”。然后，在子查询中，我们选择SC表中的SNO列，限定条件是GRADE小于60。最后，使用NOT IN关键字来排除主查询中的学生学号在子查询中出现的情况。这样，只有在S表中的学生学号不在SC表中的不及格成绩学生学号列表中的学生才会被选择。因此，这个查询语句是正确的答案。

C. SELECT Sno, Sname FROM S WHERE Sdept='计算机’ EXCEPT SELECT Sno FROM SC WHERE GRADE<60;

会报措提示：列数不一致 Error Code: 1222. The used SELECT statements have a different number of columns。EXCEPT前后列数不一致，无法执行。改为：SELECT Sno FROM S WHERE Sdept='计算机’ EXCEPT SELECT Sno FROM SC WHERE GRADE<60;可以得到结果，但是结果无学生姓名Sname 。因此不是正确的答案。

D. SELECT Sno, Sname FROM S WHERE Sdept='计算机’ and SNO in (SELECT SNO FROM SC WHERE GRADE>=60);

对于SELECT SNO FROM SC WHERE GRADE>=60这个子查询，学生A这类有的课程及格有的课程不及格的学生会出现在子查询结果中，进而最后结果中也会出现学生A这类有的课程及格有的课程不及格的学生，因此D不是正确的答案

综上所述，B是正确的答案

备注:比较新的 MySQL 8.0.31 版本支持INTERSECT 和 EXCEPT，老版本不支持

以下是我对于此题的SQL建表建数实践，感兴趣的伙伴可以试试。

##建表建数实践：

#创建S表：

#DROP TABLE S;

CREATE TABLE S (

Sno CHAR(10) PRIMARY KEY,

Sname VARCHAR(20) NOT NULL,

Sdept VARCHAR(20) NOT NULL,

Sage INT NOT NULL

);

INSERT INTO S VALUES ('S001', '张三', '计算机', 20);

INSERT INTO S VALUES ('S002', '李四', '数学', 21);

INSERT INTO S VALUES ('S003', '王五', '计算机', 19);

INSERT INTO S VALUES ('S004', '赵六', '物理', 20);

INSERT INTO S VALUES ('S005', '钱七', '计算机', 22);

#创建C表：

#DROP TABLE C;

CREATE TABLE C (

Cno CHAR(10) PRIMARY KEY,

Cname VARCHAR(20) NOT NULL

);

INSERT INTO C VALUES ('C001', '数据库');

INSERT INTO C VALUES ('C002', '数据结构');

INSERT INTO C VALUES ('C003', '计算机组成原理');

INSERT INTO C VALUES ('C004', '操作系统');

INSERT INTO C VALUES ('C005', '计算机网络');

#创建SC表：

#DROP TABLE SC;

CREATE TABLE SC (

Sno CHAR(10) NOT NULL,

Cno CHAR(10) NOT NULL,

Grade INT NOT NULL,

PRIMARY KEY (Sno, Cno),

FOREIGN KEY (Sno) REFERENCES S(Sno),

FOREIGN KEY (Cno) REFERENCES C(Cno)

);

INSERT INTO SC VALUES ('S001', 'C001', 66);

INSERT INTO SC VALUES ('S001', 'C002', 55);

INSERT INTO SC VALUES ('S001', 'C003', 44);

INSERT INTO SC VALUES ('S002', 'C001', 75);

INSERT INTO SC VALUES ('S002', 'C002', 80);

INSERT INTO SC VALUES ('S002', 'C003', 70);

INSERT INTO SC VALUES ('S003', 'C001', 90);

INSERT INTO SC VALUES ('S003', 'C002', 95);

INSERT INTO SC VALUES ('S003', 'C003', 85);

INSERT INTO SC VALUES ('S004', 'C001', 70);

INSERT INTO SC VALUES ('S004', 'C002', 80);

INSERT INTO SC VALUES ('S004', 'C003', 75);

INSERT INTO SC VALUES ('S005', 'C001', 85);

INSERT INTO SC VALUES ('S005', 'C002', 90);

INSERT INTO SC VALUES ('S005', 'C003', 80);

A.SELECT Sno, Sname FROM S WHERE Sdept='计算机' INTERSECT SELECT Sno, Sname FROM SC WHERE GRADE>=60;

B.SELECT Sno, Sname FROM S WHERE Sdept='计算机' and Sno not in (SELECT SNO FROM SC WHERE GRADE<60);

C.SELECT Sno, Sname FROM S WHERE Sdept='计算机' EXCEPT SELECT SNOFROM SC WHERE GRADE<60;

D.SELECT Sno, Sname FROM S WHERE Sdept='计算机' and SNO in (SELECT SNO FROM SC WHERE GRADE>=60);