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 阅读(862)
评论(1) 编辑 收藏 所属分类:
Oralce&&PL/SQL