Need help with a query

Question

Post reply

Need help with a query

SQL_Padwaan

Old Hand

Points: 335
More actions
April 23, 2010 at 1:39 pm

#234146

Hi all,
I am stuck on a simple query but not sure what is the best way to create it.
I need to run a query on one table where:
Salary between £20000 and £40000 ADD £5000 increase
Salary between £40000 and £55000 ADD £7000 increase
Salary between £55000 and £65000 ADD £9000 increase
So far I have come out with this:
update Tutti.dbo.TBL_EMP
set salary sum(5000)
where salary between 20000 AND 40000
set salary sum(7000)
where salary between 40000 AND 55000
set salary sum(9000)
where salary between 55000 AND 65000
go
I know there is a better way, could anyone help please?
Cheers

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Cory E. SSCertifiable Points: 7076 More actions · Answer 1

CREATE TABLE #temp

(id int, sal int)

INSERT INTO #temp VALUES (1,20000)

INSERT INTO #temp VALUES (2,40000)

INSERT INTO #temp VALUES (3,60000)

INSERT INTO #temp VALUES (4,70000)

INSERT INTO #temp VALUES (5,80000)

INSERT INTO #temp VALUES (6,90000)

UPDATE #temp

SET sal = sal + CASE WHEN sal BETWEEN 20000 and 40000 THEN 5000

WHEN sal BETWEEN 40000 and 55000 THEN 7000

WHEN sal BETWEEN 55000 and 65000 THEN 9000

END

SELECT*

FROM#temp AS T

Good luck on your homework assignment.

-- Cory

John Rowan SSC Guru Points: 56440 More actions · Answer 2

UPDATE Tutti.dbo.TBL_EMP

SETsalary =

CASE

WHEN Sarlary BETWEN 20000 AND 40000 THEN Salary + 5000

WHEN Sarlary BETWEN 40000 AND 55000 THEN Salary + 7000

WHEN Sarlary BETWEN 55000 AND 65000 THEN Salary + 9000

ELSE Salary

END

WHERE Salary BETWEEN 20000 AND 65000

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

SQL_Padwaan Old Hand Points: 335 More actions · Answer 3

Hi guys,

Thank you for the reply now I understand how to carry this out in the future.

You have both been very helpful

Thanks again

SQL_Padwaan Old Hand Points: 335 More actions · Answer 4

Hi Guys,

Just another question, I have to write a query which returns the names of each employee along with their manager’s name, or ‘No Manager’ if none is defined.

The managerid column is an int so I can't set the column I just need to display it saying there is 'no manager'

So far I have come out with something like this:

if (select name, managerid from Tutti.dbo.TBL_EMP) managerid IS NULL

BEGIN

PRINT 'No Manager'

END

--really not sure what to do

Many thanks in advance

Cory E. SSCertifiable Points: 7076 More actions · Answer 5

it may be time to read your text book. These are classic examples from class.

If you really want help, provide the table structure, some sample data, and expected output for the sample data.

In the meantime, you may want to look up "COALESCE()"

-- Cory

SQL_Padwaan Old Hand Points: 335 More actions · Answer 6

Thanks for the hint!

This worked:

SELECT name, COALESCE(NULL, NULL, 'No Manager')

from Tutti.dbo.TBL_EMP

Cheers

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 7

SQL_Padwaan (4/23/2010)
if (select name, managerid from Tutti.dbo.TBL_EMP) managerid IS NULL
BEGIN
PRINT 'No Manager'
END

:blink: my eyes! my eyes! sorry, couldn't help myself 😉

You have to try a little harder and come back with a question, a doubt ... so far it doesn't even qualify as pseudo-code.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.