SQL-实例测试总结

最新推荐文章于 2023-01-19 19:50:39 发布
最新推荐文章于 2023-01-19 19:50:39 发布
阅读量119 收藏
点赞数
文章标签： 面试 jmeter sequence tftp 安卓
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yorickjun/article/details/122740622
版权

1 练习表

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
    DNAME VARCHAR(14) ,
    LOC VARCHAR(13),
    primary key (DEPTNO)
    );
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    MGR INT(4),
    HIREDATE DATE  DEFAULT NULL,
    SAL DOUBLE(7,2),
    COMM DOUBLE(7,2),
    primary key (EMPNO),
    DEPTNO INT(2) 
    )
    ;

CREATE TABLE SALGRADE
      ( GRADE INT,
    LOSAL INT,
    HISAL INT );




INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;

2 练习题目

  1. 取得每个部门最高薪水的人员名称
# 1.取得每个部门的最高薪水
select deptno,max(sal) as maxsal from emp group by deptno;
+--------+---------+
| deptno | maxsal  |
+--------+---------+
|     10 | 5000.00 |
|     20 | 3000.00 |
|     30 | 2850.00 |
+--------+---------+
# 2.取得最高薪水人名单
select 
    e.ename,e.deptno,e.sal
from 
    emp e
join 
    (select deptno,max(sal) as maxsal from emp group by deptno) t 
on 
    e.deptno = t.deptno and e.sal = t.maxsal;
+-------+--------+---------+
| ename | deptno | sal     |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+
  1. 哪些人的薪水在部门的平均薪水之上
# 1.部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
# 2.求部门中在平均薪水之上的人
select 
    e.ename,e.deptno,e.sal,t.avgsal
from 
    emp e
join 
    (select deptno,avg(sal) as avgsal from emp group by deptno) t 
on 
    e.deptno = t.deptno and e.sal > t.avgsal;
+-------+--------+---------+-------------+
| ename | deptno | sal     | avgsal      |
+-------+--------+---------+-------------+
| ALLEN |     30 | 1600.00 | 1566.666667 |
| JONES |     20 | 2975.00 | 2175.000000 |
| BLAKE |     30 | 2850.00 | 1566.666667 |
| SCOTT |     20 | 3000.00 | 2175.000000 |
| KING  |     10 | 5000.00 | 2916.666667 |
| FORD  |     20 | 3000.00 | 2175.000000 |
+-------+--------+---------+-------------+
  1. 取得部门中(所有人的)平均的薪水等级
# 1.所有人的薪水等级
select e.empno,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
+-------+--------+-------+
| empno | deptno | grade |
+-------+--------+-------+
|  7369 |     20 |     1 |
|  7499 |     30 |     3 |
|  7521 |     30 |     2 |
|  7566 |     20 |     4 |
|  7654 |     30 |     2 |
|  7698 |     30 |     4 |
|  7782 |     10 |     4 |
|  7788 |     20 |     4 |
|  7839 |     10 |     5 |
|  7844 |     30 |     3 |
|  7876 |     20 |     1 |
|  7900 |     30 |     1 |
|  7902 |     20 |     4 |
|  7934 |     10 |     2 |
+-------+--------+-------+
# 2.取得部门的平均薪水等级
select 
    t.deptno,avg(t.grade) as avggrade
from 
    (select e.empno,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t
group by 
    t.deptno;
+--------+--------------+
| deptno | avggrade     |
+--------+--------------+
|     10 |       3.6667 |
|     20 |       2.8000 |
|     30 |       2.5000 |
+--------+--------------+
  1. 不准用组函数(Max )，取得最高薪水
# 方法一
select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+
# 方法二
select sal from emp order by sal desc limit 1;
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
# 方法三
# 1.自连接，获取除了最高薪水的其他薪水
select distinct e1.sal from emp e1 join emp e2 on e1.sal < e2.sal;
+---------+
| sal     |
+---------+
|  800.00 |
| 1250.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 1300.00 |
| 1600.00 |
| 2850.00 |
| 2450.00 |
| 2975.00 |
| 3000.00 |
+---------+
# 2.找到最高薪水
select sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on e1.sal < e2.sal);
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
  1. 取得平均薪水最高的部门的部门编号
