文章目录
1. 确定已售出产品的总数
题目:
建表语句:
DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
quantity INT(16) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES (10),(100),(1000),(10001),(2),(15);
解题答案:
select sum(quantity) as items_ordered
from OrderItems
2. 确定已售出产品项 BR01 的总数
题目:
建表语句:
select sum(quantity) as items_ordered
from OrderItems where prod_id="BR01"
解题答案:
select prod_id, prod_price, prod_price * 0.9 sale_price
from Products
3. 确定 Products 表中价格不超过 10 美元的最贵产品的价格
题目:
建表语句:
select max(prod_price) max_price
from Products
where prod_price <= 10
解题答案:
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
运行结果: