Task03 复杂一点的查询_崩撤卖溜

3.1

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

3.2

[2021-11-21 14:15:18] [HY000][1423] Field of view ‘shop.viewpractice5_1’ underlying table doesn’t have a default value
原因:当插入数据到视图时,也会将数据插入原表中,而原表中’product_id’、'product_type’被限制为NOT NULL,因此无法完成插入。

3.3

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

3.4

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

3.5

运算中含有NULL时,运算结果必然会变为NULL。

3.6

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

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

3.7

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;
浙ICP备19012682号