SQL训练营-task04

练习题

4.1

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

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

4.2

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

 对称差：

A、B的对称差，就是A-B 并上B-A

select * from product where 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

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

这题在gbase 8a中执行结果为空。

SELECT S.shop_name ,P.product_type ,P.sale_price
	FROM Product AS P
	INNER JOIN ShopProduct AS S
	ON P.product_id = S.product_id
	GROUP BY P.product_type
	ORDER BY P.sale_price desc
	limit 1

4.4

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

内连接：

SELECT p1.product_id, p1.product_name, p1.product_type, p1.sale_price, p2.max_sale_price 
FROM product3 AS p1
INNER JOIN 
	(SELECT product_type, MAX(sale_price) AS max_sale_price
	FROM product3
	GROUP BY product_type) AS p2
ON p1.product_type = p2.product_type
AND p1.sale_price = p2.max_sale_price;

 关联子查询：

select  p1.product_id, p1.product_name, p1.product_type, p1.sale_price
from product3 as p1 
where p1.sale_price=(select max(sale_price)  as max_sale_price from product3 as p2
 where p1.product_type=p2.product_type group by product_type); 

4.5

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

关联子查询：

select  p1.product_id, p1.product_name, p1.product_type, p1.sale_price,(select sum(sale_price) as sum_price from product3 as p2 
where p1.sale_price>=p2.sale_price and p1.product_id<=p2.product_id ) as '累计销售价格'  
from product3 as p1  order by p1.sale_price ;