常用SQL练习

emp员工表

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80021
 Source Host           : localhost:3306
 Source Schema         : sqlex

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

 Date: 24/11/2020 17:56:06
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (
  `EMPNO` int(0) NOT NULL,
  `ENAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `JOB` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `MGR` int(0) NULL DEFAULT NULL,
  `HIREDATE` date NULL DEFAULT NULL,
  `SAL` double(7, 2) NULL DEFAULT NULL,
  `COMM` double(7, 2) NULL DEFAULT NULL,
  `DEPTNO` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`EMPNO`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (100, 'JIONG', 'CLERK', 7839, '1998-04-08', 1250.00, NULL, 10);
INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20);
INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30);
INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30);
INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20);
INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30);
INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30);
INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10);
INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 4000.00, NULL, 20);
INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10);
INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30);
INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-26', 1100.00, NULL, 20);
INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30);
INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20);
INSERT INTO `emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);

SET FOREIGN_KEY_CHECKS = 1;

View Code

dept部门表

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80021
 Source Host           : localhost:3306
 Source Schema         : sqlex

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

 Date: 24/11/2020 17:55:56
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `DEPTNO` int(0) NOT NULL,
  `DNAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `LOC` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`DEPTNO`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES (20, 'RESEARCH', 'DALLSA');
INSERT INTO `dept` VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES (40, 'OPERATIONS', 'BOSTON');
INSERT INTO `dept` VALUES (50, '50bac', '50def');
INSERT INTO `dept` VALUES (60, 'DEVELOPER', 'CHINA');

SET FOREIGN_KEY_CHECKS = 1;

View Code

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);

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

SELECT * FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE 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 B.ENAME FROM EMP B WHERE A.MGR=B.EMPNO) AS BOSS_NAME FROM EMP A;

SELECT A.ENAME,B.ENAME AS BOSS_NAME FROM EMP A LEFT JOIN EMP B ON A.MGR=B.EMPNO;

4、列出受雇日期早于其直接上级的所有员工

SELECT A.ENAME FROM EMP A LEFT JOIN EMP B ON A.MGR=B.EMPNO WHERE A.HIREDATE<B.HIREDATE;

SELECT A.ENAME FROM EMP A WHERE A.HIREDATE<(SELECT HIREDATE FROM EMP B WHERE A.MGR=B.EMPNO);

5、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

SELECT A.DNAME,B.EMPNO,B.JOB,B.MGR,B.HIREDATE,B.SAL,B.DEPTNO FROM DEPT A LEFT JOIN EMP B ON A.DEPTNO=B.DEPTNO;

SELECT A.DNAME,DEPTNO,B.EMPNO,B.ENAME,B.JOB,B.MGR,B.SAL,B.HIREDATE FROM DEPT A LEFT JOIN EMP B USING(DEPTNO);

6、列出所有"CLERK"(办事员)的姓名及其部门名称

SELECT A.ENAME,B.DNAME FROM EMP A LEFT JOIN DEPT B ON A.DEPTNO=B.DEPTNO WHERE A.JOB='CLERK';

7、列出最低薪金大于1500的各种工作

SELECT DISTINCT JOB AS HIGHSALJOB FROM EMP GROUP BY JOB HAVING MIN(SAL)>1500;

8、列出部门在“SALES”(销售部)工作的员工姓名，假定不知道销售部的部门编号

SELECT ENAME FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES');

SELECT ENAME FROM EMP LEFT JOIN DEPT USING(DEPTNO) WHERE DNAME='SALES';

9、列出薪金高于公司平均薪金的所有员工

SELECT ENAME FROM EMP WHERE SAL>(SELECT AVG(SAL) FROM EMP);

10、列出与“SCOTT”从事相同工作的所有员工

SELECT ENAME FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='SCOTT');

11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金

SELECT A.ENAME,A.SAL FROM EMP A WHERE A.SAL IN (SELECT B.SAL FROM EMP B WHERE B.DEPTNO=30) AND A.DEPTNO<>30;

12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金

SELECT ENAME,SAL FROM EMP WHERE SAL>(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30);

13、列出在每个部门工作的员工数量、平均工资和平均服务期限

SELECT (SELECT B.DNAME FROM DEPT B WHERE A.DEPTNO=B.DEPTNO) AS DEPTNAME,COUNT(DEPTNO) AS DEPTCOUNT,AVG(SAL) AS DEPTAVGSAL,AVG(YEAR(SYSDATE())-YEAR(HIREDATE)) AS AVGHIRDYEAR FROM EMP A GROUP BY DEPTNO;

14、列出所有的员工姓名、部门名称和工资

SELECT A.ENAME,(SELECT DNAME FROM DEPT B WHERE A.DEPTNO=B.DEPTNO) AS DEPTNAME,A.SAL+IFNULL(COMM,0) AS WAGE FROM EMP A;

15、列出所有部门的详细信息和部门人数

SELECT DEPTNO,DNAME,LOC,(SELECT COUNT(1) FROM EMP WHERE EMP.DEPTNO=DEPT.DEPTNO GROUP BY DEPT.DEPTNO) AS DEPTCOUNT FROM DEPT;

16、列出各种工作的最低工资

SELECT JOB,MIN(SAL) FROM EMP GROUP BY JOB;

SELECT DISTINCT JOB,SAL FROM EMP A WHERE NOT EXISTS (SELECT NULL FROM EMP B WHERE A.JOB=B.JOB AND B.SAL<A.SAL);

17、列出各个部门的MANAGER(经理)的最低薪金

SELECT DEPTNO,MIN(SAL) FROM EMP WHERE JOB='MANAGER' GROUP BY DEPTNO;

SELECT DEPTNO,SAL FROM EMP A WHERE A.JOB='MANAGER' AND EXISTS (SELECT NULL FROM EMP B WHERE A.DEPTNO=B.DEPTNO);

18、列出所有员工的年薪，按年薪从低到高排序

SELECT ENAME,(SAL+IFNULL(COMM,0))*12 AS SALPERSAL FROM EMP ORDER BY SALPERSAL;

https://blog.csdn.net/weixin_44828911/article/details/110089408

故乡明