/*
写一个查询显示所有雇员的last name、department number、and department name
*/select emp.last_name, department_id, dep.department_name from employees emp naturaljoin departments dep;
/*
写一个查询显示所有有佣金的雇员的last name、department name、location ID和城市
*/select emp.last_name, dep.department_name, dep.location_id from employees emp naturaljoin departments dep
where emp.commission_pct isnotnull;
/*
写一个查询显示所有有佣金的雇员的last name、department name、location ID和城市
*/select emp.last_name, dep.department_name, dep.location_id from employees emp join departments dep using(department_id)where emp.commission_pct isnotnull;
/*
显示所有在其 last names 中有一个小写 a 的雇员的 last name 和 department name。
*/select emp.last_name, dep.department_name from employees emp join departments dep using(department_id)where instr(emp.last_name,'a')>0;
/*
使用内连接写一个查询显示那些工作在 Toronto 的所有雇 员 的 last name 、 job 、 department number 和 departmentname。
*/select emp.last_name, jobs.job_title, department_id, dep.department_name
from employees emp
innerjoin departments dep using(department_id)innerjoin locations loc using(location_id)innerjoin jobs using(job_id)where loc.city ='Toronto';
/*
创建一个查询显示所有与被指定雇员工作在同一部门的雇员 (同事) 的 lastnames、departmentnumbers。给每列一个
适当的标签
*/select e.last_name, c.last_name,e.department_id
from employees e,employees c
where e.department_id = c.department_id and e.employee_id <> c.employee_id;
/*
创建一个查询显示那些在雇员 Davies 之后入本公司工作 的雇员的 name 和 hiredate
*/select emp.last_name, emp.hire_date from employees emp
where hire_date >(select hire_date from employees emp
where emp.last_name ='Davies');