查询同时选修了c01课程和c02课程的成绩

Question

select cno,degree from sc where sno in(select sno from sc where cno='c01' and sno in (select sno from sc where cno='c02')) 我这个只查询了c01和c02但是结果为什么会有c03的

 

以下是题目数据

create database grademanager
use grademanager
go
create table student
(sno char(10) not null primary key,
sname varchar(8) not null,
ssex char(2) constraint con_ssex check(ssex='男'or ssex='女'),
sbirthday datetime,
sdep char(16),
speciality varchar(20)
);

create table course
(cno char(5) not null primary key,
cname varchar(20) not null,
cpno char(5),
ccredit tinyint,
FOREIGN KEY(cpno) REFERENCES course(cno)
);

create table sc
(sno char(10) not null,
cno char(5) not null,
degree    decimal(5,1) constraint con_degree check(degree between 0 and 100),
primary key(sno,cno),
FOREIGN KEY(sno) REFERENCES student(sno),
FOREIGN KEY(cno) REFERENCES course(cno)
);

create table teacher
(tno char(3) not null primary key,
tname varchar(8) not null,
tsex char(2) constraint con_tsex check(tsex='男' or tsex='女'),
tbirthday datetime,
tdept char(16)
);

create table teaching
(cno char(5) not null,
tno char(3) not null,
cterm tinyint,
primary key(tno,cno),
FOREIGN KEY(cno) REFERENCES course(cno),
FOREIGN KEY(tno) REFERENCES teacher(tno)
);

insert
into student(sno,sname,ssex,sbirthday,sdep,speciality)
values ('20050101','李勇','男','2001-01-12','cs','计算机应用'),
('20050201','刘晨','女','2002-06-04','is','电子商务'),
('20050301','王敏','女','2003-12-23','ma','数学'),
('20050202','张立','男','2003-08-25','is','电子商务');

insert
into course(cno,cname,cpno,ccredit)
values ('C01','数据库',NULL,'4'),
('C02','数学',NULL,'3'),
('C03','信息系统','C01','4'),
('C04','操作系统','C03','3');

insert
into sc(sno,cno,degree)
values ('20050101','C01','92'),
('20050101','C02','85'),
('20050101','C03','88'),
('20050201','C02','90'),
('20050201','C03','80');

insert
into teacher(tno,tname,tsex,tbirthday,tdept)
values ('101','李新','男','1977-01-12','cs'),
('102','钱军','女','1968-06-04','cs'),
('201','王小花','女','1979-12-23','is'),
('202','张小青','男','1968-08-25','is');

insert
into teaching(cno,tno,cterm)
values ('C01','101','2'),
('C02','102','1'),
('C03','201','3'),
('C04','202','4');
 

Answer 1

如果需求是查询  ，只选修了c01课程和c02课程的，那就不是这么写了。