question

-- 列出所有员工信息。
-- 列出所有部门的名称和所在地。
-- 列出所有员工名，列名显示'EMP_NAME'。
-- 列出两遍全部部门名，第一列起名'COL_A'，第二列'COL_B'。
-- 列出工资大于1000的所有员工信息。
-- 列出工资大于1000的办事员。
-- 列出所有办事员以及工资大于1500的其他员工。
-- 列出所有不是办事员的员工。
-- 列出所有被补助人员信息。
-- 列出所有姓名为S开头的员工信息。
-- 列出所有姓名为S开头H结尾的员工信息。
-- 列出所有姓名中带S的员工信息。
-- 列出所有姓名中第二位为L的员工信息。
-- 列出所有姓名中不带S的员工信息。
-- 列出所有姓名中带单引号的员工信息。
-- 列出所有姓名中带%号的员工信息。
-- 列出每个部门每个月一共发出的工资数。
-- 列出和MARTIN工资一样的员工信息。
-- 列出比SMITH和WARD工资都高的所有员工信息。
-- 列出工资比20部门所有人工资要高的所有员工信息。
-- 列出工资比20部门某些人工资要高的所有员工信息。
-- 列出工资和20部门某些人工资一样的所有员工信息。
-- 列出存在员工的部门，要求使用IN。
-- 列出不存在员工的部门，要求使用EXISTS。
-- 列出所有员工信息和所有部门信息;
-- 列出所有员工姓名及所在部门名，员工无部门，显示'NO'。
-- 列出所有部门名及其员工名，部门无员工，显示'NO'。 
-- 列出所有正式员工姓名及其所在部门名
-- 列出所有工资大于1500的员工名以及所在部门名。
-- 列出所有员工姓名，及其上级领导姓名。

answer

-- 列出所有员工信息。
SELECT *
FROM EMP;

-- 列出所有部门的名称和所在地。
SELECT DNAME, LOC
FROM DEPT;

-- 列出所有员工名，列名显示'EMP_NAME'。
SELECT ENAME 'EMP_NAME'
FROM EMP;

-- 列出两遍全部部门名，第一列起名'COL_A'，第二列'COL_B'。
SELECT DNAME 'COL_A', DNAME 'COL_B'
FROM DEPT;

-- 列出工资大于1500的所有员工信息。
SELECT *
FROM EMP
WHERE SAL > 1500;

-- 列出工资大于1000的办事员。
SELECT *
FROM EMP
WHERE JOB = 'CLERK'
  AND SAL > 1000;

-- 列出所有办事员以及工资大于1000的其他员工。
SELECT *
FROM EMP
WHERE JOB = 'CLERK'
   OR SAL > 1000;

-- 列出所有不是办事员的员工。
SELECT *
FROM EMP
WHERE JOB != 'CLERK';

-- 列出所有被补助人员信息。
SELECT *
FROM EMP
WHERE COMM IS NOT NULL;

-- 列出所有姓名为S开头的员工信息。
SELECT *
FROM EMP
WHERE ENAME LIKE 'S%';

-- 列出所有姓名为S开头H结尾的员工信息。
SELECT *
FROM EMP
WHERE ENAME LIKE 'S%H';

-- 列出所有姓名中带S的员工信息。
SELECT *
FROM EMP
WHERE ENAME LIKE '%S%';

-- 列出所有姓名中第二位为L的员工信息。
SELECT *
FROM EMP
WHERE ENAME LIKE '_L%';

-- 列出所有姓名中不带S的员工信息。
SELECT *
FROM EMP
WHERE ENAME NOT LIKE '%S%';

-- 列出所有姓名中带单引号的员工信息。
SELECT *
FROM EMP
WHERE ENAME LIKE '%\'%';

-- 列出所有姓名中带%号的员工信息。
SELECT *
FROM EMP
WHERE ENAME LIKE '%A%%' ESCAPE 'A';

-- 列出和MARTIN工资一样的员工信息。
SELECT *
FROM EMP
WHERE SAL = (
    SELECT SAL
    FROM EMP
    WHERE ENAME = 'MARTIN');

-- 列出比SMITH和WARD工资都高的所有员工信息。
SELECT *
FROM EMP
WHERE SAL > ALL (
    SELECT SAL
    FROM EMP
    WHERE ENAME IN ('SMITH', 'WARD'));

-- 列出工资比20部门所有人工资要高的所有员工信息。
SELECT *
FROM EMP
WHERE SAL > ALL (
    SELECT SAL
    FROM EMP
    WHERE DEPTNO = 20);

