Demonstrate your knowledge by constructing a SQL transaction that will update the job title for the employee, Mr. Stephen Yuan Jiang, to “North America Sales Director.”

Answer:

The SQL transaction that will update the job title for the employee, Mr. Stephen Yuan Jiang, to “North America Sales Director.” is as shown below:

BEGIN TRANSACTION;

UPDATE HumanResources.Employee
SET JobTitle = 'North America Sales Director'
WHERE BusinessEntityID=(SELECT BusinessEntityID FROM Person.Person
                                           WHERE Title='Mr' AND FirstName='Stephen'
                                            AND MiddleName='Yuan' AND LastName='Jiang');

COMMIT TRANSACTION;

Explanation:
•   The select query within the update statement will retrieve the BusinessEntityID of the employee with name Mr. Stephen Yuan Jiang.
•   The update statement will update the job title of the employee whose BusinessEntityID is retrieved by the select statement.