统计查询和组合查询（SQL SERVER 2008）

一 、实验题目：统计查询和组合查询
二 、实验目的：加深对统计查询的理解，熟练使用聚簇函数
题目六：
药品（编号，名称，价格，厂商）
处方（药品编号，数量，医生编号）
医生 （编号，姓名，科室，职称）
根据上面基本表的信息完成下列查询。
1 统计每种药品的使用数量。
2 统计提供三种以上药品的厂商。
3 统计每个科室“青霉素”用量。
4统计从没开过“秦霉素”的医生信息。

所测试的数据：

INSERT INTO 医生
(编号,姓名,科室,职称)
VALUES
('1001','张三','外科','主刀医师'),
('1002','李四','内科','主任医师'),
('1003','王五','小儿科','主治医师'),
('1004','王二','眼科','主任医师'),
('1005','赵六','妇科','主刀医师'),
('1006','张力','内科','副主任医师'),
('1007','张力','骨科','主刀医师');
select * from 医生

INSERT INTO 药品
(编号,名称,价格,厂商)
VALUES
('2001','板蓝根','28.00','云南制药'),
('2002','益气养血口服液','29.00','北方制药'),
('2003','小儿感冒颗粒','35.00','三九药业'),
('2004','999感冒灵','14.00','三九药业'),
('2005','维C银翘片','25.00','北方制药'),
('2006','珍珠明目滴眼液','19.00','三九药业'),
('2007','青霉素','5.00','北方制药'),
('2008','秦霉素','10.00','北方制药');

select * from 药品

INSERT INTO 处方
(药品编号,数量,医生编号)
VALUES
('2001','2','1002'),
('2001','2','1006'),
('2005','2','1006'),
('2006','2','1004'),
('2002','2','1005'),
('2003','2','1003'),
('2007','3','1002'),
('2007','5','1003'),
('2008','7','1006');
select * from 处方

第一题：

select 药品.名称,sum(数量)'使用数量'
from 药品 left outer join 处方 on (药品.编号 =处方.药品编号 )
group by 药品.名称;

第二题：

select 厂商
from 药品
group by 厂商
having COUNT(编号)>=3;

第三题：

select 医生.科室,sum(处方.数量)'青霉素的用量' 
from 医生,处方,药品
where 医生.编号=处方.医生编号
and 药品.编号=处方.药品编号
and 药品.名称 = '青霉素'
group by 医生.科室

union

select  科室,青霉素的用量='0' from 医生 where 科室 not in (select 医生.科室
from 医生,处方,药品
where 医生.编号=处方.医生编号
and 药品.编号=处方.药品编号
and 药品.名称 = '青霉素'
group by 医生.科室
) 

第四题：

--组合查询：
select 医生.* from 医生
except
select 医生.* from 医生,处方,药品
where 医生.编号=处方.医生编号
and 处方.药品编号=药品.编号
and 药品.名称='秦霉素';

--嵌套查询：
select 医生.* from 医生
where 编号 not in
(select 医生编号 from 处方
 where 药品编号 in
 (select 编号 from 药品
   where 名称='秦霉素'
 )
)