查詢練習(xí)
1.查詢工資大于12000的員工姓名和工資
select last_name, salary
from employees
where salary > 12000
2.查詢員工號為176的員工的姓名和部門號
select last_name, department_id
from employees
where employee_id = 176
3.選擇工資不在5000到12000的員工的姓名和工資
select last_name, salary
from employees
where salary not between 5000 and 12000
4.選擇雇用時間在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 null
8.選擇公司中有獎金的員工姓名，工資和獎金級別
select last_name, salary, commission_pct
from employees
where commission_pct is not null
9.選擇員工姓名的第三個字母是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.顯示系統(tǒng)時間
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
12.查詢員工號，姓名，工資，以及工資提高百分之20%后的結(jié)果（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_name
14.查詢各員工的姓名，并顯示出各員工在公司工作的月份數(shù)（worked_month）。
select last_name,months_between(sysdate,hire_date) worked month from employees
15.查詢員工的姓名，以及在公司工作的月份數(shù)（worked_month），并按月份數(shù)降序排列
select last_name, months_between(sysdate, hire_date) worked_month
from employees
order by worked_month desc
16.做一個查詢，產(chǎn)生下面的結(jié)果
<last_name> earns <salary> monthly but wants <salary*3>
結(jié)果.PNG
select last_name || ' earns ' || salary || ' monthly but wants ' || salary * 3
from employees
17.使用decode函數(shù)，按照下面的條件：
decode函數(shù).PNG
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 employees
18.將第7題的查詢用case函數(shù)再寫一遍。
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 employees
19.查詢公司員工工資的最大值，最小值，平均值，總和
Select max(salary), min(salary), avg(salary), sum(salary)
From employees
20.查詢各job_id的員工工資的最大值，最小值，平均值，總和
Select job_id, max(salary), min(salary), avg(salary), sum(salary)
From employees
Group by job_id
21.選擇具有各個job_id的員工人數(shù)
Select job_id, count(employee_id)
From employees
Group by job_id;
22.查詢員工最高工資和最低工資的差距（DIFFERENCE）
Select max(salary) – min(salary) difference
From employees
23.查詢各個管理者手下員工的最低工資，其中最低工資不能低于6000，沒有管理者的員工不計算在內(nèi)
Select manager_id, min(salary)
From employees
Where manager_id is not null
Group by manager_id
Having min(salary) >= 6000
24.查詢所有部門的名字，location_id，員工數(shù)量和工資平均值
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
一、找到員工表中工資最高的前三名：
關(guān)于行號
1. rownum永遠(yuǎn)按照默認(rèn)的順序生成
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 3000
2.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.自動創(chuàng)建: order by
特點：當(dāng)事務(wù)或者會話結(jié)束的時候，表中的數(shù)據(jù)自動刪除
所以 oracle中事務(wù)提交了 數(shù)據(jù)不一定保存下來了（臨時表），數(shù)據(jù)不在，但表還在
SQL> create global temporary table test2
2 (tid number,tname varchar2(20))
3 on commit delete rows;
表已創(chuàng)建。
SQL> insert into test2 values(1,'Tom');
已創(chuàng)建 1 行。
SQL> select * from test2;
TID TNAME
---------- --------------------
1 Tom
SQL> commit;
提交完成。
SQL> select * from test2;
未選定行
SQL> desc test2
名稱 是否為空? 類型
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
TID NUMBER
TNAME VARCHAR2(20)
二、找到員工表中薪水大于本部門平均薪水的員工:
相關(guān)子查詢：將主查詢中的值作為參數(shù)傳遞給子查詢:
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
三、統(tǒng)計每年入職的員工個數(shù)
每年入職的員工個數(shù).PNG
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 2
1.查詢和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'
)
