SQL实验:统计查询和组合查询

1\select 药品编号，sum(数量) as 数量
from 处方
group by 药品编号
2\select 厂商,count(*) as cnt
from 药品
group by 厂商
having count(*)>=3
3\select 医生.科室,sum(处方.数量) as 数量
from 处方,医生
where 处方.医生编号=医生.编号 and
处方.药品编号=青霉素
group by 医生.科室
4\select *
from 医生
where not exists(select * from 处方 where 药品编号=青霉素 and 医生编号=医生.编号）

药品（编号，名称，价格，厂商） medicine（docno,mname,price,maker)
处方（药品编号，数量，医生编号） chufang(docno,num,dono)
医生 （编号，姓名，科室，职称） docter(dono,doname,keshi,zhichen)

1.select m.docno,m.mname from chufang c,medicine m where c.docno=m.docno
2.select make from (select m.maker,sum(m.docno) as sale from medicine m group by m.make)t where t.sale >=3
3.select d.keshi,c.num from docter d left join chufang c on d.dono=c.dono inner join medicine m on c.docno=m.docno where m.mname='青霉素'
4.select d.* from docter d ,(select m.docno from medicine m where m.mname=&