练习题第一部分
解答如下:
3.1
alter view ViewPractice5_1 (product_name, sale_price, regist_date) as
select product_name, sale_price, regist_date
fromproduct
where sale_price >= 1000 and regist_date = '2009-09-20';
select * f
rom ViewPractice5_1
3.2
如果像习题一样创建视图ViewPractice5_1插入数据就会报错
像视图中插入数据,但是在原表中不止三列数据,其他列不允许为空,所以会报错
3.3
select product_id, product_name, product_type, sale_price ,
(select avg(sale_price) from product )
from product
3.4
drop view avgPriceByType;
create view
avgPriceByTypeAS
select product_id, product_name, product_type, sale_price,
(selectavg(sale_price)
from product P2
where P1.product_type = P2.product_type
group by P1.product_type) AS avg_sale_price from product P1;
sselect * from avgPriceByType;
3.5
本题正确
3.6
执行两条select语句后无结果
3.7
select count(case when sale_price <= 1000 then '低档商品' ELSE null END) as
low_price,count(case when sale_price between 1001and 3000 then '中档商品'
else null end) AS
mid_price,count(case when sale_price >= 3001 then '高档商品
' else null end) as high_price from product;