34道SQL练习题
三张表：
-- 第一步：创建一个空的数据库
-- 第二步：运行以下SQL创建三张表:
DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;
CREATE TABLE DEPT
(DEPTNO int(2) not null ,
DNAME VARCHAR(14) ,
LOC VARCHAR(13),
primary key (DEPTNO)
);
CREATE TABLE EMP
(EMPNO int(4) not null ,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INT(4),
HIREDATE DATE DEFAULT NULL,
SAL DOUBLE(7,2),
COMM DOUBLE(7,2),
primary key (EMPNO),
DEPTNO INT(2)
)
;
CREATE TABLE SALGRADE
( GRADE INT,
LOSAL INT,
HISAL INT );
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
30, 'SALES', 'CHICAGO');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
40, 'OPERATIONS', 'BOSTON');
commit;
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7369, 'SMITH', 'CLERK', 7902, '1980-12-17'
, 800, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20'
, 1600, 300, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7521, 'WARD', 'SALESMAN', 7698, '1981-02-22'
, 1250, 500, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7566, 'JONES', 'MANAGER', 7839, '1981-04-02'
, 2975, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28'
, 1250, 1400, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01'
, 2850, NULL, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7782, 'CLARK', 'MANAGER', 7839, '1981-06-09'
, 2450, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19'
, 3000, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7839, 'KING', 'PRESIDENT', NULL, '1981-11-17'
, 5000, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08'
, 1500, 0, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7876, 'ADAMS', 'CLERK', 7788, '1987-05-23'
, 1100, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7900, 'JAMES', 'CLERK', 7698, '1981-12-03'
, 950, NULL, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7902, 'FORD', 'ANALYST', 7566, '1981-12-03'
, 3000, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7934, 'MILLER', 'CLERK', 7782, '1982-01-23'
, 1300, NULL, 10);
commit;
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
1, 700, 1200);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
2, 1201, 1400);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
3, 1401, 2000);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
4, 2001, 3000);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
5, 3001, 9999);
commit;
select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
USE test3;
SHOW TABLES;
SELECT * FROM emp;
-- 1.取得每个部门最高薪水的人员名称
-- 思路：查询的字段需要有部门名称，部门编号，部门最高薪水人员，部门的最高薪水
-- 我的写法：
SELECT
e.ename,
t.*,
d.dname
FROM
(SELECT
deptno,
MAX(sal) AS maxsal
FROM
emp
GROUP BY deptno) t
JOIN dept d
ON t.deptno = d.deptno
JOIN emp e
ON t.deptno = e.deptno AND t.maxsal = e.sal ;
-- 答案：比我的少了个部门名称，我是将临时表t作为了主表，而答案是将emp表作为了主表
SELECT
e.ename,
t.*
FROM
emp e
JOIN
(SELECT
deptno,
MAX(sal) AS maxsal
FROM
emp
GROUP BY deptno) t
ON e.deptno = t.deptno
AND e.sal = t.maxsal ;
-- ---------------------------------------------------------
-- 2.哪些人的薪水在部门的平均薪水之上
-- 思路：平均：avg，需要按照部门分组，求出部门的平均薪水，再去和部门内员工的薪水比较;
-- 需要显示的字段：部门编号，部门名称，部门平均薪水，部门员工薪水>平均薪水的名字，和他的薪水
-- 先求出来平均薪水，再和部门每位员工的薪水比较
SELECT
d.dname,
t.*,
e.ename,
e.sal
FROM
emp e
JOIN
(SELECT
deptno,
AVG(sal) AS avgsal
FROM
emp
GROUP BY deptno) t
ON e.deptno = t.deptno
AND e.sal > t.avgsal
JOIN dept d
ON e.deptno = d.deptno ;
-- ---------------------------------------------------------
-- 3.取得部门中（所有人的）平均的薪水等级
-- 思路：读题，平均的薪水等级和平均薪水的等级是不一样的
-- 平均的薪水等级：先求每个人的薪水等级，再取平均值
-- 平均薪水的等级：先计算平均薪水，然后找出这个平均薪水所对应的等级
-- 需要显示的字段:部门编号，平均的薪水等级
SELECT
e.deptno,
AVG(s.`GRADE`)
FROM
emp e
JOIN salgrade s
ON e.sal BETWEEN s.`LOSAL`
AND s.`HISAL`
GROUP BY e.`DEPTNO` ;
-- ---------------------------------------------------------
-- 4.不准用组函数（Max），取得最高薪水
-- 思路：（1）降序截取
SELECT ename,sal FROM emp ORDER BY sal DESC LIMIT 1;
-- （2）使用max函数
SELECT MAX(sal) FROM emp;
-- ---------------------------------------------------------
-- 5.取得平均薪水最高的部门的部门编号
-- 思路：先求出部门的平均薪水，再比较
-- (1)降序截取
SELECT
deptno,
AVG(sal) avgsal
FROM
emp
GROUP BY deptno
ORDER BY avgsal DESC
LIMIT 1 ;
-- (2)使用max函数
SELECT
t.deptno,
MAX(t.avgsal)
FROM
(SELECT
deptno,
AVG(sal) avgsal
FROM
emp
GROUP BY deptno) t ;
-- ---------------------------------------------------------
-- 6.取得平均薪水最高的部门的部门名称
-- 思路：需要结合另一个部门表
-- 我的写法
SELECT
t.*,
d.dname
FROM
dept d
JOIN
(SELECT
e.deptno AS deptno,
AVG(sal) AS avgsal
FROM
emp e
GROUP BY deptno
ORDER BY avgsal DESC
LIMIT 1) t
ON d.`DEPTNO` = t.deptno ;
-- 答案
SELECT
e.deptno,
d.dname,
AVG(e.sal) AS avgsal
FROM
emp e
JOIN dept d
ON e.`DEPTNO` = e.`DEPTNO`
GROUP BY deptno
ORDER BY avgsal DESC
LIMIT 1 ;
-- 反思:做表连接做多了，有更简单的办法
-- ---------------------------------------------------------
-- 7.求平均薪水的等级最低的部门的部门名称
-- 思路：三张表都要用，先按照部门求出平均薪水，然后查找对应等级，然后比较等级，获取最低等级，获取对应的部门名称
SELECT d.dname,MIN(s.grade),t.avgsal FROM salgrade s
JOIN(SELECT e.deptno,AVG(sal) AS avgsal FROM emp e GROUP BY deptno) t
ON avgsal BETWEEN s.`LOSAL` AND s.`HISAL`
JOIN dept d
ON t.deptno = d.deptno;
-- 抛开之前的题目，求平均薪水最低对应的等级，平均薪水最低，那么他的等级也是最低
SELECT s.`GRADE` FROM salgrade s WHERE
(SELECT AVG(sal) AS avgsal FROM emp GROUP BY deptno ORDER BY avgsal ASC LIMIT 1)
BETWEEN s.`LOSAL` AND s.`HISAL`;
-- ---------------------------------------------------------
-- 8.取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名
-- 先找出普通员工（代码在mgr没有出现才表明他是个普通员工，没有管理任何人），再从其中找到最高薪水，然后找到比这个薪水高的员工的名字，他们就是领导人
SELECT MAX(sal) FROM emp WHERE empno NOT IN(SELECT DISTINCT mgr FROM emp);
-- 以上写法会得出普通员工的最高薪水为null，所以在子查询中需要将null排除
SELECT MAX(sal) FROM emp WHERE empno NOT IN(SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL);
-- 经过以上写法求出了普通员工的最高薪水，然后找出高于1600的员工的就是领导
SELECT ename,sal FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE empno NOT IN(SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL));
-- ---------------------------------------------------------
-- 9.取得薪水最高的前五名员工
SELECT ename,sal FROM emp ORDER BY sal DESC LIMIT 5;
-- 写了半天终于有个简单的题了，哈哈哈哈👍
-- ---------------------------------------------------------
-- 10.取得薪水最高的第六到第十名员工
SELECT ename,sal FROM emp ORDER BY sal DESC LIMIT 5,5;
-- ---------------------------------------------------------
-- 11.取得最后入职的 5 名员工
-- 思路：按照日期降序，取前5
SELECT ename,HIREDATE FROM emp ORDER BY HIREDATE DESC LIMIT 5;
-- ---------------------------------------------------------
-- 12.取得每个薪水等级有多少员工
-- 找到每个员工对应的薪水等级，求每个等级的员工人数，求数据的条数count
-- 我的写法：
SELECT grade,COUNT(GRADE) FROM salgrade s JOIN (SELECT sal FROM emp) t ON t.sal BETWEEN s.`LOSAL` AND s.`HISAL` GROUP BY s.`GRADE`;
-- 答案：
SELECT s.grade,COUNT(*) FROM salgrade s JOIN emp e ON e.sal BETWEEN s.`LOSAL` AND s.`HISAL` GROUP BY s.`GRADE`;
-- ---------------------------------------------------------
# 13.面试题：
#创建表
CREATE TABLE s(
sno INT(4) PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(32)
);
INSERT INTO s(sname) VALUES('zhangsan');
INSERT INTO s(sname) VALUES('lisi');
INSERT INTO s(sname) VALUES('wangwu');
INSERT INTO s(sname) VALUES('zhaoliu');
CREATE TABLE c(
cno INT(4) PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(32),
cteacher VARCHAR(32)
);
INSERT INTO c(cname,cteacher) VALUES('Java','吴老师');
INSERT INTO c(cname,cteacher) VALUES('C++','王老师');
INSERT INTO c(cname,cteacher) VALUES('C##','张老师');
INSERT INTO c(cname,cteacher) VALUES('MySQL','郭老师');
INSERT INTO c(cname,cteacher) VALUES('Oracle','黎明');
CREATE TABLE sc(
sno INT(4),
cno INT(4),
scgrade DOUBLE(3,1),
CONSTRAINT sc_sno_cno_pk PRIMARY KEY(sno,cno),
CONSTRAINT sc_sno_fk FOREIGN KEY(sno) REFERENCES s(sno),
CONSTRAINT sc_cno_fk FOREIGN KEY(cno) REFERENCES c(cno)
);
INSERT INTO sc(sno,cno,scgrade) VALUES(1,1,30);
INSERT INTO sc(sno,cno,scgrade) VALUES(1,2,50);
INSERT INTO sc(sno,cno,scgrade) VALUES(1,3,80);
INSERT INTO sc(sno,cno,scgrade) VALUES(1,4,90);
INSERT INTO sc(sno,cno,scgrade) VALUES(1,5,70);
INSERT INTO sc(sno,cno,scgrade) VALUES(2,2,80);
INSERT INTO sc(sno,cno,scgrade) VALUES(2,3,50);
INSERT INTO sc(sno,cno,scgrade) VALUES(2,4,70);
INSERT INTO sc(sno,cno,scgrade) VALUES(2,5,80);
INSERT INTO sc(sno,cno,scgrade) VALUES(3,1,60);
INSERT INTO sc(sno,cno,scgrade) VALUES(3,2,70);
INSERT INTO sc(sno,cno,scgrade) VALUES(3,3,80);
INSERT INTO sc(sno,cno,scgrade) VALUES(3,4,60);
INSERT INTO sc(sno,cno,scgrade) VALUES(4,3,50);
INSERT INTO sc(sno,cno,scgrade) VALUES(4,4,80);
# 有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
# S（SNO，SNAME）代表（学号，姓名）
# C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
# SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
# 问题：
## (1)找出没选过“黎明”老师的所有学生姓名。
SELECT * FROM s WHERE sno NOT IN(SELECT sno FROM sc WHERE sc.cno = (SELECT cno FROM c WHERE cteacher = '黎明'));
## (2)列出 2 门以上（含 2 门）不及格学生姓名及平均成绩。
-- 思路：通过学号分组，然后查找成绩小于60的count数，判断是否大于等于2;
-- 第一步：
SELECT sc.sno, s.`sname`,COUNT(*) AS studentNum FROM sc
JOIN s ON sc.`sno` = s.`sno` WHERE scgrade < 60 GROUP BY sc.sno,s.`sname` HAVING studentNum >= 2;
-- 第二步：
SELECT
s.sname,
t2.avggrade
FROM
s
JOIN
(SELECT
sc.sno,
s.`sname`,
COUNT(*) AS studentNum
FROM
sc
JOIN s
ON sc.`sno` = s.`sno`
WHERE scgrade < 60
GROUP BY sc.sno,
s.`sname`
HAVING studentNum >= 2) t1
ON t1.sno = s.`sno`
JOIN
(SELECT
sc.`sno`,
AVG(sc.`scgrade`) AS avggrade
FROM
sc
GROUP BY sc.`sno`) t2
ON t2.sno = s.`sno` ;
## (3)学过 1 号课程又学过 2 号课所有学生的姓名。
SELECT s.sname FROM s JOIN sc ON sc.sno = s.sno WHERE cno=1 AND sc.`sno` IN (SELECT sno FROM sc WHERE sc.`cno`=2);
-- ---------------------------------------------------------
-- 14.列出所有员工及领导的姓名
SELECT a.ename AS '员工',b.ename AS '领导' FROM emp a
LEFT JOIN emp b
ON a.`mgr` = b.empno;
-- ---------------------------------------------------------
-- 15.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
-- 思路：表自连做日期的比较
SELECT DISTINCT a.empno,a.ename AS '员工',a.`HIREDATE`, b.`ENAME` AS '领导',b.`HIREDATE`,d.dname FROM emp a
JOIN emp b ON a.`MGR` = b.`EMPNO`
JOIN dept d ON a.deptno = d.`DEPTNO`
WHERE a.`HIREDATE` < b.`HIREDATE`;
-- ---------------------------------------------------------
-- 16.列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门
SELECT e.*,d.`DEPTNO`,d.`DNAME` FROM dept d
LEFT JOIN emp e
ON d.`DEPTNO` = e.`DEPTNO`;
-- ---------------------------------------------------------
-- 17.列出至少有 5 个员工的所有部门
-- 思路：至少有5个 >= 5
SELECT d.dname,e.deptno FROM emp e
JOIN dept d
ON d.`DEPTNO` = e.`DEPTNO`
GROUP BY deptno HAVING COUNT(*) >= 5;
-- ---------------------------------------------------------
-- 18.列出薪金比"SMITH" 多的所有员工信息
-- 写法一：
SELECT e.ename,e.sal FROM emp e
WHERE e.sal > (SELECT sal FROM emp WHERE ename = 'SMITH');
-- 写法二：
SELECT a.ename,a.sal FROM emp a
JOIN emp b ON a.sal > b.`SAL` AND b.`ENAME` = 'SMITH';
-- ---------------------------------------------------------
-- 19.列出所有"CLERK"(办事员) 的姓名及其部门名称, 部门的人数
-- 第一步：所有"CLERK"(办事员) 的姓名及其部门名称
SELECT e.`ENAME`,e.`JOB`,d.`DNAME`,d.`DEPTNO`
FROM emp AS e JOIN dept d ON d.`DEPTNO` = e.`DEPTNO`
WHERE e.`JOB` = 'CLERK';
-- 第二步：部门的人数
SELECT COUNT(*) FROM emp e JOIN dept d ON e.`DEPTNO` = d.`DEPTNO` GROUP BY e.`DEPTNO`;
-- 第三步：表连接
SELECT t1.*,t2.deptcount FROM
(SELECT e.`ENAME`,e.`JOB`,d.`DNAME`,d.`DEPTNO`
FROM emp AS e JOIN dept d ON d.`DEPTNO` = e.`DEPTNO`
WHERE e.`JOB` = 'CLERK') t1
JOIN (SELECT e.deptno,COUNT(*) AS deptcount FROM emp e JOIN dept d ON e.`DEPTNO` = d.`DEPTNO` GROUP BY e.`DEPTNO`) t2
ON t1.deptno = t2.deptno;
-- ---------------------------------------------------------
-- 20.列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数
-- 思路：显示字段，工作名称，从事的此工作的人数
SELECT COUNT(*),e.job FROM emp e GROUP BY e.`job` HAVING MIN(e.`sal`) > 1500 ;
-- ---------------------------------------------------------
-- 21.列出在部门"SALES"<销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.
SELECT e.ename FROM emp e WHERE deptno = (SELECT deptno FROM dept WHERE dname = 'SALES');
-- ---------------------------------------------------------
-- 22.列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.
-- 第一步：先求出平均薪资
SELECT AVG(sal) avgsal FROM emp;
-- 第二步: 找出薪金高于公司平均薪资的员工
SELECT e.ename FROM emp e WHERE e.`SAL` > (SELECT AVG(sal) avgsal FROM emp);
-- 连表写出完整SQL
SELECT a.ename '员工', d.dname,b.ename '领导',s.grade
FROM emp a
JOIN dept d
ON a.`DEPTNO` = d.`DEPTNO`
LEFT JOIN emp b
ON a.`MGR` = b.`EMPNO`
JOIN salgrade s
ON a.`SAL` BETWEEN s.`LOSAL` AND s.`HISAL`
WHERE a.`SAL` > (SELECT AVG(sal) FROM emp);
-- ---------------------------------------------------------
-- 23.列出与"SCOTT" 从事相同工作的所有员工及部门名称
SELECT e.ename,e.job,d.dname FROM emp e
JOIN dept d ON d.`DEPTNO` = e.`DEPTNO`
WHERE e.`JOB` = (SELECT job FROM emp WHERE ename = 'SCOTT')
AND e.ename <> 'SCOTT';
-- ---------------------------------------------------------
-- 24.列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.
-- 第一步：查找30号部门员工的薪金
SELECT DISTINCT sal FROM emp WHERE deptno = 30;
-- 第二步：查找等于这些薪金的其他员工姓名和他们的薪金
SELECT ename,sal FROM emp WHERE sal = (SELECT DISTINCT sal FROM emp WHERE deptno = 30);
-- 注意：以上slq是错误的，报错：Subquery returns more than 1 row （子查询多余1行）
-- 更改：写法一：将 = 改为 in
SELECT ename,sal,deptno FROM emp WHERE sal IN (SELECT DISTINCT sal FROM emp WHERE deptno = 30) AND deptno <> 30;
-- 写法二：
SELECT DISTINCT ename,e.sal,e.deptno FROM emp e
JOIN (SELECT DISTINCT sal,deptno FROM emp WHERE deptno = 30) t
ON t.deptno = e.deptno
WHERE e.deptno <> 30;
-- ---------------------------------------------------------
-- 25.列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称
-- 第一步：求出30号部门员工的最高薪金
SELECT MAX(sal) FROM emp WHERE deptno = 30;
-- 第二步：做比较，完善sql
SELECT ename,sal,d.dname FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);
-- ---------------------------------------------------------
-- 26.列出在每个部门工作的员工数量, 平均工资和平均服务期限
-- 注意：有一种情况是部门存在，但是该部门没有员工，所有用到了右连接查询，和ifnull判断
-- mysql中求两个日期的“年差”，差了多少年? 使用TimeStampDiff(间隔类型，前一个日期，后一个日期)
-- 例子timestampdiff(YEAR,hiredate,now())
-- 间隔类型：SECOND 秒，MiNUTE 分钟， HOUR 小时， DAY 天， WEEK 星期， MONTH 月， QUARTER 季度， YEAR 年
SELECT d.deptno,
COUNT(*) ecount,
IFNULL(AVG(e.sal),0) AS avgsal,
IFNULL(AVG(TIMESTAMPDIFF(YEAR,hiredate,NOW())),0) AS avgservicetime
FROM emp e
RIGHT JOIN dept d
ON e.`DEPTNO` = d.`DEPTNO`
GROUP BY d.`DEPTNO`;
-- ---------------------------------------------------------
-- 27.列出所有员工的姓名.部门名称和工资.
SELECT e.ename,d.dname,e.sal FROM emp e JOIN dept d ON d.`DEPTNO` = e.`DEPTNO`;
-- ---------------------------------------------------------
-- 28.列出所有部门的详细信息和人数
-- 我的写法：
SELECT d.*,IFNULL(t.countnum,0) FROM dept d
LEFT JOIN (SELECT COUNT(*) countnum,deptno FROM emp GROUP BY deptno) t
ON t.deptno = d.`DEPTNO`;
-- 答案：
SELECT d.*,COUNT(e.`ENAME`) FROM emp e
RIGHT JOIN dept d
ON e.`DEPTNO` = d.`DEPTNO`
GROUP BY d.`DEPTNO`,d.`DNAME`,d.`LOC`;
-- ---------------------------------------------------------
-- 29.列出各种工作的最低工资及从事此工作的雇员姓名
-- 思路：按照工作分组，求出每组中的最低工资，以及对应ename相等的数据
-- 我的写法：
SELECT ename,job,sal FROM emp a WHERE sal IN (SELECT MIN(sal) minsal FROM emp GROUP BY job);
-- 答案：
SELECT e.ename,t.* FROM emp e
JOIN (SELECT job,MIN(sal) AS minsal FROM emp GROUP BY job) t
ON e.`JOB` = t.job AND e.sal = t.minsal;
-- ---------------------------------------------------------
-- 30.列出各个部门的 MANAGER(领导) 的最低薪金
SELECT deptno,MIN(sal) FROM emp WHERE `JOB` = 'MANAGER' GROUP BY deptno;
-- ---------------------------------------------------------
-- 31.列出所有员工的年工资, 按年薪从低到高排序
SELECT ename,(sal + IFNULL(comm,0)) * 12 AS yearsal
FROM emp
ORDER BY yearsal ASC;
-- ---------------------------------------------------------
-- 32.求出员工领导的薪水超过3000的员工名称与领导
SELECT a.ename AS '员工',b.ename AS '领导',b.sal
FROM emp a
JOIN emp b
ON a.`MGR` = b.`EMPNO`
WHERE b.sal > 3000;
-- ---------------------------------------------------------
-- 33.求出部门名称中, 带'S'字符的部门员工的工资合计.部门人数
SELECT d.deptno,
d.`DNAME`,
d.`LOC`,
COUNT(*),
IFNULL(SUM(sal),0) AS sumsal
FROM emp e
RIGHT JOIN dept d
ON e.`DEPTNO` = d.`DEPTNO`
WHERE d.dname LIKE '%S%'
GROUP BY d.`DEPTNO`;
-- ---------------------------------------------------------
-- 34.给任职日期超过 30 年的员工加薪 10%.
SELECT * FROM emp;
SELECT * FROM emp WHERE TIMESTAMPDIFF(YEAR,hiredate,NOW()) >30;
UPDATE emp SET sal = sal * 1.1 WHERE TIMESTAMPDIFF(YEAR,emp.`HIREDATE`,NOW()) > 30;