# 方法一
# 1.取得部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
# 2.排序找到最高的部门编号
select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

# 方法二
# 1.取得部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
# 2.获取最高的平均薪水
select max(t.avgsal) as maxavgsal from (select deptno,avg(sal) as avgsal from emp group by deptno) t;
+-------------+
| maxavgsal   |
+-------------+
| 2916.666667 |
+-------------+
# 3.找到最高平均薪水的部门
select 
    deptno,avg(sal) as avgsal 
from 
    emp 
group by 
    deptno 
having 
    avgsal = (select max(t.avgsal) as maxavgsal from (select deptno,avg(sal) as avgsal from emp group by deptno) t);
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
  1. 取得平均薪水最高的部门的部门名称
# 1.获取部门的平均薪水
select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
# 2.获取平均薪水最高的部门名称
select d.dname,avg(e.sal) as 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 |
+------------+-------------+
  1. 求平均薪水的等级最低的部门的部门名称
# 1.部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno; 
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
# 2.部门平均薪水的等级
select 
    t.deptno,s.grade
from 
    (select deptno,avg(sal) as avgsal from emp group by deptno) t 
join 
    salgrade s 
on 
    t.avgsal between s.losal and s.hisal;
+--------+-------+
| deptno | grade |
+--------+-------+
|     10 |     4 |
|     20 |     4 |
|     30 |     3 |
+--------+-------+
# 3.最低平均薪水等级
select 
    s.grade
from 
    (select deptno,avg(sal) as avgsal from emp group by deptno) t 
join 
    salgrade s 
on 
    t.avgsal between s.losal and s.hisal
order by 
    s.grade asc 
limit 1;
+-------+
| grade |
+-------+
|     3 |
+-------+
# 4.部门平均薪水等级最低的部门名称
select 
    d.dname,s.grade
from 
    (select deptno,avg(sal) as avgsal from emp group by deptno) t 
join 
    salgrade s 
on 
    t.avgsal between s.losal and s.hisal
join 
    dept d 
on 
    t.deptno = d.deptno
where 
    s.grade = (select 
              s.grade
            from 
              (select deptno,avg(sal) as avgsal from emp group by deptno) t 
            join 
              salgrade s 
            on 
              t.avgsal between s.losal and s.hisal
            order by 
              s.grade asc 
            limit 1);
+-------+-------+
| dname | grade |
+-------+-------+
| SALES |     3 |
+-------+-------+
  1. 取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名
# 1.取得所有非普通员工编号
select distinct mgr from emp where mgr is not null;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
# 2.取得普通员工的最高工资
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);
+----------+
| max(sal) |
+----------+
|  1600.00 |
+----------+
# 3.取得比普通员工的最高薪资高的领导人姓名
select ename from emp where sal > (select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));
+-------+
| ename |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING  |
| FORD  |
+-------+
  1. 取得薪水最高的前五名员工
select ename,sal from emp order by sal desc limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| FORD  | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
  1. 取得薪水最高的第六到第十名员工
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 |
+--------+---------+
  1. 取得最后入职的 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 |
+--------+------------+
  1. 取得每个薪水等级有多少员工
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 |        3 |
|     2 |        3 |
|     3 |        2 |
|     4 |        5 |
|     5 |        1 |
+-------+----------+
  1. 面试题
  2. 列出所有员工及领导的姓名
select e1.ename,e2.ename from emp e1 left join emp e2 on e1.mgr = e2.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 |
+--------+-------+
  1. 列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
select 
    e1.empno,e1.ename,e1.hiredate,e2.ename,e2.hiredate,d.dname
from 
    emp e1
join 
    emp e2
on
    e1.mgr = e2.empno
join 
    dept d 
on 
    e1.deptno = d.deptno
where 
    e1.hiredate < e2.hiredate;
