SQL语句经典面试题

SQL语句作为基本功，掌握SQL语句对一名测试工程师来说必不可少，今日给大家分享10条SQL经典面试题，记得点赞、转发、分享

SQL脚本：

/*
Navicat MySQL Data Transfer

Source Server         : homework
Source Server Version : 50731
Source Host           : localhost:3306
Source Database       : mianshi

Target Server Type    : MYSQL
Target Server Version : 50731
File Encoding         : 65001

Date: 2023-04-19 09:45:24
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for customers
-- ----------------------------
DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
  `customer_id` int(11) NOT NULL,
  `customer_name` varchar(50) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of customers
-- ----------------------------
INSERT INTO `customers` VALUES ('1', 'Acme Corporation', 'New York');
INSERT INTO `customers` VALUES ('2', 'Global Industries', 'London');
INSERT INTO `customers` VALUES ('3', 'Apex Enterprises', 'Tokyo');

-- ----------------------------
-- Table structure for orders
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `order_id` int(11) NOT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `order_date` date DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('1', '1', '2022-04-01', '1000.00');
INSERT INTO `orders` VALUES ('2', '2', '2022-03-15', '500.00');
INSERT INTO `orders` VALUES ('3', '1', '2022-03-20', '750.00');
INSERT INTO `orders` VALUES ('4', '3', '2022-04-05', '1200.00');
INSERT INTO `orders` VALUES ('5', '2', '2022-02-28', '850.00');

-- ----------------------------
-- Table structure for order_items
-- ----------------------------
DROP TABLE IF EXISTS `order_items`;
CREATE TABLE `order_items` (
  `order_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(11) DEFAULT NULL,
  PRIMARY KEY (`order_id`,`product_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`),
  CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of order_items
-- ----------------------------
INSERT INTO `order_items` VALUES ('1', '1', '2');
INSERT INTO `order_items` VALUES ('1', '3', '1');
INSERT INTO `order_items` VALUES ('2', '2', '5');
INSERT INTO `order_items` VALUES ('3', '1', '1');
INSERT INTO `order_items` VALUES ('3', '3', '3');
INSERT INTO `order_items` VALUES ('4', '4', '1');
INSERT INTO `order_items` VALUES ('4', '5', '2');
INSERT INTO `order_items` VALUES ('5', '2', '10');
INSERT INTO `order_items` VALUES ('5', '4', '2');

-- ----------------------------
-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `product_id` int(11) NOT NULL,
  `product_name` varchar(50) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of products
-- ----------------------------
INSERT INTO `products` VALUES ('1', 'Widget A', '100.00');
INSERT INTO `products` VALUES ('2', 'Widget B', '50.00');
INSERT INTO `products` VALUES ('3', 'Widget C', '75.00');
INSERT INTO `products` VALUES ('4', 'Widget D', '200.00');
INSERT INTO `products` VALUES ('5', 'Widget E', '150.00');

面试题及答案

-- 查询有超过一张订单的顾客姓名和所在城市。
select customers.customer_name,customers.city from customers 
						INNER JOIN orders ON customers.customer_id=orders
						.customer_id GROUP BY orders.customer_id HAVING COUNT(orders.order_id)>1

-- 查询购买了产品 Widget A 或 Widget B 的订单信息，包括订单编号、顾客姓名、产品名称和产品数量
select orders.order_id,customers.customer_name,products.product_name,order_items.quantity
from customers 
          INNER JOIN orders on customers.customer_id=orders.customer_id  
					INNER JOIN order_items on orders.order_id=order_items.order_id
					INNER JOIN products on order_items.product_id = products.product_id
					WHERE products.product_name in ('Widget A','Widget B')
				
-- 查询每个城市的总交易额并按降序排序。
select customers.city,SUM(orders.amount) from orders INNER JOIN customers ON customers.customer_id =orders.customer_id GROUP BY customers.city
ORDER BY SUM(orders.amount) DESC


-- 查询每个顾客的平均订单金额、总订单数量和总交易额，并按总交易额降序排序。
SELECT cu.customer_name,AVG(ord.amount),COUNT(ord.order_id),SUM(ord.amount) FROM customers cu INNER JOIN orders ord
				on cu.customer_id=ord.customer_id  
				GROUP BY cu.customer_id
				ORDER BY SUM(ord.amount)


-- 查询没有完成任何订单的顾客姓名和所在城市
SELECT c.customer_name, c.city
				FROM customers c
				LEFT JOIN orders o ON c.customer_id = o.customer_id
				WHERE o.order_id is NULL

-- 查询 order_items 表中每个商品的销售数量和销售总额，并按销售总额降序排序。
SELECT products.product_name,sum(order_items.quantity),sum(order_items.quantity)*products.price FROM order_items
				INNER JOIN products on order_items.product_id = products.product_id
				GROUP BY order_items.product_id
				ORDER BY sum(order_items.quantity)*products.price


-- 查询 orders 表中总交易额最高的前 5 个订单的信息以及对应的 customer_name。
SELECT c.customer_name, o.amount
				FROM customers c
				LEFT JOIN orders o ON c.customer_id = o.customer_id
				ORDER BY o.amount DESC
				LIMIT 5

-- 查询 orders 表中 order_date 为 2022 年 3 月份的所有记录。
SELECT * FROM orders WHERE  YEAR(order_date)=2022 AND MONTH(order_date)=3

-- 查询 customers 表中的所有记录。
SELECT * FROM customers