[SQL] Datawhale 學習筆記 Task04

參考資料：
個人原創-MySQL學習筆記
Task04：集合運算

一、知識點大總結

# 多行之間滿足一個需求就需要JOIN

# 笛卡爾積
SELECT * 
	FROM students,disc_info;

# 根據對應關係連表，實際等同於INNER JOIN
SELECT * 
	FROM students,disc_info 
	WHERE students.discipline = disc_info.nid;
SELECT students.name,disc_info.discipline 
	FROM students,disc_info 
	WHERE students.discipline = disc_info.nid;

# JOIN 左右連線如果無對應關係顯示NULL，JOIN效率高

# 取交集，INNER JOIN，也可以理解成過濾掉含NULL資料行的左右連線
SELECT A.num, A.name, B.name 
	FROM A 
	INNER JOIN B 
	ON A.nid = B.nid;

# 右連線，RIGHT JOIN
SELECT A.num, A.name, B.name 
	FROM A 
	RIGHT JOIN B 
	ON A.nid = B.nid;

# 左連線，LEFT JOIN
SELECT A.num, A.name, B.name 
	FROM A 
	LEFT JOIN B 
	ON A.nid = B.nid;

二、練習題

練習1：假設連鎖店想要增加毛利率超過 50%或者售價低於 800 的貨物的存貨量, 請使用 UNION 對分別滿足上述兩個條件的商品的查詢結果求並集.

SELECT product_id, product_name, product_type, sale_price, purchase_price
	FROM product 
	WHERE sale_price<800
	UNION
	SELECT product_id, product_name, product_type, sale_price, purchase_price
	FROM product
	WHERE sale_price > 1.5 * purchase_price;

練習2：分別使用 UNION 或者 OR 謂詞,找出毛利率不足 30%或毛利率未知的商品

SELECT *
	FROM product 
	WHERE sale_price / purchase_price < 1.3
	UNION
	SELECT * 
	FROM product 
	WHERE sale_price / purchase_price IS NULL;

練習3：找出 product 和 product2 中售價高於 500 的商品的基本資訊

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

練習4：商店決定對product表中利潤低於50%和售價低於1000的商品提價, 請使用UNION ALL 語句將分別滿足上述兩個條件的結果取並集

SELECT *
	FROM product 
	WHERE sale_price < 1000
	UNION ALL
	SELECT * 
	FROM product
	WHERE sale_price < 1.5 * purchase_price;

練習5：使用 SYSDATE()函式可以返回當前日期時間, 是一個日期時間型別的資料, 試測試該資料型別和數值,字串等型別的相容性

SELECT SYSDATE(), SYSDATE(), SYSDATE()
	UNION
	SELECT 'chen', 1234,  NULL;

練習6：找出只存在於product表但不存在於product2表的商品

SELECT * 
	FROM product
	WHERE product_id 
	NOT IN (SELECT product_id 
			FROM product2);

練習7： 使用NOT謂詞進行集合的減法運算, 求出product表中, 售價高於2000，且利潤不低於30%的商品

SELECT * 
	FROM product
	WHERE sale_price > 2000 
	AND product_id NOT IN (SELECT product_id 
                           FROM product 
                           WHERE sale_price < 1.3 * purchase_price);

練習8：使用AND謂詞查詢product表中利潤率高於50%,並且售價低於1500的商品

SELECT * 
	FROM product
	WHERE sale_price > 1.5 * purchase_price 
	AND sale_price < 1500;

練習9：使用product表和product2表的對稱差來查詢哪些商品只在其中一張表

SELECT * 
	FROM 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);

練習10：藉助上述對稱差的實現方式, 求product和product2的交

SELECT * 
	FROM product
	WHERE product_id IN (SELECT product_id FROM product2)

練習11：找出每個商店裡的衣服類商品的名稱及價格等資訊. 希望得到如下結果

SELECT  SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.product_type, P.purchase_price
  FROM shopproduct  AS SP 
  INNER JOIN product AS P 
  ON SP.product_id = P.product_id
  WHERE P.product_type = '衣服';

練習12：分別使用連結兩個子查詢和不使用子查詢的方式, 找出東京商店裡, 售價低於 2000 的商品資訊,希望得到如下結果：

-- 不使用子查詢
SELECT SP.*, P.*
	FROM shopproduct AS SP 
	INNER JOIN product AS P 
    ON SP.product_id = P.product_id
    WHERE shop_id = '000A'
    AND sale_price < 2000;

-- 使用子查詢
SELECT *
	FROM shopproduct AS SP
	INNER JOIN (SELECT *
	FROM product
	WHERE sale_price < 2000) AS P
	ON SP.product_id = P.product_id
	WHERE SP.shop_id = '000A';