Oracle查询相关的联系题

1.查询工资大于12000的员工姓名和工资

select last_name, salaryfrom employeeswhere salary > 12000

2.查询员工号为176的员工的姓名和部门号

select last_name, department_idfrom employeeswhere employee_id = 176

3.选择工资不在5000到12000的员工的姓名和工资

select last_name, salaryfrom employeeswhere salary not between 5000 and 12000

4.选择雇用时间在1998-02-01到1998-05-01之间的员工姓名，job_id和雇用时间

select last_name, job_id, hire_datefrom employeeswhere hire_date between '1-2月 -1998' and '1-5月 -1998'

5.选择在20或50号部门工作的员工姓名和部门号

select last_name, department_idfrom employeeswhere department_id in (20, 50)

6.选择在1994年雇用的员工的姓名和雇用时间

select last_name, hire_datefrom employeeswhere hire_date like '% -94'

7.选择公司中没有管理者的员工姓名及job_id

select last_name, job_idfrom employeeswhere manager_id is null

8.选择公司中有奖金的员工姓名，工资和奖金级别

select last_name, salary, commission_pctfrom employeeswhere commission_pct is not null

9.选择员工姓名的第三个字母是a的员工姓名

select last_namefrom employeeswhere last_name like '__a%'

10.选择姓名中有字母a和e的员工姓名

select last_namefrom employeeswhere last_name like '%a%' and last_name like '%e%'

11.显示系统时间

select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') from dual;

12.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）

select employee_id, last_name, salary, salary * 1.2 "new salary"from employees;

13.将员工的姓名按首字母排序，并写出姓名的长度（length）

select last_name, length(last_name)from employeesorder by last_name

14.查询各员工的姓名，并显示出各员工在公司工作的月份数（worked_month）。

select last_name, months_between(sysdate, hire_date) worked_monthfrom employees

15.查询员工的姓名，以及在公司工作的月份数（worked_month），并按月份数降序排列

select last_name, months_between(sysdate, hire_date) worked_monthfrom employeesorder by worked_month desc

16.查询公司员工工资的最大值，最小值，平均值，总和

Select max(salary), min(salary), avg(salary), sum(salary)From employees

17.查询各job_id的员工工资的最大值，最小值，平均值，总和

Select job_id, max(salary), min(salary), avg(salary), sum(salary)From employeesGroup by job_id

18.选择具有各个job_id的员工人数

Select job_id, count(employee_id)From employeesGroup by job_id;

19.查询员工最高工资和最低工资的差距（DIFFERENCE）

Select max(salary) – min(salary) differenceFrom employees

20.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

Select manager_id, min(salary)From employeesWhere manager_id is not nullGroup by manager_idHaving min(salary) >= 6000

21.查询所有部门的名字，location_id，员工数量和工资平均值

Select department_name, location_id, count(employee_id), avg(salary)From employees e join departments dOn e.department_id = d.department_idGroup by department_name, location_id

22.查询和Zlotkey相同部门的员工姓名和雇用日期

select last_name, hire_datefrom employeeswhere department_id = (      select department_id      from employees      where last_name = 'Zlotkey')

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

select employee_id, last_name, salaryfrom employeeswhere salary > (      select avg(salary)      from employees)

24.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资

select employee_id, last_name, salaryfrom employees ewhere salary > (      select avg(salary)      from employees      where department_id = e.department_id)

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

select employee_id, last_namefrom employeeswhere department_id in (      select department_id      from employees      where last_name like '%u%')

1. 查询在部门的location_id为1700的部门工作的员工的员工号，

select employee_idfrom employeeswhere department_id in (      select department_id      from departments      where location_id = 1700)

1. 查询管理者是King的员工姓名和工资

select last_name, salaryfrom employeeswhere manager_id in (      select employee_id      from employees      where last_name = 'King')