3.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'
(列名可省略)
结果如下:
3.2
会报错,因为修改视图同样也会修改原表,但表的某些字段不能为空,所以无法插入该条数据。
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
标量+关联子查询。这里关联子查询的作用为:使分组后仍然每行都对应一个平均值,而非每组对应一个。
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 product_type) AS avg_sale_price
FROM product as p1
3.5
正确
3.6
1.取出了 purchase_price 不是 500、2800、5000 的商品,而不包含 purchase_price 为 NULL 的商品,这是因为谓词无法与 NULL 进行比较。
2. NOT IN 的参数中不能包含 NULL,否则,查询结果通常为空。
3.7
使用聚合函数+CASE 实现
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;