$ ^! x9 T; B3 Z' L& eselect ename, hiredate, - S( k1 U) `6 Y$ J* hround(sysdate - hiredate) days & Y' ]3 U+ Z0 A; j" P& afrom emp; # l" N) C, C6 D) o9 T/ t& t5 l R3 }/ V$ [( ]
4.计算员工入职多少个月?用整数表示。& D, l3 T! d* _9 k
select ename, hiredate,2 t1 A+ J! \" y: ^2 e
months_between(sysdate, hiredate) months9 ?7 ]2 q( O2 M0 N2 [- v
from emp_ning; 8 ]5 [( B9 T. D& t7 k' Q0 o # p0 R) P3 j, b; oselect ename, hiredate,1 E# k x7 T9 j: d' K9 r2 X" W
round(months_between(sysdate, hiredate)) months* b5 v9 i v6 v1 s& f7 N
from emp_ning; 9 j8 g W7 H- R3 M% |/ j" H8 w% I1 L" k u) N
f3(f2(f1(p1,p2),p3),p4) : 函数的嵌套7 \" }& V3 R, P7 h* y
6 ?$ t! @: T, P: @: m, |+ u* K. W
5.计算员工的年终奖金, W4 ^# ^. o: J6 R
bonus不是null,发bonus的数字。 ' i# N3 b+ D' A/ s, T2 r/ Abonus是null,发salary * 0.5.1 e3 L% u) T7 G6 N- g
salary还是null, 100.% F- D% R5 y& u! m5 c; P
! Q c* G- J% j$ p0 V8 [. w, x
public double calculate(double bonus, # n# ?, ~" x; U# b' ]$ r% | double salary, 1 w7 ]) Y1 ^/ ?9 d8 ~7 y double comm){) r' _+ w, n" k- Z0 h' S, }) j; N
if (bonus != null) return bonus;# \( h! d; {: e: Z
if (salary != null) return salary * 0.5; 2 ` T( D3 W9 c: F" g6 o: h% `+ d return comm; 9 H! |9 |/ E) w$ \6 T3 J; N
}6 P* X1 E) g& v1 |' w( [5 h
--返回参数列表中第一个非空数据 , `7 x: b! B z--最后一个参数通常是常量。 0 v6 k7 W$ E7 v: w+ ]* xselect ename, bonus, salary, ) L, ]& u3 {( ]8 Q. e9 Hcoalesce(bonus, salary*0.5, 100) bonus ' }( p2 u& b% P& Q1 `+ x: Afrom emp_ning;- h7 h8 S# D, @( S
% P0 l/ p L @- Q4 o+ i/ G" G6.根据员工的职位,计算加薪后的薪水数据:' ~6 v* F, V) E3 W7 B" c
Analyst: 10% / x- [9 n6 s: FProgrammer: 5%. b; s* N) Z& ?
clerk: 2% 1 }- F9 i. S. q$ U# n* e其他职位:不变。. t( f, |" u# V2 U
, b% f& Q" X) z# [0 ~) D+ Zcase语句: / B; ~6 K% y, V$ lselect ename, salary, job, 0 f: ]5 E! |: h, ~) U" Qcase job when 'Analyst' then salary * 1.12 j& o+ }& I/ \. ]* F
when 'Programmer' then salary * 1.05 : T4 W9 O! ?0 w/ o9 z7 @ when 'clerk' then salary * 1.02 ( Q+ v3 z! Z- n6 P) p0 gelse salary . h# C( g* C& n }5 V3 J4 T% Kend new_salary 2 I; |' i+ K$ b& x( O( \- n0 zfrom emp_ning;. Z$ R6 L. Q) H) j( c
# i! c, q' @, Q4 d2 Dselect ename, salary, job, 4 W( W/ n9 h+ y" x: |2 G* P; mdecode(job, 'Analyst', salary * 1.1, 6 h# B+ p% q" K- D& w% R2 Z 'Programmer', salary * 1.05, 0 M1 h* @4 _# s3 \$ X9 _! v 'clerk', salary * 1.02, u2 B2 y3 [: \
salary) new_salary ' D E7 k/ w& U% Z$ r8 Ifrom emp_ning; - f6 N: y% p; K5 X$ w' X3 O $ P2 {3 o- {: H5 B8 ~1 S, _1 j+ X7.薪水由低到高排序 ( X3 w5 i( y1 \1 l) Hselect ename, salary from emp_ning1 Z9 C+ G' Z Z: ~0 [: h6 n
order by salary; 6 ?% b3 e" J1 g, Y/ Y' p3 o5 ]4 {" ^
select ename, salary from emp_ning6 ` V! Q) j. i J4 h
order by salary desc; --倒序排列 descend & t- M* r8 C, X* M* u6 P A. r( ~; @3 k' U& p; `4 Odesc emp_ning; --查看表结构 describe6 ?, x0 I0 m0 J# E; W2 F
1 O+ a& u/ I( _8.按入职时间排序,入职时间越早排在前面。 & a1 }7 Y7 I( oselect ename, hiredate( Y. g% N, l* ^/ I" U5 G3 w
from emp_ning : { x% O o" k. gorder by hiredate;6 J, v7 p7 W, C5 ~% @$ R8 _5 H
( @7 K& m+ s& A0 ?9 e8 P9.按部门排序,同一部门按薪水由高到低排序 8 h# h/ j! W9 H3 Gselect ename, deptno, salary 9 y+ J5 y: c" `from emp_ning& c+ ?( v+ D! F# o" r8 w
order by deptno, salary desc;7 d2 W' r& d. q+ `& E1 }
, T, f2 M+ Z8 ^; Y' x; e L
10.员工表中有多少条记录?% O" ?, k' M) {, ?: `5 G3 C3 ]/ v
select count(*) from emp_ning; ! m# r" ?2 w& m% u5 Q4 p0 k8 i$ x; m7 E$ n Y1 Q2 r
openlab帐户下有多少个表?; p" _1 V6 V( l
select count(*) from user_tables; , y3 w$ X6 I5 A: a1 p1 F . I1 h/ S: d1 \openlab帐户下有多少个名字中包含emp的表? $ Y6 x i5 e+ l" j6 E1 z% _select count(*) from user_tables * u8 t0 w; Y/ [* Vwhere table_name like '%emp%';1 a2 e' g8 Z, P! z. m
' a5 K @+ A( t
--表名默认大写 : Y' J9 W6 U8 uselect count(*) from user_tables * d! L: k4 s. Z" J, Gwhere table_name like '%EMP%';; k8 p. I5 B5 J( Y) M. Z5 }: ^2 k
, Q% S% k# C% L1 ~9 n+ ?
--入职时间不是null的数据总数。 1 w$ t9 }& W+ c. S3 {--count函数忽略空值。 : ]( ]) F0 I M% z$ C: h' g1 s! rselect count(hiredate) from emp_ning;# p5 O% k$ V- h: D& ?9 O( }& v8 @! B
7 l( c2 w' ^4 g4 O
11.计算员工的平均薪水和薪水总和是多少? + Z0 j% e, x+ qselect avg(salary) avg_sal, H: z, ^8 G: L$ N- w
sum(salary) sum_sal/ o2 m% ~( m: H6 n+ D: X
from emp_ning; 0 S# x- x; I+ w5 ?; k3 I1 ]# e# l- i' |5 w
avg(salary) = sum(salary) / count(salary) 4 B/ S2 {' Q: D. f# ^( T& F 68500 / 10 = 6850 1 ^. D' x/ B" B--纠正逻辑上的错误 / i! ]3 I/ d" Y$ T* y5 n- Dselect sum(salary)/count(*) avg_sal, ' f/ t6 Y$ V3 N0 f0 i& z4 K sum(salary) sum_sal 8 {% q. q4 h1 N) sfrom emp_ning; / H8 D6 F3 W& r* B. c( a% T 9 F1 R3 o* T# Z6 h0 @$ |# _/ @2 ?select avg(nvl(salary,0)) avg_sal, : k9 f3 i. o. Y sum(salary) sum_sal4 A8 S4 E7 Q1 e0 ~% _: _) z
from emp_ning;7 R: f! ~6 c& f, ]+ k. q
) ]. [0 x4 F6 Z3 u z% N
12.计算员工的最高薪水和最低薪水 # C/ o4 I: s0 C: j" Z. z7 oselect max(salary) max_sal, 1 K' w: R; }/ h( k; o6 [ min(salary) min_sal 6 n* _' z t; |6 m4 \from emp_ning;, z6 Q! V3 a4 S
# X4 A! h$ E, V2 @$ `
组函数:count / avg / sum / max / min 忽略空值' I# ?9 P/ y0 [6 x. s6 k- V' t
其中:avg / sum 针对数字的操作。 3 ?+ q( S& s4 P( ]) \ K7 U4 s max / min 对所有数据类型都可以操作。6 l: j/ Z- }7 Z' _
+ d6 g; ?$ f3 D5 X/ @ c$ N o
13.计算最早和最晚的员工入职时间。 . W, t( t. h* P6 gselect max(hiredate) max_hiredate, , G2 F" J9 P2 ?* Y: B4 { min(hiredate) min_hiredate1 M0 b) m( {1 w7 S% u
from emp_ning; 3 {! O2 J, u5 i4 N 7 Z+ }: n0 N2 \4 U; k0 X14.按部门计算每个部门的最高和最低薪水分别是多少?1 D: [# e$ f) {
格式如下: # E# Z4 f$ q0 b! g2 Z10 4500 3000$ N2 C3 z& u! {! }) P! K9 y
20 15000 8000 3 E/ z4 s$ q; w4 c2 Z% }, c30 10000 5000 & `. M6 E5 t5 \' m5 _ 5 T6 Q8 l+ o/ R! w4 D$ P4 y2 H--group by 列名 : 表示按哪个列分组 2 ~1 K6 v: d* |. V N1 a+ wselect deptno, max(salary) max_s, min(salary) min_s , A I4 _, K( ]9 Wfrom emp_ning* ~" @* C. x3 M4 X0 o. V
group by deptno;4 |/ E" d. D3 i, p5 D, H6 o
: H4 S2 Y/ J# [7 H15.计算每个部门的薪水总和和平均薪水? . f$ y. K' _; D( ^, e: n* Vselect deptno, sum(salary) sum_s,1 d. o3 {# Q$ U$ Z$ ?; g5 B
avg(nvl(salary,0)) avg_s# T1 H8 e, ^" g Y' u0 W
from emp_ning2 b Q6 @4 P3 K" n, \
group by deptno;% V: d% {, p% ^ I+ \
2 P6 `- g% L# ~) k K$ f16.每个部门的统计信息:格式如下: B! l0 g" g: V! M* Q3 h8 \& e
deptno max_s min_s sum_s avg_s emp_num . ^: g7 e# ?9 X, e( U) G" K10 10000 5000 23000 6789 3 ' M$ f2 k! i/ W& v2 g( v.... 7 I& }4 a8 P/ d& m. e2 g8 R' hselect deptno, max(salary) max_s, * Z) b+ {5 l7 O* M# b; I3 m& s min(salary) min_s, P! V; h% P& D sum(salary) sum_s,7 J. n) g0 ~+ J
avg(nvl(salary,0)) avg_s,( L2 D! K' o! \9 {& A7 H
count(*) emp_num& s- T6 b& G& k. z: P, h
from emp_ning9 L/ I; c/ v# h/ `0 {- ?( [
group by deptno; ' c3 h5 }) ^4 p 9 S* O- F8 B/ s3 E" }select后出现的列,凡是没有被组函数处理的列,必须' B% }0 ?3 n' ~1 K+ q- C
出现在group by 短语中。 . ]. `. m! o* h# J0 d9 i, `; n' ?$ t- K3 f. O
按职位分组,每个职位的最高、最低薪水和人数? 9 M f+ r4 C8 N$ ?select job, max(salary) max_s, / _/ B; a& U0 C* ` min(salary) min_s, " R# x2 [7 z, r- P count(*) emp_num ; O0 @8 \ h0 a, Y8 Y7 o& Lfrom emp_ning & J! {0 v. I z0 y' s+ hgroup by job - H7 p2 J) b" I' M, r+ ]+ forder by emp_num; 5 E Q, z$ Y2 f+ |% D) x9 l17.平均薪水大于5000元的部门数据? 9 f$ U& I B( F6 ?: F0 Gselect deptno, avg(nvl(salary,0)) avg_s $ c8 o$ L. P8 E6 W; z$ Q/ Afrom emp_ning6 D1 |, j: k+ p6 u/ M+ P0 N- A3 I
where deptno is not null6 X) o7 m: r3 ]: X8 F
group by deptno1 p$ p" o3 z6 D/ P4 e6 m
having avg(nvl(salary,0)) > 5000; 3 ^6 X" g4 B6 }# W; u6 k$ J 1 c2 X+ D! Y5 u18.薪水总和大于20000元的部门数据?: y" {3 U" p% P% q! o
select deptno, sum(salary) sum_s ; c+ i9 z* Y% _/ x$ Dfrom emp_ning/ E1 D2 \- X d8 B; f1 p
where deptno is not null+ k1 `, n' D+ d: F2 f( }5 x" o! V
group by deptno ( k# C1 g' h* R* F0 L. n" H& T) C/ ]having sum(salary) > 20000;: w6 |2 G& ]& q4 o! z% ~$ b2 U$ x
1 F9 c7 _+ c ]% a* t* z19.哪些职位的人数超过2个人? 5 ?- R0 H* @; [7 mselect job, count(*) emp_num $ Y. M; }' C% V/ Mfrom emp_ning# S b$ ^. b7 f) G; F
where job is not null) A# f! w* a2 X* M" b/ G" P i
group by job8 D2 H0 V& l8 m% a1 G
having count(*) > 2 : G/ R, P" @6 w. h) h' \, Oorder by emp_num; ( l+ ~- {+ ]7 P+ G9 I3 J8 Y) r; F8 d' b5 F0 l; x3 u8 A' ^ d4 D% j! S$ P