草庐IT

MYSQY 学习笔记之 练习题

ITWEL 2023-03-28 原文

试题一：取得每个部门最高薪水的人员名单？
分析：

① 先将员工表依据部门编号进行分组并找出每个分组中最高薪水的人员信息

②将最高薪水表与原表进行连接

#员工信息表
mysql> 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 | 1000.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 1000.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 | 1000.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
#按照部门进行分组并查处每个组中最高薪水
mysql> select deptno ,max(sal) maxsalary from emp group by deptno;
+--------+-----------+
| deptno | maxsalary |
+--------+-----------+
| 20 | 3000.00 |
| 30 | 2850.00 |
| 10 | 1000.00 |
+--------+-----------+
3 rows in set (0.00 sec)
#表连接
mysql> select e.ename,t.* from emp e join (select deptno,max(sal) as maxsal from emp group by deptno ) t on t.deptno =e.deptno and t.maxsal = e.sal;
+--------+--------+---------+
| ename | deptno | maxsal |
+--------+--------+---------+
| SCOTT | 20 | 3000.00 |
| FORD | 20 | 3000.00 |
| BLAKE | 30 | 2850.00 |
| CLARK | 10 | 1000.00 |
| KING | 10 | 1000.00 |
| MILLER | 10 | 1000.00 |
+--------+--------+---------+
6 rows in set (0.00 sec)
试题二：哪些人的薪水在部门的平均薪资之上？
分析：

①按照部门进行分组并找出每个部门的平均薪资（作为t表）

③表连接

      条件如下：I. 两张表的deptno相同 

                      II. 员工表的员工薪水 大于  t表中的平均薪水                         

#按照部门进行分组后然后再求出每个部门的平均薪资
mysql> select deptno, avg(sal) avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 1000.000000 |
+--------+-------------+
3 rows in set (0.00 sec)

#表连接
mysql> select t.*,e.ename,e.sal from emp e join (select deptno, avg(sal) avgsal from emp group by deptno) t on e.deptno = t.deptno and e.sal>t.avgsal;
+--------+-------------+-------+---------+
| deptno | avgsal | ename | sal |
+--------+-------------+-------+---------+
| 30 | 1566.666667 | ALLEN | 1600.00 |
| 20 | 2175.000000 | JONES | 2975.00 |
| 30 | 1566.666667 | BLAKE | 2850.00 |
| 20 | 2175.000000 | SCOTT | 3000.00 |
| 20 | 2175.000000 | FORD | 3000.00 |
+--------+-------------+-------+---------+
5 rows in set (0.00 sec)
试题三：取得部门中（所有人的）平均的薪水等级？？
分析：

区分： 平均的薪水等级VS 平均薪水的等级

          平均的薪水等级：先求每个员工的薪水等级，然后再求平均值

          平均薪水的等级：先求薪水的平均值，然后再求薪水等级

① 先显示出每个部门中所有员工的基本信息及薪水等级

②薪水等级求平均值


#先显示出每个部门中所有员工的基本信息及薪水等级
#方式一：
mysql> select e.deptno,e.ename,e.sal ,d.grade from salgrade d,emp e where e.sal between d.losal and d.hisal order by e.deptno;
+--------+--------+---------+-------+
| deptno | ename | sal | grade |
+--------+--------+---------+-------+
| 10 | CLARK | 1000.00 | 1 |
| 10 | KING | 1000.00 | 1 |
| 10 | MILLER | 1000.00 | 1 |
| 20 | SMITH | 800.00 | 1 |
| 20 | ADAMS | 1100.00 | 1 |
| 20 | JONES | 2975.00 | 4 |
| 20 | SCOTT | 3000.00 | 4 |
| 20 | FORD | 3000.00 | 4 |
| 30 | JAMES | 950.00 | 1 |
| 30 | WARD | 1250.00 | 2 |
| 30 | MARTIN | 1250.00 | 2 |
| 30 | TURNER | 1500.00 | 3 |
| 30 | ALLEN | 1600.00 | 3 |
| 30 | BLAKE | 2850.00 | 4 |
+--------+--------+---------+-------+
14 rows in set (0.00 sec)
#方式二：通过join...on..的方式实现表的连接
mysql> select e.deptno,e.ename,e.sal ,d.grade from salgrade d join emp e on e.sal between d.losal and d.hisal order by e.deptno;
+--------+--------+---------+-------+
| deptno | ename | sal | grade |
+--------+--------+---------+-------+
| 10 | CLARK | 1000.00 | 1 |
| 10 | KING | 1000.00 | 1 |
| 10 | MILLER | 1000.00 | 1 |
| 20 | SMITH | 800.00 | 1 |
| 20 | ADAMS | 1100.00 | 1 |
| 20 | JONES | 2975.00 | 4 |
| 20 | SCOTT | 3000.00 | 4 |
| 20 | FORD | 3000.00 | 4 |
| 30 | JAMES | 950.00 | 1 |
| 30 | WARD | 1250.00 | 2 |
| 30 | MARTIN | 1250.00 | 2 |
| 30 | TURNER | 1500.00 | 3 |
| 30 | ALLEN | 1600.00 | 3 |
| 30 | BLAKE | 2850.00 | 4 |
+--------+--------+---------+-------+
14 rows in set (0.00 sec)

#薪水等级求平均值
mysql> select e.deptno,e.ename,e.sal ,avg(d.grade) from salgrade d,emp e where e.sal between d.losal and d.hisal group by e.deptno;
+--------+-------+---------+--------------+
| deptno | ename | sal | avg(d.grade) |
+--------+-------+---------+--------------+
| 20 | SMITH | 800.00 | 2.8000 |
| 30 | JAMES | 950.00 | 2.5000 |
| 10 | CLARK | 1000.00 | 1.0000 |
+--------+-------+---------+--------------+
3 rows in set (0.00 sec)
试题四：不准用组函数（Max），取得最高薪水（给出两种方案）
分析：

