阿里天池龙珠计划-SQL训练营-Task04

 

练习题

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  IN (SELECT product_id FROM product2)
UNION
SELECT * 
  FROM product2
 WHERE product_id  IN (SELECT product_id FROM product)

4.3

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

SELECT product_type, product_name, shop_name, sale_price
FROM product p1
	INNER JOIN shopproduct sp ON p1.product_id = sp.product_id
WHERE (product_type, sale_price) IN (
	SELECT product_type, max(sale_price)
	FROM product
	GROUP BY product_type
)
ORDER BY product_type

4.4

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

内连接

SELECT p1.product_type, p1.product_name, p1.sale_price
FROM product p1
	INNER JOIN (
		SELECT product_type, MAX(sale_price) AS sale_price
		FROM product
		GROUP BY product_type
	) a
	ON p1.product_type = a.product_type
		AND p1.sale_price = a.sale_price
ORDER BY product_type

子查询

SELECT product_type, product_name, sale_price
FROM product p1
WHERE (product_type, sale_price) IN (
	SELECT product_type, max(sale_price)
	FROM product
	GROUP BY product_type
)
ORDER BY product_type

4.5

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

SELECT product_id, product_type, product_name, sale_price
	, (
		SELECT sum(sale_price)
		FROM product p2
		WHERE p1.sale_price > p2.sale_price
			OR p1.sale_price = p2.sale_price
	) AS total_sale_price
FROM product p1
ORDER BY sale_price;