+-------+-------+------------+-------+------------+------------+
| empno | ename | hiredate   | ename | hiredate   | dname      |
+-------+-------+------------+-------+------------+------------+
|  7369 | SMITH | 1980-12-17 | FORD  | 1981-12-03 | RESEARCH   |
|  7499 | ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | SALES      |
|  7521 | WARD  | 1981-02-22 | BLAKE | 1981-05-01 | SALES      |
|  7566 | JONES | 1981-04-02 | KING  | 1981-11-17 | RESEARCH   |
|  7698 | BLAKE | 1981-05-01 | KING  | 1981-11-17 | SALES      |
|  7782 | CLARK | 1981-06-09 | KING  | 1981-11-17 | ACCOUNTING |
+-------+-------+------------+-------+------------+------------+
  1. 列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门
select 
    d.dname,e.ename
from 
    emp e 
right join 
    dept d 
on 
    e.deptno = d.deptno;
+------------+--------+
| dname      | ename  |
+------------+--------+
| RESEARCH   | SMITH  |
| SALES      | ALLEN  |
| SALES      | WARD   |
| RESEARCH   | JONES  |
| SALES      | MARTIN |
| SALES      | BLAKE  |
| ACCOUNTING | CLARK  |
| RESEARCH   | SCOTT  |
| ACCOUNTING | KING   |
| SALES      | TURNER |
| RESEARCH   | ADAMS  |
| SALES      | JAMES  |
| RESEARCH   | FORD   |
| ACCOUNTING | MILLER |
| OPERATIONS | NULL   |
+------------+--------+
  1. 列出至少有 5 个员工的所有部门
select deptno from emp group by deptno having count(*) >= 5;
+--------+
| deptno |
+--------+
|     20 |
|     30 |
+--------+
  1. 列出薪金比"SMITH" 多的所有员工信息
select ename,sal from emp where sal > (select sal from emp where ename = 'SMITH');
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
  1. 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数
# 1.列出所有"CLERK"( 办事员) 的姓名及其部门名称
select 
    e.ename,d.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 |
+--------+------------+
# 2.部门人数
select deptno,count(*) as countnum from emp group by deptno;
+--------+----------+
| deptno | countnum |
+--------+----------+
|     10 |        3 |
|     20 |        5 |
|     30 |        6 |
+--------+----------+
# 3.列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数
select 
    e.ename,d.dname,t.countnum
from 
    emp e
join 
    dept d 
on 
    e.deptno = d.deptno
join 
    (select deptno,count(*) as countnum from emp group by deptno) t
on 
    d.deptno = t.deptno
where 
    job = 'CLERK';
+--------+------------+----------+
| ename  | dname      | countnum |
+--------+------------+----------+
| SMITH  | RESEARCH   |        5 |
| ADAMS  | RESEARCH   |        5 |
| JAMES  | SALES      |        6 |
| MILLER | ACCOUNTING |        3 |
+--------+------------+----------+
  1. 列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数
select job,count(*) from emp group by job having min(sal) > 1500;
+-----------+----------+
| job       | count(*) |
+-----------+----------+
| ANALYST   |        2 |
| MANAGER   |        3 |
| PRESIDENT |        1 |
+-----------+----------+
  1. 列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.
# 方法一
select 
    e.ename
from 
    emp e
join 
    dept d 
on 
    e.deptno = d.deptno
where 
    d.dname = 'SALES';
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+
# 方法二
select 
    ename
from 
    emp
where
    deptno = (select deptno from dept where dname = 'SALES');
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+
  1. 列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.
select 
    e.ename,e.deptno,e.mgr,s.grade
from 
    emp e
join 
    dept d 
on 
    e.deptno = d.deptno
join 
    salgrade s 
on 
    e.sal between s.losal and s.hisal
where 
    e.sal > (select avg(sal) from emp);
+-------+--------+------+-------+
| ename | deptno | mgr  | grade |
+-------+--------+------+-------+
| JONES |     20 | 7839 |     4 |
| BLAKE |     30 | 7839 |     4 |
| CLARK |     10 | 7839 |     4 |
| SCOTT |     20 | 7566 |     4 |
| KING  |     10 | NULL |     5 |
| FORD  |     20 | 7566 |     4 |
+-------+--------+------+-------+
  1. 列出与"SCOTT" 从事相同工作的所有员工及部门名称
