[数据库系统] 四、分组操作符与聚集函数

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

使用 group by 语句结合聚集函数解决数据统计问题 

数据统计

一般的数据统计关系代数表达式如下：

其中L是属性集。含义是在属性集L上分组，分组后用函数fun运算 ，如

表示按性别sex的不同取值分组，再计算每个不同性别的学生人数。 假设有下面关系，利用上述分组计数统计则能得到： 男 2 女 2

educoder

编程要求

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

• 1.统计 course表中学分数（credit）大于2的课程门数；

• 2.统计所有专业必修课（BT开头的课程代码）的学分总数。

• 3.按课程类别统计每个类别课程的门数，如课程代码BT001，BT002都是专业必修课。

本题使用的关系如下：

course(cno,cname,credit) 对应课程代码，课程名称，学分，

其中课程代码前2位代表不同类型的课程，如 BT 代表 专业必修课，XZ 代表专业限选课。

测试说明

测试过程：

• 本关涉及到的测试文件是 step1_test.sh ，平台将运行用户补全的 step1.sql 文件，得到数据；

• 将得到的数据与答案比较，判断代码是否正确。

   

  USE test_wyy_db_guet
  GO
  
  SET NOCOUNT ON
  
  -- ********** Begin ********** --
  -- ********** 此处写第一题的SQL语句 ********** --
  select count(cno)
  from course
  where credit>2;
  -- ********** End ********** --
  
  GO
   
  -- ********** Begin ********** --
  -- ********** 此处写第二题的SQL语句 ********** --
  select sum(credit)
  from course
  where cno like 'BT%';
  -- ********** End ********** --
  
  GO
  
   
  -- ********** Begin ********** --
  -- ********** 此处写第三题的SQL语句 ********** --
  select left(cno,2),count(cno)
  from course
  group by left(cno,2);
  -- ********** End ********** --
  
  GO

  结果：

  原数据：

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

educoder

本关使用的关系为printer(model,color,type,price)

表示的含义是

model：打印机型号；

color：是否彩色， T 彩色，F 黑白

type:类型，ink-jet 表示喷墨， laser 表示激光；

price：单价

编程要求

• 1.统计激光彩色打印机有多少种型号;
• 2.找出最便宜的喷墨打印机价格。
• 3.找出最贵的激光打印机型号和价格。

USE test_wyy_db_guet
Go

SET NOCOUNT ON


-- ********** Begin ********** --
---------- 第一题----------
select count(model)
from printer
where color='T' and type='laser';
-- ********** End ********** --
GO


-- ********** Begin ********** --
---------- 第二题----------
select min(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'
);
-- ********** 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.查询在一种或两种电脑（含PC和laptop）中出现过的硬盘的容量。

2.统计各生产厂商生产的电脑（不区分pc和laptop)的平均处理速度的最大值。

3.统计出各厂商生产价格高于1000的产品数量，不用区分是pc还是laptop

4.格分别统计各厂商生产的pc，laptop的平均价。

V_test:

maker	model	type	price	hd	speed	
--------------------	------------------------------
A	    1001	pc	    2114	250	266
A	    1002	pc	    995	    250	210
A	    1003	pc	    478	    80	130
A	    2004	laptop	1150	120	200
A	    2005	laptop	630	    250	216
A	    2006	laptop	2500	120	200
B	    1005	pc	    630    	250	134
B	    1006	pc	    1049	320	266
B	    2007	laptop	1700	100	183
C	    1007	pc	    510	    320	230
D    	1008	pc	    770    	200	266
D   	1009	pc    	650	    250	250
D	    1010	pc	    959	    250	210
E	    1011	pc	    649	    300	266
E	    1012	pc    	529    	160	180
E    	1039	pc	    449	    80	206
E	    2001	laptop	3672	240	200
E	    2002	laptop	949	    80	173
E	    2003	laptop	478	    80	180
F    	2008	laptop	1428	160	160
F	    2009	laptop	900	    80	160
G	    2010	laptop	2300	250	200

USE test_wyy_db_guet
Go

SET NOCOUNT ON

---------- 第1题 ----------
-- ********** Begin ********** --
select distinct hd
from V_test
group by hd
having count(*)<=2 
;
-- ********** End ********** --

GO

---------- 第2题 ----------
-- ********** Begin ********** --

select max(avgs)
from (
    select maker,avg(speed) as avgs
    from V_test
    group by maker
)b  --这里的结尾的b可以是其他的字母

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

GO

---------- 第3题 ----------
-- ********** Begin ********** --
select maker ,count(model)
from V_test
where price>1000
group by maker;

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

GO
---------- 第4题 ----------
-- ********** Begin ********** --
select maker , type ,avg(price)
from V_test
group by maker,type;
-- ********** End ********** --

GO