DataWhale|SQL task04 集合运算

练习题1：UNION

假设连锁店想要增加成本利润率超过 50%或者售价低于 800 的货物的存货量, 请使用 UNION 对分别满足上述两个条件的商品的查询结果求并集。

SELECT product_id,product_name,product_type,sale_price,purchase_price
FROM product
WHERE (sale_price-purchase_price) /purchase_price>0.5
UNION
SELECT product_id,product_name,product_type,sale_price,purchase_price
FROM product
WHERE sale_price<800;

练习题2：UNION和OR

分别使用 UNION 或者 OR 谓词,找出成本利润率不足 30%或成本利润率未知的商品。

SELECT product_id,product_name,product_type,sale_price,purchase_price
FROM product
WHERE (sale_price-purchase_price) /sale_price<0.3
UNION 
SELECT product_id,product_name,product_type,sale_price,purchase_price
FROM product
WHERE (sale_price-purchase_price) /sale_price IS NULL;

SELECT product_id,product_name,product_type,sale_price,purchase_price
FROM product
WHERE (sale_price-purchase_price) /sale_price<0.3 OR (sale_price-purchase_price) /sale_price IS NULL;

练习题3：UNION ALL

商店决定对product表中成本利润低于50% 或者 售价低于1000的商品提价, 请使用UNION ALL 语句将分别满足上述两个条件的结果取并集. 查询结果类似下表:

SELECT product_id,product_name,product_type,sale_price,purchase_price
FROM product
WHERE (sale_price-purchase_price) /purchase_price>0.5
UNION ALL
SELECT product_id,product_name,product_type,sale_price,purchase_price
FROM product
WHERE sale_price<1000;

练习题4：隐式数据类型转换

使用 SYSDATE()函数可以返回当前日期时间, 是一个日期时间类型的数据, 试测试该数据类型和数值,字符串等类型的兼容性。

例如, 以下代码可以正确执行, 说明时间日期类型和字符串,数值以及缺失值均能兼容。

SELECT SYSDATE(), SYSDATE(), SYSDATE()
 UNION
SELECT 'chars', 123, null

练习题5：EXPECT

找出只存在于Product表但不存在于Product2表的商品。由于MySQL中不支持EXPEC，因此考虑使用NOT IN 解决该题。

-- 使用 NOT IN 子句的实现方法
SELECT * 
  FROM Product
 WHERE product_id NOT IN (SELECT product_id 
                            FROM Product2)

练习题6：EXPECT 与NOT 谓词

使用NOT谓词进行集合的减法运算, 求出Product表中, 售价高于2000,但利润低于30%的商品

SELECT * 
  FROM product
 WHERE (sale_price-purchase_price) /purchase_price<0.3 
 AND sale_price NOT IN (SELECT sale_price 
                        FROM product
                        WHERE sale_price<=2000);

练习题7： INTERSECT与AND谓词

使用AND谓词查找product表中利润率高于50%,并且售价低于1500的商品,查询结果如下所示。

SELECT * 
FROM product
WHERE (sale_price-purchase_price) /purchase_price>0.5
AND sale_price <1500;

 

练习题8：对称差

使用Product表和Product2表的对称差来查询哪些商品只在其中一张表, 结果类似于:

SELECT * 
  FROM product
 WHERE product_name NOT IN (SELECT product_name 
                            FROM product2)
UNION
SELECT * 
  FROM product2
 WHERE product_name NOT IN (SELECT product_name 
                            FROM product);

 

练习题9：内连接

找出每个商店里的衣服类商品的名称及价格等信息. 希望得到如下结果

SELECT S.shop_id,S.shop_name,S.product_id,P.product_id,P.product_name,P.product_type,P.purchase_price 
FROM shop_product AS S
INNER JOIN (SELECT product_id, product_name,product_type,purchase_price 
            FROM product AS P
            WHERE product_type="衣服")AS P
ON S.product_id=P.product_id;

 

练习题10：内连接+子查询

分别使用连结两个子查询和不使用子查询的方式, 找出东京商店里, 售价低于 2000 的商品信息,希望得到如下结果。

-- 使用子查询
SELECT S.shop_id,S.shop_name,S.product_id,P.product_name,P.product_type,P.purchase_price 
FROM (SELECT shop_id, shop_name,product_id
FROM shop_product
WHERE shop_name="东京"
) AS S
INNER JOIN (SELECT product_id, product_name,product_type,purchase_price 
            FROM product AS P
            WHERE sale_price<2000) AS P
ON S.product_id=P.product_id;

-- 不使用子查询
SELECT S.shop_id,S.shop_name,S.product_id,P.product_name,P.product_type,P.purchase_price
FROM  shop_product AS S
INNER JOIN product AS P
ON S.product_id=P.product_id
WHERE S.shop_name="东京" AND P.sale_price<2000;

练习题11：内连接+group by

每个商店中, 售价最高的商品的售价分别是多少?

练习题12：思考题

上述查询得到了每个商品售价最高的商品, 但并不知道售价最高的商品是哪一个.如何获取每个商店里售价最高的商品的名称和售价?

SELECT S.shop_id,P.product_name,MAX(sale_price) AS price
FROM  shop_product AS S
INNER JOIN product AS P
ON S.product_id=P.product_id
GROUP BY S.shop_id,S.shop_name;

 注: 这道题的一个简易的方式是使用下一章的窗口函数. 当然, 也可以使用其他我们已经学过的知识来实现, 例如, 在找出每个商店售价最高商品的价格后, 使用这个价格再与 Product 列进行连结, 但这种做法在价格不唯一时会出现问题。