-- 列出工资比20部门某些人工资要高的所有员工信息。
SELECT *
FROM EMP
WHERE SAL > ANY (
    SELECT SAL
    FROM EMP
    WHERE DEPTNO = 20);

-- 列出工资和20部门某些人工资一样的所有员工信息。
SELECT *
FROM EMP
WHERE SAL = SOME (
    SELECT SAL
    FROM EMP
    WHERE DEPTNO = 20);

-- 列出存在员工的部门，要求使用IN。
SELECT *
FROM DEPT
WHERE DEPTNO IN (
    SELECT DEPTNO
    FROM EMP
    WHERE DEPTNO IS NOT NULL
);

-- 列出不存在员工的部门，要求使用EXISTS。
SELECT *
FROM DEPT
WHERE NOT EXISTS(
        SELECT 1 FROM EMP WHERE EMP.DEPTNO = DEPT.DEPTNO
    );

-- 列出所有员工信息和所有部门信息;
SELECT *
FROM EMP
         JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

-- 列出所有员工姓名及所在部门名，员工无部门，显示'NO'。
SELECT ENAME, DNAME, IFNULL(EMP.DEPTNO, 'NO') RESULT
FROM EMP
         LEFT JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

-- 列出所有部门名及其员工名，部门无员工，显示'NO'。
SELECT ENAME, DNAME, IFNULL(EMP.EMPNO, 'NO') RESULT
FROM EMP
         RIGHT JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

-- 列出所有正式员工姓名及其所在部门名。
SELECT ENAME, DNAME
FROM EMP
         JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
WHERE DNAME IS NOT NULL;

-- 列出所有工资大于1500的员工名以及所在部门名。
SELECT ENAME, DNAME
FROM EMP
         JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
WHERE SAL > 1500;

-- 列出所有员工姓名，及其上级领导姓名。
SELECT XIAO.ENAME XIAO, DA.ENAME DA
FROM EMP XIAO
         JOIN EMP DA ON XIAO.MGR = DA.EMPNO;

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

question

-- 列出公司的所有职位。
-- 列出编号在7499和7654之间的所有员工名和编号。
-- 列出编号为7499和7654的两个员工的姓名和编号，不许用OR。
-- 列出工资在0-2000或者3000以上的员工的姓名和工资。
-- 按照"2019年01月01日 星期一 00点00分00秒"的格式展示当前系统时间。
-- 列出所有员工从入职到今天一共经历了多少天。
-- 列出当年第一天。
-- 列出当周第一天。
-- 列出当周最后一天。
-- 列出前一周第一天。
-- 列出前一周最后一天。
-- 列出当月最后一天。
-- 列出前一月最后一天。
-- 利用CONCAT()函数查询当年最后一天。
-- 利用CONCAT()函数查询当月第一天。
-- 利用CONCAT()函数查询前一月第一天。
-- 利用CONCAT_WS()函数，以"XXX-XXX"的格式来显示员工姓名和该员工上级领导姓名。
-- 重复打印10遍"I LOVE YOU"。
-- 查询员工姓名，以及该姓名中，字母"A"第一次出现的位置。
-- 查询员工姓名，但要求将该姓名反转之后，再将其中所有的字母"A"变为屏蔽词，用"*"代替。
-- 查询员工姓名，统一设置为5字节，不够用空格补，超出截取到5字节。
-- 查询员工姓名，并在所有姓名的3号位置插入一个空格。
-- 列出每个部门每个月一共发出的工资数。
-- 列出存在员工的每个部门的平均工资。
-- 列出每种职业的最低工资（BOSS忽略）。
-- 列出每种职业的最低补助，如果没有补助显示0。
-- 列出平均工资高于2000的所有部门名。
-- 列出每个部门工资的平均值，最大值和最小值。
-- 列出所有员工名和他的补助情况 `YES` 或 `NO`。
-- 列出所有员工名和他的月总收入。

answer

-- 列出公司的所有职位。
SELECT DISTINCT JOB
FROM EMP
WHERE JOB IS NOT NULL;

-- 列出编号在7499和7654之间的所有员工名和编号。
SELECT ENAME, EMPNO
FROM EMP
WHERE EMPNO BETWEEN 7499 AND 7654;

-- 列出编号为7499和7654的两个员工的姓名和编号，不许用OR。
SELECT ENAME, EMPNO
FROM EMP
WHERE EMPNO IN (7499, 7654);

-- 列出工资在0-2000或者3000以上的员工的姓名和工资。
SELECT ENAME, SAL
FROM EMP
WHERE SAL BETWEEN 0 AND 2000
   OR SAL > 3000;

