七:
select Rm_num,sex,count(Acct_num) as renshu into #linshi from Account group by Rm_num
select * from #linshi where Rm_num>3 and sex=0
select Rm_num,sex,count(Acct_num) as renshu into #linshi from Account group by Rm_num
select * from #linshi where Rm_num>3 and sex=0
from Account
group by Rm_num
having count(*)>3八.请发SQL 语句获取在2003-06-01 到 2004-06-01
一年期间缴费超过5000元的的学生姓名,学生帐号、缴费总金额,一个学生一条记录。select b.Acct_num,b.Acct_nm,sum(a.Trn_amt) as Trn_amt
from Transact a join Account b
on a.Acct_num=b.Acct_num
where Trn_dt between '2003-06-01' and '2004-06-01'
group by b.Acct_num,b.Acct_nm
having sum(a.Trn_amt)>5000十.请发SQL 检查是否有男女生同住一个宿舍的情况发生。
如果有列出宿舍号码.select distinct Rm_num
from Account a
where exists(select 1 from Account where Rm_num=a.Rm_num and sex=1)
and exists(select 1 from Account where Rm_num=a.Rm_num and sex=0)
如果有列出宿舍号码.select distinct a.Rm_num
from Account a join Account b
on a.Rm_num=b.Rm_num
where a.sex=1 and b.sex=0