练习题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 列进行连结, 但这种做法在价格不唯一时会出现问题。