本笔记为阿里云天池龙珠计划SQL训练营的学习内容,链接为:https://tianchi.aliyun.com/specials/promotion/aicampsql;
/* 练习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-9-20';
*/
/* 练习3.2
INSERT INTO viewpractice5_1 VALUES('刀子',300,'2009-11-02');
结果是报错"filed of view doesn't have the default value."
*/
/* 练习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 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的产品及价格。
2. 什么也不会显示。
*/
/* 习题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;
*/