oracle數據庫中
在scott用戶下的emp和dept表中查詢
1，查詢平均工資最高的那個部門的名稱，最高工資，最低工資，平均工資。
?
?1
1
，查詢平均工資最高的那個部門的名稱，最高工資，最低工資，平均工資。
?2
????
1
>
使用rownum的方式
?3
????
select
?????d.dname?部門名稱,
?4
???????????????
min
(e.sal)?最低工資,
?5
???????????????
max
(e.sal)?最高工資,
?6
???????????????
avg
(e.sal)?平均工資
?7
????
from
?emp?e,dept?d?
where
?e.deptno
=
d.deptno?
and
?e.deptno
=
?8
????????????????????????(
select
?deptno?
from
?
?9
????????????????????????(
select
?
avg
(sal)?
as
?avgsal,deptno?
from
?emp?
10
??????????????????????????
group
?
by
?deptno?
order
?
by
?avgsal?
desc
)?
where
?rownum?
=
?
1
)?
11
????
group
?
by
?d.dname;
12
13
????
2
>
不使用rownum的方式
14
15
????
select
??d.dname?部門名稱,
16
????????
min
(e.sal)?最低工資,
17
????????
max
(e.sal)?最高工資,
18
????????
avg
(e.sal)?平均工資
19
????????
from
?emp?e,dept?d?
where
?e.deptno?
=
?d.deptno?
20
????????
and
?d.deptno?
=
?????(
select
?aa.deptno?
from
?(
select
?
avg
(sal)?
as
?avgsal,deptno?
from
?emp?
group
?
by
?deptno)?aa?
21
????????????????????
where
?aa.avgsal?
=
?(
select
?
MAX
(avgsal)?
from
?
22
????????????????????????????(
select
?
avg
(sal)?
as
?avgsal,deptno?
from
?emp?
group
?
by
?deptno)))
23
????????
group
?
by
?d.dname;
?
2，求工資由高到低，排名第三的員工姓名
1
select
?
*
?
from
(
select
?
*
?
from
?emp?
order
?
by
?sal?
desc
)?
where
?rownum?
<
?
4
2
minus
3
select
?
*
?
from
(
select
?
*
?
from
?emp?
order
?
by
?sal?
desc
)?
where
?rownum?
<
?
3
3，求當前系統的日期，不要年和月。
1
select
?to_char(sysdate,
'
----mm--dd
'
)?
from
?dual;
?
4，查詢處員工人數最多的那個部門的
?????? 部門編號，部門名稱，部門人數
?1
1>不使用rownum的方式
?2
????select?????e.deptno??部門編號,
?3
????????d.dname??????部門名稱,
?4
????????count(*)??部門人數
?5
????from?emp?e,dept?d?where?e.deptno?=?d.deptno?and?e.deptno?=
?6
????(select?aa.deptno?from
?7
????(select?count(empno)?as?empno_count,deptno?from?emp?group?by?deptno)?aa?where?aa.empno_count?=
?8
????(select?max(empno_count)?as?max_count?from(
?9
????????????????????????????????select?count(empno)?as?empno_count,deptno?from?emp?group?by?deptno)))
10
????group?by?d.dname,e.deptno;
11
12
????2>使用rownum的方式
13
????select?e.deptno??部門編號,
14
?????????d.dname??部門名稱,
15
????????count(*)?部門人數
16
????from?emp?e,dept?d?where?e.deptno?=?d.deptno?and?e.deptno?=
17
????(select?deptno?from
18
????(select?count(empno)?as?empno_count,deptno?from?emp?group?by?deptno?order?by?empno_count?desc)?where?rownum?=?1)
19
????group?by?e.deptno,d.dname;
5，查詢工資成本最高的那個部門的
?????? 部門編號，部門名稱，部門月工資成本
?1
>
不使用rownum的方式
?2
????
select
?e.deptno?
as
?部門編號,
?3
???????????d.dname?
as
?部門名稱,
?4
???????????
sum
(e.sal)?
as
?部門月工資成本
?5
????????
from
?emp?e,dept?d?
where
?e.deptno?
=
?d.deptno?
and
?e.deptno?
=
?6
????????(
select
?aa.deptno?
from
?7
????????????(
select
?
sum
(sal)?
as
?sum_sal,deptno?
from
?emp?
group
?
by
?deptno)?aa?
where
?aa.sum_sal?
=
?8
????????????(
select
?
max
(sum_sal)?
from
?9
????????????????????????????(
select
?
sum
(sal)?
as
?sum_sal,deptno?
from
?emp?
group
?
by
?deptno)))
10
????
group
?
by
?e.deptno,d.dname;
11
????
12
????
2
>
使用rownum的方法
13
????
select
?e.deptno?
as
?部門編號,
14
???????d.dname?
as
?部門名稱,
15
???????
sum
(e.sal)?
as
?部門月工資成本
16
????
from
?emp?e,dept?d?
where
?e.deptno?
=
?d.deptno?
and
?e.deptno?
=
17
????????(
select
?deptno?
from
18
????????(
select
?
sum
(sal)?
as
?sum_sal,deptno?
from
?emp?
group
?
by
?deptno?
order
?
by
?sum_sal?
desc
)?
where
?rownum?
=
?
1
)
19
????
group
?
by
?e.deptno,d.dname;
6，按 某某人 的上級是 某某人的格式查詢處所有員工的信息，
????? 如果某某人沒有上級，則上級的名字為“未知”
6
1
2
select
?a.ename?
as
?員工?,nvl(b.ename,
'
未知
'
)
as
?上級?
from
?emp?a,emp?b?
3
where
?a.mgr?
=
?b.empno(
+
)
?
7，查詢處在公司工作時間最長的原工信息
????? 部門名稱，員工姓名，在公司工作的時間
7題
1
select
?d.dname?
as
?部門名稱,ename?
as
?員工姓名,?sysdate
-
hiredate?
as
?工作時間?
from
?emp?e,dept?d?
2
????????
where
?e.deptno?
=
?d.deptno?
3
????????
and
?e.hiredate?
=
?(
select
?aa.min_hiredate?
from
?(
select
?
min
(hiredate)?min_hiredate?
from
?emp)?aa?
4
?????????????????????
where
?aa.min_hiredate?
=
?(
select
?
min
(hiredate)?
from
?emp))
5
8，顯示裁員信息，例如，那個部門只有一個就不裁，若部門員工人數大于1人，則裁員工工資最高的那個人。
8題
1
select
?ename?
as
?員工姓名,deptno?
as
?員工部門,sal?
as
?工資?
from
?emp
2
????
where
?sal?
in
(
select
?
max
(sal)?
from
?emp?
where
?deptno?
in
3
????????????(
select
?deptno?
from
?emp?
group
?
by
?deptno?
having
?
count
(
*
)?
>
?
1
)?
group
?
by
?deptno)
EMP表
EMPNO ENAME????? JOB????????????MGR??? HIREDATE????????? SAL????? COMM????DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
?7369 SMITH????? CLERK???????????????7902????1980-12-17????????800.00?????????????????????20
?7499 ALLEN????? SALESMAN??????7698????1981-2-20????????1600.00??? 300.00???? 30
?7521 WARD?????? SALESMAN??????7698????1981-2-22????????1250.00??? 500.00???? 30
?7566 JONES????? MANAGER?????????7839????1981-4-2?????????2975.00?????????????????????20
?7654 MARTIN???? SALESMAN??????7698?????1981-9-28???? 1250.00?? 1400.00???? 30
?7698 BLAKE????? MANAGER?????????7839????1981-5-1?????????2850.00?????????????????????30
?7782 CLARK????? MANAGER?????????7839?????1981-6-9???????? 2450.00?????????????????????10
?7788 SCOTT????? ANALYST??????????7566????1987-4-19????????3000.00?????????????????????20
?7839 KING?????? PRESIDENT??????????????????????1981-11-17??? 5000.00?????????????????????10
?7844 TURNER???? SALESMAN??????7698????1981-9-8?????????1500.00????? 0.00????????30
?7876 ADAMS????? CLERK???????????????7788????1987-5-23????????1100.00?????????????????????20
?7900 JAMES????? CLERK???????????????7698????1981-12-3?????????950.00????????????????????????30
?7902 FORD?????? ANALYST????????????7566????1981-12-3??????? 3000.00???????????????????? 20
?7934 MILLER???? CLERK???????????????7782????1982-1-23??????? 1300.00?????????????????????10
dept表
DEPTNO????DNAME?????????????LOC
------????--------------????-------------
??? 10???????ACCOUNTING???? NEW YORK
??? 20????RESEARCH?????????????DALLAS
??? 30????SALES??????????????????????CHICAGO
??? 40????OPERATIONS??????? BOSTON
posted on 2006-09-04 22:43
JavaCoffe 閱讀(872)
評論(1) 編輯 收藏 所屬分類:
Oralce&&PL/SQL