CORRECT TEXT
You are employed as a SQL Server 2012 database developer at ABC.com. ABC.com has a
database named HRDB with tables named Staff and SalaryHistory. The Staff and SalaryHistory
tables were created using the following Transact-SQL code:
CREATE TABLE HRDB.Staff
(
StaffID int NOT NULL PRIMARY KEY,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
JobTitle nvarchar(50) NOT NULL,
StaffAddress nvarchar (200) NULL,
StaffCity nvarchar(50) NOT NULL,
StaffState nvarchar(50) NOT NULL,
StaffPhone varchar (10) NOT NULL,
Salary smallmoney NOT NULL
)
GO
CREATE TABLE HRDB.SalaryHistory
(
StaffID int NOT NULL,
IncreaseDate date DEFAULT GETDATE(),
PrevSalary smallmoney NOT NULL,
NewSalary smallmoney NOT NULL
)
GO
You must write a Transact-SQL query to affect a salary increase of 6.5% for all staff members with
a JobTitle of Support Technician. Your query must also update the data in the SalaryHistory table.
What Transact SQL statement would accomplish this task?
To answer, type the correct code in the answer area.
Answer: See the explanation.
Explanation:
UPDATE Staff
SET Salary = Salary * 1.065
WHERE JobTitle = 'Support Technician'
OUTPUT inserted.StaffID, deleted.Salary, inserted.Salary
INTO SalaryHistory(StaffID, PrevSalary, NewSalary)
1. Shouldn’t WHERE statement be the last one?
2. The task was to update the data in the SalaryHistory table, anyway OUTPUT INTO will insert new rows for each increased salary, instead of updating the old ones.
0
0
WHERE Clause Should be last statement
0
0
Correct is:
UPDATE Staff
SET Salary = Salary * 1.065
OUTPUT inserted.StaffID, deleted.Salary, inserted.Salary
INTO SalaryHistory(StaffID, PrevSalary, NewSalary)
WHERE JobTitle = ‘Support Technician’
I tested
1
0
Mirda is Correct
0
0
Mirda is right, and tested.
thanks Mirda
0
0
How about ‘IncreaseDate’?
0
0
What about “IncreaseDate”? Will that be updated automatically?
0
0
Yes Ian increase date will take current date automatically as it is provided default date to be today’s date.
0
0
i agree with engda date will take the current date automatically since “DEFAULT GETDATE()”
0
0
Besides, part of that new 200Q 70-461 dumps are available here:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
0
0