方案一：sal降序，limit1

      ①将表依据薪水的高低进行排序

      ②然后通过limit进行取出最高薪水

方案二：表的自连接

      ①通过自连接生成一薪水表（原表的最高薪水不在其中）

      ②通过not in 的方式查出最高薪水      

#方案一：
#将表按照薪水高低降序排序
mysql> select ename,sal from emp order by sal desc ;
+--------+---------+
| ename | sal |
+--------+---------+
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS | 1100.00 |
| CLARK | 1000.00 |
| KING | 1000.00 |
| MILLER | 1000.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+
14 rows in set (0.00 sec)
#通过limit来取出排序表中的第一个记录
mysql> select ename,sal from emp order by sal desc limit 1;
+-------+---------+
| ename | sal |
+-------+---------+
| FORD | 3000.00 |
+-------+---------+
1 row in set (0.00 sec)

#方式二：自连接
mysql> select sal from emp;
+---------+
| sal |
+---------+
| 800.00 |
| 950.00 |
| 1000.00 |
| 1000.00 |
| 1000.00 |
| 1100.00 |
| 1250.00 |
| 1250.00 |
| 1500.00 |
| 1600.00 |
| 2850.00 |
| 2975.00 |
| 3000.00 |
| 3000.00 |
+---------+
14 rows in set (0.00 sec)
#通过自连接得到的结果中只有 3000（最高薪）不满足（distinct是用来去重的）
mysql> select distinct a.sal from emp a join emp b on a.sal < b.sal;
+---------+
| sal |
+---------+
| 800.00 |
| 950.00 |
| 1000.00 |
| 1100.00 |
| 1250.00 |
| 1500.00 |
| 1600.00 |
| 2850.00 |
| 2975.00 |
+---------+
9 rows in set (0.00 sec)

mysql> select sal from emp where sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal);
+---------+
| sal |
+---------+
| 3000.00 |
| 3000.00 |
+---------+
2 rows in set (0.00 sec)

#去重
mysql> select distinct sal from emp where sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal);
+---------+
| sal |
+---------+
| 3000.00 |
+---------+
1 row in set (0.00 sec)
试题五：取得平均薪水最高的部门的部门编号（至少给出两种方案）
分析：

方案一：limit

    ①将员工表根据部门进行分组

    ②根据函数（avg）求出每个组中的平均薪水

    ③根据薪水进行排序

    ④然后通过limit选取薪水最高的部门

方案二：max

    ①将员工表根据部门进行分组并求出平均值

    ②通过max函数求出最大的


#方案一
#根据部门进行分组、求出平均值、排序
mysql> select deptno,avg(sal) avgsal from emp group by deptno order by avg(sal) desc;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 1000.000000 |
+--------+-------------+
3 rows in set (0.00 sec)

#通过limit选取第一个记录
mysql> select deptno,avg(sal) avgsal from emp group by deptno order by avg(sal) desc limit 1;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 20 | 2175.000000 |
+--------+-------------+
1 row in set (0.00 sec)

mysql> select deptno from emp group by deptno order by avg(sal) desc limit 1;
+--------+
| deptno |
+--------+
| 20 |
+--------+
1 row in set (0.00 sec)

方案二：max

#第一步：求出每个部门的平均薪水
mysql> select deptno,avg(sal) avgsal from emp group by deptno order by avg(sal) desc;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 1000.000000 |
+--------+-------------+
3 rows in set (0.00 sec)

#通过max函数找到平均薪水最高的部门
mysql> select t.deptno,max(t.avgsal) maxavgsal from (select deptno,avg(sal) avgsal from emp group by deptno order by avg(sal) desc) t;
+--------+-------------+
| deptno | maxavgsal |
+--------+-------------+
| 20 | 2175.000000 |
+--------+-------------+
1 row in set (0.00 sec)
试题六：取得平均薪水最高的部门名称
分析：

①先取得平均薪水最高的·部门编号

②连表查询得到部门编号对应的部门名称

mysql> select e.deptno,avg(e.sal) ,d.dname from emp e,dept d where e.deptno = d.deptno group by e.deptno order by avg(e.sal) desc;
+--------+-------------+------------+
| deptno | avg(e.sal) | dname |
+--------+-------------+------------+
| 20 | 2175.000000 | RESEARCH |
| 30 | 1566.666667 | SALES |
| 10 | 1000.000000 | ACCOUNTING |
+--------+-------------+------------+
3 rows in set (0.00 sec)

mysql> select e.deptno,avg(e.sal) ,d.dname from emp e,dept d where e.deptno = d.deptno group by e.deptno order by avg(e.sal) desc limit 1;
+--------+-------------+----------+
| deptno | avg(e.sal) | dname |
+--------+-------------+----------+
| 20 | 2175.000000 | RESEARCH |
+--------+-------------+----------+
1 row in set (0.00 sec)
#剔除无用信息
mysql> select d.dname from emp e,dept d where e.deptno = d.deptno group by e.deptno order by avg(e.sal) desc limit 1;
+----------+
| dname |
+----------+
| RESEARCH |
+----------+
1 row in set (0.00 sec)

试题七：取得平均薪水等级最低的部门名称
#找出每个部门的平均薪水
mysql> select e.deptno,avg(e.sal) ,d.dname from emp e,dept d where e.deptno = d.deptno group by e.deptno order by avg(e.sal) desc;
+--------+-------------+------------+
| deptno | avg(e.sal) | dname |
+--------+-------------+------------+
| 20 | 2175.000000 | RESEARCH |
| 30 | 1566.666667 | SALES |
| 10 | 1000.000000 | ACCOUNTING |
+--------+-------------+------------+
3 rows in set (0.00 sec)

