select top1 mods.D# from somd inner join mods on smod.M#=mods.M# inner join enrl on smod.S#=enrl.S# where smod.AYR=2001/02 and enrl.status='E'
1:--第一問 select top 1 Dname from ( select Dname count(M#) as count from dept A,mods B, enrl C smod D where A.D# =B.D# and C.S#=D.S# and B.M#=D.M# and (C.AYR='2001' or C.AYR='2002') group by S# ) order by count desc --第二問 select top 1 Dname from ( select Dname count(M#) as count from dept A,mods B, enrl C smod D where A.D# =B.D# and C.S#=D.S# and B.M#=D.M# and (C.AYR='2001' or C.AYR='2002') and C.Status='E' group by S# ) order by count desc
2: select A.S#,max(A.Firstnames) as Firstnames ,sum(C.Credits) as TotalCredits from stud A , enrl B, mods C smod D where A.S#=B.S# and A.S#=D.S# and C.M#=D.M# and B.Status='E' and (B.AYR='1998' or B.AYR='1999') group by S# having sum(C.Credits)<120
4: --第一問 select top 1 Dname from ( select max(Dname) as Dname, count(S#) as [count] from dept A,mods B, enrl C smod D where A.D# =B.D# and C.S#=D.S# and B.M#=D.M# and (C.AYR='2001' or C.AYR='2002') and C.Status='E' group by A.D# ) order by [count] desc --第二問 select F.Dname from ( select top 1 D#,Dname from ( select max(Dname) as Dname,D#,count(S#) as [count] from dept A,mods B, enrl C smod D where A.D# =B.D# and C.S#=D.S# and B.M#=D.M# and (C.AYR='2001' or C.AYR='2002') and C.Status='E' group by A.D# ) E order by [count] desc )F , user G where F.D#=G.D#
這個有一錯。 1:--第一問 select top 1 Dname from ( select max(Dname) as Dname, count(M#) as count from dept A,mods B, enrl C smod D where A.D# =B.D# and C.S#=D.S# and B.M#=D.M# and (C.AYR='2001' or C.AYR='2002') group by S# ) E order by count desc --第二問 select top 1 Dname from ( select max(Dname) as Dname, count(M#) as count from dept A,mods B, enrl C smod D where A.D# =B.D# and C.S#=D.S# and B.M#=D.M# and (C.AYR='2001' or C.AYR='2002') and C.Status='E' group by S# ) E order by count desc 這個是有錯啊 我想發出去說的。但是不允許發啊。因為我的次數超過了三次。 實在是不好意思哦
smod.S#=enrl.S# where smod.AYR=2001/02 and enrl.status='E'
select top 1 Dname
from
(
select Dname count(M#) as count from dept A,mods B, enrl C smod D
where A.D# =B.D# and C.S#=D.S# and B.M#=D.M# and (C.AYR='2001' or C.AYR='2002')
group by S#
)
order by count desc
--第二問
select top 1 Dname
from
(
select Dname count(M#) as count from dept A,mods B, enrl C smod D
where A.D# =B.D# and C.S#=D.S# and B.M#=D.M# and (C.AYR='2001' or C.AYR='2002')
and C.Status='E'
group by S#
)
order by count desc
select A.S#,max(A.Firstnames) as Firstnames ,sum(C.Credits) as TotalCredits from stud A ,
enrl B, mods C smod D
where A.S#=B.S# and A.S#=D.S# and C.M#=D.M# and
B.Status='E' and (B.AYR='1998' or B.AYR='1999')
group by S#
having sum(C.Credits)<120
--第一問
select top 1 Dname
from
(
select max(Dname) as Dname, count(S#) as [count] from dept A,mods B, enrl C smod D
where A.D# =B.D# and C.S#=D.S# and B.M#=D.M# and (C.AYR='2001' or C.AYR='2002')
and C.Status='E'
group by A.D#
)
order by [count] desc
--第二問
select F.Dname from
(
select top 1 D#,Dname
from
(
select max(Dname) as Dname,D#,count(S#) as [count] from dept A,mods B, enrl C smod D
where A.D# =B.D# and C.S#=D.S# and B.M#=D.M# and (C.AYR='2001' or C.AYR='2002')
and C.Status='E'
group by A.D#
) E
order by [count] desc
)F , user G
where F.D#=G.D#
4.列出2001/02学年,所有Status为E的学生最多的那个系的名称。并且该系是否有最多的员工?如果不是,那么列出员工最多的那个系的名称。
1:--第一問
select top 1 Dname
from
(
select max(Dname) as Dname, count(M#) as count from dept A,mods B, enrl C smod D
where A.D# =B.D# and C.S#=D.S# and B.M#=D.M# and (C.AYR='2001' or C.AYR='2002')
group by S#
) E
order by count desc
--第二問
select top 1 Dname
from
(
select max(Dname) as Dname, count(M#) as count from dept A,mods B, enrl C smod D
where A.D# =B.D# and C.S#=D.S# and B.M#=D.M# and (C.AYR='2001' or C.AYR='2002')
and C.Status='E'
group by S#
) E
order by count desc
這個是有錯啊
我想發出去說的。但是不允許發啊。因為我的次數超過了三次。
實在是不好意思哦