SQL面试笔试

#(1)请写出要查询员工J开头的名字其工号(EMPNO)及部门名称(DEPTNA)的 SQL语句
 SELECT e.emp,e.name,d.deptna FROM emp e left join dept d on d.deptno =e.deptno where e.name like 'J%'

#(2)请写出要查询 Kevin 所在部门的部门代号(DEPTNO)及部门名称(DEPTNA)的 SQL 语句
 SELECT e.name,d.deptno,d.deptna FROM emp e left join dept d on d.deptno =e.deptno where e .name = 'kevin'
 
 #(3)请使用多表查询(JOIN),写出要查询所有员工,包含没有部门的 SQL 语句
 SELECT e.name,d.deptna FROM emp e left join dept d on d.deptno =e.deptno
 
 
 #(4)请使用多表查询(JOIN),写出要查询有员工没有部门,以及有部门没有员工的 SQL语句
 SELECT e.name,d.deptna 
 FROM emp e 
 LEFT JOIN dept d ON e.deptno = d.deptno
 WHERE d.deptno IS NULL
 UNION 
 SELECT e.name,d.deptna 
 FROM dept d
 LEFT JOIN emp e ON e.deptno = d.deptno 
 WHERE e.deptno IS NULL

 #dept org
 #(1)请写出要查询部门MIS下属的所有部门的SQL 语句

 SELECT d.deptno, d.deptna
FROM dept d
INNER JOIN org o ON d.deptno = o.child_deptno
INNER JOIN dept p ON  o.parent_deptno = p.deptno
WHERE p.deptna = 'MIS';

 #(2)请写出要查询部门ERP 上层的所有部门的 SQL 语句
 
SELECT d.*
FROM dept d
INNER JOIN org o ON d.deptno = o.parent_deptno
INNER JOIN dept p ON o.child_deptno = p.deptno
WHERE p.deptna = 'ERP';