oracle子查询

练习所需要的表是：HR 用户下的 Employees , departments

1、 编写一个查询，使其显示与Zlotkey在同一个部门的所有员工的姓氏和聘用日期。但不

包括Zlotkey。

select last_name,hire_date from employees e join departments d

on d.department_id=e.department_id and e.department_id in (select department_id from employees where last_name<>'Zlotkey')

2、 创建一个查询，显示薪金超过平均薪金的所有员工的员工编号和姓氏。按薪金升序排列

结果。

select employee_id,last_name from employees where salary>(select avg(salary) from employees) order by salary asc

3、 编写一个查询，使其显示所有员工的员工编号和姓氏，条件是：他们所工作的部门里有

员工的姓氏中包含一个“u”。

select employee_id,last_name from employees

where department_id in(select department_id from employees where last_name like'%u%')/

4、 显示部门地点标识为1700的所有员工的姓氏、部门编号和职务标识。 select e.last_name,e.department_id,e.job_id from employees e join departments d

on d.department_id=e.department_id and e.department_id in(select e.department_id from employees where location_id='1700')

5、 显示King的每个下属员工的姓氏和薪金。 select last_name,salary from employees

where manager_id in(select manager_id from employees where last_name='King') and last_name<>'King'

6、 显示Executive部门中每位员工的部门编号、姓氏和职务标识。 select department_id,last_name,job_id from employees

where department_id in(select department_id from departments where department_name='Executive')

7、 编写一个查询，使其显示所有员工的员工编号、姓氏和薪金，条件是：他们的薪金超过

平均薪金并且他们所工作的部门里有员工的姓氏包含一个“u”。 select employee_id,last_name,salary from employees where salary>(select avg(salary) from employees) and last_name like'%u%'

8、 执行下面语句创建一张表。

create table st1 (name varchar(20),chinese int,maths int,english int);

insert into st1 values('z3',95,93,80); insert into st1 values('l4',100,72,88); insert into st1 values('w5',99,68,92);

commit;

编写一个查询以下面形式显示 NAME l4 l4 l4 w5 w5 w5 z3 z3 z3

SUBJECT chinese english maths chinese english maths chinese english maths SCORE 100 88 72 99 92 68 95 80 93 9、 执行下面语句创建一张表。

create table st2 (name varchar(20),subject char(10),score number);

insert into st2 values('z3','chinese',95); insert into st2 values('z3','maths',93); insert into st2 values('z3','english',80); insert into st2 values('l4','chinese',100); insert into st2 values('l4','maths',72); insert into st2 values('l4','english',88); insert into st2 values('w5','chinese',99); insert into st2 values('w5','maths',68); insert into st2 values('w5','english',92); commit;

编写一个查询，以下面形式显示 NAME z3 w5 l4 CHINESE 95 99 100 MATHS 93 68 72 ENGLISH 80 92 88

本文来源：https://www.bwwdw.com/article/inut.html