经常考人的sql题
S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
C (C#,CN ) C#,CN 分别代表课程编号、课程名称
SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩
1. 使用标准SQL嵌套语句查询选修课程名称为’数学’的学员学号和姓名
select [s#],sn from s where s.[s#] in (select [s#] from sc,c where sc.[c#]=c.[c#] and c.cn='数学');
2. 使用标准SQL嵌套语句查询选修课程编号为4的学员姓名和所属单位
SELECT sn, sd
FROM s, SC
WHERE sc.[s#]=s.[s#] and sc.[c#] = 4;
3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
select sn,sd from s,sc where sc.[s#]=s.[s#] and sc.[c#]<>’c5’
4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
select sn,sd from s a where (not exists (select * from c b where not exists (select * from sc c where a.[s#]= c.[s#] and c.[c#]=b.[c#])))
5. 查询选修了课程的学员人数
select count(t.[s#]) from (select [s#] from sc group by [s#]) t
6. 查询选修课程超过5门的学员学号和所属
select sn,sd from s,sc where (s.[s#]=sc.[s#]) and (sc.[s#] in (select sc.[s#] from sc group by sc.[s#] having count(*) >2)) group by s.sn,s.sd;