#找出每个部门的平均薪水等级

mysql> select t.*,e.grade from salgrade e join ( select e.deptno,avg(e.sal) avgsal ,d.dname from emp e,dept d where e.deptno = d.deptno group by e.deptno order by avg(e.sal) desc) t on t.avgsal between e.losal and e.hisal;
+--------+-------------+------------+-------+
| deptno | avgsal | dname | grade |
+--------+-------------+------------+-------+
| 20 | 2175.000000 | RESEARCH | 4 |
| 30 | 1566.666667 | SALES | 3 |
| 10 | 1000.000000 | ACCOUNTING | 1 |
+--------+-------------+------------+-------+
3 rows in set (0.00 sec)

#找出最低薪水
mysql> select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1;
+-------------+
| avgsal |
+-------------+
| 1000.000000 |
+-------------+
1 row in set (0.00 sec)

#找出平均薪水中等级最低的
mysql> select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1)between losal and hisal;
+-------+
| grade |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)

mysql> select t.*,s.grade from salgrade s join ( select e.deptno,avg(e.sal) avgsal ,d.dname from emp e,dept d where e.deptno = d.deptno group by e.deptno order by avg(e.sal) desc) t on t.avgsal between s.losal and s.hisal where s.grade =(select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1)between losal and hisal);
+--------+-------------+------------+-------+
| deptno | avgsal | dname | grade |
+--------+-------------+------------+-------+
| 10 | 1000.000000 | ACCOUNTING | 1 |
+--------+-------------+------------+-------+
1 row in set (0.00 sec)
试题八：取得比普通员工（员工代码没有在mgr字段上出现的）最高薪水还要高的领导人姓名

分析：

① 找到普通员工的最高薪水

②找出高于最高薪水的领导

#查找所有领导的编号
mysql> select distinct mgr from emp where mgr is not null;
+------+
| mgr |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
6 rows in set (0.00 sec)
#从非领导编号中查找员工中的最高工资
mysql> select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null);
+----------+
| max(sal) |
+----------+
| 1600.00 |
+----------+
1 row in set (0.00 sec)

#再根据最员工的最高工资找到对应的领导人名字
mysql> 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));
+-------+---------+
| ename | sal |
+-------+---------+
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
+-------+---------+
4 rows in set (0.00 sec)

试题九：取得薪水最高的前五名员工
分析

①将表进行排序（升序）

②通过limit来获取前五个员工的信息

#说明：第一个员工对应的标号是0
mysql> select ename ,sal from emp order by sal desc limit 0,5;
+-------+---------+
| ename | sal |
+-------+---------+
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| ALLEN | 1600.00 |
+-------+---------+
5 rows in set (0.00 sec)
试题十：取得薪水最高的第六到第十名员工
分析：

① 将表按照薪水进行降序排列

② 通过limit获取特定名次的员工

#从第六名（下表为5）到第十名（共5个人）
mysql> select ename ,sal from emp order by sal desc limit 5,5;
+--------+---------+
| ename | sal |
+--------+---------+
| TURNER | 1500.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS | 1100.00 |
| CLARK | 1000.00 |
+--------+---------+
5 rows in set (0.00 sec)
试题十一：取得最后入职的5名员工
分析：如期也是可以降序或升序排列的

① 将员工表按照员工入职的时间降序排列

② 通过limit获取的前五个员工就是最后入职的五个员工

#根据入职时间降序排列（由晚到早）
mysql> select ename,hiredate from emp order by hiredate desc;
+--------+------------+
| ename | hiredate |
+--------+------------+
| ADAMS | 1987-05-23 |
| SCOTT | 1987-04-19 |
| MILLER | 1982-01-23 |
| JAMES | 1981-12-03 |
| FORD | 1981-12-03 |
| KING | 1981-11-17 |
| MARTIN | 1981-09-28 |
| TURNER | 1981-09-08 |
| CLARK | 1981-06-09 |
| BLAKE | 1981-05-01 |
| JONES | 1981-04-02 |
| WARD | 1981-02-22 |
| ALLEN | 1981-02-20 |
| SMITH | 1980-12-17 |
+--------+------------+
14 rows in set (0.00 sec)
#通过limit获取前五个
mysql> select ename,hiredate from emp order by hiredate desc limit 0,5;
+--------+------------+
| ename | hiredate |
+--------+------------+
| ADAMS | 1987-05-23 |
| SCOTT | 1987-04-19 |
| MILLER | 1982-01-23 |
| JAMES | 1981-12-03 |
| FORD | 1981-12-03 |
+--------+------------+
5 rows in set (0.00 sec)
试题十二：取得每个薪水等级有多少员工
分析：

①找出每个员工的薪水等级

②分组统计

mysql> select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename | sal | grade |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| JAMES | 950.00 | 1 |
| CLARK | 1000.00 | 1 |
| KING | 1000.00 | 1 |
| MILLER | 1000.00 | 1 |
| ADAMS | 1100.00 | 1 |
| WARD | 1250.00 | 2 |
| MARTIN | 1250.00 | 2 |
| TURNER | 1500.00 | 3 |
| ALLEN | 1600.00 | 3 |
| BLAKE | 2850.00 | 4 |
| JONES | 2975.00 | 4 |
| SCOTT | 3000.00 | 4 |
| FORD | 3000.00 | 4 |
+--------+---------+-------+
14 rows in set (0.00 sec)
#按照级别分组统计
mysql> select s.grade, count(*) from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade;
+-------+----------+
| grade | count(*) |
+-------+----------+
| 1 | 6 |
| 2 | 2 |
| 3 | 2 |
| 4 | 4 |
+-------+----------+
4 rows in set (0.00 sec)
试题十四：列出所有员工及领导姓名
分析：

①将一张表当做两张表来使用

②第一个表中取出员工名称 和 领导编号 ，第二个表中取出编号对应领导名称

