Update and function

Question

I am a student, this is part of my homework.

I have to Update two employee's pay by 10%...is there a function for that or do I have to calculate the figures and just change the numbers i.e.

update Employee set Wage=10 where Wage=51000

This is the entire question: choose an EEO-1 Classification: Increase all employees’ salaries that have: the selected EEO-1 classification by 10%.

The EEO classification is in a Job Title table and the Salaries are in the Employee Table. I need to join them somehow also. It needs to be a single statement...

Thanks

Answer 1

The secret with updates is to make sure you can do the select first.

So focus on how you can get all the fields you want to select.

Your key is going to be an Inner Join (no doubt someone will give a full answer but I'm against interrupting the learning process).

Next, you need to work out how to calculate the wage.

The fortunate thing is you can do this in your select first to make sure you're on the right track.

Something like:

SELECT *, (wage*1.1) as newWage FROM Employee 
 *INNER JOIN STUFF* 
 WHERE *CONDITION STUFF (EEO-1 etc.)*

Once you are happy that the select looks good you can turn it into an UPDATE and the cool thing is you can actually update a joined query and just grab your select inner join and where stuff.

UPDATE Employee
SET Salary.Wage = (wage*1.1)
FROM Employee
* INNER JOIN STUFF*
WHERE *CONDITION STUFF*

Answer 2

Something like:

declare @classification varchar(100)
set @classification = '<your value>'

update Employee set Wage = Wage *1.10 
join JobTitle on Employee.JobTitleId = JobTitle.JobTitleId
where JobTitle.[EEO-1 Classification] = @classification

Answer 3

Since it is homework, I'll give you some hints:

You can update the Wage by making it equal to itself + 10% or multiply by 110%

The second table can be addressed in the where clause; hint: there needs to be a field in each table that are equivalent.

The EEo-1 classification can be set to equal a parameter.

Answer 4

What you need is an UPDATE statement which uses an INNER JOIN.

UPDATE Employee
SET Wage = e.Wage * 1.10
FROM Employee as e
INNER JOIN JobTitle as jt
  ON jt.[id] = e.[jobId]
Where jt.Title = 'EEO-1'

I've had to make some assumptions about your schema here, I'm assuming there is some link between Employee and Job Title tables. I'm also assuming the Job Title is a string and not an integer. Hopefully this gives you the rough outline of the syntax you need.

Answer 5

As this is homework you need to know the smart alec answer. The answers posted using JOIN syntax are wrong because it is proprietary code. The correct smart alec answer is to use Standard SQL (preferably one supported by your dialect of choice e.g. SQL Server). You should also point out that ISO/IEC 11179 Standard for data element names requires a plural names for set that may have more than one member (e.g. JobTitles rather than JobTitle) and to look for established collection names already used in the enterprise (e.g. Personnel rather than Employees). Furthermore, if the job title attribute is modelled using a separate table then salaries should have their own table too (and named Payroll rather than Salaries):

UPDATE Payroll 
   SET Wage = Wage * 1.1 
 WHERE EXISTS (
               SELECT * 
                 FROM Employees AS E1
                      INNER JOIN JobTitles AS J1
                         ON E1.employee_number = J1.employee_number
                            AND J1.EEO_classification = 'EEO-1'
                WHERE E1.employee_number = Payroll.employee_number
              );

Then again, the enterprise would need to past wages as well as current wages (e.g. to be able to file tax returns), therefore Payroll is likely to be a valid-state temporal table and you would need to find the most recent state for each employee... You know, the more I think about this the more I'm convinced this enterprise doesn't exists at all. My advice is to assume it is a trick question and refuse to answer it until they've at least fixed the schema... perhaps ask about an internship during the summer so you could fix it yourself...