SQL ch3

---

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";

SELECT product_name, sale_price, regist_date FROM VIEWPractice5_1;

3.3

SELECT product_id, product_name, product_type,sale_price, (SELECT avg(sale_price) FROM product) as avg_sale_price
FROM product

3.4

SELECT  p1.product_id, p1.product_name, p1.product_type, p1.sale_price, p2.avg_sale_price as avg_sale_price
FROM product p1
join
(SELECT product_type, avg(sale_price) as avg_sale_price FROM product GROUP BY product_type) p2
on p1.product_type=p2.product_type

3.7

 SELECT
 count( case when sale_price<=1000 then sale_price else null end) as low_price,
 count( case when (sale_price>1000 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