3、34道MySQL练习题及答案

2023-11-11

作业

取得每个部门最高薪水的人员名称

第一步：取得每个部门最高薪水（按照部门编号分组，找出每一组最大值）
select deptno,max(sal) maxsal from emp group by deptno;
 +--------+---------+
    | deptno | maxsal  |
    +--------+---------+
    |     20 | 3000.00 |
    |     30 | 2850.00 |
    |     10 | 5000.00 |
    +--------+---------+

第二步：将以上结果当作临时表t，t表和emp e表进行连接，
条件是：t.deptno = e.deptno and t.maxsal = e.sal

select e.ename,t.* from (select deptno,max(sal) maxsal from emp group by deptno) t join emp e on t.deptno = e.deptno and t.maxsal = e.sal;

    +-------+--------+---------+
    | ename | deptno | maxsal  |
    +-------+--------+---------+
    | BLAKE |     30 | 2850.00 |
    | SCOTT |     20 | 3000.00 |
    | KING  |     10 | 5000.00 |
    | FORD  |     20 | 3000.00 |
    +-------+--------+---------+

哪些人的薪水在部门的平均薪水之上

第一步：找出每个部门的平均薪水
select deptno,avg(sal) avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+

第二步：找出比平均薪水高的人
select e.deptno,e.ename,e.sal,t.avgsal from (select deptno,avg(sal) avgsal from emp group by deptno) t join emp e on e.deptno =t.deptno where e.sal>t.avgsal;
+--------+-------+---------+-------------+
| deptno | ename | sal     | avgsal      |
+--------+-------+---------+-------------+
|     30 | ALLEN | 1600.00 | 1566.666667 |
|     20 | JONES | 2975.00 | 2175.000000 |
|     30 | BLAKE | 2850.00 | 1566.666667 |
|     20 | SCOTT | 3000.00 | 2175.000000 |
|     10 | KING  | 5000.00 | 2916.666667 |
|     20 | FORD  | 3000.00 | 2175.000000 |
+--------+-------+---------+-------------+

取得部门中（所有人的）平均的薪水等级

select deptno,avg(grade) from emp e join salgrade s on e.sal between losal and hisal group by deptno;
+--------+------------+
| deptno | avg(grade) |
+--------+------------+
|     20 |     2.8000 |
|     30 |     2.5000 |
|     10 |     3.6667 |
+--------+------------+
平均的薪水等级：先计算每一个薪水的等级，然后找出薪水等级的平均值。
平均薪水的等级：先计算平均薪水，然后找出每个平均薪水的等级值。

不准用组函数（Max），取得最高薪水

第一种方案：降序
select ename,sal from emp order by sal desc limit 1;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+

第二种方案：表的自连接
select distinct a.sal from emp a join emp b on a.sal <b.sal;
--同一个表中的数据大小相比较，取a中的，a中的小于b中的全部取出来了，则a中最大的就剩下了，然后看哪一个不在这个结果中就是最大的。
select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal <b.sal);
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+

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

第一种方案：降序取第一个
	第一步：找出每个部门的平均薪水
	select deptno,avg(sal) from emp group by deptno;
	+--------+-------------+
    | deptno | avg(sal)    |
    +--------+-------------+
    |     20 | 2175.000000 |
    |     30 | 1566.666667 |
    |     10 | 2916.666667 |
    +--------+-------------+
    第二步：select deptno,avg(sal) avgsal from emp group by deptno order by avgsal desc 	limit 1;
    +--------+-------------+
    | deptno | avgsal      |
    +--------+-------------+
    |     10 | 2916.666667 |
    +--------+-------------+
    
第二种方案：max
	select max(avgsal) from (select avg(sal) avgsal from emp group by deptno) t;
	select deptno,avg(sal) avgsal from emp group by deptno having avgsal =(select max(avgsal) from (select avg(sal) avgsal from emp group by deptno)t);
	
	+--------+-------------+
    | deptno | avgsal      |
    +--------+-------------+
    |     10 | 2916.666667 |
    +--------+-------------+

