Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query Help (Oracle 11g)
SQL Query Help [message #644762] Tue, 17 November 2015 00:03 Go to next message
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.
Re: SQL Query Help [message #644763 is a reply to message #644762] Tue, 17 November 2015 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
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 )
Re: SQL Query Help [message #644770 is a reply to message #644763] Tue, 17 November 2015 03:45 Go to previous messageGo to next message
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.
Re: SQL Query Help [message #644772 is a reply to message #644770] Tue, 17 November 2015 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
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?

Re: SQL Query Help [message #644775 is a reply to message #644772] Tue, 17 November 2015 04:08 Go to previous messageGo to next message
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.
Re: SQL Query Help [message #644777 is a reply to message #644775] Tue, 17 November 2015 04:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your first parameter in RPAD is wrong.

Note that the first line is also wrong. Smile

Re: SQL Query Help [message #645122 is a reply to message #644777] Fri, 27 November 2015 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
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 ***

Re: SQL Query Help [message #645141 is a reply to message #645122] Fri, 27 November 2015 06:03 Go to previous message
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 Thumbs Up
Previous Topic: SQL Query Help
Next Topic: SQL Query Suggestion
Goto Forum:
  


Current Time: Tue Mar 19 04:36:09 CDT 2024