SQL21 打折

描述

我们的示例商店正在进行打折促销,所有产品均降价 10%。Products 表包含 prod_id产品 id、prod_price 产品价格

问题

编写 SQL语句,从 Products 表中返回 prod_id、prod_price 和 sale_price。sale_price 是一个包含促销价格的计算字段。提示:可以乘以 0.9,得到原价的 90%(即 10%的折扣)

示例结果

返回产品 id prod_id、产品价格 prod_price、销售价格 sale_price

prod_id prod_price sale_price
a0011 9.49 8.541
a0019 600 540
b0019 1000 900

示例解析

sale_price 的价格是 prod_price 的 90%

示例

  1. DROP TABLE IF EXISTS `Products`;
  2. CREATE TABLE IF NOT EXISTS `Products` (
  3. `prod_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',
  4. `prod_price` DOUBLE NOT NULL COMMENT '产品价格'
  5. );
  6. INSERT INTO `Products` VALUES ('a0011',9.49),
  7. ('a0019',600),
  8. ('b0019',1000);

解答

此题考察了取别名以及如何在 SQL 中直接使用算数运算,sale_price 不是表中的数据,而是通过 prod_price 而来,所以需要通过关键字 AS 来进行取别名,最终的实现语句如下。

  1. SELECT prod_id, prod_price, prod_price * 0.9 AS sale_price FROM Products;

SQL22 顾客登录名

描述

我们的商店已经上线了,正在创建顾客账户。所有用户都需要登录名,默认登录名是其名称和所在城市的组合。

给出 Customers 表 如下:

cust_id cust_name cust_contact cust_city
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

问题

编写 SQL 语句,返回顾客 ID(cust_id)、顾客名称(cust_name)和登录名(user_login),其中登录名全部为大写字母,并由顾客联系人的前两个字符(cust_contact)和其所在城市的前三个字符(cust_city)组成。提示:需要使用函数、拼接和别名。

示例结果

返回顾客 id cust_id,顾客名称 cust_name,顾客登录名 user_login

cust_id cust_name user_login
a1 Andy Li ANOAK
a2 Ben Liu BEOAK
a3 Tony Dai TOOAK
a4 Tom Chen TOOAK
a5 An Li ANOAK
a6 Lee Chen LEOAK
a7 Hex Liu HEOAK

示例解析

例如,登录名是 ANOAK(Andy Li,居住在 Oak Park)

示例

  1. DROP TABLE IF EXISTS `Customers`;
  2. CREATE TABLE IF NOT EXISTS `Customers`(
  3. cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
  4. cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名',
  5. cust_contact VARCHAR(255) NOT NULL COMMENT '客户联系人',
  6. cust_city VARCHAR(255) NOT NULL COMMENT '客户城市'
  7. );
  8. 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');

解答

根据题意以及示例结果,可以看到登录名全是大写,所以需要用到函数 upper(),然后需要将顾客联系人前两个字符和所在城市的前三个字符相拼接,则需要用到函数 concat(),再接着就是需要从 cust_namecust_city 中截取字符串,则需要用到 substring(),最后则是通过将字符串拼接转换之后取别名为 user_login,需要用到关键字 AS。以上提到的三个函数用法如下:

  • upper(字符串):将字符串中所有字符转换为大写。
  • substring(字符串, 起始位置, 截取的字符数),需要注意的是起始位置是从 1 开始的。
  • concat(字符串 1, 字符串 2, 字符串 3, …)
  1. SELECT cust_id, cust_name, upper(concat(substring(cust_name, 1, 2), substring(cust_city, 1, 3))) AS user_login FROM Customers;

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

描述

Orders 订单表

order_num order_date
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

问题

编写 SQL 语句,返回 2020 年 1 月的所有订单的订单号(order_num)和订单日期(order_date),并按订单日期升序排序

【示例结果】

返回订单号 order_num,和 order_date 订单时间

order_num order_date
a0001 2020-01-01 00:00:00
a0003 2020-01-01 12:00:00
a0002 2020-01-02 00:00:00

示例解析

a0001、a0002、a0003 时间属于 2020 年 1 月

示例

  1. DROP TABLE IF EXISTS `Orders`;
  2. CREATE TABLE IF NOT EXISTS `Orders`(
  3. order_num VARCHAR(255) NOT NULL COMMENT '订单号',
  4. order_date TIMESTAMP NOT NULL COMMENT '订单日期'
  5. );
  6. INSERT `Orders` VALUES ('a0001','2020-01-01 00:00:00'),
  7. ('a0002','2020-01-02 00:00:00'),
  8. ('a0003','2020-01-01 12:00:00'),
  9. ('a0004','2020-02-01 00:00:00'),
  10. ('a0005','2020-03-01 00:00:00');

解答

其实我们依然可以将本题看做模糊查询,只要订单日期满足对应条件即可,可以使用关键字 WHERE … LIKE … 来实现。

  1. SELECT order_num, order_date FROM Orders WHERE order_date LIKE '2020-01%' ORDER BY order_date;

此外,SQL 中也提供了对于日期的操作函数 date_format(),用于返回日期的一部分。

  1. SELECT order_num, order_date FROM Orders WHERE date_format(order_date, '%Y-%m') = '2020-01' ORDER BY order_date;

另外,我们还可以单独提取出日期中的年份和月份,然后将两个条件串联,筛选出同时满足两个条件的数据。

  1. SELECT order_num, order_date FROM Orders WHERE year(order_date) = 2020 AND month(order_date) = 1 ORDER BY order_date;

SQL24 确定已售出产品的总数

描述

OrderItems 表代表售出的产品,quantity 代表售出商品数量。

quantity
10
100
1000
10001
2
15

问题

编写 SQL 语句,确定已售出产品的总数。

示例结果

返回 items_ordered 列名,表示已售出商品的总数。

items_ordered
11128

示例

  1. DROP TABLE IF EXISTS `OrderItems`;
  2. CREATE TABLE IF NOT EXISTS `OrderItems`(
  3. quantity INT(16) NOT NULL COMMENT '商品数量'
  4. );
  5. INSERT `OrderItems` VALUES (10),(100),(1000),(10001),(2),(15);

解答

考察求和函数以及取别名两个知识点,取别名需要关键字 AS,而且可要可不要。

而求和函数则是 SUM(列名) ,它会统计列中所有记录的综合。

  1. SELECT SUM(quantity) items_ordered FROM OrderItems;

SQL25 确定已售出产品项 BR01 的总数

描述

OrderItems 表代表售出的产品,quantity 代表售出商品数量,产品项为 prod_id。

quantity prod_id
10 AR01
100 AR10
1000 BR01
10001 BR010

问题

修改创建的语句,确定已售出产品项(prod_id)为 “BR01” 的总数。

示例结果

返回商品项已订购订单数

items_ordered
1000

示例解析

已订购商品 BR01 的数量 quantity 为 1000。

示例

  1. DROP TABLE IF EXISTS `OrderItems`;
  2. CREATE TABLE IF NOT EXISTS `OrderItems`(
  3. quantity INT(16) NOT NULL COMMENT '商品数量',
  4. prod_id VARCHAR(255) NOT NULL COMMENT '商品项'
  5. );
  6. INSERT `OrderItems` VALUES (10,'AR01'),(100,'AR10'),(1000,'BR01'),(10001,'BR010');

解答

主要考察的知识点:

  • 求和函数:SUM()
  • 取别名:AS
  • 条件查询:WHERE

需要注意的是 SQL 语句中关键字的先后顺序,否则可能会导致语句出错。

  1. SELECT SUM(quantity) AS items_ordered FROM OrderItems WHERE prod_id = 'BR01';

致谢

感谢牛客网提供的题目列表。