Question

根据题目写出SQL查询语句

管理岗位业务培训信息，建立3个表：
S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄；
C (C#,CN ) C#,CN 分别代表课程编号、课程名称；
SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩。

1. 使用标准SQL嵌套语句查询选修课程名称为‘收基础’的学员学号和姓名；
2. 使用标准SQL嵌套语句查询选修课程编号为‘2’学员姓名和所属单位；
3. 使用标准SQL嵌套语句查询不选修课程编号为‘5’的学员姓名和所属单位；
4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位；
5. 查询选修了课程的学员人数；
6. 查询选修课程超过5门的学员学号和所属单位。

Answer 1

1.
SELECT S#,SN
FROM S
WHERE S# IN
(SELECT S#
FROM SC
WHERE C# IN
(SELECT C#
FROM C
WHERE CN="收基础"))

2.
SELECT SN,SD
FROM S
WHERE S# IN
(SELECT S#
FROM SC
WHERE C#=2)

3.
SELECT SN,SD
FROM S
WHERE S# NOT IN
(SELECT S#
FROM SC
WHERE C#=5)

4.
SELECT SN,SD
FROM S
WHERE S# IN
(SELECT S#
FROM SC
GROUP BY S#
HAVING COUNT(*) =
(SELECT COUNT(*)
FROM C))

5.SELECT COUNT(DISTINCT S#)
FROM SC

6.
SELECT SN,SD
FROM S
WHERE S# IN
(SELECT S#
FROM SC
GROUP BY S#
HAVING COUNT(*)>5)

Answer 2

1.
SELECT S#,SN FROM S WHERE S# IN (SELECT S# FROM SC WHERE C# IN (SELECT C# FROM C WHERE CN="收基础"))

2.
SELECT SN,SD FROM S WHERE S# IN (SELECT S# FROM SC WHERE C#=2)

3.
SELECT SN,SD FROM S WHERE S# NOT IN (SELECT S# FROM SC WHERE C#=5)

4.
暂时还没弄出来

5.
SELECT SN,SD FROM S WHERE S# EXISTS (SELECT * FROM SC WHERE S.S#=SC.S#)

6.
SELECT SN,SD FROM S WHERE S# IN (SELECT S# FROM SC GROUP BY S# HAVING COUNT(*)>5)