10-28 查询选修张老师讲授所有课程的学生(MSSQL)

本题目要求编写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'张老师')
)