SQL每日一练——第12天：高级数据过滤

1. 检索供应商名称

题目：

建表语句：

DROP TABLE IF EXISTS `Vendors`;
CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_name` VARCHAR(255) NOT NULL COMMENT 'vend名称',
  `vend_country` VARCHAR(255) NOT NULL COMMENT 'vend国家',
  `vend_state` VARCHAR(255) NOT NULL COMMENT 'vend州'
);
INSERT INTO `Vendors` VALUES ('apple','USA','CA'),
('vivo','CNA','shenzhen'),
('huawei','CNA','xian');

解题答案：

select vend_name
from Vendors
where vend_country='USA'
and vend_state='CA'

2. 检索并列出已订购产品的清单

题目：

建表语句：

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(

	prod_id VARCHAR(255) NOT NULL COMMENT '商品号',
	order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
	quantity INT(255) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('BR01','a1','105'),('BR02','a2','1100'),('BR02','a2','200'),('BR03','a4','1121'),('BR017','a5','10'),('BR02','a2','19'),('BR017','a7','5')

解题答案：

SELECT order_num, prod_id, quantity
FROM OrderItems
WHERE quantity >= 100 AND prod_id REGEXP 'BR01'|'BR02'|'BR03'

3. 返回所有价格在 3美元到 6美元之间的产品的名称和价格

题目：

建表语句：

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品名称',
`prod_price` INT(255) NOT NULL COMMENT '产品价格'
);
INSERT INTO `Products` VALUES ('a0011','egg',3),
('a0019','sockets',4),
('b0019','coffee',15);

解题答案：

select prod_name,
        prod_price
from Products
where prod_price>=3 and prod_price<=6
order by prod_price

4. 纠错2

题目：

建表语句：

DROP TABLE IF EXISTS `Vendors`;
CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_name` VARCHAR(255) NOT NULL COMMENT 'vend名称',
  `vend_country` VARCHAR(255) NOT NULL COMMENT 'vend国家',
  `vend_state` VARCHAR(255) NOT NULL COMMENT 'vend州'
);
INSERT INTO `Vendors` VALUES ('apple','USA','CA'),
('beef noodle king','USA','CA'),
('vivo','CNA','shenzhen'),
('huawei','CNA','xian');

解题答案：

SELECT vend_name 
FROM Vendors 
WHERE vend_country = 'USA' AND vend_state = 'CA'
ORDER BY vend_name 

运行结果：