Oracle查询练习02

1 . 查询和Zlotkey相同部门的员工姓名和雇用日期

select last_name, hire_datefrom employeeswhere department_id = (      select department_id      from employees      where last_name = 'Zlotkey')

2 . 查询工资比公司平均工资高的员工的员工号，姓名和工资。

select employee_id, last_name, salaryfrom employeeswhere salary > (      select avg(salary)      from employees)

3 . 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资

select employee_id, last_name, salaryfrom employees ewhere salary > (      select avg(salary)      from employees      where department_id = e.department_id)

4 . 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

select employee_id, last_namefrom employeeswhere department_id in (      select department_id      from employees      where last_name like '%u%')

5 . 查询在部门的location_id为1700的部门工作的员工的员工号，

select employee_idfrom employeeswhere department_id in (      select department_id      from departments      where location_id = 1700)

6 . 查询管理者是King的员工姓名和工资

select last_name, salaryfrom employeeswhere manager_id in (      select employee_id      from employees      where last_name = 'King')