mysql> 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 | 1000.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 1000.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 | 1000.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

#使用左外连接
mysqlmysql> select e.ename ,d. ename from emp e left join emp d on e.mgr=d.empno;
+--------+-------+
| ename | ename |
+--------+-------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+-------+
14 rows in set (0.00 sec)
试题十五：列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称
分析：

①条件限制中添加 编号对应相同 并且 员工受雇时间早于直接上级受雇时间

mysql> select a.ename '员工' ,a.hiredate ,b.ename'领导',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;
+-------+------------+-------+------------+------------+
| 员工 | hiredate | 领导 | hiredate | dname |
+-------+------------+-------+------------+------------+
| SMITH | 1980-12-17 | FORD | 1981-12-03 | RESEARCH |
| ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | SALES |
| WARD | 1981-02-22 | BLAKE | 1981-05-01 | SALES |
| JONES | 1981-04-02 | KING | 1981-11-17 | RESEARCH |
| BLAKE | 1981-05-01 | KING | 1981-11-17 | SALES |
| CLARK | 1981-06-09 | KING | 1981-11-17 | ACCOUNTING |
+-------+------------+-------+------------+------------+
6 rows in set (0.00 sec)
试题十六：列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
分析：左外连接

mysql> select e.*,d.* from emp e right join dept d on e.deptno = d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 1000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 1000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 40 | OPERATIONS | BOSTON |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
15 rows in set (0.00 sec)
试题十七：列出至少有5个员工的所有部门
分析：

①先查找出每个部门中的员工总人数

②然后再通过条件限制找到满足条件的部门

#查询出每个部门中的人数并按照部门人数从高到底进行排列
mysql> select deptno,count(deptno) number from emp group by deptno order by count(deptno) desc;
+--------+--------+
| deptno | number |
+--------+--------+
| 30 | 6 |
| 20 | 5 |
| 10 | 3 |
+--------+--------+
3 rows in set (0.00 sec)

mysql> select deptno from emp group by deptno having count(*) >=5;
+--------+
| deptno |
+--------+
| 20 |
| 30 |
+--------+
2 rows in set (0.00 sec)
试题十八：列出薪金比“SMITH”多的员工信息
分析：

①先查询对应员工的薪水信息

②根据第①步中查出的薪水信息来筛选员工信息

#分开查询
#先查询对应名称员工的薪水
mysql> select ename,sal from emp where ename ='SMITH';
+-------+--------+
| ename | sal |
+-------+--------+
| SMITH | 800.00 |
+-------+--------+
1 row in set (0.00 sec)

#根据薪水来进行筛选出薪资大于800的员工信息
mysql> select empno,ename,job,mgr,hiredate,sal ,comm,deptno from emp where sal>800;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 | 1000.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 1000.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 | 1000.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)

#嵌套查询
mysql> select empno,ename,job,mgr,hiredate,sal comm,deptno from emp where sal>(select sal from emp where ename ='SMITH');
+-------+--------+-----------+------+------------+---------+--------+
| empno | ename | job | mgr | hiredate | comm | deptno |
+-------+--------+-----------+------+------------+---------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 1000.00 | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 1000.00 | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1000.00 | 10 |
+-------+--------+-----------+------+------------+---------+--------+
13 rows in set (0.00 sec)
试题十九：列出所有“CLERKsele（办事员）”的姓名及其部门名称和人数
分析：两张表之间的连接查询

①从第一张表中查出对应职位的员工姓名

②根据第一个表中的部门编号对应第二个表中的部门编号从而查找部门名称

#查找对应的工作对应的员工姓名及部门名称
mysql> select e.ename,d.dname,d.deptno from emp e,dept d where e.job='CLERK' and (e.deptno = d.deptno);
+--------+------------+--------+
| ename | dname | deptno |
+--------+------------+--------+
| SMITH | RESEARCH | 20 |
| JAMES | SALES | 30 |
| MILLER | ACCOUNTING | 10 |
| ADAMS | RESEARCH | 20 |
+--------+------------+--------+
4 rows in set (0.00 sec)

#每个部门的人数
mysql> select deptno,count(*) as deptcount from emp group by deptno;
+--------+-----------+
| deptno | deptcount |
+--------+-----------+
| 20 | 5 |
| 30 | 6 |
| 10 | 3 |
+--------+-----------+
3 rows in set (0.00 sec)

mysql> select t1.*,t2.deptcount from (select e.ename,d.dname,d.deptno from emp e,dept d where e.job='CLERK' and (e.deptno = d.deptno)) t1 join (select deptno,count(*) as deptcount from emp group by deptno)t2 on t1.deptno=t2.deptno;
+--------+------------+--------+-----------+
| ename | dname | deptno | deptcount |
+--------+------------+--------+-----------+
| SMITH | RESEARCH | 20 | 5 |
| JAMES | SALES | 30 | 6 |
| MILLER | ACCOUNTING | 10 | 3 |
| ADAMS | RESEARCH | 20 | 5 |
+--------+------------+--------+-----------+
4 rows in set (0.00 sec)
试题二十：列出底薪金大于1500的各种工作及从事此工作的全部雇员人数
分析：

① 根据工作分组

②然后统计每个分组中工资大于1500员工总人数

#查询工资大于1500的员工信息
mysql> select job from emp group by job having min(sal)>1500;
+---------+
| job |
+---------+
| ANALYST |
+---------+
1 row in set (0.00 sec)

#根据工作进行分组后并统计每个工作中的员工总数
mysql> select job ,count(*) from emp group by job having min(sal)>1500;
+---------+----------+
| job | count(*) |
+---------+----------+
| ANALYST | 2 |
+---------+----------+
1 row in set (0.00 sec)
试题二十一：列出在部门“SALES”<销售部>工作的员工的姓名，假定不知道销售部门的编号
分析：

