--1． 查询员工的姓名、部门编号和名称(等值连接)
SELECT e.LAST_NAME,d.DEPARTMENT_ID,d.DEPARTMENT_NAME
FROM departments d,employees e
where d.DEPARTMENT_ID=e.DEPARTMENT_ID;
--2． 创建部门编号为80的所有职务，并包括部门地址
select j.JOB_ID,d.LOCATION_ID
from departments d
natural join jobs j
where d.DEPARTMENT_ID=80;
--3． 查询获得奖金的所有员工姓名、部门名称、地址ID和对应的城市
select e.LAST_NAME,d.DEPARTMENT_NAME,d.LOCATION_ID,l.CITY
from employees e
join departments d
ON e.DEPARTMENT_ID=d.DEPARTMENT_ID
join locations l
on d.LOCATION_ID=l.LOCATION_ID
where e.COMMISSION_PCT is not null;
--4． 查询所有LastName中名字包含小写a的所有员工姓名和部门名称
SELECT e.LAST_NAME,d.DEPARTMENT_NAME
from employees e
natural join departments d
where e.LAST_NAME like '%a%';
--5. 查询所有在多伦多（Toronto）员工名字、职务、部门编号和部门名称
SELECT e.LAST_NAME,e.JOB_ID,d.DEPARTMENT_ID,d.DEPARTMENT_NAME
from employees e
join departments d
on e.department_id=d.department_id
join LOCATIONS l
on d.LOCATION_ID=l.LOCATION_ID
where l.CITY='Toronto';
--6． 查询员工名字、编号以及他们经理的名字和编号，并标记为Employee，
--Emp#，Manager，Mgr#，最后按管理者编号升序排列
select e.LAST_NAME as "Employee",e.EMPLOYEE_ID as "Emp#", m.LAST_NAME as "Manager",e.EMPLOYEE_ID as "Mgr#"
from employees e
join EMPLOYEES m
on e.EMPLOYEE_ID=m.EMPLOYEE_ID
ORDER BY m.MANAGER_ID;
--7． 显示员工姓名、部门编号以及在同一部门工作的所有员工
SELECT e.DEPARTMENT_ID,e.LAST_NAME,d.DEPARTMENT_ID,d.DEPARTMENT_NAME
from employees e
join departments d
on e.DEPARTMENT_ID=d.DEPARTMENT_ID;
--8． 在员工Davies之后聘用的人员姓名和入职日期
select e.LAST_NAME,TO_CHAR(e.HIRE_DATE,'yyyy-mm-dd')
from employees e
where e.HIRE_DATE>=
(SELECT f.HIRE_DATE FROM EMPLOYEES f WHERE f.LAST_NAME='Davies');