取得平均薪水最高的部门的部门名称

第一步：找出每个部门的平均薪水的等级
select d.dname,avg(sal) avgsal from emp e join dept d on e.deptno=d.deptno group by d.dname order by avgsal desc limit 1;

+------------+-------------+
| dname      | avgsal      |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+

求平均薪水的等级最低的部门的部门名称

mysql> select deptno,avg(sal) avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

mysql> select * from (select deptno,avg(sal) avgsal from emp group by deptno) t join salgrade s on t.avgsal between losal and hisal order by grade limit 1 ;
+--------+-------------+-------+-------+-------+
| deptno | avgsal      | GRADE | LOSAL | HISAL |
+--------+-------------+-------+-------+-------+
|     30 | 1566.666667 |     3 |  1401 |  2000 |
+--------+-------------+-------+-------+-------+

select dname from dept where deptno =(select deptno from (select deptno,avg(sal) avgsal from emp group by deptno) t join salgrade s on t.avgsal between losal and hisal order by grade limit 1);
+-------+
| dname |
+-------+
| SALES |
+-------+

取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名

先找出是领导的，值为null的排除掉
select distinct mgr from emp where mgr is not null;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
找出普通员工；即编号不在以上结果中的都是普通员工
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);
+----------+
| max(sal) |
+----------+
|  1600.00 |
+----------+
找出薪资高于以上结果的领导的新明
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     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+

取得薪水最高的前五名员工

select ename,sal from emp order by sal desc limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

取得薪水最高的第六到第十名员工

select ename,sal from emp order by sal desc limit 5,5;
+--------+---------+
| ename  | sal     |
+--------+---------+
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD   | 1250.00 |
+--------+---------+

取得最后入职的5名员工。

select ename,hiredate from emp order by hiredate desc limit 5;
+--------+------------+
| ename  | hiredate   |
+--------+------------+
| ADAMS  | 1987-05-23 |
| SCOTT  | 1987-04-19 |
| MILLER | 1982-01-23 |
| JAMES  | 1981-12-03 |
| FORD   | 1981-12-03 |
+--------+------------+

取得每个薪水等级有多少员工

select grade,count(grade) from emp e join salgrade s on e.sal between losal and hisal group by grade;
+-------+--------------+
| grade | count(grade) |
+-------+--------------+
|     1 |            3 |
|     3 |            2 |
|     2 |            3 |
|     4 |            5 |
|     5 |            1 |
+-------+--------------+

面试题

有3个表S(学生表)，C（课程表），SC（学生选课表）

S（SNO，SNAME）代表（学号，姓名）

C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）

SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）

问题：

请用标准SQL语言写出答案，方言也行（请说明是使用什么方言）。

CREATE TABLE SC
(
  SNO      VARCHAR(200),
  CNO      VARCHAR(200),
  SCGRADE  VARCHAR(200)
);

CREATE TABLE S
(
  SNO    VARCHAR(200 ),
  SNAME  VARCHAR(200)
);

CREATE TABLE C
(
  CNO       VARCHAR(200),
  CNAME     VARCHAR(200),
  CTEACHER  VARCHAR(200)
);

INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', '语文', '张'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英语', '李'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', '数学', '赵'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明'); 
commit;
 
