OraFAQ Forum: SQL & PL/SQL » SQL Query Help

Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query Help (Oracle 11g)

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

SQL Query Help [message #644762]

Tue, 17 November 2015 00:03

arifs3738
Messages: 39
Registered: November 2015
Location: India

Member

Question:List employee name, salary for all employees showing salary in bar chart form?

ENAME		SALARY		GRAPH
SCOTT		2000		****
JOHN		6000		************
Note: One * for 500.

SQL QUERY:

SELECT E.*, 
CASE WHEN SAL=500 THEN '*'
WHEN SAL=1000 THEN '**'
WHEN SAL=1500 THEN '***'
WHEN SAL=2000 THEN '****'
WHEN SAL=2500 THEN '*****'
WHEN SAL=3000 THEN '******'
WHEN SAL=3500 THEN '*******'
WHEN SAL=4000 THEN '********'
WHEN SAL=4500 THEN '*********'
WHEN SAL=5000 THEN '**********'
ELSE 'N/A'
END AS GRAPH
FROM EMP E
ORDER BY GRAPH;

The Query which i have written is too basic and not generic. Please can you help me in a query which would be generic or more better than me. I know my query is paethaetic Embarassed

moving on with sql so need guidance.

Report message to a moderator

Re: SQL Query Help [message #644763 is a reply to message #644762]

Tue, 17 November 2015 00:21

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

Senior Member
Account Moderator

Have a look at RPAD function, especially parameters 2 and 3.
And find better titles to your topics than "SQL Query Help" or "Need Help in SQL Query"; remember your topics has also the purpose to help others that search for the same question.

(Adding the link I see there is the solution in the SQL Reference page. Smile

)

Report message to a moderator

Re: SQL Query Help [message #644770 is a reply to message #644763]

Tue, 17 November 2015 03:45

arifs3738
Messages: 39
Registered: November 2015
Location: India

Member

SELECT EMPNO, ENAME, SAL, RPAD(' ', ROUND(SAL/500), '*') "Salary"
FROM EMP;

Above query is not giving correct data for each record. Please help.

Report message to a moderator

Re: SQL Query Help [message #644772 is a reply to message #644770]

Tue, 17 November 2015 03:47

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

Senior Member
Account Moderator

what does mean "is not giving correct data for each record"?
What does it give?
What do you want it gives? And why?

Report message to a moderator

Re: SQL Query Help [message #644775 is a reply to message #644772]

Tue, 17 November 2015 04:08

arifs3738
Messages: 39
Registered: November 2015
Location: India

Member

SELECT EMPNO, ENAME, SAL, RPAD(' ', ROUND(SAL/500), '*') "Salary"
FROM EMP

OUTPUT:

7369	SMITH	800	 *
7499	ALLEN	1600	 **
7521	WARD	1250	 **

For Second record 3 '*' should have been as output but only 2 '*' are coming.

Report message to a moderator

Re: SQL Query Help [message #644777 is a reply to message #644775]

Tue, 17 November 2015 04:15

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

Senior Member
Account Moderator

Your first parameter in RPAD is wrong.

Note that the first line is also wrong. Smile

Report message to a moderator

Re: SQL Query Help [message #645122 is a reply to message #644777]

Fri, 27 November 2015 00:45

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

Senior Member
Account Moderator

Correct query:

SQL> col Salary format a12
SQL> SELECT EMPNO, ENAME, SAL, RPAD('*', ROUND(SAL/500), '*') "Salary"
  2  FROM EMP
  3  /
     EMPNO ENAME             SAL Salary
---------- ---------- ---------- ------------
      7369 SMITH             800 **
      7499 ALLEN            1600 ***
      7521 WARD             1250 ***
      7566 JONES            2975 ******
      7654 MARTIN           1250 ***
      7698 BLAKE            2850 ******
      7782 CLARK            2450 *****
      7788 SCOTT            3000 ******
      7839 KING             5000 **********
      7844 TURNER           1500 ***
      7876 ADAMS            1100 **
      7900 JAMES             950 **
      7902 FORD             3000 ******
      7934 MILLER           1300 ***

Report message to a moderator

Re: SQL Query Help [message #645141 is a reply to message #645122]

Fri, 27 November 2015 06:03

arifs3738
Messages: 39
Registered: November 2015
Location: India

Member

I was so close in this small query.

But Champ: Michel Cadot is always champ. Love your efforts and appreciate your help.

Smile

Report message to a moderator

Previous Topic:	SQL Query Help
Next Topic:	SQL Query Suggestion

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 22 11:59:35 CDT 2026