从部门表中选出销售部门编号和员工表中的编号对应取出员工姓名

mysql> select e.ename from emp e,dept d where d.dname='SALES'and (e.deptno=d.deptno);
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
+--------+
6 rows in set (0.00 sec)
试题二十二：列出薪金高于公司平均薪资的所有员工，所在部门、上级领导、雇员的工资等级
mysql> select e.ename'员工',d.dname,l.ename'领导',s.grade from emp e join dept d on e.deptno =d.deptno left join emp l on e.mgr =l.empno join salgrade s on e.sal between s.losal and s.hisal where e.sal >(select avg(sal) from emp);
+-------+----------+-------+-------+
| 员工 | dname | 领导 | grade |
+-------+----------+-------+-------+
| FORD | RESEARCH | JONES | 4 |
| SCOTT | RESEARCH | JONES | 4 |
| JONES | RESEARCH | KING | 4 |
| BLAKE | SALES | KING | 4 |
+-------+----------+-------+-------+
4 rows in set (0.00 sec)
试题二十三：列出与“SCOTT”从事相同工作的所有员工及其部门名称
分析：

① 先找到对应员工对应的职业

②然后根据限制条件找（职业和两张表对应的部门编号）到合适的员工信息

#第一步：从员工信息表总查找该员工对应的职业名称
mysql> select job from emp where (ename ='SCOTT');
+---------+
| job |
+---------+
| ANALYST |
+---------+
1 row in set (0.00 sec)

#根据职业名称和对应的员工表和部门表编号匹配找到合适的信息
mysql> select e.ename,e.job,d.dname from emp e,dept d where d.deptno= e.deptno and ( job='ANALYST');
+-------+---------+----------+
| ename | job | dname |
+-------+---------+----------+
| SCOTT | ANALYST | RESEARCH |
| FORD | ANALYST | RESEARCH |
+-------+---------+----------+
2 rows in set (0.00 sec)

#将‘SCOTT’排除在外
mysql> select e.ename ,e.job ,d.dname from emp e join dept d on e.deptno = d.deptno where e.job =(select job from emp where ename ='SCOTT') and e.ename<>'SCOTT';
+-------+---------+----------+
| ename | job | dname |
+-------+---------+----------+
| FORD | ANALYST | RESEARCH |
+-------+---------+----------+
1 row in set (0.00 sec)
试题二十四：列出薪金等于部门 30 中员工的薪资的其他员工的姓名和薪资
#查找部门编号为30的薪资
mysql> select distinct sal from emp where deptno =30;
+---------+
| sal |
+---------+
| 950.00 |
| 1250.00 |
| 1500.00 |
| 1600.00 |
| 2850.00 |
+---------+
5 rows in set (0.00 sec)
#查询其他员工
mysql> select ename,sal from emp where sal in(select distinct sal from emp where deptno =30) and deptno <>30;
Empty set (0.00 sec)
试题二十五：列出薪资高于所在部门 30 工作的所有员工的薪资的员工姓名和薪资、部门名称
#找出最高薪资
mysql> select max(sal)from emp;
+----------+
| max(sal) |
+----------+
| 3000.00 |
+----------+
1 row in set (0.00 sec)

mysql> select e.ename,e.sal,d.dname from emp e join dept d on e.deptno =d.deptno where e.sal >(select max(sal) from emp where deptno =30);
+-------+---------+----------+
| ename | sal | dname |
+-------+---------+----------+
| JONES | 2975.00 | RESEARCH |
| SCOTT | 3000.00 | RESEARCH |
| FORD | 3000.00 | RESEARCH |
+-------+---------+----------+
3 rows in set (0.00 sec)
试题二十六：列出在每个部门工作的员工数量，平均薪资和平均服务期限
分析：

平均服务期限=系统当前年份-入职年份

如何计算呢？？

TimeStampDiff(间隔类型，前一个日期，后一个日期)

间隔类型：

    （1）SECOND:秒     （2）MINUTE：分钟  （3）HOUR：小时

    （4）WEEK：星期     （5）MONTH：月      （6）QUARTER:季度

    （7）YEAR:年

