暑期SQL编程Task04学习笔记打卡

练习题5.1

SELECT *
FROM product
WHERE sale_price > 500
UNION
SELECT *
FROM product2
WHERE sale_price > 500;

练习题5.2

SELECT* FROM
(SELECT * FROM product UNION SELECT * FROM product2) AS allproduct
WHERE allproduct.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));

练习5.3

SELECT SP.shop_id,SP.shop_name,P.product_id,product_name,P.product_type,P.MAX_PRICE
FROM shopproduct AS SP
INNER JOIN
(SELECT product_id,product_name,P1.product_type,MAX_PRICE
FROM product AS P1
INNER JOIN 
(SELECT product_type, MAX(sale_price) AS MAX_PRICE
FROM product 
GROUP BY product_type) AS P2
ON P1.product_type=P2.product_type
WHERE sale_price=MAX_PRICE) AS P

练习5.4

（1）内连结

SELECT product_id,product_name,P1.product_type,MAX_PRICE
FROM product AS P1
INNER JOIN 
(SELECT product_type, MAX(sale_price) AS MAX_PRICE
FROM product 
GROUP BY product_type) AS P2
ON P1.product_type=P2.product_type
WHERE sale_price=MAX_PRICE;

（2）关联子查询

SELECT product_type, product_name, sale_price
FROM product AS P1
WHERE  sale_price>=
(SELECT MAX(sale_price)
 FROM product AS P2 
 WHERE P1.product_type=P2.product_type
 GROUP BY product_type);

练习5.5

SELECT product_id, product_name, sale_price,
	   (SELECT SUM(sale_price) FROM product AS P2
		WHERE P1.sale_price >= P2.sale_price
		AND P1.product_id <= P2.product_id) AS '累计售价'
FROM product AS P1 
ORDER BY sale_price;


SELECT P1.product_id ,P1.product_name ,P2.sale_price ,SUM(P2.sale_price) AS '累计售价'
FROM Product AS P1 
LEFT JOIN Prouduct AS P2
ON P1.sale_price >= P2.sale_price
AND P1.product_id != P2.product_id
GROUP BY P1.product_id ,P1.product_type
ORDER BY sale_price DESC