数据库四（习题练习）

1）员工信息表emp

字段中文名字依次是：工号，姓名，工作岗位，部门经理，受雇日期，薪金，奖金，部门编号

2）部门信息表dept

字段中文名字依次是：部门编号、部门名称、所在地

练习题目

1． 列出至少有4个员工的所有部门编号和名称。

• 方法一：
  select deptno,dname from dept where
  deptno in(select deptno from (select count(*) count,deptno
  from emp group by deptno) dn where dn.count>=4);
• 方法二：
  select s.deptno,dept.DNAME
  from (select deptno,count(*) as sum from emp group by deptno having sum>=4) as s
  left join dept
  on s.deptno=dept.DEPTNO;
  

2． 列出薪金比“SMITH”多的所有员工。

• select empno,ename from emp where
  sal>(select sal from emp where ename=“SMITH”);
  

3． 列出所有员工的姓名及其直接上级的姓名。

• select emp1.ename,emp.ename mgrname from emp emp1 inner join
  emp on emp1.mgr=emp.empno;
  

4． 列出受雇日期早于其直接上级的所有员工。

• 方法一：
  select * from (select emp1.ename name,emp1.hiredate time,emp.ename as mgrname,emp.hiredate as mgrtime from emp emp1
  inner join emp
  on emp1.mgr=emp.empno) s where s.time<s.mgrtime;
• 方法二：
  select e.EMPNO,e.HIREDATE as time,emp.HIREDATE as date from emp as e
  left join
  emp
  on e.MGR=emp.EMPNO having time<date;
  

5． 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

• select * from dept left join emp
  on dept.DEPTNO=emp.deptno;

6． 列出所有“CLERK”（办事员）的姓名及其部门名称。

• 方法一：
  select ename,dname from emp,dept where
  emp.deptno=dept.deptno and emp.job=“CLERK”;
• 方法二：
  select ename,dname from (select * from emp where job=“CLERK”) as emp
  left join dept
  on emp.DEPTNO=dept.DEPTNO;

7． 列出最低薪金大于1500的各种工作。

• select s.job from
  (select min(sal) min,job from emp group by job having min>1500) s;
  

8． 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号

• select ename from emp
  where emp.deptno=(select deptno from dept where dept.dname=“SALES”);
  

9． 列出薪金高于公司平均薪金的所有员工。

• select ename from emp where sal>(select avg(sal) from emp);

10．列出与“SCOTT”从事相同工作的所有员工。

• select * from emp where job=(select job from emp where ename=“SCOTT”) and ename<>“SCOTT”;
  

11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

• 方法一：
  select ename,sal from emp where sal in (select sal from emp where deptno=30);
• 方法二：
  select DISTINCT * from emp
  inner join (select sal from emp where deptno=30) as e
  on emp.sal=e.sal;
  

12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

• select * from emp where sal>(select max(sal) from emp where deptno=30);
  

13．列出在每个部门工作的员工数量、平均工资和平均服务期限。

• select count(*),avg(sal),avg(DATEDIFF(CURRENT_DATE,HIREDATE))from emp group by deptno;
  

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

• select ename,dname,sal + case when comm is null then 0 else comm end as money from emp
  left join
  dept
  on emp.deptno=dept.deptno;
  

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

• select * from dept
  left join
  (select count(*) count,deptno from emp group by deptno) s
  on s.deptno=dept.deptno;
  

16．列出各种工作的最低工资。

• 方法一：
• 方法二：

17．列出各个部门的MANAGER（经理）的最低薪金。

• 方法一：
  select * from
  (select deptno,min(sal) from emp where job=“MANAGER” group by deptno ) as s left join
  dept
  on s.deptno=dept.deptno;
• 方法二：
  select * from (select * from emp where job=“MANAGER”) as emp
  where sal=(
  select min(sal) from (select * from emp where job=“MANAGER”) as e where emp.DEPTNO=e.deptno);

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

• 方法一：
  select s.ename ,s.money from
  (select ename,12 *sal + case when comm is null then 0 else comm end money from emp) s order by s.money;
• 方法二：
  select ename,12*sal+case when comm is null then 0 else comm end as money
  from emp ORDER BY money;
  

思考： 列出每个部门薪水前两名最高的人员名称以及薪水

