Application A is designed to execute the following SQL statements within a single Unit of Work
(UOW).
UPDATE employee SET salary = salary * 1.1 WHERE empno=’000010′
UPDATE department SET deptname = ‘NEW dept’ WHERE deptno=’A00′
Application B is designed to execute the following SQL statements within a single Unit of Work
(UOW).
UPDATE department SET deptname = ‘OLD DEPT’ WHERE deptno=’A00′
UPDATE employee SET salary = salary * 0.5 WHERE empno=’000010′
Application A and application B execute their first SQL statement at the same time. When
application A and application B try to execute their second SQL statement, a deadlock occurs.
What will happen?

Application A holds an Update lock on a single row and application B is trying to read that row. If
both applications are using isolation level UR, which of the following will occur?

To which of the following resources can a lock be applied?

Given the following stored procedure:
CREATE PROCEDURE increase_salary ( IN p_workdept CHAR(6),
OUT p_sum DECIMAL(9,2) )
SET p_sum = (SELECT SUM(salary) FROM employee WHERE workdept=p_workdept);
How can this stored procedure be called from the Command Line Processor?

Which kind of non-sourced UDF can be created so that it only returns a single value?

Given that tables T1 and T2 contain the following rows:
Table T1:
C1 C2
— —
1 4
1 3
1 2
Table T2:
C1 C2
— —
1 1
1 2
1 3
Which of the following queries will return only those rows that exist in both T1 and T2?

Which of the following will begin a new unit of work?

The EMPLOYEE table contains the following information:
EMPNO NAME WORKDEPT
——— —— —————
101 SAM A11
102 JOHN C12
103 JANE –
104 PAT Remote
105 ANNE –
106 BOB A11
The MANAGER table contains the following information:
MGRID NAME DEPTNO EMPCOUNT
——— ——– ———– —————
1 WU B01 –
2 JONES A11 –
3 CHEN – –
4 SMITH – –
5 THOMAS C12 –
After this statement is executed:
UPDATE manager m SET empcount = (SELECT COUNT(workdept) FROM employee e WHERE
workdept=m.deptno)
What is the result of the following query?
SELECT mgrid, empcount FROM MANAGER WHERE empcount IS NOT NULL ORDER BY mgrid

Which of the following SQL statements will return the year and average salary for all employees
hired within a given year that have a salary greater than $30,000?

Given the following table definition:
STOCK:
item VARCHAR(30)
status CHAR(1)
quantity INT
price DEC(7,2)
If items are indicated to be out of stock by setting STATUS to NULL and QUANTITY and PRICE to
zero, which of the following statements would be used to update the STOCK table to indicate that
all the items whose description begins with the letter “S” are out of stock?