【学习笔记】阿里云天池龙珠计划SQL训练营-Task04：集合运算-表的加减法和join等

本笔记为阿里云天池龙珠计划SQL训练营的学习内容

链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql

练习题

4.1

找出 product 和 product2 中售价高于 500 的商品的基本信息。

select * from product where sale_price > 500
union
select * from product2 where sale_price > 500

4.2

借助对称差的实现方式, 求product和product2的交集。

select * from product
where product_id not in(
select product_id from product where product_id not in (select product_id from product2)
union
select product_id from product2 where product_id not in (select product_id from product)
)

4.3

每类商品中售价最高的商品都在哪些商店有售 ？

SELECT
	shop_name,
	product_id 
FROM
	shopproduct 
WHERE
	product_id IN ( SELECT a.product_id FROM ( SELECT product_type, product_name, product_id, max( sale_price ) FROM product GROUP BY product_type ) a ) 
GROUP BY
	shop_name,
	product_id

4.4¶

分别使用内连结和关联子查询每一类商品中售价最高的商品。

SELECT
	product_type,
	product_name,
	product_id,
	max( sale_price ) 
FROM
	product 
GROUP BY
	product_type

4.5

用关联子查询实现：在product表中，取出 product_id, produc_name, slae_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。

( SELECT product_id, product_name, sale_price FROM product GROUP BY product_id ORDER BY sale_price ) UNION
SELECT
	'所有' AS product_id,
	'合计' AS product_name,
	SUM( sale_price ) 
FROM
	product