Datawhale SQL 02

目錄

• • 2.1
  • 2.2
  • 2.3
  • 2.4
  • 2.5
  • 2.6
  • 2.7

2.1

SELECT product_name,regist_date
FROM product
WHERE regist > '2009-4-28';
           

2.2

1. 傳回購買價格為空的所有記錄
2. 傳回購買價格不為空的所有記錄
3. 傳回空清單

2.3

SELECT product_name,sale_price,purchase_price
 FROM product
 WHERE sale_price>=purchase_price+500;

           

SELECT product_name, sale_price, purchase_price 
 FROM product
 WHERE sale_price-500>=purchase_price;

           

2.4

SELECT product_name, product_type, (sale_price*0.9-purchase_price) as profit
FROM product
WHERE (sale_price*0.9-purchase_price)>100 AND (product_type='辦公用品' OR '廚房用具');
           

2.5

錯誤包括：

1、SUM後面使用了中文括号

2、product_name列的類型為str，不能使用SUM函數（值為0）

3、WHERE子句應該放在SELCET後面，GROUP BY

4、注釋符後面缺少空格

• SELECT product_id 将得到每一組第一列的值，但無意義。最好替換為聚合鍵product_type。

2.6

SELECT product_type, SUM(sale_price) AS sum, SUM(purchase_price) AS sum
	FROM product
	GROUP BY product_type
	HAVING  SUM(sale_price) > 1.5*SUM(purchase_price);
           

2.7

SELECT *
 FROM product
 ORDER BY -regist_date, purchase_price