SQL每日一练——第14天：创建计算字段、使用函数处理数据

1. 别名

题目：

建表语句：

DROP TABLE IF EXISTS `Vendors`;
CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_id` VARCHAR(255) NOT NULL COMMENT '供应商id',
  `vend_name` VARCHAR(255) NOT NULL COMMENT '供应商名称',
  `vend_address` VARCHAR(255) NOT NULL COMMENT '供应商地址',
  `vend_city` VARCHAR(255) NOT NULL COMMENT '供应商城市'
);
INSERT INTO `Vendors` VALUES ('a001','tencent cloud','address1','shenzhen'),
('a002','huawei cloud','address2','dongguan'),
('a003','aliyun cloud','address3','alibaba');

解题答案：

select
vend_id 
,vend_name as vname
,vend_address as vaddress
,vend_city as vcity
from Vendors 
order by vname 

2. 打折

题目：

建表语句：

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_price` DOUBLE NOT NULL COMMENT '产品价格'
);
INSERT INTO `Products` VALUES ('a0011',9.49),
('a0019',600),
('b0019',1000);

解题答案：

select prod_id, prod_price, prod_price * 0.9 sale_price
from Products

3. 顾客登录名

题目：

建表语句：

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
	cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名',
	cust_contact VARCHAR(255) NOT NULL COMMENT '客户联系人',
	cust_city VARCHAR(255) NOT NULL COMMENT '客户城市'
);
INSERT `Customers` VALUES ('a1','Andy Li','Andy Li','Oak Park'),('a2','Ben Liu','Ben Liu','Oak Park'),('a3','Tony Dai','Tony Dai','Oak Park'),('a4','Tom Chen','Tom Chen','Oak Park'),('a5','An Li','An Li','Oak Park'),('a6','Lee Chen','Lee Chen','Oak Park'),('a7','Hex Liu','Hex Liu','Oak Park');

解题答案：

select
cust_id
,cust_name
,upper(concat(substr(cust_contact,1,2),substr(cust_city,1,3))) as user_login
from Customers

4. 返回 2020 年 1 月的所有订单的订单号和订单日期

题目：

建表语句：

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
	order_num VARCHAR(255) NOT NULL COMMENT '订单号',
	order_date TIMESTAMP NOT NULL COMMENT '订单日期'
);
INSERT `Orders` VALUES ('a0001','2020-01-01 00:00:00'),
('a0002','2020-01-02 00:00:00'),
('a0003','2020-01-01 12:00:00'),
('a0004','2020-02-01 00:00:00'),
('a0005','2020-03-01 00:00:00');

解题答案：

select order_num, order_date
from Orders
where order_date > '2019-12-31' and order_date < '2020-02-01'
order by order_date asc

运行结果：