SQL查询测试6

 
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- 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 departments
-- ----------------------------
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments`  (
  `DepartmentID` int(0) NOT NULL AUTO_INCREMENT,
  `DepartmentName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`DepartmentID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of departments
-- ----------------------------
INSERT INTO `departments` VALUES (1, 'HR');
INSERT INTO `departments` VALUES (2, 'Finance');
INSERT INTO `departments` VALUES (3, 'Engineering');
INSERT INTO `departments` VALUES (4, 'Sales');
INSERT INTO `departments` VALUES (5, 'Marketing');
INSERT INTO `departments` VALUES (6, 'IT');
INSERT INTO `departments` VALUES (7, 'Operations');
INSERT INTO `departments` VALUES (8, 'Customer Service');
INSERT INTO `departments` VALUES (9, 'Research and Department');
INSERT INTO `departments` VALUES (10, 'Legal');
INSERT INTO `departments` VALUES (11, 'Quality Assurance');

SET FOREIGN_KEY_CHECKS = 1;

此查询会找出什么？
SELECT name, COUNT(*) FROM employees GROUP BY name HAVING COUNT(*) > 1;

这条语句会返回什么？
SELECT e.name FROM employees e LEFT JOIN departments d ON e.department = d.DepartmentName WHERE d.dept_id IS NULL;

此查询会返回什么结果？
SELECT department, MAX(salary) FROM employees GROUP BY department;

如果两名员工的薪水相同，会发生什么？
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees;

以下查询中，如果 salary = 55000，category 是什么？

SELECT name,
       CASE 
           WHEN salary >= 50000 THEN 'High'
           WHEN salary >= 30000 THEN 'Medium'
           ELSE 'Low'
       END AS category
FROM employees;