本题目要求编写SQL语句,
查询选修了张老师所讲授的所有课程的学生。
提示:MSSQLServer 评测SQL语句。
表结构:
请在这里写定义表结构的SQL语句。例如:
-- 学生表
CREATE TABLE stu (
sno char(4) NOT NULL,
sname nvarchar(8) NOT NULL,
sex smallint DEFAULT NULL,
mno char(2) DEFAULT NULL,
birdate datetime DEFAULT NULL,
memo ntext,
PRIMARY KEY (sno)
);
-- 课程表
CREATE TABLE cou (
cno char(4) NOT NULL,
cname nvarchar(30) NOT NULL,
credit smallint DEFAULT NULL,
ptime char(5) DEFAULT NULL,
teacher nvarchar(10) DEFAULT NULL,
PRIMARY KEY ( cno )
);
-- 学生选课成绩表
CREATE TABLE sc (
sno char(4) NOT NULL, -- 学生学号
cno char(4) NOT NULL, -- 课程号
grade decimal(4,1) DEFAULT NULL,
PRIMARY KEY ( sno , cno ),
CONSTRAINT fk_sc_cno FOREIGN KEY ( cno ) REFERENCES cou ( cno ),
CONSTRAINT fk_sc_sno FOREIGN KEY ( sno ) REFERENCES stu ( sno )
)
表样例
请在这里给出上述表结构对应的表样例。例如
stu
表:
cou
表:
sc
表:
输出样例:
请在这里给出输出样例。例如:
思路:
1.首先,我们需要获取"N'张老师'"所教授的所有课程的课程编号。为此,我们使用一个子查询来从"cou"表中获取所有符合条件的课程编号。子查询的语法如下:
SELECT cno FROM cou WHERE teacher = N'张老师'
2.接下来,我们需要获取所有选修了上述课程的学生的学号。为此,我们使用另一个子查询来从"sc"表中获取所有符合条件的学生编号。子查询的语法如下:
SELECT sno FROM sc WHERE cno IN ( SELECT cno FROM cou WHERE teacher = N'张老师' ) GROUP BY sno HAVING COUNT(cno) = ( SELECT COUNT(cno) FROM cou WHERE teacher = N'张老师' )
3.在这个子查询中,我们首先使用IN运算符来筛选出选修了指定课程的所有学生。然后,使用GROUP BY子句将结果按学生编号分组,并使用HAVING子句筛选出选修了与"N'张老师'"所教授课程数量相同的学生。最后,我们使用主查询中的WHERE子句将结果进一步筛选,以获取符合条件的学生的姓名。
select sname
from stu
where sno in (
select sno
from sc
where cno in (
select cno
from cou
where teacher=N'张老师'
)
group by sno
having count(cno)=
(select count(cno)
from cou
where teacher=N'张老师')
)