精选 SQL 问题及解答2

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for sales

-- ----------------------------

DROP TABLE IF EXISTS `sales`;

CREATE TABLE `sales`  (
  `SaleID` int(0) NOT NULL,
  `EmployeeID` int(0) NULL DEFAULT NULL,
  `Department` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `Sales` decimal(10, 2) NULL DEFAULT NULL,
  `Date` date NULL DEFAULT NULL,
  PRIMARY KEY (`SaleID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sales
-- ----------------------------
INSERT INTO `sales` VALUES (1, 101, 'Electronics', 500.00, '2023-08-01');
INSERT INTO `sales` VALUES (2, 102, 'Electronics', 300.00, '2023-08-03');
INSERT INTO `sales` VALUES (3, 101, 'Furniture', 150.00, '2023-08-02');
INSERT INTO `sales` VALUES (4, 103, 'Electronics', 250.00, '2023-08-04');
INSERT INTO `sales` VALUES (5, 104, 'Furniture', 200.00, '2023-08-02');
-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `id` int(0) NULL DEFAULT NULL,
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `department` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `salary` int(0) NULL DEFAULT NULL,
  `manager_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (1, 'Aditi', 'HR', 30000, 5);
INSERT INTO `employees` VALUES (2, 'Rahul', 'IT', 50000, 6);
INSERT INTO `employees` VALUES (3, 'Neha', 'IT', 60000, 6);
INSERT INTO `employees` VALUES (4, 'Aman', 'Sales', 40000, 7);
INSERT INTO `employees` VALUES (5, 'Kiran', 'HR', 70000, 0);
INSERT INTO `employees` VALUES (6, 'Mohit', 'IT', 80000, 0);
INSERT INTO `employees` VALUES (7, 'Suresh', 'Sales', 65000, 0);
INSERT INTO `employees` VALUES (8, 'Pooja', 'HR', 30000, 5);
-- ----------------------------
-- Table structure for customers
-- ----------------------------
DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers`  (
  `customer_id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NOT NULL,
  `email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `phone` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  PRIMARY KEY (`customer_id`) USING BTREE,
  UNIQUE INDEX `email`(`email`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of customers
-- ----------------------------
INSERT INTO `customers` VALUES (1, 'John Doe', 'john@example.com', '1234567890');
INSERT INTO `customers` VALUES (2, 'Jane Smith', 'jane@example.com', '0987654321');
INSERT INTO `customers` VALUES (5, 'Blau Hoss', 'Blau@example.com', '11111111');

SET FOREIGN_KEY_CHECKS = 1;

问题描述：按日期计算销售额的累计总计。

解决方案

SELECT date, sales, SUM(sales) OVER(ORDER BY date) AS running_total FROM sales;

问题描述：找出薪资高于平均水平的员工。

解决方案

SELECT name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees );

问题描述：找出总薪资最高的部门。

解决方案

SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department ORDER BY total_salary DESC LIMIT 1;

问题描述：找出至少下过一单的客户。

解决方案

SELECT name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE c.customer_id = o.customer_id );

问题描述：删除重复记录，但保留其中一条唯一记录。

解决方案

DELETE FROM employees WHERE id NOT IN ( SELECT MIN(id) FROM employees GROUP BY name );