INSERT INTO S ( SNO, SNAME ) VALUES ( '1', '学生1'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', '学生2'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', '学生3'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', '学生4'); 
commit;
 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80'); 
commit;

1、找出没选过“黎明”老师的所有学生姓名。

--先找出黎明所教的课程号
select cno from c where cteacher='黎明';
--在选课表中选了黎明的课的学生
select distinct sno from sc where cno = (select cno from c where cteacher='黎明');
--查找没有选课的学生的姓名
select sname from s where sno not in(select distinct sno from sc where cno = (select cno from c where cteacher='黎明'));
+-------+
| sname |
+-------+
| 学生3 |
| 学生4 |
+-------+

2、列出2门以上（含2门）不及格学生姓名及平均成绩。

--找出两门不及格学生的sno
select sno from sc where scgrade <60 group by sno having count(sno)>2;
+------+
| sno  |
+------+
| 1    |
+------+
--求平均成绩
select sname,avg(scgrade) from sc join s on sc.sno=s.sno group by sname;
--加入条件
select sname,avg(scgrade) from sc join s on sc.sno=s.sno where s.sno=(select sno from sc where scgrade <60 group by sno having count(sno)>2) group by sname;
+-------+--------------+
| sname | avg(scgrade) |
+-------+--------------+
| 学生1 |           46 |
+-------+--------------+


---另一种方法
select
	sc.sno, s.sname, count(*) as studentNum
from
	sc
join
	s
on 
	sc.sno = s.sno
where
	sc.scgrade < 60
group by
	sc.sno, s.sname
having
	studentNum >= 2;

3、即学过1号课程又学过2号课所有学生的姓名。

--学过1号课程的学生
select s.sno, s.sname from sc join s on sc.sno = s.sno where cno = 1
--学过2号课的
select sno from sc where cno = 2;
--合并：从学过1号课的学生中找学过2号课的
select s.sno, s.sname from sc join s on sc.sno = s.sno
where cno = 1 and s.sno in (select sno from sc where cno = 2);
+------+-------+
| sno  | sname |
+------+-------+
| 1    | 学生1 |
| 2    | 学生2 |
+------+-------+
--另一种方法
select sno,sname from s where sno in (select sno from sc where cno = 2) and s.sno in (select sno from sc where cno = 2);
+------+-------+
| sno  | sname |
+------+-------+
| 1    | 学生1 |
| 2    | 学生2 |
+------+-------+

列出所有员工及领导的姓名

--a表是员工表，b表是领导表。连接条件就是员工的领导编号=领导的员工编号。即：a.mgr = b.empno
--所有的员工的领导都必须显示出来，所以用左外连接
select a.ename '员工',b.ename '领导' from emp a left join emp b on a.mgr =b.empno;
        +--------+-------+
        | 员工   | 领导  |
        +--------+-------+
        | 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 |
        +--------+-------+
--做一个空处理
select a.ename '员工',ifnull(b.ename,'领导自己') '领导' from emp a left join emp b on a.mgr =b.empno;
        +--------+----------+
        | 员工   | 领导     |
        +--------+----------+
        | SMITH  | FORD     |
        | ALLEN  | BLAKE    |
        | WARD   | BLAKE    |
        | JONES  | KING     |
        | MARTIN | BLAKE    |
        | BLAKE  | KING     |
        | CLARK  | KING     |
        | SCOTT  | JONES    |
        | KING   | 领导自己 |
        | TURNER | BLAKE    |
        | ADAMS  | SCOTT    |
        | JAMES  | BLAKE    |
        | FORD   | JONES    |
        | MILLER | CLARK    |
        +--------+----------+

列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

--早于：小于
select a.empno,a.deptno,a.ename from emp a join emp b on a.mgr = b.empno where a.hiredate < b.hiredate;
    +-------+--------+-------+
    | empno | deptno | ename |
    +-------+--------+-------+
    |  7369 |     20 | SMITH |
    |  7499 |     30 | ALLEN |
    |  7521 |     30 | WARD  |
    |  7566 |     20 | JONES |
    |  7698 |     30 | BLAKE |
    |  7782 |     10 | CLARK |
    +-------+--------+-------+

--再用以上表与部门表连接
select t.empno,t.ename,d.dname from dept d join (select a.empno,a.deptno,a.ename from emp a join emp b on a.mgr = b.empno where a.hiredate < b.hiredate) t on d.deptno = t.deptno;

    +-------+-------+------------+
    | empno | ename | dname      |
    +-------+-------+------------+
    |  7369 | SMITH | RESEARCH   |
    |  7499 | ALLEN | SALES      |
    |  7521 | WARD  | SALES      |
    |  7566 | JONES | RESEARCH   |
    |  7698 | BLAKE | SALES      |
    |  7782 | CLARK | ACCOUNTING |
    +-------+-------+------------+

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

select d.dname,e.* from dept d left join emp e on e.deptno = d.deptno;

+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| dname      | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| ACCOUNTING |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
| ACCOUNTING |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
| ACCOUNTING |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
| RESEARCH   |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
| RESEARCH   |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
| RESEARCH   |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
| RESEARCH   |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
| RESEARCH   |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
| SALES      |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
| SALES      |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
| SALES      |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
| SALES      |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
| SALES      |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
| SALES      |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
| OPERATIONS |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+

列出至少有5个员工的所有部门

select dname,count(*) 部门人数 from dept d left join emp e on d.deptno = e.deptno group by dname having 部门人数>=5;

+----------+----------+
| dname    | 部门人数 |
+----------+----------+
| RESEARCH |        5 |
| SALES    |        6 |
+----------+----------+

列出薪金比"SMITH"多的所有员工信息

select * from emp where sal>(select sal from emp where ename='SMITH');

+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 | 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.

--先找出姓名和部门名称
select ename,dname from emp e join dept d on e.deptno = d.deptno where job='CLERK';
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| MILLER | ACCOUNTING |
+--------+------------+
--找出部门人数
select deptno,count(*) cc from emp group by deptno;
+--------+----+
| deptno | cc |
+--------+----+
|     20 |  5 |
|     30 |  6 |
|     10 |  3 |
+--------+----+

--表连接
--先找出每个员工所在部门的人数，再找出办事员所在坐在部门
select ename,dname,cc from emp e join (select deptno,count(*) cc from emp group by deptno) t on t.deptno=e.deptno join dept d on e.deptno = d.deptno where job='CLERK';

列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数

select job,count(job) from emp group by job having min(sal) >1500;
+-----------+------------+
| job       | count(job) |
+-----------+------------+
| MANAGER   |          3 |
| ANALYST   |          2 |
| PRESIDENT |          1 |
+-----------+------------+

列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号

--找出销售部的部门编号
select deptno from dept where dname='SALES';
--找出部门编号为以上的员工的姓名
select ename from emp where deptno=(select deptno from dept where dname='SALES');

+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+

列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级

select e.ename '员工',d.dname '部门',ifnull(f.ename,'领导自己') '领导',s.grade from emp e 
join dept d on d.deptno=e.deptno
left join emp f on e.mgr=f.empno		--这里用左连接，要保证所有的员工都存在
join salgrade s on e.sal between losal and hisal
where e.sal >(select avg(sal) from emp);
+-------+------------+----------+-------+
| 员工  | 部门       | 领导     | grade |
+-------+------------+----------+-------+
| FORD  | RESEARCH   | JONES    |     4 |
| SCOTT | RESEARCH   | JONES    |     4 |
| CLARK | ACCOUNTING | KING     |     4 |
| BLAKE | SALES      | KING     |     4 |
| JONES | RESEARCH   | KING     |     4 |
| KING  | ACCOUNTING | 领导自己  |     5 |
+-------+------------+----------+-------+

列出与"SCOTT"从事相同工作的所有员工及部门名称

--先找出scott所从事工作
select job from emp where ename='SCOTT';

+---------+
| job     |
+---------+
| ANALYST |
+---------+
--表连接
select ename,job,dname from emp e join dept d on d.deptno=e.deptno
where job=(select job from emp where ename='SCOTT') and ename <> 'SCOTT';
+-------+---------+----------+
| ename | job     | dname    |
+-------+---------+----------+
| FORD  | ANALYST | RESEARCH |
+-------+---------+----------+

列出薪金等于（部门30中员工的薪金）的其他员工的姓名和薪金

--先找出部门30中的员工的薪金
select distinct sal from emp where deptno=30;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
|  950.00 |
+---------+

--找出其他的
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工作的所有员工的薪金）的员工姓名和薪金.部门名称.

--先找出部门30工作的所有员工的最高薪金
select max(sal) from emp where deptno = 30;
+----------+
| max(sal) |
+----------+
|  2850.00 |
+----------+

select ename,sal,dname from emp e join dept d on d.deptno=e.deptno where sal>(select max(sal) from emp where deptno = 30);
+-------+---------+------------+
| ename | sal     | dname      |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH   |
| SCOTT | 3000.00 | RESEARCH   |
| KING  | 5000.00 | ACCOUNTING |
| FORD  | 3000.00 | RESEARCH   |
+-------+---------+------------+

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

在mysql中怎么计算两个日期的“年差”，差了多少年。

TimeStampDiff(间隔日期，前一个日期，后一个日期)
timestampdiff(YEAR,hiredate,now());
间隔类型：
        SECOND 		秒
        MINUTE 		分钟
        HOUR		小时
        DAY			天
        WEEK		星期
        MONTH		月
        QUARTER		季度
        YEAR		年

select dname '部门',count(ename) '员工数量',ifnull(avg(sal),0) '平均薪资',avg(timestampdiff(year,hiredate,now())) '平均服务年限' 
from dept d left join emp e on d.deptno = e.deptno group by dname;

+------------+----------+-------------+--------------+
| 部门        | 员工数量  | 平均薪资     | 平均服务年限  |
+------------+----------+-------------+--------------+
| ACCOUNTING |        3 | 2916.666667 |      39.0000 |
| RESEARCH   |        5 | 2175.000000 |      36.8000 |
| SALES      |        6 | 1566.666667 |      39.3333 |
| OPERATIONS |        0 |    0.000000 |         NULL |
+------------+----------+-------------+--------------+

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

select ename,dname,sal from emp e join dept d on d.deptno=e.deptno;
+--------+------------+---------+
| ename  | dname      | sal     |
+--------+------------+---------+
| SMITH  | RESEARCH   |  800.00 |
| ALLEN  | SALES      | 1600.00 |
| WARD   | SALES      | 1250.00 |
| JONES  | RESEARCH   | 2975.00 |
| MARTIN | SALES      | 1250.00 |
| BLAKE  | SALES      | 2850.00 |
| CLARK  | ACCOUNTING | 2450.00 |
| SCOTT  | RESEARCH   | 3000.00 |
| KING   | ACCOUNTING | 5000.00 |
| TURNER | SALES      | 1500.00 |
| ADAMS  | RESEARCH   | 1100.00 |
| JAMES  | SALES      |  950.00 |
| FORD   | RESEARCH   | 3000.00 |
| MILLER | ACCOUNTING | 1300.00 |
+--------+------------+---------+

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

select d.*,count(ename) '人数' from dept d left join emp e on e.deptno=d.deptno group by deptno,dname,loc;

+--------+------------+----------+------+
| DEPTNO | DNAME      | LOC      | 人数 |
+--------+------------+----------+------+
|     10 | ACCOUNTING | NEW YORK |    3 |
|     20 | RESEARCH   | DALLAS   |    5 |
|     30 | SALES      | CHICAGO  |    6 |
|     40 | OPERATIONS | BOSTON   |    0 |
+--------+------------+----------+------+

列出各种工作的最低工资及从事此工作的雇员姓名

--先找出每个工作以及最低工资
select job,min(sal) minsal from emp group by job;
+-----------+---------+
| job       | minsal  |
+-----------+---------+
| CLERK     |  800.00 |
| SALESMAN  | 1250.00 |
| MANAGER   | 2450.00 |
| ANALYST   | 3000.00 |
| PRESIDENT | 5000.00 |
+-----------+---------+
--找出工资为最低工资的人的姓名-表连接
select ename,t.* from emp e join (select job,min(sal) minsal from emp group by job)t on t.job=e.job and e.sal = t.minsal;

+--------+-----------+---------+
| ename  | job       | minsal  |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| WARD   | SALESMAN  | 1250.00 |
| MARTIN | SALESMAN  | 1250.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| FORD   | ANALYST   | 3000.00 |
+--------+-----------+---------+

列出各个部门的MANAGER(领导)的最低薪金

select deptno,min(sal) from emp where job='MANAGER' group by deptno;
+--------+----------+
| deptno | min(sal) |
+--------+----------+
|     20 |  2975.00 |
|     30 |  2850.00 |
|     10 |  2450.00 |
+--------+----------+

列出所有员工的年工资,按年薪从低到高排序

select ename,sal*12 yearsal from emp order by yearsal;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| JAMES  | 11400.00 |
| ADAMS  | 13200.00 |
| WARD   | 15000.00 |
| MARTIN | 15000.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| ALLEN  | 19200.00 |
| CLARK  | 29400.00 |
| BLAKE  | 34200.00 |
| JONES  | 35700.00 |
| SCOTT  | 36000.00 |
| FORD   | 36000.00 |
| KING   | 60000.00 |
+--------+----------+

求出员工领导的薪水超过3000的员工名称与领导名称

select e.ename '员工',f.ename '领导' from emp e join emp f on e.mgr = f.empno where f.sal>3000;
+-------+------+
| 员工  | 领导 |
+-------+------+
| JONES | KING |
| BLAKE | KING |
| CLARK | KING |
+-------+------+

求出部门名称中,带’S’字符的部门员工的工资合计、部门人数

select dname,count(ename) '人数',ifnull(sum(sal),0) '工资总和' from emp e right join dept d on d.deptno =e.deptno where dname like '%S%' group by dname;

+------------+------+----------+
| dname      | 人数 | 工资总和 |
+------------+------+----------+
| RESEARCH   |    5 | 10875.00 |
| SALES      |    6 |  9400.00 |
| OPERATIONS |    0 |     0.00 |
+------------+------+----------+

给任职日期超过30年的员工加薪10%

--先找出任职日期超过30年的员工
update emp set sal =sal*1.1 where timestampdiff(year,hiredate,now())>30;

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

数据库

sql

3、34道MySQL练习题及答案的相关文章

Mysql 中 UNION 子句的替代方案

我有两张桌子表 a 表 b table a ID 1 2 3 4 5 7 table b ID 2 3 4 5 6 我必须得到这样的输出而无需UNION命令 ID 1 2 3 4 5 6 7 注意我有一个联合解决方案 select fr
独立对列进行排序，使得所有空值都位于每列的最后

这是一个名为的示例表animal name color fox brown fox red dog gold 现在我想要的是这样的结果 fox dog brown gold red 名称应该是结果的列不同颜色值作为行我的第一个想法是
sql server 按组排名

问题看似简单但我却无法理解这是针对 sql 服务器的 what I have in a table What I need as a output cksum id cksum id 2162514679 204 2162514679
Mysql 创建定义器

我创建了一个在 CentOS Web 服务器上运行的 Intranet Web 应用程序该应用程序使用另一个本地服务器始终是 CentOS 作为 MySQL 数据库在数据库内部我创建了例程这些例程总是这样开始 CREATE DEFI
MySQL 查询计算上个月

我想计算上个月的订单总额我收到了从当前日期获取当月数据的查询 SELECT SUM goods total AS Total Amount FROM orders WHERE order placed date gt date sub c
将第三个表链接到多对多关联中的桥接表

设计这个数据库的正确方法是什么这是我设置表格的方式我在名为教师的表和名为仪器的表之间存在多对多关系然后我有一个连接两者的桥接表我想将另一个表与 BRIDGE 表关联起来意思是乐器老师的组合该表有 3 行指定老师可以教
PHP 通过 SSL 连接到 MS SQL

我想要实现的目标非常简单我想通过安全连接从 PHP 脚本连接到外部 MS SQL 数据库然而这已被证明是有问题的到目前为止经过三个小时的研究我不知所措客户端的平台是Ubuntu 这意味着我无法使用SQLSRV 安全连接已经在不
gem install mysql：无法构建 gem 本机扩展 (Mac Lion)

我为 Mac OS X Lion 安装了 MySQL 5 5 27 来自 dmg 现在我尝试安装 mysql gem gem install mysql Building native extensions This could take
为什么 SqlClient 在传递 SqlXml 时使用不必要的 XML 转换？

我有一个关于从 C 代码将 xml 数据类型传递给查询的问题首先这是 SQL Server 上的一个表 CREATE TABLE dbo XmlTable id int IDENTITY 1 1 NOT NULL dat xml NOT
单独的逗号分隔值并存储在sql server的表中

我有一个存储过程它将逗号分隔的值作为输入我需要将其分开并需要将其作为单独的行存储在表中令 SP 的输入为 Rule ID ListType ID Values 1 2 319 400 521 8465 2013 我需要将它存储在一个名
初级SQL部分：避免重复表达式

我对 SQL 完全陌生但我们可以说StackExchange 数据浏览器 https data stackexchange com 我只想按信誉列出前 15 位用户我写了这样的内容 SELECT TOP 15 DisplayName I
为表中的每个组选择前 N 行

我面临一个非常常见的问题即为表中的每个组选择前 N 行考虑一个表id name hair colour score列我想要一个结果集对于每种头发颜色都能得到前 3 名得分手的名字为了解决这个问题我得到了我所需要的Rick O
XML 解析：格式良好的检查：未声明的实体

我正在使用 SSMS 2008 但收到以下错误你知道这意味着什么吗 Msg 9448 Level 16 State 1 Line 4 XML parsing line 1 character 89 well formed check un
如何使用sql作为xml路径('')但保留回车符

我有下面的代码 select select cast Narrative as Varchar max char 13 from officeclientledger where ptmatter matter and ptTrans 4
如何从shell脚本自动登录MySQL？

我有一个 MySQL 服务器其中有一个用户和密码我想在 shell 脚本中执行一些 SQL 查询而不指定密码如下所示 config sh MYSQL ROOT root MYSQL PASS password mysql sh sou
我可以使用 HSQLDB 进行 junit 测试克隆 mySQL 数据库吗

我正在开发一个 spring webflow 项目我想我可以使用 HSQLDB 而不是 mysql 进行 junit 测试吗如何将我的 mysql 数据库克隆到 HSQLDB 如果您使用 spring 3 1 或更高版本您可以使用 s
SQL不允许表中有重复记录

如何使其不添加重复项我想让它通过 ID 之外的所有其他列进行检查我希望这个无效 ID col1 col2 col3 1 first middle last ID col1 col2 col3 2 first middle last 我希
如何在NiFi中映射流文件中的列数据？

我有 csv 文件其结构如下 Alfreds Centro Ernst Island Bacchus Germany Mexico Austria UK Canada 01 02 03 04 05 现在我必须将这些数据移入数据库如下所示
SQL - != 'NULL' 的解释

我的SSMS代码如下 Select top 50 From FilteredContact Where statuscode 1 and emailaddress1 NULL and telephone1 NULL and address1
MS ACCESS 计数/求和行数，不重复

我有下表我需要计算总行数而不包括任何重复记录 CustomerID test1 test1 test2 test3 test4 test4 如您所见总行数为 6 但有两个 test1 和两个 test4 我希望查询返回 4 IOW 我想

随机推荐

解决Element中点开Dialog 对话框灰布在最上层的方法

解决Element中点开Dialog 对话框灰布在最上层的方法解决Element中点开Dialog 对话框灰布在最上层的方法 append to body Dialog 自身是否插入至 body 元素上嵌套的 Dialog 必须指定该属
TCP如何保证可靠性传输

校验和在数据传输的过程中将发送的数据段都当做一个16位的整数将这些整数加起来并且前面的进位不能丢弃补在后面继续相加最后取反得到校验和发送方在发送数据之前计算检验和并进行校验和的填充接收方收到数据后对数据以同样的方式
基于WebSocket和Android的多人实时聊天系统(Demo)的实现

实现效果服务端实现服务端参考之前的这篇文件Android初学使用WebSocket与服务器进行通信这里做了一些修改就是再服务端收到消息后将消息群发给所有在线的客户端服务端的Bean实体类 Data AllArgsConstru
项目开发中的一些小配置，随时更新（springboot版本）

这里是目录哦 1 appication properties 2 log4j properties 3 MySwaggerConfig java 4 MybatisplusConfig java 1 appication propertie
如何使用Python 操作PDF？总结用Python操作PDF的几种方法

本篇文章小编给大家分享一个用python来操作pdf图像的几种方法如果有需求的小伙伴一定要耐心阅读完这篇文章小编希望能够通过这篇文章来给大家的学习带来一定的帮助 Python操作PDF有以下几种方法 1 基本操作 PyPDF2 导入模块
C++进制转换的两种方法

进制的转换 stoi函数
离线升级openssh 从7.4升至9.0

离线升级openssh 从7 4升至9 0 难点无法使用联网下载的命令yum等前提需要先更新openssl 到1 11 1 链接 https blog csdn net weixin 42235875 article details
内核杂谈——页表项存放的是物理地址还是虚拟地址？

目录 L0 L1 L2 表项 L3 表项总结 pgd t 不只是物理地址谈谈对映射的理解思考当你不去细细读代码的话这个问题可能会困扰着你我们以ARM64四级页表为例谈谈页表项里藏得是什么本文讨论的是内核线性映射过程时建立的临
从Jaccard到BERT：语义检索的度量手段

原文标题 Semantic Search Measuring Meaning From Jaccard to Bert 作者 James Briggs 原文地址 https towardsdatascience com semantic s
用一个海龟（turtle) 来写 “张”

