【无标题】

3.1
构建试图代码：
Create view xx
As
Select xx
From xx
Where xxx
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.3
SELECT product_id,product_name,product_type,sale_price,(SELECT AVG(sale_price) FROM product) AS sale_price_avg
FROM product;

3.4
CREATE VIEW avgpricebytype
AS
SELECT product_id,product_name,product_type,sale_price,(SELECT AVG(sale_price) FROM product) AS sale_price_avg
FROM product;

笔记：！！！
获取前两个元素
如果要输出第几个元素的话，需要进行一个嵌套，变成只含有两个元素，然后进行嵌套

3.5
四则运算中含有 NULL 时（不进行特殊处理的情况下），运算结果是否必然会变为NULL ？
是的，四则运算加减乘除中如果有null，结果就为null。

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

（2）SELECT product_name,purchase_price
FROM product
WHERE purchase_price NOT IN(500,2800,5000) AND purchase_price IS NOT NULL ;

3.7
需要用到case when 。。。then else xx end
SELECT
COUNT(case when sale_price<=1000 then sale_price ELSE NULL end ) AS low_price,
COUNT(case when sale_price>=1001 AND sale_price<=3000 then sale_price ELSE NULL end ) AS mid_price,
COUNT(case when sale_price>=3001 then sale_price ELSE NULL end) AS high_price
FROM product;