练习题解答如下:
4.1
select *
from product
where sale_price >= 500 union
select *
from product2
where sale_price >=500;
4.2
select *
from product
where product_id not in (select product_id from product2)
union
select *
from product2
where product_id not in (select product_id from product);
4.3
select s.product_id, shop_name, product_type
from (
select product_id, m.product_type
from (select * from product union select * from product2) all_product
join (
select max(sale_price) max, product_type
from (select * from product union select * from product2) all_product
group by product_type
) on all_product.product_type = m.product_type
where sale_price = max
) id
left join ShopProduct s
on id.product_id = s.product_id;
本题涉及到将表左连接和嵌套,不容易理解。
4.4
select *
from (select * from product union select * from product2) p1
where sale_price = (
select max(sale_price)
from (select * from product union select * from product2) p2
where p2.product_type = p1.product_type
group by product_type
);
代码需完善
4.5
select product_id, product_name, sale_price,(
slelct sum( sale_price )
from product p2
where p2.sale_price<=p.sale_price ) s_sale
from product p
order by sale_price;