1.查询工资大于12000的员工姓名和工资
select last_name, salary from employees where salary > 120002.查询员工号为176的员工的姓名和部门号
select last_name, department_id from employees where employee_id = 1763.选择工资不在5000到12000的员工的姓名和工资
select last_name, salary from employees where salary not between 5000 and 120004.选择雇用时间在1998-02-01到1998-05-01之间的员工姓名，job_id和雇用时间
select last_name, job_id, hire_date from employees where hire_date between '1-2月 -1998' and '1-5月 -1998'5.选择在20或50号部门工作的员工姓名和部门号
select last_name, department_id from employees where department_id in (20, 50)6.选择在1994年雇用的员工的姓名和雇用时间
select last_name, hire_date from employees where hire_date like '% -94'7.选择公司中没有管理者的员工姓名及job_id
select last_name, job_id from employees where manager_id is null8.选择公司中有奖金的员工姓名，工资和奖金级别
select last_name, salary, commission_pct from employees where commission_pct is not null9.选择员工姓名的第三个字母是a的员工姓名
select last_name from employees where last_name like '__a%'10.选择姓名中有字母a和e的员工姓名
select last_name from employees where 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 employees order by last_name14.查询各员工的姓名，并显示出各员工在公司工作的月份数（worked_month）。
select last_name,months_between(sysdate,hire_date) worked month from employees15.查询员工的姓名，以及在公司工作的月份数（worked_month），并按月份数降序排列
select last_name, months_between(sysdate, hire_date) worked_month from employees order by worked_month desc16.做一个查询，产生下面的结果
<last_name> earns <salary> monthly but wants <salary*3> select last_name || ' earns ' || salary || ' monthly but wants ' || salary * 3 from employees17.使用decode函数，按照下面的条件：
select last_name, job_id, decode(job_id, 'AD_PRES', 'A', 'ST_MAN', 'B', 'IT_PROG', 'C', 'SA_REP', 'D', 'ST_CLERK', 'E', 'F') GRADE from employees18.将第7题的查询用case函数再写一遍。
select last_name, job_id, case job_id when 'AD_PRES' then 'A' when 'ST_MAN' then 'B' when 'IT_PROG' then 'C' when 'SA_REP' then 'D' when 'ST_CLERK' then 'E' else 'F' end from employees19.查询公司员工工资的最大值，最小值，平均值，总和
Select max(salary), min(salary), avg(salary), sum(salary) From employees20.查询各job_id的员工工资的最大值，最小值，平均值，总和
Select job_id, max(salary), min(salary), avg(salary), sum(salary) From employees Group by job_id21.选择具有各个job_id的员工人数
Select job_id, count(employee_id) From employees Group by job_id;22.查询员工最高工资和最低工资的差距（DIFFERENCE）
Select max(salary) – min(salary) difference From employees23.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
Select manager_id, min(salary) From employees Where manager_id is not null Group by manager_id Having min(salary) >= 600024.查询所有部门的名字，location_id，员工数量和工资平均值
Select department_name, location_id, count(employee_id), avg(salary) From employees e join departments d On e.department_id = d.department_id Group by department_name, location_id一、找到员工表中工资最高的前三名：
关于行号 1. rownum永远按照默认的顺序生成 2. rownum只能使用 < <=; 不能使用> >= SQL> select rownum,empno,ename,sal 2 from (select * from emp order by sal desc) 3 where rownum<=3; ROWNUM EMPNO ENAME SAL ---------- ---------- ---------- ---------- 1 7839 KING 5000 2 7788 SCOTT 3000 3 7902 FORD 30002.rownum只能使用 < <=; 不能使用> >=
SQL> select * 2 from (select rownum r,e1.* 3 from (select * from emp order by sal) e1 4 where rownum <=8 5 ) 6 where r >=5;临时表:
1. create global temporary table ***** 2.自动创建: order by 特点：当事务或者会话结束的时候，表中的数据自动删除 所以 oracle中事务提交了 数据不一定保存下来了（临时表），数据不在，但表还在 SQL> create global temporary table test2 2 (tid number,tname varchar2(20)) 3 on commit delete rows; 表已创建。 SQL> insert into test2 values(1,'Tom'); 已创建 1 行。 SQL> select * from test2; TID TNAME ---------- -------------------- 1 Tom SQL> commit; 提交完成。 SQL> select * from test2; 未选定行 SQL> desc test2 名称 是否为空? 类型 ----------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------- TID NUMBER TNAME VARCHAR2(20)二、找到员工表中薪水大于本部门平均薪水的员工:
相关子查询：将主查询中的值作为参数传递给子查询:
SQL> select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal 2 from emp e 3 where sal > (select avg(sal) from emp where deptno=e.deptno); EMPNO ENAME SAL AVGSAL ---------- ---------- ---------- ---------- 7499 ALLEN 1600 1566.66667 7566 JONES 2975 2175 7698 BLAKE 2850 1566.66667 7788 SCOTT 3000 2175 7839 KING 5000 2916.66667 7902 FORD 3000 2175解：
SQL> select e.empno,e.ename,e.sal,d.avgsal 2 from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d 3 where e.deptno=d.deptno and e.sal > d.avgsal; EMPNO ENAME SAL AVGSAL ---------- ---------- ---------- ---------- 7698 BLAKE 2850 1566.66667 7499 ALLEN 1600 1566.66667 7902 FORD 3000 2175 7788 SCOTT 3000 2175 7566 JONES 2975 2175 7839 KING 5000 2916.66667三、统计每年入职的员工个数
SQL> select count(*) Total, 2 sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980", 3 sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981", 4 sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982", 5 sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987" 6 from emp; TOTAL 1980 1981 1982 1987 ---------- ---------- ---------- ---------- ---------- 14 1 10 1 21.查询和Zlotkey相同部门的员工姓名和雇用日期
select last_name, hire_date from employees where department_id = ( select department_id from employees where last_name = 'Zlotkey' )2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
select employee_id, last_name, salary from employees where salary > ( select avg(salary) from employees )3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
select employee_id, last_name, salary from employees e where salary > ( select avg(salary) from employees where department_id = e.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%' )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' )