SQL编程题目

考察面试者SQL编写能力，如多字段排序、关联表查询、分组SQL编写

按部门编号升序，工资倒序排列员工信息？（考察多字段排序）

select * from emp order by deptno asc ,sal desc ;

列出deptno=30的部门名称及员工？ （考察关联表查询）

select *
from dept
left join emp e on dept.deptno = e.deptno and dept.deptno =30;

select *
from dept
         join  emp e on dept.deptno = e.deptno
where dept.deptno =30;

列出每个部门最高，最低，平均工资，部门人数？（考察分组SQL编写）

思路：先找出按什么字段分组 > 分组字段放select之后 > 聚合函数列出需求字段

select deptno, max(sal) ,min(sal),avg(sal), count(*)
from emp 
group by deptno;

列出市场部（SALES)及研发部（RESEARCH)的员工？ （考察多条件 or 关键词应用）

思路：先关联出两张表 > 筛选条件列出要求

select d.dname,e.*
from emp as e ,dept as d
         where  e.deptno = d.deptno and (d.dname = 'SALES' or d.dname = 'RESEARCH')

select d.dname,emp.*
from emp
inner join dept d on emp.deptno = d.deptno
    where d.dname = 'SALES' or d.dname = 'RESEARCH'

列出人数超过3人的部门？（考察对分组二次筛选）

思路：先关联表分组 > 在添加 HAVING 子语句对分组二次筛选

select dname
from dept
         left join emp e on dept.deptno = e.deptno
group by dept.dname

output

+----------+
|dname     |
+----------+
|ACCOUNTING|
|RESEARCH  |
|SALES     |
+----------+

select dname
from dept
         left join emp e on dept.deptno = e.deptno
group by dept.dname
having count(*) > 3

HAVING 关键字和 WHERR 的区别--HAVING 关键字是使用在 分组关键字（GROUP BY）之后的，是对分组进行条件筛选

WHERE--关键字是整个SQL语句的筛选条件

计算JONES年薪比SMITH高多少？考察的是子查询（嵌套查询能力）

思路： 先计算两人年薪 > 然后子查询相减

select ename, sal *12
from emp
where ename = 'SMITH';

select sal *12
from emp
where ename = 'JONES';

列出直接向King汇报的员工？考察的是子查询？

思路：先找King编号 > 关联emp.mgr查询

# 方法1
select *
from emp
where mgr = (select empno
             from emp
             where ename = 'King');
# 方法2

select *
from emp
   , (select empno
      from emp
      where ename = 'King') a

where emp.mgr = a.empno;
# 方法3

select *
from emp
         inner join (select empno
                     from emp
                     where ename = 'King') a on emp.mgr = a.empno;
# 方法4
select *
from emp
         inner join (select empno
                     from emp
                     where ename = 'King') a
where emp.mgr = a.empno

##列出公司所有员工的工龄，并倒序排列？(考察MySQL 关于日期的内置函数)

# 方法1
select DATE_FORMAT(now(), '%Y') - DATE_FORMAT(hiredate
    , '%Y') as seniority
from emp
order by seniority desc;

# 方法2
select *
from (select DATE_FORMAT(now(), '%Y') - DATE_FORMAT(hiredate
             , '%Y') as seniority
         from emp) c
order by c.seniority desc;

计算管理者与基层员工平均薪资差额?

SELECT a_avg - b_avg FROM
(SELECT avg(sal) a_avg FROM emp where job = 'MANAGER' OR job = 'PRESIDENT') a ,
(SELECT avg(sal) b_avg FROM emp where job in ('CLERK' , 'SALESMAN' , 'ANALYST')) b