select 
    e.ename,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 | dname    |
+-------+----------+
| FORD  | RESEARCH |
+-------+----------+
  1. 列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.
# 1.部门30中员工的薪金
select distinct sal from emp where deptno = 30;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
|  950.00 |
+---------+
# 2.薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.
select 
    ename,sal 
from 
    emp 
where 
    sal in (select distinct sal from emp where deptno = 30) and deptno <> 30;
    
Empty set (0.01 sec)
  1. 列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称
# 1.部门30中员工的最高薪金
select max(sal) as maxsal from emp where deptno = 30;
+---------+
| maxsal  |
+---------+
| 2850.00 |
+---------+
# 2.薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称
select 
    e.ename,e.sal,d.dname
from 
    emp e 
join 
    dept d 
on 
    e.deptno = d.deptno
where 
    e.sal > (select max(sal) as maxsal from emp where deptno = 30);
+-------+---------+------------+
| ename | sal     | dname      |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH   |
| SCOTT | 3000.00 | RESEARCH   |
| KING  | 5000.00 | ACCOUNTING |
| FORD  | 3000.00 | RESEARCH   |
+-------+---------+------------+
  1. 列出在每个部门工作的员工数量, 平均工资和平均服务期限
select 
    d.deptno,count(e.ename) as countnum,ifnull(avg(e.sal),0) as avgsal,ifnull(avg(timestampdiff(YEAR,hiredate,now())),0) as avgtime
from 
    emp e 
right join 
    dept d 
on 
    e.deptno = d.deptno
group by 
    d.deptno;
+--------+----------+-------------+---------+
| deptno | countnum | avgsal      | avgtime |
+--------+----------+-------------+---------+
|     10 |        3 | 2916.666667 | 40.0000 |
|     20 |        5 | 2175.000000 | 37.8000 |
|     30 |        6 | 1566.666667 | 40.0000 |
|     40 |        0 |    0.000000 |  0.0000 |
+--------+----------+-------------+---------+
  1. 列出所有员工的姓名、部门名称和工资。
select e.ename,d.dname,e.sal from emp e join dept d on e.deptno = d.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 |
+--------+------------+---------+
  1. 列出所有部门的详细信息和人数
select 
    d.*,count(e.ename) as countnum 
from 
    emp e 
right join 
    dept d 
on 
    e.deptno = d.deptno
group by
    d.deptno,d.dname,d.loc;
+--------+------------+----------+----------+
| DEPTNO | DNAME      | LOC      | countnum |
+--------+------------+----------+----------+
|     10 | ACCOUNTING | NEW YORK |        3 |
|     20 | RESEARCH   | DALLAS   |        5 |
|     30 | SALES      | CHICAGO  |        6 |
|     40 | OPERATIONS | BOSTON   |        0 |
+--------+------------+----------+----------+
  1. 列出各种工作的最低工资及从事此工作的雇员姓名
# 1.各种工作的最低工资
select job,min(sal) as minsal from emp group by job;
+-----------+---------+
| job       | minsal  |
+-----------+---------+
| ANALYST   | 3000.00 |
| CLERK     |  800.00 |
| MANAGER   | 2450.00 |
| PRESIDENT | 5000.00 |
| SALESMAN  | 1250.00 |
+-----------+---------+
# 2.各种工作的最低工资及从事此工作的雇员姓名
select 
    e.ename,t.job,t.minsal 
from 
    emp e 
join 
    (select job,min(sal) as minsal from emp group by job) t 
on 
    e.job = t.job and e.sal = t.minsal;
+--------+-----------+---------+
| 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 |
+--------+-----------+---------+
  1. 列出各个部门的 MANAGER( 领导) 的最低薪金
select 
    deptno,min(sal) as minsal
from 
    emp e
where 
    job = 'MANAGER'
group by 
    deptno;
+--------+---------+
| deptno | minsal  |
+--------+---------+
|     10 | 2450.00 |
|     20 | 2975.00 |
|     30 | 2850.00 |
+--------+---------+
  1. 列出所有员工的 年工资, 按 年薪从低到高排序
