Need some SQL help

[GT98]

I'm stuck on this:

I have an SQL table that I need to update for a class...I have to give all my employee's a 5% raise under the employee table. The thing is I don't know how to select multiple entries in a single column so I can do this...

Here is the table:

CREATE TABLE [Employee] (
	[Social_security_number] [numeric](18, 0) NOT NULL ,
	[Last_name] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[First_name] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Address] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[City] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[State] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Zip_code] [numeric](18, 0) NULL ,
	[Telephone_area_code] [numeric](18, 0) NULL ,
	[Telephone_number] [numeric](18, 0) NULL ,
	[Email_address] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Job_title_code] [numeric](18, 0) NULL ,
	[Hire_date] [datetime] NULL ,
	[Salary] [money] NULL ,

[Sasq]

not knowing the actual engine.*
update Employee set Salary=(Salary*1.05)

*syntax milage may vary

[GT98]

not knowing the actual engine.*
update Employee set Salary=(Salary*1.05)

*syntax milage may vary

MSSQL...LOL

I'll try it

[cjolley]

And if it's MSSQL you don't even have to do a COMMIT afterwards.

[Umfriend]

In DB2 the updates (and deletes) used to go faster if you included "Where 1=1", but that was 3270 and early 90's.

How to you begin transactions in MSSQL then that must be rollbackable? I'm gonna have to do a lot of MSSQL very soon so I am interested.

[cjolley]

You can set your instance to not auto commit.
It's just on by default. Oracle can be set to auto commit too, I've just never heard of anyone doing it.
A good friend of our's son is one of the lead programmers for MSSQL at Microsoft and I have teased him about that a couple of times.
Though, they really can't change it because so many apps are already in production that assume it's on.


So, just turn auto commit off and program for transactions.
Good luck with thier locking mechanism.

[GT98]

Ok got another question. I have to now calculate the max salary for exempt employees and non exempt employees in one statement and also do the minmum salary for both in another statement. The problem I'm having is that when I did the Job_Title Table, my Exempt_non_exempt_status is entered as Exempt or Non_exempt in the colum and I'm not sure how to write the statement to define which one it should look at to get the data in the above statements. Any pointers?

	1000	Cheif Financal Officer   	Exempt    	100000	250000
	1005	Network Administrator    	Exempt    	45000	100000
	1123	Window Cleaner           	Exempt    	18000	30000
	12355	Programmer               	Exempt    	35000	75000
	12400	Security guard           	Non-Exempt	30000	90000
	12455	Salesperson              	Non-Exempt	16500	157500

[cjolley]

You didn't say what the name of the exempt column was, but assuming it's "exempt_col" then:

select exempt_col, max(salary) max_sal
from Job_Title
group by exempt_col

and then:

select exempt_col, min(salary) min_sal
from Job_Title
group by exempt_col



or in one statement:

select exempt_col, max(salary) max_sal, min(salary) min_sal
from Job_Title
group by exempt_col

at least that is how it would be in most dbs. that is standard sql.

[cjolley]

Are you supposed to join the employee table to the job_title table and find the max and min actual salaries, or just the theoretical max and min straight from the job_titles table?

[Umfriend]

Good luck with thier locking mechanism.

Yeah well, it's not as if many users will be running on it. I assume they don't lock entire tables just like that, erhm, right??

[Marshmallowman]

They don't lock anything

[Sasq]

*mcdba for sql7.
but I try not to admit that in public, and am doing my best to forget

[Umfriend]

OK, hijack coming in:

I need to prototype some modelling/data mining on a DB. I have MSSQL (2005 or Express). I have VB 2005, and I hate it. I just don't get how to connect to a DB and run a select query and then simply crunch numbers.

I prefer VBA, quick and dirty, but again, how do I connect to a DB (at least I have done VBA in Excel before and it is ideal for my purposes). So how do I connect to a DB, run either a select query from a string in VBA or obtain a recordset after running a query saved in the DB (running it from out of VBA though).?

[Tjalfe]

I found such information for VB here:

http://www.xtremevbtalk.com/

[cjolley]

They don't lock anything

eh?

http://support.microsoft.com/default.aspx?scid=kb;en-us;323630&sd=tech