mysql> select d.*,count(e.ename) ecount ,avg(e.sal) as avgsal,avg(timestampdiff(YEAR,hiredate,now()))as avgsevertime from emp e right join dept d on e.deptno = d.deptno group by d.deptno,d.dname,d.loc;
+--------+------------+----------+--------+-------------+--------------+
| DEPTNO | DNAME | LOC | ecount | avgsal | avgsevertime |
+--------+------------+----------+--------+-------------+--------------+
| 10 | ACCOUNTING | NEW YORK | 3 | 1000.000000 | 40.3333 |
| 20 | RESEARCH | DALLAS | 5 | 2175.000000 | 38.4000 |
| 30 | SALES | CHICAGO | 6 | 1566.666667 | 40.8333 |
| 40 | OPERATIONS | BOSTON | 0 | NULL | NULL |
+--------+------------+----------+--------+-------------+--------------+
4 rows in set (0.00 sec)
试题二十七：列出所有员工的姓名、部门名称和工资
mysql> select e.ename '员工姓名',d.deptno '部门名称',e.sal '工资' from emp e,dept d where d.deptno=e.deptno;
+----------+----------+---------+
| 员工姓名 | 部门名称 | 工资 |
+----------+----------+---------+
| SMITH | 20 | 800.00 |
| ALLEN | 30 | 1600.00 |
| WARD | 30 | 1250.00 |
| JONES | 20 | 2975.00 |
| MARTIN | 30 | 1250.00 |
| BLAKE | 30 | 2850.00 |
| CLARK | 10 | 1000.00 |
| SCOTT | 20 | 3000.00 |
| KING | 10 | 1000.00 |
| TURNER | 30 | 1500.00 |
| ADAMS | 20 | 1100.00 |
| JAMES | 30 | 950.00 |
| FORD | 20 | 3000.00 |
| MILLER | 10 | 1000.00 |
+----------+----------+---------+
14 rows in set (0.00 sec)
试题二十八：列出所有部门的详细信息和人数
#根据部门编号简单的将两张表连接起来，显示员工对应的基本信息
mysql> select e.ename,sal,e.hiredate,e.mgr,e.comm,d.dname from emp e,dept d where d.deptno=e.deptno order by e.deptno;
+--------+---------+------------+------+---------+------------+
| ename | sal | hiredate | mgr | comm | dname |
+--------+---------+------------+------+---------+------------+
| CLARK | 1000.00 | 1981-06-09 | 7839 | NULL | ACCOUNTING |
| KING | 1000.00 | 1981-11-17 | NULL | NULL | ACCOUNTING |
| MILLER | 1000.00 | 1982-01-23 | 7782 | NULL | ACCOUNTING |
| SMITH | 800.00 | 1980-12-17 | 7902 | NULL | RESEARCH |
| JONES | 2975.00 | 1981-04-02 | 7839 | NULL | RESEARCH |
| SCOTT | 3000.00 | 1987-04-19 | 7566 | NULL | RESEARCH |
| ADAMS | 1100.00 | 1987-05-23 | 7788 | NULL | RESEARCH |
| FORD | 3000.00 | 1981-12-03 | 7566 | NULL | RESEARCH |
| ALLEN | 1600.00 | 1981-02-20 | 7698 | 300.00 | SALES |
| WARD | 1250.00 | 1981-02-22 | 7698 | 500.00 | SALES |
| MARTIN | 1250.00 | 1981-09-28 | 7698 | 1400.00 | SALES |
| BLAKE | 2850.00 | 1981-05-01 | 7839 | NULL | SALES |
| TURNER | 1500.00 | 1981-09-08 | 7698 | 0.00 | SALES |
| JAMES | 950.00 | 1981-12-03 | 7698 | NULL | SALES |
+--------+---------+------------+------+---------+------------+
14 rows in set (0.00 sec)
#根据部门进行分组并统计部门中的员工人数
mysql> select d.deptno,d.dname,d.loc,count(e.ename) from emp e right join dept d on e.deptno=d.deptno group by d.deptno,d.dname,d.loc;
+--------+------------+----------+----------------+
| deptno | dname | loc | count(e.ename) |
+--------+------------+----------+----------------+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 40 | OPERATIONS | BOSTON | 0 |
+--------+------------+----------+----------------+
4 rows in set (0.00 sec)
试题二十九：列出各种工作的最低工资及从事此工作的雇员姓名
分析：

     根据工资进行分组，然后从分组中找出工资最少的员工信息

#查询员工的姓名、工作、薪资等信息
mysql> select e.ename name,e.job,e.sal from emp e;
+--------+-----------+---------+
| name | job | sal |
+--------+-----------+---------+
| SMITH | CLERK | 800.00 |
| ALLEN | SALESMAN | 1600.00 |
| WARD | SALESMAN | 1250.00 |
| JONES | MANAGER | 2975.00 |
| MARTIN | SALESMAN | 1250.00 |
| BLAKE | MANAGER | 2850.00 |
| CLARK | MANAGER | 1000.00 |
| SCOTT | ANALYST | 3000.00 |
| KING | PRESIDENT | 1000.00 |
| TURNER | SALESMAN | 1500.00 |
| ADAMS | CLERK | 1100.00 |
| JAMES | CLERK | 950.00 |
| FORD | ANALYST | 3000.00 |
| MILLER | CLERK | 1000.00 |
+--------+-----------+---------+
14 rows in set (0.00 sec)

#根据员工的工作分组并查询信息
mysql> select e.ename name,e.job,min(e.sal) lowestsalary from emp e group by job;
+-------+-----------+--------------+
| name | job | lowestsalary |
+-------+-----------+--------------+
| SCOTT | ANALYST | 3000.00 |
| SMITH | CLERK | 800.00 |
| CLARK | MANAGER | 1000.00 |
| KING | PRESIDENT | 1000.00 |
| WARD | SALESMAN | 1250.00 |
+-------+-----------+--------------+
5 rows in set (0.00 sec)
试题三十：列各个部门的 MANAGER（领导）的最低薪金
mysql> select deptno,min(sal) from emp where job ='MANAGER'group by deptno;
+--------+----------+
| deptno | min(sal) |
+--------+----------+
| 10 | 1000.00 |
| 30 | 2850.00 |
| 20 | 2975.00 |
+--------+----------+
3 rows in set (0.00 sec)
试题三十一：列出所有员工的年工薪，按照年薪从低到高排序
mysql> select ename '员工姓名',sal*12 '年薪' from emp order by (sal*12) asc;
+----------+----------+
| 员工姓名 | 年薪 |
+----------+----------+
| SMITH | 9600.00 |
| JAMES | 11400.00 |
| CLARK | 12000.00 |
| KING | 12000.00 |
| MILLER | 12000.00 |
| ADAMS | 13200.00 |
| WARD | 15000.00 |
| MARTIN | 15000.00 |
| TURNER | 18000.00 |
| ALLEN | 19200.00 |
| BLAKE | 34200.00 |
| JONES | 35700.00 |
| SCOTT | 36000.00 |
| FORD | 36000.00 |
+----------+----------+
14 rows in set (0.00 sec)

