mysql 练习题

数据库及表的数据

# 创建database
create database monkey1024;
 
# 创建tables  岗位信息
create table dept(
    DEPTNO int(2),
    DNAME varchar(14),
    LOC varchar(13)
);
# 插入数据
INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');

# 创建tables    员工信息
create table emp(
    EMPNO int(4),
    ENAME varchar(10),
    JOB varchar(9),
    MGR int(4),
    HIREDATE date,
    SAL double(7,2),
    COMM double(7,2),
    DEPTNO int(2)
);
# 插入数据
# 括号里面的内容:员工编号;姓名;岗位;上级领导的标号;入职日期;薪水;补助;部门编号
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

# 创建tables    薪水级别
create table salgrade(
    GRADE int(11),
    HISAL int(11),
    LOSAL int(11)
);
# 插入数据
INSERT INTO salgrade VALUES (1,1200,700); 
INSERT INTO salgrade VALUES (2,1400,1201); 
INSERT INTO salgrade VALUES (3,2000,1401);
INSERT INTO salgrade VALUES (4,3000,2001);
INSERT INTO salgrade VALUES (5,9999,3001);

练习1.获取每个部门的薪水最高的人员的名字

select ENAME,max(SAL) FROM emp group by DEPTNO;

注:如果觉得这个对的话,不妨对照以下emp表中具体的内容.可以发现匹配的名字是不准确的.这是因为分组后,select后面只能跟聚合函数,或者分组的依据.

SELECT 
    e.ENAME, t.maxSal
FROM
    emp e
        JOIN
    (SELECT 
        DEPTNO, MAX(SAL) AS maxSal
    FROM
        emp
    GROUP BY DEPTNO) t ON e.DEPTNO = t.DEPTNO
WHERE
    e.sal = t.maxSal;

查询结果

+-------+---------+
| ENAME | maxSal  |
+-------+---------+
| BLAKE | 2850.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+

练习2:哪些人的薪水在部门平均值之上

SELECT 
    e.ENAME, e.SAL, t.avgSal
FROM
    emp e
        JOIN
    (SELECT 
        DEPTNO, (SAL) AS avgSal
    FROM
        emp
    GROUP BY DEPTNO) t ON t.DEPTNO = e.DEPTNO
WHERE
    e.SAL > t.avgSal;

查询结果:

+-------+---------+---------+
| ENAME | SAL     | avgSal  |
+-------+---------+---------+
| JONES | 2975.00 |  800.00 |
| BLAKE | 2850.00 | 1600.00 |
| SCOTT | 3000.00 |  800.00 |
| KING  | 5000.00 | 2450.00 |
| ADAMS | 1100.00 |  800.00 |
| FORD  | 3000.00 |  800.00 |
+-------+---------+---------+

3.查看每个部门平均薪水等级

SELECT 
    t.DEPTNO, t.avgSal, s.GRADE
FROM
    salgrade s
        JOIN
    (SELECT 
        DEPTNO, AVG(SAL) AS avgSal
    FROM
        emp
    GROUP BY DEPTNO) t ON t.avgSal BETWEEN s.LOSAL AND s.HISAL;

注:这里的between后面是先小后大.

查询结果

+--------+-------------+-------+
| DEPTNO | avgSal      | GRADE |
+--------+-------------+-------+
|     10 | 2916.666667 |     4 |
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
+--------+-------------+-------+

4.查看每个人的薪资的级别

SELECT 
    t.Name, t.pSal, s.GRADE
FROM
    salgrade s
        JOIN
    (SELECT 
        ENAME AS Name, SAL AS pSal
    FROM
        emp) t ON t.pSal BETWEEN s.LOSAL AND s.HISAL;

注:与上一练习一样啦.

5.不用max()函数获得最高的薪水

select SAL from emp order by SAL desc limit 0,1;

6.获得所有部门平均薪资最高的部门编号

SELECT 
    MAX(t.avgSal)
FROM
    emp e
        JOIN
    (SELECT 
        DEPTNO, AVG(SAL) AS avgSal
    FROM
        emp
    GROUP BY DEPTNO) t ON e.DEPTNO = t.DEPTNO;

查询结果

+---------------+
| MAX(t.avgSal) |
+---------------+
|   2916.666667 |
+---------------+

当然可以简化查询语句

select max(t.avgSal) from (select DEPTNO,avg(SAL) as avgSal from emp group by DEPTNO) t;

接下来就要查看其部门编号了

select DEPTNO ,avg(SAL) as avgSal from emp group by DEPTNO
having avgSal=
(select max(t.avgSal) from 
(select DEPTNO,avg(SAL) as avgSal from emp group by DEPTNO) t);

