4.1
找出 product 和 product2 中售价高于 500 的商品的基本信息。
SELECT * FROM product WHERE sale_price >500
UNION
SELECT * FROM product2 WHERE sale_price
4.2
借助对称差的实现方式, 求product和product2的交集。
SELECT * FROM product WHERE product_id in (SELECT * FROM product2)
4.3
每类商品中售价最高的商品都在哪些商店有售 ?
SELECT p.product_id,shop_name FROM product p JOIN shopproduct sp
ON p.product_id = sp.product_id
WHERE p.product_id in (
SELECT MAX(sale_price),product_id FROM product
GROUP BY product_type)
4.4
分别使用内连结和关联子查询每一类商品中售价最高的商品。
SELECT product_id FROM product WHERE sale_price in(SELECT max(sale_price)
FROM product group by product_type)
group by product_type
SELECT product_id FROM product p1 JOIN product p2
on p1.product_id = p2.product_id
WHERE p1.sale_price > any (p2.sale_price)
GROUP BY product_type
4.5
用关联子查询实现:在product
表中,取出 product_id, produc_name, slae_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。
SELECT product_id, produc_name, slae_price,(SELECT sum(sal_price) FROM product ) sum_price FROM product
ORDER BY sale_price desc