#改进（如果薪水为null，就设置为0）
mysql> select ename,(sal+ifnull(comm,0))*12 as yearsal from emp order by yearsal asc;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | 9600.00 |
| JAMES | 11400.00 |
| CLARK | 12000.00 |
| KING | 12000.00 |
| MILLER | 12000.00 |
| ADAMS | 13200.00 |
| TURNER | 18000.00 |
| WARD | 21000.00 |
| ALLEN | 22800.00 |
| MARTIN | 31800.00 |
| BLAKE | 34200.00 |
| JONES | 35700.00 |
| SCOTT | 36000.00 |
| FORD | 36000.00 |
+--------+----------+
14 rows in set (0.00 sec)
试题三十二：求出员工领导的薪水超过 3000 的员工名称与领导名称
分析：

①先求出员工和领导的总工资（每个月）

②通过条件限制从而选出合适的员工和领导

mysql> select e.ename,d.ename,d.sal+e.sal from emp e,emp d where e.mgr=d.empno;
+--------+-------+-------------+
| ename | ename | d.sal+e.sal |
+--------+-------+-------------+
| SMITH | FORD | 3800.00 |
| ALLEN | BLAKE | 4450.00 |
| WARD | BLAKE | 4100.00 |
| JONES | KING | 3975.00 |
| MARTIN | BLAKE | 4100.00 |
| BLAKE | KING | 3850.00 |
| CLARK | KING | 2000.00 |
| SCOTT | JONES | 5975.00 |
| TURNER | BLAKE | 4350.00 |
| ADAMS | SCOTT | 4100.00 |
| JAMES | BLAKE | 3800.00 |
| FORD | JONES | 5975.00 |
| MILLER | CLARK | 2000.00 |
+--------+-------+-------------+
13 rows in set (0.00 sec)

mysql> select e.ename '员工名称',d.ename'领导名称',d.sal+e.sal '总薪水' from emp e,emp d where e.mgr=d.empno and ((d.sal + e.sal)>3000);
+----------+----------+---------+
| 员工名称 | 领导名称 | 总薪水 |
+----------+----------+---------+
| SMITH | FORD | 3800.00 |
| ALLEN | BLAKE | 4450.00 |
| WARD | BLAKE | 4100.00 |
| JONES | KING | 3975.00 |
| MARTIN | BLAKE | 4100.00 |
| BLAKE | KING | 3850.00 |
| SCOTT | JONES | 5975.00 |
| TURNER | BLAKE | 4350.00 |
| ADAMS | SCOTT | 4100.00 |
| JAMES | BLAKE | 3800.00 |
| FORD | JONES | 5975.00 |
+----------+----------+---------+
11 rows in set (0.00 sec)
试题三十三：求出部门名称中带‘S’字符的部门员工的工资合计、部门人数
分析：

通过 %来查找带有特定字母的名字

mysql> select d.dname '部门',sum(e.sal) '工资合计'from emp e,dept d where e.deptno = d.deptno group by e.deptno ;
+------------+----------+
| 部门 | 工资合计 |
+------------+----------+
| RESEARCH | 10875.00 |
| SALES | 9400.00 |
| ACCOUNTING | 3000.00 |
+------------+----------+
3 rows in set (0.00 sec)

mysql> select d.dname '部门',sum(e.sal) '工资合计',count(e.ename) '部门人数'from emp e,dept d where d.dname like "%S%" and (e.deptno = d.deptno) group by e.deptno ;
+----------+----------+----------+
| 部门 | 工资合计 | 部门人数 |
+----------+----------+----------+
| RESEARCH | 10875.00 | 5 |
| SALES | 9400.00 | 6 |
+----------+----------+----------+
2 rows in set (0.00 sec)
#方式二

mysql> select d.deptno,d.dname,d.loc,count(e.ename),ifnull(sum(e.sal),0) as sumsal from emp e join dept d on e.deptno =d.deptno where d.dname like'%S%' group by d.deptno,d.dname,d.loc;
+--------+----------+---------+----------------+----------+
| deptno | dname | loc | count(e.ename) | sumsal |
+--------+----------+---------+----------------+----------+
| 20 | RESEARCH | DALLAS | 5 | 10875.00 |
| 30 | SALES | CHICAGO | 6 | 9400.00 |
+--------+----------+---------+----------------+----------+
2 rows in set (0.01 sec)
试题三十四：给任职日期超过 30 年的员工加薪 10%
#显示员工表
mysql> 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 | 1000.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 1000.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 | 1000.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
练习题练习spanstylecolorMYSQL练习题MySQL数据库yyds干货盘点

