单表查询（四）-分组操作符与聚集函数

第1关：数据统计（初级）

编程要求

我们已经为你建好了数据库与数据表，并添加了相应的数据内容。 你只需根据右侧提示，完成以下任务：

• 1.补全右侧代码片段中 第一题 下 Begin-End 区间的代码，统计 course表中学分数（credit）大于2的课程门数；

• 2.补全右侧代码片段中 第二题 下 Begin-End 区间的代码，统计所有专业必修课（BT开头的课程代码）的学分总数。

• 3.补全右侧代码片段中 第三题 下 Begin-End 区间的代码，按课程类别统计每个类别课程的门数，如课程代码BT001，BT002都是专业必修课。

本题使用的关系如下： course(cno,cname,credit) 对应课程代码，课程名称，学分，其中课程代码前2位代表不同类型的课程，如 BT 代表 专业必修课，XZ 代表专业限选课。

USE test_wyy_db_guet
GO

SET NOCOUNT ON

 
-- ********** Begin ********** --
-- ********** 此处写第一题的SQL语句 ********** --
select count(credit)
from course
where credit>2;


-- ********** End ********** --

GO
 
-- ********** Begin ********** --
-- ********** 此处写第二题的SQL语句 ********** --
select sum(credit)
from course
where cno like 'BT%';
-- ********** End ********** --

GO

 
-- ********** Begin ********** --
-- ********** 此处写第三题的SQL语句 ********** --
SELECT SUBSTRING(cno,1,2),COUNT(cno)
FROM course
GROUP BY SUBSTRING(cno,1,2);
-- ********** End ********** --

GO

第2关：数据统计初级应用

编程要求

• 1.补全右侧代码片段中 第一题 下 Begin-End 区间的代码，统计激光彩色打印机有多少种型号;
• 2.补全右侧代码片段中 第二题 下 Begin-End 区间的代码，找出最便宜的喷墨打印机价格。
• 3.补全右侧代码片段中 第三题 下 Begin-End 区间的代码，找出最贵的激光打印机型号和价格。

USE test_wyy_db_guet
Go

SET NOCOUNT ON


-- ********** Begin ********** --
---------- 第一题----------
SELECT COUNT(DISTINCT model)
FROM printer
WHERE type='laser' AND color='T';
-- ********** End ********** --
GO


-- ********** Begin ********** --
---------- 第二题----------
SELECT min(DISTINCT price)
FROM printer
WHERE type='ink-jet' ;
-- ********** End ********** --
GO

-- ********** Begin ********** --
---------- 第三题----------
SELECT model,price
FROM printer
WHERE price IN (SELECT MAX(price) FROM printer WHERE type='laser') AND type='laser';
-- ********** End ********** --
GO

第3关：数据统计综合应用

任务描述

本关任务：

• 灵活使用分组操作和聚集函数解决比较复杂的数据统计问题

本关使用的关系说明：

product(maker,model,type) maker:表示生产厂商 model：生产的产品型号 type：产品类型，有pc laptop两种

pc(model,speed,ram,hd,price) 表示型号，速度，内存大小，硬盘大小，价格

laptop(model,speed,ram,hd,screen,price) 表示型号，速度，内存大小，硬盘大小，屏幕大小和价格 ####编程要求

• 本题可使用视图V_test（视图已经创建完成，可直接使用，你不需要再次创建视图） 了解视图的详细信息可参考下面的创建视图语句： create view V_test as select product.maker,product.model,product.type,pc.price,pc.hd,pc.speed from product join pc on product.model=pc.model union select product.maker,product.model,product.type,laptop.price,laptop.hd,laptop.speed from product join laptop on product.model=laptop.model

• 1.补全右侧代码片段中 第1题 下 Begin-End 区间的代码，查询在一种或两种电脑（含PC和laptop）中出现过的硬盘的容量。

• 2.补全右侧代码片段中 第2题 下 Begin-End 区间的代码，统计各生产厂商生产的电脑（不区分pc和laptop)的平均处理速度的最大值。

• 3.补全右侧代码片段中 第3题 下 Begin-End 区间的代码，统计出各厂商生产价格高于1000的产品数量，不用区分是pc还是laptop

• 4.补全右侧代码片段中 第4题 下 Begin-End 区间的代码，分别统计各厂商生产的pc，laptop的平均价格。

USE test_wyy_db_guet
Go

SET NOCOUNT ON

---------- 第1题 ----------
-- ********** Begin ********** --
SELECT hd
FROM V_test
GROUP BY hd
HAVING COUNT(*)<3;


-- ********** End ********** --

GO

---------- 第2题 ----------
-- ********** Begin ********** --
SELECT MAX(AvgSpeed) FROM(
SELECT maker AS maker,AVG(speed) AS AvgSpeed
FROM V_test
GROUP BY maker
)name;


-- ********** End ********** --

GO

---------- 第3题 ----------
-- ********** Begin ********** --
SELECT maker,COUNT(*)
FROM V_test
WHERE price>1000
GROUP BY maker;


-- ********** End ********** --

GO
---------- 第4题 ----------
-- ********** Begin ********** --
SELECT maker as maker,type,AVG(price)
FROM V_test
GROUP BY maker,type;


-- ********** End ********** --

GO