sample select SQL query

Question

0.00/5 (No votes)

See more:

Hii

i have table [ employee ]
it has 3 columns
==========================
emp_id int pk
emp_name nvarchar(50)
salary int
==========================
i need a query for this :

i need to retrieve name of employee and in which level he belongs to

ex :
1000 to 2000 so it is level 1
2100 to 3000 so it is level 2
3100 to 4000 so it is level 3
==========================

so the result will be as below

1 Jack level2
2 chris level3
3 jonny level1

how can i do that ?

Posted 1-Apr-14 12:39pm

Hercal

Add a Solution

3 solutions

Solution 2

SQL

DECLARE i INT;

SET i=1000;

SELECT EMP_ID,
       EMP_NAME,
       ( SALARY / I ) + 1 AS level
FROM   EMPLOYEE

Posted 1-Apr-14 15:18pm

Guruprasad.K.Basavaraju

Comments

Hercal 2-Apr-14 5:54am

thanksss :)

CHill60 2-Apr-14 8:39am

UpVoted 4 - quite a neat solution but would not give the results the OP quoted ... even if we both think that was an error in itself ;-)

Guruprasad.K.Basavaraju 2-Apr-14 9:06am

Guess it was a typo :)

Solution 3

Try this:

SQL

SELECT emp_id, emp_name,
CASE when salary < 2100 THEN 'level 1'
WHEN salary  < 3100 THEN 'level 2'
ELSE 'level 3' END
FROM table1

Posted 1-Apr-14 15:50pm

Peter Leow

Comments

Hercal 2-Apr-14 5:54am

thanks :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2014-04-01T13:13:00

You need to know about the CASE statement in SQL - here is an article on that subject A Simple Use of SQL CASE Expression[^]
You also need to know about the BETWEEN clause - here is the reference[^]
Based on your expected results you will end up with something like

SQL

Select emp_id, emp_name,
CASE when salary BETWEEN 1000 AND 2000 THEN 1
WHEN salary BETWEEN 2100 AND 3000 THEN 2
WHEN salary BETWEEN 3100 AND 4000 THEN 3
ELSE 0 END AS level

Note that I've introduced a default situation of level=0

Personally I think you might have meant your ranges to have been 1000-2000, 2001-3000 and 3001-4000 - unless you deliberately meant to leave a 100 unit gap between them