/*练习题-第一部分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*/
INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');
/*练习题-第一部分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 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*/
是
/*练习题-第二部分3.6*/
1. 结果是显示购买价格不为500,2800,5000的产品及价格。
2. 什么也不会显示。
/*练习题-第二部分3.7*/
SELECT (select count(0) from product where sale_price<=1000) AS low_price ,
(select count(0) from product where sale_price BETWEEN 1001 and 3000) AS mid_price ,
(select count(0) from product where sale_price>3000) AS high_price
FROM product limit 1;