3、34道MySQL练习题及答案

最新推荐文章于 2023-12-19 20:23:21 发布
最新推荐文章于 2023-12-19 20:23:21 发布
阅读量580 收藏
点赞数
分类专栏： MySQL 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_69066786/article/details/130725003
版权

作业

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

    第一步：取得每个部门最高薪水（按照部门编号分组，找出每一组最大值）
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 |
    +-------+--------+---------+

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

    第一步：找出每个部门的平均薪水
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 |
+--------+-------+---------+-------------+

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

    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 |
+--------+------------+
平均的薪水等级：先计算每一个薪水的等级，然后找出薪水等级的平均值。
平均薪水的等级：先计算平均薪水，然后找出每个平均薪水的等级值。

  4. 不准用组函数（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 |
+---------+

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

    第一种方案：降序取第一个
	第一步：找出每个部门的平均薪水
	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 |
    +--------+-------------+

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

    第一步：找出每个部门的平均薪水的等级
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 |
+------------+-------------+

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

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

  8. 取得比普通员工(员工代码没有在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 |
+-------+---------+

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

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

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

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

  11. 取得最后入职的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 |
+--------+------------+

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

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

  13. 面试题

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

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

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

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

    --早于：小于
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 |
    +-------+-------+------------+

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

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

  17. 列出至少有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 |
+----------+----------+

  18. 列出薪金比"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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

  19. 列出所有"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';

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

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

  21. 列出在部门"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  |
+--------+

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

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

  23. 列出与"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 |
+-------+---------+----------+

  24. 列出薪金等于（部门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)

  25. 列出薪金高于（在部门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   |
+-------+---------+------------+

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

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

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

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

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

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

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

    --先找出每个工作以及最低工资
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 |
+--------+-----------+---------+

  30. 列出各个部门的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 |
+--------+----------+

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

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

  32. 求出员工领导的薪水超过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 |
+-------+------+

  33. 求出部门名称中,带’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 |
+------------+------+----------+

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

    --先找出任职日期超过30年的员工
