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.查询选修了课程的学员人数

1和2怎么用SQL语句写？

Answer 1

你写的不太明确~但我知道你什么意思了~我假设你有3个课程

第一题
select 学员姓名=SN,
课程1=sum(case when C#=1 then CN else end),
课程2=sum(case when C#=2 then CN else end),
课程3=sum(case when C#=3 then CN else end)
from SC inner join S ON SC.S#=S.S#
group by SN

第二题
select 人数=count(S)
from SC
group by S#

Answer 2

select sn,sd where s# in (select s# from sc group by c# having count(c#)=(select count(*) from c))

Answer 3

1.
SELECT SN,SD
FROM S
WHERE S# IN
(SELECT S#,COUNT(C#) FROM SC GROUP BY S#
HAVING COUNT(C#)=(SELECT COUNT(*) FROM C))
2.
SELECT COUNT(1) AS 学员人数
FROM SC
GROUP BY S#

Answer 4

2.select distinct S# from SC

1.
select S.S#,S.SN
(select S#,count(*) zongshu from SC grouop by S#) a,
(select count(*) zongshu from C) b,
S
where a.zongshu=b.zongshu and a.S#=S.S#
order by S.S#