1 import turtle as t 2 3 t pen pencolor cyan pensize 10 4 5 t speed fast 6 for in range 3 7 t fd 20 8 t rt 90 9 t backwa
JS语法之：require

JS中一个模块加载另一个模块有两套规范 CommonJS规范和AMD规范 CommonJS规范加载模块是同步的也就是说只有加载完成才能执行后面的操作 AMD规范则是非同步加载模块允许指定回调函数由于Node js主要用于服务器编
接口测试工具soapUI

1 soapUI安装及破解参考 http blog csdn net liuchangxin1982 article details 47614625 1 安装方法先下载SOAPUI 我这里用的是5 1 2 PRO 版下载路径 htt
Git下载、安装及环境配置（超详细）

本文首发自个人自有博客 FaxMiao个人博客一个关注Web前端开发技术关注用户体验记录前端点滴坚持更多原创为大家提供高质量技术博文本文主要介绍下Git的下载安装及基本配置予以初学者朋友帮助更快的了解Git基础知识和快速使
Mysql5.7.41windows安装

Mysql5 7 41windows安装 Mysql5 7 41windows安装 Mysql5 7 41官方下载 mysql安装配置PATH路径任意位置打开cmd都可以连接Mysql 环境验证并登录友情链接 Mysql5 7 41
利用Fastjson进行泛型的序列化和反序列化

