sql练习day03

#3.1
CREATE VIEW ViewPractices5_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’;
SELECT * FROM ViewPractices5_1;

#3.2
INSERT INTO ViewPractices5_1 VALUES(‘刀子’,3000,‘2009-11-02’);
UPDATE ViewPractices5_1
SELECT * FROM ViewPractices5_1;

#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 p2
WHERE p1.product_type = p2.product_type
GROUP BY product_type) AS avg_sale_price
FROM product AS p1;

#3.5 正确，当我们的运算或者函数中有null的时候，结果的确会变成null

#3.6
SELECT product_name,purchase_price
FROM product
WHERE purchase_price NOT IN (500,2800,5000);

SELECT product_name,purchase_price
FROM product
WHERE purchase_price NOT IN (500,2800,5000,NULL);

#3.7
SELECT SUM(CASE WHEN sale_price<=1000 THEN product_id ELSE 0 END) AS low_price,
SUM(CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN product_idELSE 0 END) AS mid_price,
SUM(CASE WHEN sale_price>=3001 THEN product_id ELSE 0 END) AS high_price
FROM product;