OraFAQ Forum: Training & Certification » substitute employee salary by '*'

Home » Other » Training & Certification » substitute employee salary by '*'

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

substitute employee salary by '*' [message #290527]

Sat, 29 December 2007 08:34

emadnabil
Messages: 179
Registered: August 2007

Senior Member

Hii all

I want to make a select statment of the employee table
that shows the employee first name and
'*' if the salary > 1000
'**' if the salary > 2000
'***' if the salary > 3000
etc...

so the result be like that

employee name    salary
emad               *
bebo               *
omda               **
gemy               ***
lolo               ***
memy               ***
lala               ****

Report message to a moderator

Re: substitute employee salary by '*' [message #290532 is a reply to message #290527]

Sat, 29 December 2007 09:21

SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada

Member

column sal_graph format a15

select
  ename,
  sal,
  lpad( '*', trunc( sal/1000 ), '*' ) sal_graph
from
  emp
;

ENAME             SAL SAL_GRAPH
---------- ---------- ---------------
SMITH             800
ALLEN            1600 *
WARD             1250 *
JONES            2975 **
MARTIN           1250 *
BLAKE            2850 **
CLARK            2450 **
SCOTT            3000 ***
KING             5000 *****
TURNER           1500 *
ADAMS            1100 *
JAMES             950
FORD             3000 ***
MILLER           1300 *

14 rows selected.

--
Joe Fuda
SQL Snippets

Report message to a moderator

Re: substitute employee salary by '*' [message #290541 is a reply to message #290532]

Sat, 29 December 2007 10:23

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks that way OP does not have to make his homework.
You can also pass his grade for him. Thumbs Down

Regards
Michel

Report message to a moderator

Re: substitute employee salary by '*' [message #290591 is a reply to message #290532]

Sun, 30 December 2007 03:47

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

[slighty off topic] Until puzzle forum arrives, here's a trick I often used to get a quick look of data.
If you want a graph of the ratio of each salary to the greatest one:

SQL> column sal_graph format a10
SQL> column ratio_to_max format a52 heading " Ratio to max salary (*=2% of max)"
SQL> select ename, sal, lpad( '*', trunc(sal/1000), '*') sal_graph,
  2         rpad(rpad('|',1+round(50*sal/max(sal) over ()),'*'),51)||'|' ratio_to_max
  3  from emp
  4  /
ENAME             SAL SAL_GRAPH   Ratio to max salary (*=2% of max)
---------- ---------- ---------- ----------------------------------------------------
SMITH             800            |********                                          |
ALLEN            1600 *          |****************                                  |
WARD             1250 *          |*************                                     |
JONES            2975 **         |******************************                    |
MARTIN           1250 *          |*************                                     |
BLAKE            2850 **         |*****************************                     |
CLARK            2450 **         |*************************                         |
SCOTT            3000 ***        |******************************                    |
KING             5000 *****      |**************************************************|
TURNER           1500 *          |***************                                   |
ADAMS            1100 *          |***********                                       |
JAMES             950            |**********                                        |
FORD             3000 ***        |******************************                    |
MILLER           1300 *          |*************                                     |

14 rows selected.

Regards
Michel

[Updated on: Sun, 30 December 2007 03:51]

Report message to a moderator

Re: substitute employee salary by '*' [message #290608 is a reply to message #290591]

Sun, 30 December 2007 10:14

SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada

Member

Geez, first you complain about doing the OP's homework, then you help them get bonus points. You crack me up sometimes Michel! Laughing

Report message to a moderator

Re: substitute employee salary by '*' [message #290611 is a reply to message #290608]

Sun, 30 December 2007 11:20

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It seems to me it is clear I was not talking to OP; anyway if he (think he) can shine with this. Rolling Eyes

Regards
Michel

Report message to a moderator

Re: substitute employee salary by '*' [message #290615 is a reply to message #290591]

Sun, 30 December 2007 11:50

Frank
Messages: 7901
Registered: March 2000

Senior Member

Not very helpful if you can only see 80 positions..

Report message to a moderator

Previous Topic:	Oracle structure and versions
Next Topic:	cant 'start' oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 13:55:24 CST 2024