Home » Other » Training & Certification » substitute employee salary by '*'
substitute employee salary by '*' [message #290527] Sat, 29 December 2007 08:34 Go to next message
emadnabil
Messages: 171
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               ****
Re: substitute employee salary by '*' [message #290532 is a reply to message #290527] Sat, 29 December 2007 09:21 Go to previous messageGo to next message
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
Re: substitute employee salary by '*' [message #290541 is a reply to message #290532] Sat, 29 December 2007 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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


Re: substitute employee salary by '*' [message #290591 is a reply to message #290532] Sun, 30 December 2007 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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
Re: substitute employee salary by '*' [message #290611 is a reply to message #290608] Sun, 30 December 2007 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: substitute employee salary by '*' [message #290615 is a reply to message #290591] Sun, 30 December 2007 11:50 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Not very helpful if you can only see 80 positions..
Previous Topic: Oracle structure and versions
Next Topic: cant 'start' oracle
Goto Forum:
  


Current Time: Thu Mar 28 23:39:27 CDT 2024