Mysql 子查询练习

importFile:

/*
 Navicat Premium Data Transfer

 Source Server         : MySQL 8.0.28
 Source Server Type    : MySQL
 Source Server Version : 80028
 Source Host           : localhost:3306
 Source Schema         : dbtest5

 Target Server Type    : MySQL
 Target Server Version : 80028
 File Encoding         : 65001

 Date: 10/04/2023 20:11:23
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `deptno` int NOT NULL,
  `dname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`deptno`) USING BTREE,
  UNIQUE INDEX `dname`(`dname`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (10, '总裁办');
INSERT INTO `dept` VALUES (20, '研究院');
INSERT INTO `dept` VALUES (40, '运营部');
INSERT INTO `dept` VALUES (30, '销售部');

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (
  `empno` int NOT NULL,
  `ename` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `job` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `mgr` int NULL DEFAULT NULL,
  `sal` decimal(7, 2) NULL DEFAULT NULL,
  `comm` decimal(7, 2) NULL DEFAULT NULL,
  `deptno` int NULL DEFAULT NULL,
  PRIMARY KEY (`empno`) USING BTREE,
  UNIQUE INDEX `ename`(`ename`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (9369, '张三', '保洁', 9902, 900.00, NULL, 20);
INSERT INTO `emp` VALUES (9499, '孙七', '销售', 9698, 2600.00, 300.00, 30);
INSERT INTO `emp` VALUES (9521, '周八', '销售', 9698, 2250.00, 500.00, 30);
INSERT INTO `emp` VALUES (9566, '李四', '经理', 9839, 3995.00, NULL, 20);
INSERT INTO `emp` VALUES (9654, '吴九', '销售', 9698, 2250.00, 1400.00, 30);
INSERT INTO `emp` VALUES (9839, '刘一', '董事长', NULL, 6000.00, NULL, 10);
INSERT INTO `emp` VALUES (9844, '郑十', '销售', 9698, 2500.00, 0.00, 30);
INSERT INTO `emp` VALUES (9900, '萧十一', '保洁', 9698, 1050.00, NULL, 30);
INSERT INTO `emp` VALUES (9902, '赵六', '分析员', 9566, 4000.00, NULL, 20);
INSERT INTO `emp` VALUES (9936, '张%一', '保洁', 9982, 1200.00, NULL, NULL);
INSERT INTO `emp` VALUES (9982, '陈二', '经理', 9839, 3450.00, NULL, 10);
INSERT INTO `emp` VALUES (9988, '王五', '分析员', 9566, 4000.00, NULL, 20);

SET FOREIGN_KEY_CHECKS = 1;

#题目1：查询工资大于9566号员工工资的员工的信息
SELECT* FROM emp
WHERE sal>(SELECT sal FROM emp where empno=9566)
#题目2：返回job与9499号员工相同，sal比9844号员工多的员工姓名，job和工资
SELECT ename,job,sal
FROM emp
WHERE job=(SELECT job FROM emp where empno=9499)
		  And sal>(SELECT sal FROM emp WHERE empno=9844);

#题目3：返回公司工资最少的员工的ename,job和sal
SELECT ename,job,sal
FROM emp
where sal=(select MIN(sal) FROM emp)


#题目4：查询与9499号员工的mgr和deptno相同的其他员工的empno，mgr，deptno。
SELECT empno,mgr,deptno
FROM emp
WHERE mgr=(SELECT mgr FROM emp WHERE empno=9499)

#题目5：查询最低工资大于20号部门最低工资的部门id和其最低工资
SELECT deptno,MIN(sal)
FROM emp
GROUP BY deptno 
HAVING MIN(sal)>(SELECT MIN(sal) FROM emp WHERE deptno=20)


#题目6：查询每个部门的最低工资的员工编号和员工姓名
SELECT MIN(sal),empno,ename FROM emp WHERE deptno is NOT NULL GROUP BY deptno


#题目7：返回其它job中比job为‘经理’任一工资低的员工的员工号、姓名、job 以及sal
SELECT empno,ename,job,sal
FROM emp
WHERE job<>"经理" AND sal<ANY(SELECT sal FROM emp WHERE job="经理")

#题目8：返回其它job中比job为‘经理’的所有工资低的员工的员工编号、员工姓名、job 以及sal
SELECT empno,ename,job,sal
FROM emp
WHERE job<>"经理" AND sal<ALL(SELECT sal FROM emp WHERE job="经理")


#题目9：查询员工中工资大于公司平均工资的员工的ename,sal和其deptno
SELECT ename,sal,deptno
FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp)

#题目10：查询员工中工资大于本部门平均工资的员工的ename,sal和其deptno
SELECT ename,sal
FROM emp e1
WHERE sal>(SELECT AVG(sal)FROM emp e2 WHERE deptno=e1.deptno)

#题目11：查询员工的empno,sal,deptno,按照dname 排序
SELECT empno,sal,deptno 
FROM emp e
ORDER BY
(SELECT deptno FROM dept d where e.deptno=d.deptno)

#题目12：查询公司管理者的empno，ename，job，deptno信息
 #方式①：使用自连接
SELECT DISTINCT e1.empno,e1.ename,e1.job,e1.deptno
 FROM emp e1 JOIN emp e2
 WHERE e1.empno=e2.mgr

 #方式②：使用子查询
SELECT empno,ename,job,deptno
FROM emp
WHERE 
empno in(SELECT DISTINCT mgr FROM emp)

 #方式③：使用EXISTS
SELECT empno,ename,job,deptno
FROM emp e1
WHERE EXISTS (SELECT * FROM emp e2 WHERE e2.mgr=e1.empno)

#题目13：查询dept表中，不存在于emp表中的部门的deptno和dname
SELECT deptno,dname
FROM dept d
WHERE NOT EXISTS (SELECT deptno FROM emp e WHERE d.deptno=e.deptno)

题目：

使用CREATE DATABASE语句创建字符集为utf8mb4的数据库zhangchulan_dbtest5，（zhangchulan修改为自己姓名全拼）

#题目1：查询工资大于9566号员工工资的员工的信息

 #题目2：返回job与9499号员工相同，sal比9844号员工多的员工姓名，job和工资

#题目3：返回公司工资最少的员工的ename,job和sal

#题目4：查询与9499号员工的mgr和deptno相同的其他员工的empno，mgr，deptno。

#题目5：查询最低工资大于20号部门最低工资的部门id和其最低工资

题目6：查询每个部门的最低工资的员工编号和员工姓名

#题目7：返回其它job中比job为‘经理’任一工资低的员工的员工号、姓名、job 以及sal 

#题目8：返回其它job中比job为‘经理’的所有工资低的员工的员工编号、员工姓名、job 以及sal

#题目9：查询员工中工资大于公司平均工资的员工的ename,sal和其deptno

#题目10：查询员工中工资大于本部门平均工资的员工的ename,sal和其deptno

题目11：查询员工的empno,sal,deptno,按照dname 排序

 

#题目12：查询公司管理者的empno，ename，job，deptno信息

 #方式①：使用自连接

 #方式②：使用子查询

 #方式③：使用EXISTS

#题目13：查询dept表中，不存在于emp表中的部门的deptno和dname