有关MYSQY 学习笔记之 练习题的更多相关文章

  1. LC滤波器设计学习笔记（一）滤波电路入门 - 2

    目录前言滤波电路科普主要分类实际情况单位的概念常用评价参数函数型滤波器简单分析滤波电路构成低通滤波器RC低通滤波器RL低通滤波器高通滤波器RC高通滤波器RL高通滤波器部分摘自《LC滤波器设计与制作》，侵权删。前言最近需要学习放大电路和滤波电路，但是由于只在之前做音乐频谱分析仪的时候简单了解过一点点运放，所以也是相当从零开始学习了。滤波电路科普主要分类滤波器：主要是从不同频率的成分中提取出特定频率的信号。有源滤波器：由RC元件与运算放大器组成的滤波器。可滤除某一次或多次谐波，最普通易于采用的无源滤波器结构是将电感与电容串联，可对主要次谐波（3、5、7）构成低阻抗旁路。无源滤波器：无源滤波器，又称

  2. CAN协议的学习与理解 - 2

    最近在学习CAN，记录一下，也供大家参考交流。推荐几个我觉得很好的CAN学习，本文也是在看了他们的好文之后做的笔记首先是瑞萨的CAN入门，真的通透；秀！靠这篇我竟然2天理解了CAN协议！实战STM32F4CAN！原文链接：https://blog.csdn.net/XiaoXiaoPengBo/article/details/116206252CAN详解（小白教程）原文链接：https://blog.csdn.net/xwwwj/article/details/105372234一篇易懂的CAN通讯协议指南1一篇易懂的CAN通讯协议指南1-知乎(zhihu.com)视频推荐CAN总线个人知识总

  3. 深度学习部署：Windows安装pycocotools报错解决方法 - 2

    深度学习部署：Windows安装pycocotools报错解决方法1.pycocotools库的简介2.pycocotools安装的坑3.解决办法更多Ai资讯：公主号AiCharm本系列是作者在跑一些深度学习实例时，遇到的各种各样的问题及解决办法，希望能够帮助到大家。ERROR:Commanderroredoutwithexitstatus1:'D:\Anaconda3\python.exe'-u-c'importsys,setuptools,tokenize;sys.argv[0]='"'"'C:\\Users\\46653\\AppData\\Local\\Temp\\pip-instal

  4. 牛客网专项练习30天Pytnon篇第02天 - 2

    1.在Python3中，下列关于数学运算结果正确的是:（B）a=10b=3print(a//b)print(a%b)print(a/b)A.3，3，3.3333...B.3，1，3.3333...C.3.3333...，3.3333...，3D.3.3333...，1，3.3333...解析：    在Python中，//表示地板除（向下取整），%表示取余，/表示除（Python2向下取整返回3）2.如下程序Python2会打印多少个数:（D）k=1000whilek>1:    print(k)k=k/2A.1000 B.10C.11D.9解析：    按照题意每次循环K/2，直到K值小于等

  5. ruby - 我正在学习编程并选择了 Ruby。我应该升级到 Ruby 1.9 吗？ - 2

    我完全不是程序员，正在学习使用Ruby和Rails框架进行编程。我目前正在使用Ruby1.8.7和Rails3.0.3，但我想知道我是否应该升级到Ruby1.9，因为我真的没有任何升级的“遗留”成本。缺点是什么？我是否会遇到与普通gem的兼容性问题，或者甚至其他我不太了解甚至无法预料的问题？ 最佳答案 你应该升级。不要坚持从1.8.7开始。如果您发现不支持1.9.2的gem，请避免使用它们(因为它们很可能不被维护)。如果您对gem是否兼容1.9.2有任何疑问，您可以在以下位置查看:http://www.railsplugins.or

  6. ruby - 我如何学习 ruby​​ 的正则表达式？ - 2

    如何学习ruby​​的正则表达式？(对于假人) 最佳答案 http://www.rubular.com/在Ruby中使用正则表达式时是一个很棒的工具，因为它可以立即将结果可视化。 关于ruby-我如何学习ruby​​的正则表达式？，我们在StackOverflow上找到一个类似的问题： https://stackoverflow.com/questions/1881231/

  7. 深度学习12. CNN经典网络 VGG16 - 2

    深度学习12.CNN经典网络VGG16一、简介1.VGG来源2.VGG分类3.不同模型的参数数量4.3x3卷积核的好处5.关于学习率调度6.批归一化二、VGG16层分析1.层划分2.参数展开过程图解3.参数传递示例4.VGG16各层参数数量三、代码分析1.VGG16模型定义2.训练3.测试一、简介1.VGG来源VGG（VisualGeometryGroup）是一个视觉几何组在2014年提出的深度卷积神经网络架构。VGG在2014年ImageNet图像分类竞赛亚军，定位竞赛冠军；VGG网络采用连续的小卷积核（3x3）和池化层构建深度神经网络，网络深度可以达到16层或19层，其中VGG16和VGG

  8. 机器学习——时间序列ARIMA模型(四)：自相关函数ACF和偏自相关函数PACF用于判断ARIMA模型中p、q参数取值 - 2

    文章目录1、自相关函数ACF2、偏自相关函数PACF3、ARIMA(p,d,q)的阶数判断4、代码实现1、引入所需依赖2、数据读取与处理3、一阶差分与绘图4、ACF5、PACF1、自相关函数ACF自相关函数反映了同一序列在不同时序的取值之间的相关性。公式：ACF(k)=ρk=Cov(yt,yt−k)Var(yt)ACF(k)=\rho_{k}=\frac{Cov(y_{t},y_{t-k})}{Var(y_{t})}ACF(k)=ρk​=Var(yt​)Cov(yt​,yt−k​)​其中分子用于求协方差矩阵，分母用于计算样本方差。求出的ACF值为[-1,1]。但对于一个平稳的AR模型，求出其滞

  9. Unity Shader 学习笔记（5）Shader变体、Shader属性定义技巧、自定义材质面板 - 2

    写在之前Shader变体、Shader属性定义技巧、自定义材质面板，这三个知识点任何一个单拿出来都是一套知识体系，不能一概而论，本文章目的在于将学习和实际工作中遇见的问题进行总结，类似于网络笔记之用，方便后续回顾查看，如有以偏概全、不祥不尽之处，还望海涵。1、Shader变体先看一段代码......Properties{ [KeywordEnum(on,off)]USL_USE_COL("IsUseColorMixTex?",int)=0 [Toggle(IS_RED_ON)]_IsRed("IsRed?",int)=0}......//中间省略，后续会有完整代码 #pragmamulti_c

  10. Tcl脚本入门笔记详解（一） - 2

    TCL脚本语言简介•TCL（ToolCommandLanguage）是一种解释执行的脚本语言（ScriptingLanguage）,它提供了通用的编程能力：支持变量、过程和控制结构；同时TCL还拥有一个功能强大的固有的核心命令集。TCL经常被用于快速原型开发，脚本编程，GUI和测试等方面。•实际上包含了两个部分：一个语言和一个库。首先，Tcl是一种简单的脚本语言，主要使用于发布命令给一些互交程序如文本编辑器、调试器和shell。由于TCL的解释器是用C\C++语言的过程库实现的，因此在某种意义上我们又可以把TCL看作C库，这个库中有丰富的用于扩展TCL命令的C\C++过程和函数，所以，Tcl是

随机推荐