oracle学习72-oracle之单行函数之子查询课后练习2

1.  查询和Zlotkey相同部门的员工姓名和雇用日期
a)  select last_name,hire_date
b)  from employees
c)  where department_id = (
d)                        select department_id
e)                        from employees
f)                        where last_name = 'Zlotkey'
g)                        )
h)  and last_name <> 'Zlotkey'
2.  查询工资比公司平均工资高的员工的员工号，姓名和工资。
a)  select last_name,employee_id,salary
b)  from employees
c)  where salary > (select avg(salary)
d)                 from employees)
3.  查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
a)  select employee_id,last_name,salary
b)  from employees e1
c)  where salary > (
d)                 select avg(salary)
e)                 from employees e2
f)                 where e1.department_id = e2.department_id
g)                 group by department_id
h)                 )
4.  查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
a)  select employee_id,last_name
b)  from employees
c)  where department_id in (
d)                         select department_id
e)                         from employees
f)                         where last_name like '%u%'
g)                          )
h)  and last_name not like '%u%'
5. 查询在部门的location_id为1700的部门工作的员工的员工号
select employee_id
from employees
where department_id in (
                       select department_id
                       from departments
                       where location_id = 1700
                       )
6.查询管理者是King的员工姓名和工资
select last_name,salary
from employees
where manager_id in (
                   select employee_id
                   from employees
                   where last_name = 'King'
                   )