Q1
CREATE VIEW ViewPractice5_1(product_type, sale_price, regist_date)
AS
SELECT product_type, sale_price,regist_date
FROM product
WHERE regist_date = "2009-09-20" and sale_price>=1000;
Q2
SQL 错误 [1423] [HY000]: Field of view 'shop.viewpractice5_1' underlying table doesn't have a default value
还是因为视图的定义,视图只是原表的一个窗口,所以它修改也只能修改透过窗口能看到的内容。
Q3
SELECT product_id,
product_name,
sale_price,
(SELECT AVG(sale_price)
FROM product) AS avg_price
FROM product;
Q4
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 sale_price_all
FROM product AS P1
Q5
运算或者函数中含有 NULL 时,结果全都会变为NULL .
Q6
运行结果1:返回purchase_price结果不为500,2800,5000,且不为NULL 的行。
运行结果2:返回为空。就是上题提到的运算或函数中含有 NULL 时,结果全都会变为NULL 。筛选含/不含NULL的项,用IS NULL或IS NOT NULL.
Q7
SELECT
SUM(CASE WHEN sale_price BETWEEN 0 AND 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>3000 THEN 1 ELSE 0 END) AS high_price
FROM product;