SQL打卡TASK04

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；

-- 习题1答案
select *
from product
where sale_price>500
union
select * 
from product2
where sale_price>599;

-- 习题2答案:并集-对称差=交集
select *
from (select*
    from product
    union
    select *
    from product) as bingji
where product_id NOT IN (select *
                            from product
                            where product_id not in (select * from product2)
                            union
                            select * 
                            from product2
                            where product_id not in (select * from product));

-- 习题4答案,习题3答案在4基础上left outer join商店名on product_id
select p3.*, sp.shop_name
from (
    select p.product_type, p.product_id, p.product_name, p.sale_price,p2.max_price
    from product as p
    inner join (select product_type, max(sale_price) as max_price
                from product
                group by product_type) as p2
    on p.product_type = p2.product_type
    where sale_price=max_price )  as p3
left outer join shopproduct as sp
on p3.product_id= sp.product_id;

-- 习题5答案
select m.product_id,m.product_name,m.sale_price,x2.sum_price
from product as m
inner join(
        select product_name, sum(x.p2_price) as sum_price
        from (select p1.product_id,
                     p1.product_name,
                     p1.sale_price,
                     p2.product_id as p2_id,
                     p2.product_name as p2_name,
                     p2.sale_price as p2_price
                from product as p1
                left outer join product as p2
                on p1.sale_price >= p2.sale_price
                order by p1.sale_price,p1.product_id) as x
        group by product_name
        order by sum_price) as x2
on m.product_name=x2.product_name
order by m.sale_price;