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

时间:2023-02-12 07:54:05


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'
)