update emp set sal =sal*1.1 where timestampdiff(year,hiredate,now())>30;
蛋翼
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL练习（函数）
Me11on的博客
09-02 185
函数的使用
【MySQL练习】函数练习
m0_59865073的博客
11-28 1241
函数练习
数据库---34练习题
X..RH
08-13 975
1、取得每个部门最高薪水的人员名称 select e.ename ,t.* from emp e join (select deptno,max(sal) maxsal from emp group by deptno) t on t.deptno=e.deptno and t.maxsal=e.sal; 2、哪些人的薪水在部门的平均薪水之上 select e.ename, e.sal from emp e join (select deptno,avg(sal) avgsal from emp gro
MySQL基础06 - 34道练习题
weixin_43636084的博客
03-26 86
【代码】MySQL基础06 - 34道练习题。
MySQL函数练习题
效率工具，软件测试，实用干货，资源分享，这里全是黑科技。
10-09 649
【代码】MySQL函数练习题。
MySQL练习题及答案-11页
06-01
MySQL是一种广泛使用的开源关系型数据库管理系统，其特点包括高效、可靠和可扩展性。...通过这些练习题，我们可以深入理解MySQL的各个核心概念，提升在数据库管理、查询优化和故障排查方面的能力。
MySQL数据库操作练习题及答案（以员工信息数据库为例）经典.rar
最新发布
05-30
这份"MySQL数据库操作练习题及答案（以员工信息数据库为例）经典.rar"资料，旨在帮助学习者深入理解和掌握MySQL的各种核心功能，包括数据的过滤、连接、分组、聚合、子查询、字符串处理、表操作以及触发器的使用。...
MySQL SQL语句练习题及答案
12-21
MySQL SQL语句练习题及答案 本资源提供了 MySQL SQL 语句的练习题及答案，涵盖了创建表、插入数据、删除数据、更新数据、查询数据等多方面的知识点。 一、创建表 在 MySQL 中，创建表使用 CREATE TABLE 语句。...
MYSQL练习题及答案.pdf
05-28
MYSQL练习题及答案.pdf 本资源摘要信息主要涵盖了MYSQL相关的练习题和答案，涉及到数据库设计、SQL语句编写、数据查询等方面的知识点。 一、数据库设计 1. 数据库设计的基本概念：数据库设计是指根据用户需求，...
MySQL基础练习题及答案
03-11
### MySQL基础练习题解析 #### 1. 查询`student`表的所有记录 ...以上是针对给定的`MySQL`基础练习题及其答案的详细解析，这些题目涵盖了SQL语言的基础使用方法，包括数据查询、过滤、排序、聚合计算等常用操作。
【MySQL练习-使用函数】
qq_45466520的博客
03-12 1103
使用函数 来啦来啦！！！今天给大家分享点什么好呢？前面我们创建了表，又对表做了一些简单的查询和where子句之间的查找，要不今天’言chengxu‘请大家哈啤酒？腰子自备噶，毕竟以后在代码的海洋里遨游，强健的体魄是必不可少滴。我们言归正传，今天给大家分享的是MySQL函数的使用，跟着小up一起来练习叭。 MySQL中提供了大量函数来简化用户对数据库的操作，比如字符串的处理、日期的运算、数值的运算等等。使用函数可以大大提高SELECT语句操作数据库的能力，同时也给数据的转换和处理提供了方便。 （在sql中使用
mysql存储过程和存储函数练习题
热门推荐
alicharity的博客
05-28 1万+
课 序 授课日期 授课班次 授课教师 批准人 5 课题 实验五 存储过程和存储函数 目的要求 1. 掌握通过SQL语句CREATE PROCEDURE创建存储过程的方法。 2. 掌握使用SQL语句CALL调用存储过程的方法。 3. 掌握使用SQL语句ALTER PROCEDURE修改存储过程的方法。 4. 掌握使用SQL语句DROP PROCEDURE删除存储过程的方法。 5. 掌握使用CREATE FUNCTION创建存储函数的方法。 6. 掌握使用SQL语句ALTER FUNCTION修
Mysql函数大全+案例练习
weixin_45934981的博客
04-18 1596
函数，在Java中我们叫方法，MySQL 函数会对传递进来的参数进行处理，并返回一个处理结果，也就是返回一个值。 MySQL 包含了大量并且丰富的函数，我们可以对 MySQL 常用函数进行简单的分类，大概包括数值型函数、字符串型函数、日期时间函数、聚合函数等。
MySQL总结练习题
m0_64642443的博客
10-10 368
对之前学习的mysql基础和进阶进行一些练习，巩固前面学完的知识。
MySQL经典50题（部分题目包含窗口函数只支持MySQL版本8.x及以后或使用hive操作）
weixin_63713552的博客
09-14 125
【代码】MySQL经典50题（部分题目包含窗口函数只支持MySQL版本8.x及以后）
Mysql经典例题练习与总结
qq_42265608的博客
06-14 3780
Mysql练习题
mysql函数练习
m0_58485238的博客
07-14 370
2、创建一个存储过程avg_sai，有3个参数，分别是deptno，job，接收平均工资，1、创建一个可以统计表格内记录条数的存储函数 ，函数名为count_sch()功能查询emp表dept为30，job为销售员的平均工资。其中total作为接受数据的变量。
MySQL存储函数
Asmywishi的博客
12-19 400
1.声明输入变量时，如果存储函数声明in输入会报错（默认为in），去掉in则不会。2.存储函数有returns和return两个必须存在的关键字。原本的名字xxxx已经更改为aaaa，update代码生效。3.调用的方式不同一个是select另一个是call。动态功能在第一问中已经实现，这里稍作修改。先查看运行前，fruits表的内容。
MYSQL——存储过程和存储函数练习题
TYW0823的博客
12-10 626
创建一个存储过程包括参数、变量、变量赋值、条件判断、UPDATE语句、SELECT返回结果集。
MySQL数据库题库练习题100道及答案解析
05-23
很抱歉，我无法提供完整的MySQL数据库题库练习题及答案解析。但是，我可以给您提供一些常见的MySQL面试题，希望能对您有所帮助。 1. 什么是MySQL？它的特点是什么？ 答：MySQL是一种开源的关系型数据库管理系统，它拥有以下特点： - 开源、免费 - 跨平台性 - 高性能 - 可扩展性 - 可定制性 - 安全性 2. 如何在MySQL中创建表？ 答：可以使用以下语句来创建表： ``` CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY (one or more columns) ); ``` 3. 如何在MySQL中插入数据？ 答：可以使用以下语句来插入数据： ``` INSERT INTO table_name (column1, column2, column3, ..., columnN) VALUES (value1, value2, value3, ..., valueN); ``` 4. 如何在MySQL中更新数据？ 答：可以使用以下语句来更新数据： ``` UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; ``` 5. 如何在MySQL中删除数据？ 答：可以使用以下语句来删除数据： ``` DELETE FROM table_name WHERE condition; ``` 6. 如何在MySQL中查询数据？ 答：可以使用以下语句来查询数据： ``` SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY column_name ASC|DESC LIMIT count OFFSET offset; ``` 7. 如何在MySQL中连接多个表？ 答：可以使用以下语句来连接多个表： ``` SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column; ``` 8. 如何在MySQL中创建索引？ 答：可以使用以下语句来创建索引： ``` CREATE INDEX index_name ON table_name (column1, column2, ...); ``` 以上是一些常见的MySQL面试题，希望能对您有所帮助。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值