本笔记为阿里云天池龙珠计划SQL训练营的学习内容,链接为:https://tianchi.aliyun.com/specials/promotion/aicampsql;
内容会做在一个思维导图中—MySQL笔记 - GitMind
第三次作业
1.
CREATE VIEW ViewPractice5_1 AS
SELECT product_name, sale_price, regist_date
FROM product
WHERE sale_price >= 1000
AND regist_date = '2009-09-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 p2
WHERE p1.product_type = p2.product_type
GROUP BY p1.product_type) AS avg_sale_price
FROM product p1;
3.5
正确
3.6
(1)选出了价格不为500, 2800, 5000的,且没有null的商品。
(2)not in参数中不能包含null,会返回空
3.7
SELECT SUM(CASE sale_price WHEN <= 1000 THE 1 ELSE 0 END)
AS low_price;
SUM (CASE sale_price WHEN BETWEEN 1001 AND 3000 THE 1 ELSE 0 END) AS mid_price;
SUM(CASE sale_price WHEN >= 3001 THE 1 ELSE 0 END)
AS high_price;
FROM product;