select 
    ename,(sal + ifnull(comm,0))*12 as yearsal
from 
    emp
order by
    yearsal asc;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| JAMES  | 11400.00 |
| ADAMS  | 13200.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| WARD   | 21000.00 |
| ALLEN  | 22800.00 |
| CLARK  | 29400.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| JONES  | 35700.00 |
| SCOTT  | 36000.00 |
| FORD   | 36000.00 |
| KING   | 60000.00 |
+--------+----------+
  1. 求出员工领导的薪水超过3000的员工名称与领导
select 
    e1.ename,e2.ename
from 
    emp e1
left join 
    emp e2
on 
    e1.mgr = e2.empno
where
    e2.sal > 3000;
+-------+-------+
| ename | ename |
+-------+-------+
| JONES | KING  |
| BLAKE | KING  |
| CLARK | KING  |
+-------+-------+
  1. 求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数
select 
    d.dname,ifnull(sum(e.sal),0) as sumsal,count(e.empno) as countnum
from 
    emp e
right join 
    dept d
on 
    e.deptno = d.deptno
where 
    d.dname like '%S%'
group by
    d.deptno;
+------------+----------+----------+
| dname      | sumsal   | countnum |
+------------+----------+----------+
| RESEARCH   | 10875.00 |        5 |
| SALES      |  9400.00 |        6 |
| OPERATIONS |     0.00 |        0 |
+------------+----------+----------+
攻城老湿
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
MySQL练习题（34道）
09-12
内容概要: 此练习题可以配套本人MySQL讲解专栏使用，在本人文件分享中，有nanxi.sql文件，该文件为此练习题配套文件，文档中所有题目均来源于该文件. 适合人群： 初学mysql的任何人群，已经配套专栏系统学习过mysql的任何人群，以及已经有过基础，想测试一下基础的人群都可以使用。 能学到什么: 建议不使用任何笔记等的情况下独立完成题目，能够较好的巩固基础，查缺补漏，该题目都是基础题，其中只有一道题目为某面试题，难度偏低。适合上手。 阅读建议： 如果有任何不明白的地方欢迎私信博主，后续会在mysql专栏出文章讲解该题目。敬请期待！
SQL 2005-2008查询分析器
04-26
总结，SQL 2005-2008查询分析器是SQL Server管理员和开发者的强大工具，提供了丰富的功能来编写、测试和优化SQL语句。随着版本的更新，查询分析器不断进化，增加了更多优化性能和管理数据库的特性，极大地提高了工作...
MYSQL34道练习题
yaoqinghao的博客
02-08 363
学习课程来源：http://www.bjpowernode.com/javavideo/111.html 1、取得每个部门最高薪水的人员名称 //第一步：找出每个部门的最高薪水 select deptno,max(sal) as maxsal from emp group by deptno; +----------+--------+ | max(sal) | deptno | +----------+--------+ | 3000.00 | 20 | | 2850.00 | 30
MySql7——34道题目
m0_38057941的博客
10-12 1217
1、取得每个部门最高薪水的人员名称 a.取得每个部门最高薪水 select max(sal) as ‘maxsal’,deptno from emp group by deptno; b.emp表与 a步骤的表，进行连接 select e.ename,e.deptno,e.sal,t.maxsal from emp e join (select max(sal) as ‘maxsal’,deptno from emp group by deptno) t on t.maxsal=e.sal; 2.哪
SQL用户数据自动生成测试用例（第一版）
Happyflystone　－　无枪狙击手
05-16 1597
        好多的网友在SQL版问问题，一般我们要根据网友的数据自己手工创建表格，插入数据，本工具自动生成表格及数据，节省时间，才能抢到更多的分哦比如： 我有如下数据：ID Name Sdate1               aa 2009-01-012       bb                           2009-01-013 cc 2009-01-01如何
