数据库系统作业2

一、统计函数的使用：

1、在表item中计算所有商品的数量，价格平均值，价格最大值，价格最小值，价格方差。

SQL语句:

SELECT COUNT(*) as NumberOfItems, AVG(x.I_PRICE) as Averageprice, MAX(x.I_PRICE) as Maxprice, Min(x.I_PRICE) as Minprice, VARIANCE(x.I_PRICE) as Pricevariance
FROM bmsql_item x;

查询结果截图：

2、在表stock中统计每个仓库保存的商品数量平均值，输出列为w_id, avg。

（提示1：使用group语句）

题意理解：在stock表中统计每一个仓库中保存的不同商品种类的商品数量平均值。
SQL语句：

	SELECT x.S_W_ID as w_id, AVG(x.S_QUANTITY) as avg
	FROM  bmsql_stock x
	GROUP BY x.S_W_ID;

查询结果：

二、正则表达式的使用：

1、找出所有以’NB’为名字开头或者以’VT‘为结尾的商品的所有信息；（用一个正则表达式解决，不要用or）

SQL语句

SELECT *
FROM bmsql_item x
WHERE  x.I_NAME ~ '^NB|VT$';

查询结果：

2、统计以名字开头字母在h-m之间的商品数量，以及平均价格。

SQL语句：

SELECT count(*), avg(x.I_PRICE)
FROM bmsql_item x
WHERE  x.I_NAME ~ '^[h-m]';

查询结果截图：

三、all/any的使用：

我们作以下定义：
如果商品a在仓库b中的数量大于10，则称为仓库b有储备商品a；
如果数量大于50，则称仓库b有充分储备商品a；
如果数量大于95，则称仓库b主要储备商品a。
商品a在仓库b中的数量为s_quantity from bmsql_stock where s_i_id=a and s_w_id=b。

1、对于二.1中的商品，找出在所有仓库中都有储备的商品，输出商品的所有信息。

SQL语句：

SELECT x.*
FROM bmsql_item x
WHERE  x.I_NAME ~ '^NB|VT$' AND 10 < all(
	SELECT y.S_QUANTITY
	FROM bmsql_stock y
	WHERE y.S_I_ID = x.I_ID
);

查询结果：

2、找出至少有一个仓库主要储备且该仓库销售税（w_tax）大于0.16的商品 （使用any），输出这些商品的所有信息。

SQL语句：

SELECT x.*
FROM bmsql_item x
WHERE  95 < any(
	SELECT z.S_QUANTITY
	FROM bmsql_stock z,  bmsql_warehouse y
	WHERE x.I_ID = z.S_I_ID AND z.S_W_ID = y.W_ID AND y.W_TAX > 0.16
);

查询结果截图：

四、嵌套查询(in)：

1、找到有商品税大于0.18的仓库主要储备的所有商品，输出它们的所有信息。

（提示：先用语句A找到这些仓库，然后使用select s_quantity from bmsql_stock where s_i_id=i_id and s_w_id in A 表示对于i_id商品，这些仓库的储备数量，然后结合any函数找到这些商品）

SQL语句

SELECT distinct x.*
FROM bmsql_item x, bmsql_stock y
WHERE y.S_QUANTITY > 95 AND y.S_I_ID = x.I_ID AND y.S_W_ID IN (
					SELECT z.W_ID
					FROM bmsql_warehouse z
					WHERE 0.18 < any(
										SELECT a.D_TAX
										FROM bmsql_district a
										WHERE a.D_W_ID = z.W_ID
										)
																) ;

解释SQL语义：
  最底层的嵌套循环先使用any找出所有“有商品税大于0.18”的仓库，然后由这些仓库的id在stock表中找到所有这些仓库的主要储备的商品，输出商品的所有信息。
查询结果：

2、找到主要贮备有以’SP’为开头的商品的仓库，输出仓库的所有信息。

SQL语句：

SELECT x.*  
FROM bmsql_warehouse x, bmsql_stock y
WHERE y.S_QUANTITY > 95 AND x.W_ID = y.S_W_ID AND y.S_I_ID IN (
											SELECT z.I_ID
											FROM bmsql_item z
											WHERE z.I_NAME  ~ '^SP'
											) ;

解释SQL语义：最里层循环先找到所有以“SP”为开头的所有商品的id，然后再搜索stock表，只要商品id在我们之前找到的item id表里面且储存量大于95则记录下该对应仓库的id，最后输出这些仓库的所有信息。
  
查询结果截图：

五、综合题：

1、找到以’SP’为开头，且在所有仓库储存的平均数量大于50的商品的全部信息。

SQL语句

SELECT x.*
FROM bmsql_item x
WHERE x.I_NAME  ~ '^SP' AND 50 < ALL(
										SELECT AVG(y.S_QUANTITY)  
										FROM bmsql_stock y
										WHERE y.S_I_ID = x.I_ID 
										GROUP BY y.S_I_ID
										);

解释SQL语义：
  首先搜索item表，商品需要满足名字以“SP”为开头，还需要满足在所有仓库平均储存数量大于50：搜索stock表，先按item的id分组，计算遍历到的商品在所有仓库的平均储存数量，计算平均值，平均值得大于50；

查询结果截图

2、找到所有满足条件的仓库的编号(w_id)：该仓库在所有地区的销售税都小于0.15。

（注意仓库a在区域b的销售税为d_tax where d_id=b and d_w_id=a。）
（提示：可以使用true = all()来查询所有条件都成立。）

SQL语句：

SELECT x.W_ID
FROM bmsql_warehouse x
WHERE 0.15 > ALL(
					SELECT y.D_TAX
					FROM bmsql_district y
					WHERE y.D_W_ID = x.W_ID
				);

解释SQL语义：先选出当前仓库id对应在所有地区的销售税，然后使用all语句判断是否选出的所有销售税都小于0.15，是的话则记录该仓库id。
查询结果截图：

3、统计五.2中的仓库主要储备的商品数量，价格平均值，输出列为w_id, number，avg_price。

SQL语句：

SELECT a.S_W_ID as w_id, count(b) as number, avg(b.I_PRICE)
FROM bmsql_stock a, bmsql_item b
WHERE a.S_W_ID IN(
					SELECT x.W_ID
					FROM bmsql_warehouse x
					WHERE 0.15 > ALL(
						SELECT y.D_TAX
						FROM bmsql_district y
						WHERE y.D_W_ID = x.W_ID
										) 
					)AND a.S_QUANTITY > 95 AND a.S_I_ID = b.I_ID
GROUP BY a.S_W_ID;

解释SQL语句：
  像五.2一样找出在所有地区销售税都小于0.15的仓库，对stock仓库按照仓库id进行划分，对于已经找到所有仓库id需要满足：某商品储存量大于95，记录下这些商品，然后计算商品数量与价格平均值。

查询结果截图：