public class SerialClass
qt下QString转换为const char*字符串

概述本文直在说明将QString类型的变量转换为const char 类型实现可以采用下面的方式来实现 QString toStdString c str 示例下面是一个示例 const QString msg nis你好 cons
10个Python爬虫入门实例，建议收藏！！

1 爬取强大的BD页面打印页面信息第一个爬虫示例爬取百度页面 import requests 导入爬虫的库不然调用不了爬虫的函数 response requests get http www baidu com 生成一个respon
STM32 之八在线升级（IAP）超详细图解及需要注意的问题解决

IAP 是啥 IAP In Application Programming 即在应用编程也就是用户可以使用自己的程序对MCU的中的运行程序进行更新而无需借助于外部烧写器其实ST官网也给出了IAP的示例程序感兴趣的可以直接去官网搜索
【PHPMailer SMTP connect() failed】

PHPMailer发送邮件报错 SMTP connect failed 第一种情况端口问题现在一般使用的就是ssl的端口465 第二种情况参数里面有一个password参数注意这里是授权码不是邮箱登录密码
3、34道MySQL练习题及答案

作业取得每个部门最高薪水的人员名称第一步取得每个部门最高薪水按照部门编号分组找出每一组最大值 select deptno max sal maxsal from emp group by deptno deptno maxsal

3、34道MySQL练习题及答案

作业

3、34道MySQL练习题及答案 的相关文章

随机推荐

热门标签

3、34道MySQL练习题及答案的相关文章