mysql查询34练
自律
09-21 396
文末是sql脚本，执行建表 1、取得每个部门最高薪水的人员名称 求每个部门部门号和最高薪水 SELECT `DEPTNO`,MAX(`SAL`) maxsal FROM `emp` GROUP BY `DEPTNO`; 最终sql语句 SELECT e.`ENAME`,t.* FROM (SELECT `DEPTNO`,MAX(`SAL`) maxsal FROM `emp` GROUP BY `DEPTNO`) t,emp e WHERE t.`DEPTNO`=e.`DEPTNO` AND t.max
SQL-数据库---图书管理系统.docx
10-10
+ 统一提交课程设计报告，按照需求分析、概要设计、详细设计、软件实现、软件测试、课程设计总结的框架撰写课程设计报告 * 附录： + 源程序清单（可节选主要代码） + 完整的数据库文件、代码、课程设计报告电子版...
PHP连接SQL server数据库测试脚本运行实例
10-14
本文将详细阐述如何使用PHP连接到SQL Server数据库，并提供两个不同扩展的测试脚本实例：sqlsrv和dblib。 首先，让我们关注sqlsrv扩展，这是专为PHP与SQL Server通信设计的。以下是一个在PHP 7上使用sqlsrv扩展连接...
V1.0-sqlServer索引使用总结.docx
07-30
本文档总结了 SQL Server 中索引的使用方法、分类和注意事项，并提供了实践测试的示例代码。 索引的使用目的 索引的使用目的主要是为了提高查询速度和效率。通过创建索引，可以快速地定位到所需的数据，从而减少...
MySQL34道练习题
最新发布
heliuerya的博客
01-19 3659
MySQL34道练习题
mysql练习题
08-02
MYSQL子查询、相关子查询、inner join、left|right join、cross join 、行列转换等高级查询练习题
数据库学习练习sql文件
08-11
用于mysql学习sql文件，练习mysql基本的查询、删除、修改，增加。
mysql练习题-sql建表文件.txt
03-12
mysql建表习题，对应着博主的另两篇pdf，此文件可以改成sql后缀，这样在navicat中可以直接导入，此文件中所用的是mysql语句
MYSQL练习题34道.zip
06-16
MySQL数据库基础练习题34道，有题有答案，包括练习用.sql文件，适合新手练习。 基础的增删改查。
mysql基础练习库.zip
04-14
该myemployees库包括四个表分别为department，employees，jobs，locations，为某公司的岗位数据信息，特别适合用来数据库基础的练习。
MySQL之34道作业题
weixin_48485216的博客
04-15 832
表 部门表dept create database MyTest; use MyTest; create table dept( DEPTNO varchar(20), DNAME varchar(20), LOC varchar(20), primary key(DEPTNO)); insert into dept(deptno,dname,loc) values("10","ACCOUNTING","NEW YORK"), ("20","RESEARCH","DALLAS"), ("30
MySQL练习题32道（包括源文件和习题答案）
qq_45876930的博客
10-18 1913
二、单表查询 !!下列练习使用db10库中数据，选择db10库，如果没有先创建(参考sql脚本) 07、列出emp表中的所有员工，显示所有列 select * from emp; ±—±-------±-------±-----------±-------±-----±-----±------+ | id | name | gender | birthday | dept | job | sal | bonus | ±—±-------±-------±-----------±-----.
MySQL SQL语句练习题
喝醉酒的小白
08-04 5709
34道MySQL作业题
MySQL学习34道作业题
weixin_44230339的博客
04-26 3636
MySQL学习34道作业题 文章目录MySQL学习34道作业题0 练习数据1 取得每个部门最高薪水的人员名称2 哪些人的薪水在部门平均薪水之上3 取得部门中所有人的平均的薪水等级4 不准使用组函数Max，给出最高薪水（给出两种解决方案）5 取得平均薪水最高的部门和部门编号6 取得平均薪水最高的部门名称7 求平均薪水的等级最高的部门的部门名称8 取得比普通员工（员工代码没在mgr字段出现的）最高薪水...
企业人事管理系统实例-SQL课程设计与软件工程实验报告综述
总的来说，本文从数据库技术的角度出发，对企业人事管理系统实例的SQL课程设计进行了详细的分析和总结。通过对数据库设计、实现、测试和评价等方面的论述，本文旨在提高学生对数据库技术的理解和应用能力。同时，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

攻城老湿

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值