use pubs
GO

if exists( select * from sysobjects where name = 'SC' )
drop table SC

if exists( select * from sysobjects where name = 'S' )
drop table S

if exists( select * from sysobjects where name = 'C' )
drop table C
GO

-------------
create table S--学生信息表
(
S# varchar(5) not null,--学号
SN varchar(10),--姓名
SD varchar(10),--单位
SA int--年龄
)
GO

create table C--课程
(
C# varchar(5) not null,--课程编号
CN varchar(10)--课程名
)
GO

create table SC--选课
(
S# varchar(5),--学号
C# varchar(5),--课程编号
G float--成绩
)
GO

alter table S add constraint SPK_S# primary key(S#)
alter table C add constraint CPK_C# primary key(C#)
alter table SC add constraint SCFK_S# foreign key(S#) references S(S#)
alter table SC add constraint SCFK_C# foreign key(C#) references C(C#)
GO

insert into S
select 's001', '张三', '长沙', 22 union
select 's002', '李四', '岳阳', 19 union
select 's003', '王五', '衡阳', 21 union
select 's004', '赵六', '湘潭', 22 union
select 's005', '阿飞', '洙洲', 20 union
select 's006', 'KO', '怀化', 18
GO

insert into C
select 'C1', '语文' union
select 'C2', '数学' union
select 'C3', '英语' union
select 'C4', '税收基础' union
select 'C5', 'JAVA' union
select 'C6', 'C语言' union
select 'C7', 'SQL'
GO

insert into SC
select 's003', 'C1', 98 union
select 's002', 'C1', 56 union
select 's006', 'C1', 78 union

select 's003', 'C2', 76 union
select 's004', 'C2', 52 union
select 's005', 'C2', 65 union

select 's006', 'C3', 78 union
select 's003', 'C3', 68 union
select 's001', 'C3', 71 union

select 's002', 'C4', 83 union
select 's003', 'C4', 92 union
select 's004', 'C4', 85 union

select 's001', 'C5', 70 union
select 's003', 'C5', 63 union
select 's005', 'C5', 55 union

select 's001', 'C6', 95 union
select 's003', 'C6', 63 union
select 's004', 'C6', 69 union

select 's003', 'C7', 89 union
select 's005', 'C7', 85 union
select 's006', 'C7', 78
GO

---------------------
--4> 使用嵌套语句查询选修全部课程的学员姓名和所属单位

--6> 查询选修课程超过了5门的学员学号和所属单位

这2个问题我写的太复杂了.....
劳驾前辈们了

declare @i int
select @i = count(*) from c

select a.sn,a.sd from s as a,(
select s# , count(*) as totc
from sc
group by s#
having count(*) = @i) as b
where a.s#=b.s#

sn sd
---------- ----------
王五 衡阳

（所影响的行数为 1 行）

select a.sn,a.sd from s as a,(
select s# , count(*) as totc
from sc
group by s#
having count(*) > 5) as b
where a.s#=b.s#

sn sd
---------- ----------
王五 衡阳

（所影响的行数为 1 行）

谢谢...
潇洒老乌龟兄..好面熟啊
你是不是昨天晚上11点还在csdn上 帮我解决了1道查询题目的那位?


修改了下

select SN, SD from S where S# in( select S# from SC group by S# having count(*) = 7 )



select SN, SD from S where S# in( select S# from SC group by S# having count(*) >=5 )

[此贴子已经被作者于2006-10-4 14:28:58编辑过]

select SN, SD from S where S# in( select S# from SC group by S# having count(*) = 7 )
你如何知道科目总数为7?

--6> 查询选修课程超过了5门的学员学号和所属单位
>=5?

你如何知道科目总数为7?

乌龟兄..还是你细心
select SN, SD from S where S# in(
select S# from SC group by S# having count(*) = (
select count(*) from C
)
)