• 方法一：
  select emp1.ename,emp1.sal,emp1.deptno from emp emp1 where
  (select count(*) from emp where emp.deptno=emp1.deptno and emp1.sal<emp.sal)<2;

• 方法二：
  select * from emp
  where 2>(
  select count(*) from emp as e where emp.DEPTNO=e.deptno and emp.sal<e.sal);

注意：

• 字段相加有一个为null，所加结果为null
• 如果是表中原有的字段名称用where，聚合函数求出的，例如count，age重起名后期需要判断用having。
• 逻辑and和逻辑or表达式，其操作数是从左到右求值的，如果第一个参数能判断结果，后面结果将不会执行。
• where是对已经搜索出的结果的过滤条件
• 在on的条件下，and和or与on同时对前面的集合（表）起作用，而存在where的时候，先对集合进行on的抽取连接再根据where进行筛选。

附录代码

-- 建立员工信息表emp
DROP TABLE IF EXISTS  `emp`;
CREATE TABLE `emp` (
  `EMPNO` int(4) NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` varchar(10) DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` int(7) DEFAULT NULL,
  `COMM` int(7) DEFAULT NULL,
  `DEPTNO` int(2) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入数据
insert into `emp`(`EMPNO`,`ENAME`,`JOB`,`MGR`,`HIREDATE`,`SAL`,`COMM`,`DEPTNO`) values
('7369','SMITH','CLERK','7902','1980-12-17','800',null,'20'),
('7499','ALLEN','SALESMAN','7698','1981-02-20','1600','300','30'),
('7521','WARD','SALESMAN','7698','1981-02-22','1250','500','30'),
('7566','JONES','MANAGER','7839','1981-04-02','2975',null,'20'),
('7654','MARTIN','SALESMAN','7698','1981-09-28','1250','1400','30'),
('7698','BLAKE','MANAGER','7839','1981-05-01','2850',null,'30'),
('7782','CLARK','MANAGER','7839','1981-06-09','2450',null,'10'),
('7788','SCOTT','ANALYST','7566','1987-04-19','3000',null,'20'),
('7839','KING','PRESIDENT',null,'1981-11-17','5000',null,'10'),
('7844','TURNER','SALESMAN','7698','1981-09-08','1500','0','30'),
('7876','ADAMS','CLERK','7788','1987-05-23','1100',null,'20'),
('7900','JAMES','CLERK','7698','1981-12-03','950',null,'30'),
('7902','FORD','ANALYST','7566','1981-12-03','3000',null,'20'),
('7934','MILLER','CLERK','7782','1982-01-23','1300',null,'10');

-- 建立部门信息表dept
DROP TABLE IF EXISTS  `dept`;
CREATE TABLE `dept` (
  `DEPTNO` int(2) NOT NULL,
  `DNAME` varchar(14) DEFAULT NULL,
  `LOC` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入数据
insert into `dept`(`DEPTNO`,`DNAME`,`LOC`) values
('10','ACCOUNTING','NEW YORK'),
('20','RESEARCH','DALLAS'),
('30','SALES','CHICAGO'),
('40','OPERATIONS','BOSTON');

-- 查询表
select * from emp;
select * from dept;

-- 1． 列出至少有4个员工的所有部门编号和名称。
select deptno,dname from dept where 
deptno in(select deptno from (select count(*) count,deptno  
from emp group by deptno) dn where dn.count>=4);

select s.deptno,dept.DNAME
from (select deptno,count(*) as sum from emp group by deptno having sum>=4) as s
left join dept
on s.deptno=dept.DEPTNO;

-- 2． 列出薪金比“SMITH”多的所有员工。
select empno,ename from emp where 
sal>(select sal from emp where ename="SMITH");

-- 3． 列出所有员工的姓名及其直接上级的姓名。
select emp1.ename,emp.ename mgrname from emp emp1  inner join 
emp on emp1.mgr=emp.empno;

select e.EMPNO,e.ENAME,emp.ENAME from emp as e
left join
emp
on e.MGR=emp.EMPNO;

– 4． 列出受雇日期早于其直接上级的所有员工。
select * from (select emp1.ename name,emp1.hiredate time,emp.ename as mgrname,emp.hiredate as mgrtime from emp emp1
inner join emp
on emp1.mgr=emp.empno) s where s.time<s.mgrtime;

select e.EMPNO,e.HIREDATE as time,emp.HIREDATE as date from emp as e
left join
emp
on e.MGR=emp.EMPNO having time<date;

-- 5． 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select * from dept left join emp 
on dept.DEPTNO=emp.deptno;

-- 6． 列出所有“CLERK”（办事员）的姓名及其部门名称。
select ename,dname from emp,dept where 
emp.deptno=dept.deptno and emp.job="CLERK";

select ename,dname from (select * from emp where job="CLERK") as emp
left join dept
on emp.DEPTNO=dept.DEPTNO;
-- 7． 列出最低薪金大于1500的各种工作。
select s.job from
(select min(sal) min,job from emp group by job having  min>1500) s;
-- 8． 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号
select ename from emp 
where emp.deptno=(select deptno from dept where dept.dname="SALES");
-- 9． 列出薪金高于公司平均薪金的所有员工。
select ename from emp where sal>(select avg(sal) from emp);
-- 10．列出与“SCOTT”从事相同工作的所有员工。 
select * from emp where job=(select job from emp where ename="SCOTT") and ename<>"SCOTT";
-- 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select ename,sal from emp where sal in (select sal from emp where deptno=30);

select DISTINCT * from emp
inner join (select sal from emp where deptno=30) as e
on emp.sal=e.sal;
-- 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 
select * from emp where sal>(select max(sal) from emp where deptno=30);
-- 13．列出在每个部门工作的员工数量、平均工资和平均服务期限。 
 select count(*),avg(sal),avg(DATEDIFF(CURRENT_DATE,HIREDATE))from emp group by deptno;
-- 14．列出所有员工的姓名、部门名称和工资。
select ename,dname,sal + case when comm is null then 0 else comm end as money from emp
left join
dept
on emp.deptno=dept.deptno;

-- 15．列出所有部门的详细信息和部门人数。 
select * from dept 
left join 
(select count(*) count,deptno from emp group by deptno) s
on s.deptno=dept.deptno;

-- 16．列出各种工作的最低工资。 
select job,min(money) from (
select job,sal+case when comm is null then 0 else comm end money from emp)
s group by s.money;

-- 17．列出各个部门的MANAGER（经理）的最低薪金。 
select * from
(select deptno,min(sal) from emp where job="MANAGER" group by deptno )  as s left join 
dept 
on s.deptno=dept.deptno;

select * from (select * from emp where job="MANAGER") as emp
where sal=(
select min(sal) from (select * from emp where job="MANAGER") as e where emp.DEPTNO=e.deptno);
-- 18．列出所有员工的年工资,按年薪从低到高排序。
select s.ename ,s.money from 
(select ename,12 *sal + case when comm is null then 0 else  comm end money from emp) s order by s.money;

select ename,12*sal+case when comm is null then 0 else comm end as money 
from emp ORDER BY money;

– 思考： 列出每个部门薪水前两名最高的人员名称以及薪水
select emp1.ename,emp1.sal,emp1.deptno from emp emp1 where
(select count(*) from emp where emp.deptno=emp1.deptno and emp1.sal<emp.sal)<2;

select * from emp
where 2>(
select count(*) from emp as e where emp.DEPTNO=e.deptno and emp.sal<e.sal);

java
java入门基础学习（一）
java入门基础学习（二）
java入门基础学习（三）
java入门基础学习（四）
java入门基础学习（五）
java入门基础学习（六）
java入门基础学习（七）
java入门基础学习（八）
java入门基础学习（九）
java入门基础学习（十）
java入门基础学习（十一）
java入门基础学习（十二)
java入门基础学习（十三）
java入门基础学习（十四）Maven Git
java总结,题目+笔记
java进阶之常见对象（一）
java进阶之常见对象（二）
java进阶之冒泡排序
java进阶之选择排序
java进阶之面向对象（封装）
java进阶之面向对象（代码块、继承）
java进阶之面向对象（多态、抽象、接口）
java进阶之匿名内部类、访问修饰符、包
java进阶之io流（字节流，字符流）
Linux
Linux基础一
Linux基础二
Mysql
mysql一
mysql二
mysql三