实验4 SQL的聚集函数查询与创建视图.pdfVIP

实验4SQL的聚集函数查询与创建视图

一、实验目的

学习掌握使用SQL的SELECT语句的聚集函数查询对表进行查询操作和创建视图。

二、实验准备

1.环境

操作系统：Windows7或Windows200XServer

数据库管理系统：SQLServer2008

2.准备工作

⑴本次实验使用超市的商品销售关系数据库CRM。从ftp服务器上该数据库拷贝到D：盘的自

己建立存储数据库文件的文件夹下。在SQLServer2008中利用数据库附加功能将数据库附加到系

统中。

(2)复习SELECT语句的基本语法格式，学习SQL的集函数，在SELECT语句中使用集函数进行

查询的方法，学习使用CREATEVIEW语句创建视图。

三、实验内容

使用SQL语句完成如下操作：

①在SELECT语句中使用聚集函数进行查询；

②创建视图。

四、实验要求

在SQLServer2008的对象资源管理器中，在关系数据库CRM中，使用SQL语句完成下面的

操作：

1.聚集函数查询与分组查询

(1)查询产地为“广东深圳”、按供货商号,供货商名分类汇总当前库存中供货商所供货的商

品金额合计、商品种类合计、商品最高单价与最低单价的差价。

SELECTInventory.SupnoAS供货商号,Supplier.SupnameAS供货商名,SUM(Num*Price)

AS金额总和,COUNT(*)AS商品种类合计,(MAX(Price)-MIN(Price))AS最大值与最小值差价

FROMInventoryJOINSupplierONInventory.Supno=Supplier.Supno

WHEREInventory.Address=广东深圳

GROUPBYInventory.Supno,Supplier.Supname

(2)在销售记录表中，查询按月、分商品汇总销售的商品号,商品名,数量合计、金额合计。

SELECTSUBSTRING(Date,5,2)AS月份,Commodityno,Commodityname,SUM(DISTINCT

Salesnum)AS数量合计,SUM(Price*Salesnum)AS金额合计

FROMSales

GROUPBYSUBSTRING(Date,5,2),Commodityno,Commodityname

(3)查询会员中购买了3种或3种以上不同商品种类，并且每种商品单价都大于等于2000元

的会员号、会员名、购买商品的金额平均值、金额的标准差和不同商品种类的数量，结果按金额平

均值降序排序。

SELECTMGQ.Memberno,Member.Membername,AVG(Price*Num)AS金额平均

值,STDEV(Price*Num)AS金额的标准差,COUNT(DISTINCTCommodityno)AS商品种类数

量

FROMMGQJOINMemberONMGQ.Memberno=Member.Memberno

GROUPBYMGQ.Memberno,Member.MembernameHAVINGCOUNT(*)=3AND

MIN(Price)=2000

ORDERBY金额平均值DESC

(4)查询所有大于等于平均年龄的女会员的会员号和会员名，以及所购买商品号,商品名,单

价、数量和金额。

SELECT

Member.Memberno,Member.Membername,MGQ.Commodityno,Inventory.Commodityname,MGQ.

Price,MGQ.Num,MGQ.Price*MGQ.NumAS金额

FROMMemberJOINMGQONMember.Memberno=MGQ.Memberno

JOINInventoryONMGQ.Commodityno=Inventory.Commodityno

WHERESex