本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：tianchi.aliyun.com/specials/pr…；

练习题-第一部分

3.1

创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。

条件 1：销售单价大于等于 1000 日元。
条件 2：登记日期是 2009 年 9 月 20 日。
条件 3：包含商品名称、销售单价和登记日期三列。

对该视图执行 SELECT 语句的结果如下所示。

SELECT * FROM ViewPractice5_1;

执行结果

product_name | sale_price | regist_date
--------------+------------+------------
T恤衫         | 　 1000    | 2009-09-20
菜刀          |    3000    | 2009-09-20

答案：

CREATE VIEW ViewPractice5_1 
    AS SELECT product_name, sale_price, regist_date 
    FROM product 
    WHERE sale_price >= 1000 
    AND regist_date = '2009-09-20';

3.2

向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果呢？

INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');

答案：

视图插⼊的数据也会在原表中插⼊，但原表有三个带有 NOT NULL 约束的字段,因此在插入到原表中时会报错；

3.3

请根据如下结果编写 SELECT 语句，其中 sale_price_all 列为全部商品的平均销售单价。

答案：

SELECT product_id, 
       product_name,
       product_type,
       sale_price,
       (SELECT AVG(sale_price) FROM product) AS sale_price_all 
FROM product;

3.4

请根据习题一中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为AvgPriceByType）。

提示：其中的关键是 avg_sale_price 列。与习题三不同，这里需要计算出的是各商品种类的平均销售单价。这与使用关联子查询所得到的结果相同。也就是说，该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。

答案：

CREATE VIEW AvgPriceByType 
       AS SELECT product_id,
                 product_name,
                 product_type,
                 sale_price, 
                 (SELECT AVG(sale_price) 
                  FROM product p2 
                  WHERE p1.product_type = p2.product_type 
                  GROUP BY p1.product_type) AS avg_sale_price 
       FROM product p1;

3.5

运算或者函数中含有 NULL 时，结果全都会变为NULL ？（判断题）

答案：正确

3.6

对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？

①

SELECT product_name, purchase_price 
FROM product 
WHERE purchase_price NOT IN (500, 2800, 5000);

答案：

这个语句查询出 purchase_price 不是 500、2800、5000 的商品，但是由于谓词不与null比较，所以不包含 purchase_price 为 NULL 的商品；

②

SELECT product_name, purchase_price 
FROM product 
WHERE purchase_price NOT IN (500, 2800, 5000, NULL);

答案：

语句中NOT IN的参数中能包含 NULL则查询结果通常为空。

3.7

按照销售单价（ sale_price）对练习 6.1 中的 product（商品）表中的商品进行如下分类。

低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、圆珠笔）
中档商品：销售单价在1001日元以上3000日元以下（菜刀）
高档商品：销售单价在3001日元以上（运动T恤、高压锅）

请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。

执行结果

low_price | mid_price | high_price
----------+-----------+------------
   5      |         1 |         2

答案：

SELECT SUM(CASE WHEN sale_price <= 1000 THEN 1 ELSE 0 END) AS low_price,  
       SUM(CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN 1 ELSE 0 END) AS mid_price, 
       SUM(CASE WHEN sale_price >= 3001 THEN 1 ELSE 0 END) AS high_price 
FROM product;

Task03：复杂查询方法-视图、子查询、函数等-天池龙珠计划SQL训练营

练习题-第一部分

3.1

3.2

3.3

3.4

3.5

3.6

3.7