（课后练习题）Task02：SQL基础查询与排序-天池龙珠计划SQL训练营

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql 

目录

练习题-第一部分

练习题1

练习题2

①

②

③

练习题3

练习题4

练习题-第二部分

练习题5

练习题6

练习题7

---

本课程用表插入数据sql如下：

- DML ：插入数据
STARTTRANSACTION;
INSERT INTO product VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO product VALUES('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO product VALUES('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO product VALUES('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO product VALUES('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO product VALUES('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO product VALUES('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO product VALUES('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
COMMIT;

0001	T恤衫	衣服	1000	500	2009-09-20
0002	打孔器	办公用品	500	320	2009-09-11
0003	运动T恤	衣服	4000	2800
0004	菜刀	厨房用具	3000	2800	2009-09-20
0005	高压锅	厨房用具	6800	5000	2009-01-15
0006	叉子	厨房用具	500		2009-09-20
0007	擦菜板	厨房用具	880	790	2008-04-28
0008	圆珠笔	办公用品	100		2009-11-11

练习题-第一部分

练习题1

编写一条SQL语句，从product（商品）表中选取出“登记日期（regist在2009年4月28日之后”的商品，查询结果要包含product_name和regist_date两列。

解：

选取，然后查询，即SELECT语句：

SELECT <列名>, ……
  FROM <表名>
 WHERE <条件表达式>;

sql如何比较时间类型？答案是像数值类型那样比较：

运算符	含义
=	和~相等
<>	和~不相等
>=	大于等于~
>	大于~
<=	小于等于~
<	小于~

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

运行结果如下：

练习题2

请说出对product 表执行如下3条SELECT语句时的返回结果。

①

SELECT *
  FROM product
 WHERE purchase_price = NULL;

运行结果为： 

 

②

SELECT *
  FROM product
 WHERE purchase_price <> NULL;

运行结果为： 

③

SELECT *
  FROM product
 WHERE product_name > NULL;

运行结果为：

 

 为什么会这样空的呢？看来空值NULL不能用运算符进行比较。

所以要这样写：

或者： 

 

练习题3

代码清单2-22（2-2节）中的SELECT语句能够从product表中取出“销售单价（saleprice）比进货单价（purchase price）高出500日元以上”的商品。请写出两条可以得到相同结果的SELECT语句。执行结果如下所示。

product_name | sale_price | purchase_price 
-------------+------------+------------
T恤衫         | 　 1000    | 500
运动T恤       |    4000    | 2800
高压锅        |    6800    | 5000

解：

 第一种：

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 ;

运行结果：

 

练习题4

请写出一条SELECT语句，从product表中选取出满足“销售单价打九折之后利润高于100日元的办公用品和厨房用具”条件的记录。查询结果要包括product_name列、product_type列以及销售单价打九折之后的利润（别名设定为profit）。

提示：销售单价打九折，可以通过saleprice列的值乘以0.9获得，利润可以通过该值减去purchase_price列的值获得。

解：

 “销售单价打九折之后利润高于100日元” = “sale_price*0.9 - purchase_price  as profit" ,"profit >100"

这样的写法报错了，错在哪呢？百思不得其解。

看了看答案后，才明白SELECT和AS并非类似其他编程语言定义函数的功能，所以要这样写： 

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 product_type = '厨房用具');

这样就正常运行了：

 

练习题-第二部分

练习题5

请指出下述SELECT语句中所有的语法错误。

SELECT product_id, SUM（product_name）
--本SELECT语句中存在错误。
  FROM product 
 GROUP BY product_type 
 WHERE regist_date > '2009-09-01';

解：

运行后，可以看到报错：

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE regist_date > '2009-09-01'' at line 5   
 这里引用教程：

1. GROUP BY书写位置

GROUP BY的子句书写顺序有严格要求，不按要求会导致SQL无法正常执行，目前出现过的子句书写顺序为：

1**.**SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

其中前三项用于筛选数据，GROUP BY对筛选出的数据进行处理

2. 在WHERE子句中使用GROUP BY

SELECT purchase_price, COUNT(*)
  FROM product
 WHERE product_type = '衣服'
 GROUP BY purchase_price;

3. 常见错误

在使用聚合函数及GROUP BY子句时，经常出现的错误有：

1. 在聚合函数的SELECT子句中写了聚合健以外的列 使用COUNT等聚合函数时，SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键）。
2. 在GROUP BY子句中使用列的别名 SELECT子句中可以通过AS来指定别名，但在GROUP BY中不能使用别名。因为在DBMS中 ,SELECT子句在GROUP BY子句后执行。
3. 在WHERE中使用聚合函数 原因是聚合函数的使用前提是结果集已经确定，而WHERE还处于确定结果集的过程中，所以相互矛盾会引发错误。 如果想指定条件，可以在SELECT，HAVING（下面马上会讲）以及ORDER BY子句中使用聚合函数。

可见有三个错误：

1. 书写顺序错误，应该交换一下WHERE和GROUP BY的位置

2. 在聚合函数的SELECT子句中写了聚合健以外的列。使用SUM聚合函数时，SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键）。

3.SUM(product_name)，product_name 是字符串类型，应该填入数值类型才能计算，又或者使用COUNT来计数

练习题6

请编写一条SELECT语句，求出销售单价（sale_price列）合计值大于进货单价（purchase_price列）合计值1.5倍的商品种类。执行结果如下所示。

product_type | sum  | sum 
-------------+------+------
衣服         | 5000 | 3300
办公用品      |  600 | 320

解： 

正如上文所示，想要在限定条件中使用聚合函数，就不能使用WHERE，而使用HAVING：

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

运行结果正确： 

练习题7

此前我们曾经使用SELECT语句选取出了product（商品）表中的全部记录。当时我们使用了ORDERBY子句来指定排列顺序，但现在已经无法记起当时如何指定的了。请根据下列执行结果，思考ORDERBY子句的内容。

解：

这样写就非常接近了，但还差点：

怎样写才能把运动T恤置顶呢？想了想还是看看答案 ：

SELECT *
  FROM product 
ORDER BY  regist_date DESC,sale_price;

然而与答案写法并无区别，可能是版本的问题？