数据库练习答案-职员

1 检索职员最多的部门编号和名称

select d.deptno,d.dname
from emp e
join dept d on e.deptno = d.deptno
where d.deptno in(
    select max(deptno)
    from emp
    group by deptno
)

2 检索平均工资高于部门为‘SALES’的部门编号和名称

select d.deptno,d.dname
from emp e 
join dept d on e.deptno = d.deptno
where d.deptno in(
    select deptno
    from emp
    group by deptno
    having avg(sal) > (
        select avg(sal)
        from emp
        where dname = 'SALES'
    )
)

3 检索部门名为“SLAES”中低于本部门职员平均工资的所有员工工号和姓名；

select e.empno , e.ename
from emp as e inner join dept as d 
on e.deptno = d.deptno
where  e.sal in (
    select emp.sal
    from emp
    where emp.deptno in (
        select dept.deptno
    from dept
    where dept.dname = 'SLAES'
    )
    group by empno
    having emp.sal < AVG(sal)
)

 4 查询部门编号在20及以下的部门编号、最低工资和最高工资，要求显示最低工资大于1000

select e.deptno,MIN(sal) as '最低工资',MAX(sal) as '最高工资'
from emp as e inner join dept as d 
on e.deptno = d.deptno
where d.deptno <= 20
group by deptno
having MIN(sal) > 1000

5 查询以字母“J”开头的员工姓名，部门和工资信息，要求其工资大于1500

select e.ename,e.deptno,e.sal
from emp as e inner join dept as d
on e.deptno = d.deptno
where e.ename like 'J%' and e.sal > 1500

6 查询工作年限大于38年员工的姓名、岗位、部门编号及工作年限信息 

select e.ename , e.job ,e.deptno,timestampdiff(year,curdate() , e.hiredate) as '工作年限'
from emp as e 
where timestampdiff(year,curdate(),e.hiredate) > 38