-- 按照"2019年01月01日 星期一 00点00分00秒"的格式展示当前系统时间。
SELECT DATE_FORMAT(NOW(), '%Y年%M月%D日 %A %H点%I分%S秒') RESULT;

-- 列出所有员工从入职到今天一共经历了多少天。
SELECT ENAME, DATEDIFF(CURDATE(), HIREDATE) RESULT
FROM EMP
WHERE HIREDATE IS NOT NULL;

-- 列出当年第一天。
SELECT DATE_SUB(CURDATE(), INTERVAL DAYOFYEAR(NOW()) - 1 DAY) RESULT;

-- 列出当周第一天。
SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) RESULT;

-- 列出当周最后一天。
SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) - 6 DAY) RESULT;

-- 列出前一周第一天。
SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 7 DAY) RESULT;

-- 列出前一周最后一天。
SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 7 - 6 DAY) RESULT;

-- 列出当月最后一天。
SELECT LAST_DAY(CURDATE()) RESULT;

-- 列出前一月最后一天。
SELECT LAST_DAY(CURDATE() - INTERVAL 1 MONTH) RESULT;

-- 利用CONCAT()函数查询当年最后一天。
SELECT CONCAT(YEAR(CURDATE()), '-12-31') RESULT;

-- 利用CONCAT()函数查询当月第一天。
SELECT CONCAT(DATE_FORMAT(CURDATE(), '%Y-%M-'), '01') RESULT;

-- 利用CONCAT()函数查询前一月第一天。
SELECT CONCAT(DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%M-'), '01') RESULT;

-- 利用CONCAT_WS()函数，以"XXX-XXX"的格式来显示员工姓名和该员工上级领导姓名。
SELECT CONCAT_WS('-', XIAO.ENAME, DA.ENAME)
FROM EMP XIAO
         JOIN EMP DA ON XIAO.MGR = DA.EMPNO;

-- 重复打印10遍"I LOVE YOU"。
SELECT REPEAT('I LOVE YOU', 10);

-- 查询员工姓名，以及该姓名中，字母"A"第一次出现的位置。
SELECT ENAME, INSTR(ENAME, 'A')
FROM EMP;

-- 查询员工姓名，但要求将该姓名反转之后，再将其中所有的字母"A"变为屏蔽词，用"*"代替。
SELECT ENAME, REPLACE(REVERSE(ENAME), 'A', '*')
FROM EMP;

-- 查询员工姓名，统一设置为5字节，不够用空格补，超出截取到5字节。
SELECT ENAME, RPAD(ENAME, 5, SPACE(1))
FROM EMP;

-- 查询员工姓名，并在所有姓名的3号位置插入一个空格。
SELECT ENAME, INSERT(ENAME, 3, 0, SPACE(1))
FROM EMP;

-- 列出每个部门每个月一共发出的工资数。
SELECT DEPTNO, SUM(SAL + IFNULL(COMM, 0))
FROM EMP
WHERE DEPTNO IS NOT NULL
GROUP BY DEPTNO;

-- 列出存在员工的每个部门的平均工资。
SELECT DEPTNO, AVG(SAL)
FROM EMP
WHERE DEPTNO IS NOT NULL
GROUP BY DEPTNO;

-- 列出每种职业的最低工资（BOSS忽略）。
SELECT JOB, MIN(SAL)
FROM EMP
WHERE JOB != 'PRESIDENT'
GROUP BY JOB;

-- 列出每种职业的最低补助，如果没有补助显示0。
SELECT JOB, MIN(IFNULL(COMM, 0))
FROM EMP
WHERE JOB IS NOT NULL
GROUP BY JOB;

-- 列出平均工资高于2000的所有部门名。
SELECT DNAME, AVG(SAL)
FROM DEPT
         JOIN EMP ON DEPT.DEPTNO = EMP.DEPTNO
GROUP BY EMP.DEPTNO
HAVING AVG(SAL) > 2000;

-- 列出每个部门工资的平均值，最大值和最小值。
SELECT DEPTNO, AVG(SAL), MAX(SAL), MIN(SAL)
FROM EMP
WHERE DEPTNO IS NOT NULL
GROUP BY DEPTNO;

-- 列出所有员工名和他的补助情况 `YES` 或 `NO`。
SELECT ENAME, IF(COMM IS NOT NULL, 'YES', 'NO') RESULT
FROM EMP;

-- 列出所有员工名和他的月总收入。
SELECT ENAME, SAL + IFNULL(COMM, 0) RESULT
FROM EMP;