经典sql查询(一)

员工表结构如下：

CREATE TABLE `emp`  (
  `EMPNO` int(0) NOT NULL AUTO_INCREMENT COMMENT '员工号',
  `ENAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '员工姓名',
  `JOB` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '工作',
  `MGR` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '上级编号',
  `HIREDATE` datetime(0) NULL DEFAULT NULL COMMENT '雇佣日期',
  `SAL` int(0) NULL DEFAULT NULL COMMENT '薪金',
  `COMM` int(0) NULL DEFAULT NULL COMMENT '佣金',
  `DEPTNO` int(0) NULL DEFAULT NULL COMMENT '部门编号',
  PRIMARY KEY (`EMPNO`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '员工表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (1, 'SMITH', 'CLERK', '12', '1980-12-17 00:00:00', 800, NULL, 20);
INSERT INTO `emp` VALUES (2, 'ALLEN', 'SALESMAN', '1', '1981-02-20 00:00:00', 1600, 300, 30);
INSERT INTO `emp` VALUES (3, 'WARD', 'SALESMAN', '1', '1981-02-20 00:00:00', 1250, 500, 30);
INSERT INTO `emp` VALUES (4, 'JONES', 'MANAGER', '1', '1981-04-02 00:00:00', 2975, NULL, 20);
INSERT INTO `emp` VALUES (5, 'MARTIN', 'SALESMAN', '1', '1981-09-28 00:00:00', 1250, 1400, 30);
INSERT INTO `emp` VALUES (6, 'BLAKE', 'MANAGER', '1', '1981-05-01 00:00:00', 2850, NULL, 30);
INSERT INTO `emp` VALUES (7, 'CLARK', 'MANAGER', '1', '1981-06-09 00:00:00', 2450, NULL, 10);
INSERT INTO `emp` VALUES (8, 'SCOTT', 'ANALYST', '1', '1987-04-19 00:00:00', 4000, NULL, 20);
INSERT INTO `emp` VALUES (9, 'KING', 'PRESIDENT', 1, '1981-11-17 00:00:00', 5000, NULL, 10);
INSERT INTO `emp` VALUES (10, 'TURNER', 'SALESMAN', '8', '1981-09-08 00:00:00', 1500, 0, 30);
INSERT INTO `emp` VALUES (11, 'ADAMS', 'CLERK', '10', '1987-05-23 00:00:00', 1100, NULL, 20);
INSERT INTO `emp` VALUES (12, 'JAMES', 'CLERK', '5', '1981-12-03 00:00:00', 950, NULL, 30);
INSERT INTO `emp` VALUES (13, 'FORD', 'ANALYST', '7', '1981-12-03 00:00:00', 3000, NULL, 20);
INSERT INTO `emp` VALUES (14, 'MILLLER', 'CLERK', '7', '1982-01-23 00:00:00', 1300, NULL, 10);
INSERT INTO `emp` VALUES (15, 'EricHu', 'Developer', '1', '2011-05-26 00:00:00', 5500, 14, 10);
INSERT INTO `emp` VALUES (16, 'huyong', 'PM', '1', '2011-05-26 00:00:00', 5500, 14, 10);
INSERT INTO `emp` VALUES (17, 'WANGJING', 'Developer', '1', '2011-05-26 00:00:00', 5500, 14, 10);

 

 部门表结构如下：

 

CREATE TABLE `dept`  (
  `DEPTNO` int(0) NOT NULL AUTO_INCREMENT COMMENT '部门编号',
  `DNAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '部门名称',
  `LOC` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '地点',
  PRIMARY KEY (`DEPTNO`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES (40, 'OPERATIONS', 'BOSTON');
INSERT INTO `dept` VALUES (50, '50abc', '50def');
INSERT INTO `dept` VALUES (60, 'Developer', 'HaiKou');

提示：工资=薪金+佣金

用SQL完成以下问题列表

1、列出至少有一个员工的所有部门

SELECT DNAME FROM dept WHERE DEPTNO IN(SELECT DEPTNO FROM emp)

或者

SELECT DNAME FROM dept WHERE DEPTNO IN(SELECT DEPTNO FROM emp GROUP BY DEPTNO HAVING count(DEPTNO)>=1)

或者

 

SELECT DNAME FROM dept A WHERE EXISTS (SELECT NULL FROM EMP B WHERE B.DEPTNO=A.DEPTNO);

 

 2、列出薪金比“SMITH”多的所有员工

 

SELECT * FROM emp WHERE SAL>(SELECT SAL FROM emp WHERE ENAME='SMITH')

或者

SELECT * FROM EMP A WHERE EXISTS(SELECT NULL FROM emp B WHERE B.SAL<A.SAL AND B.ENAME='SMITH')

或者

SELECT * FROM emp A,(SELECT SAL AS SALARY FROM EMP WHERE ENAME='SMITH') B WHERE A.SAL>B.SALARY;

3、 列出所有员工的姓名及其直接上级的姓名

SELECT A.ENAME,(SELECT ENAME FROM emp b WHERE B.EMPNO=A.MGR) AS BOSS_NAME from emp A

或者

SELECT e.ENAME,m.ENAME BOSS_NAME from emp e LEFT JOIN emp m ON e.MGR=m.EMPNO