test_lesson06子查询

测试

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'

)