SQL学习TASK3打卡

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

-- 习题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-9-20";
-- 习题2答案：会报错因为不满足原表约束条件
-- 习题3答案：
SELECT product_id,
    product_name,
    product_type,
    sale_price,
    (SELECT AVG(sale_price) FROM product) AS sale_price_all
FROM product;
-- 习题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;
-- 习题5答案：正确
/* 习题6答案：两列，且purchase_price非500，2800，5000的记录
结果为空，谓词无法与null进行比较 */
-- 习题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;