本笔记为阿里云天池龙珠计划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-09-20';
3.2
插入不了数据,因为原表(product)中设计表的时候限制了product_id,product_type,product_name字段不能为null,而视图中不包含product_id,product_type两个字段的内容,所以不能更新。
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
(product_id,product_name,product_type,sale_price,avg_sale_price)
AS SELECT
product_id,
product_name,
product_type,
sale_price,
(SELECT AVG(sale_price) FROM product AS S2
WHERE S1.product_type=S2.product_type
GROUP BY S1.product_type) AS avg_sale_price
FROM product AS S1;
3.5
错,当使用IS NULL或者IS NOT NULL是返回布尔值
3.6
第一条语句返回product表中purchase_price不为500,2800, 5000的商品的product_name, purchase_price
第二条语句在NOT IN判断语句中包含了NULL,所以会引发错误,因为NULL类型的特殊,对于NULL的判断只能使用IS和IS NOT两种方式。
3.7
SELECT COUNT((CASE WHEN sale_price<=1000 THEN product_id ELSE NULL END )) AS low_price,
COUNT((CASE WHEN (sale_price BETWEEN 1001 AND 3000) THEN product_id ELSE NULL END)) AS mid_price,
COUNT((CASE WHEN sale_price>=3001 THEN product_id ELSE NULL END)) AS high_price
FROM product;