ql/sql---子查询练习02

--1.查询和ZLOTKEY的员工姓名和雇佣日期

select last_name,hire_date
from employees
where department_id = (
                        select department_id
                        from employees
                        where last_name='ZLOTKEY'
                      )
and last_name <> 'ZLOTKEY'

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

select last_name,employee_id,salary
from employees
where salary > (
                select avg(salary)
                from employees
               )
--3.查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资

select employee_id,last_name,salary
from employees e1
where sakary > (
                select avg(salary)
                from employees e2
                where e1.department_id=e2.employment_id
                group by department_id
               )

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

select employee_id,last_name
from employees
where department_id in (
                        select department_id
                        from employees
                        where last_name like '%u%'
                       )
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 lacotion_id =1700
                       )

--6.查询管理者是King的员工姓名和工资
select last_name,salary
from employees
where manager_id in(
                    select employee_id
                    from employees
                    where last_name = 'King'
                   )