查询结果

+--------+-------------+
| DEPTNO | avgSal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

当然还有一种简单的方法

select DEPTNO,avg(SAL) as avgSal from emp group by DEPTNO order by avgSal desc limit 0,1;

注:查询出所有部门的编号和平均薪资,然后倒序排列结果,再选取第一条数据.

7.查询最高薪资部门的名字

select d.DNAME,d.DEPTNO from dept d
join
(select DEPTNO,avg(SAL) as avgSal from emp group by DEPTNO order by avgSal desc limit 0,1) t
on
d.DEPTNO=t.DEPTNO;

查询结果

+------------+--------+
| DNAME      | DEPTNO |
+------------+--------+
| ACCOUNTING |     10 |
+------------+--------+

8.求平均薪资水平最低的部门

SELECT 
    d.DNAME, d.DEPTNO
FROM
    dept d
        JOIN
    (SELECT 
        t.DEPTNO, MIN(t.GRADE)
    FROM
        (SELECT 
        s.GRADE, t.DEPTNO
    FROM
        salgrade s
    JOIN (SELECT 
        DEPTNO, AVG(SAL) AS avgSal
    FROM
        emp
    GROUP BY DEPTNO) t ON t.avgSal BETWEEN s.LOSAL AND s.HISAL) t) t1 ON d.DEPTNO = t1.DEPTNO;

9.找出比普通员工(非MANAGER)最高工资还高的经理的名字

select e.ENAME from emp e
join 
(select distinct(MGR) as mgr from emp where MGR is not null) t
on e.EMPNO=t.mgr
where e.SAL>
(select max(SAL) as maxSal from emp where EMPNO not in (select distinct(MGR) from emp where MGR is not null));

查询结果

+-------+
| ENAME |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING  |
| FORD  |
+-------+

10.查询每个薪水等级有多少员工

Step1查询每个员工的员工编号和薪水等级

select e.EMPNO,s.GRADE from emp e
join 
salgrade s
on 
e.SAL between s.LOSAL and s.HISAL;

查询结果

+-------+-------+
| EMPNO | GRADE |
+-------+-------+
|  7369 |     1 |
|  7499 |     3 |
|  7521 |     2 |
|  7566 |     4 |
|  7654 |     2 |
|  7698 |     4 |
|  7782 |     4 |
|  7788 |     4 |
|  7839 |     5 |
|  7844 |     3 |
|  7876 |     1 |
|  7900 |     1 |
|  7902 |     4 |
|  7934 |     2 |
+-------+-------+

Step2查询每个等级里的员工个数

select t.GRADE ,count(t.EMPNO) from (
select e.EMPNO,s.GRADE from emp e
join 
salgrade s
on 
e.SAL between s.LOSAL and s.HISAL) t
group by t.GRADE;

11.列出所有入职日期大于其上级入职日期的员工

select a.ENAME,a.HIREDATE,b.ENAME as uName from emp a
join emp b
on a.MGR=b.EMPNO
where a.HIREDATE>b.HIREDATE;

查询结果

+--------+------------+-------+
| ENAME  | HIREDATE   | uName |
+--------+------------+-------+
| SCOTT  | 1987-04-19 | JONES |
| FORD   | 1981-12-03 | JONES |
| MARTIN | 1981-09-28 | BLAKE |
| TURNER | 1981-09-08 | BLAKE |
| JAMES  | 1981-12-03 | BLAKE |
| MILLER | 1982-01-23 | CLARK |
| ADAMS  | 1987-05-23 | SCOTT |
+--------+------------+-------+

12.列出至少有5个员工的部门名称

select d.DNAME,d.DEPTNO from dept d
join (select e.DEPTNO,count(e.EMPNO) from emp e group by e.DEPTNO having count(DEPTNO)>=5) t
on d.DEPTNO=t.DEPTNO;

查询结果

+----------+--------+
| DNAME    | DEPTNO |
+----------+--------+
| RESEARCH |     20 |
| SALES    |     30 |
+----------+--------+

13.列出最低薪资大于1500的工作及其员工人数

select JOB,min(SAL),count(*) from emp group by job having min(SAL)>1500;

14.查询从事"SALES"工作的人员姓名

select e.ENAME from emp e
join (select DEPTNO from dept  where DNAME='SALES') t
on e.DEPTNO=t.DEPTNO;

查询结果

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

posted on 2018-08-07 22:33 董大志阅读(442) 评论(0) 收藏举报

刷新页面返回顶部

董大志

mysql 练习题

数据库及表的数据

公告

导航