1.
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?
2.
Which kind of non-sourced UDF can be created so that it only returns a single value?
3.
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?
4.
Which of the following will begin a new unit of work?
5.
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?
6.
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?
7.
CREATE TABLE customer (cid BIGINT NOT NULL PRIMARY KEY, info XML) How many names will be returned for this XQuery?
8.
In which of the following situations would DB2 retain resources associated with a transaction at COMMIT time?
9.
When defining a referential constraint between the parent table T2 and the dependent table T1, which of the following is true?
10.
Given the following requirements:
Create a table to contain employee data, with a unique numeric identifier automatically assigned when a row is added, has an EDLEVEL column that permits only the values 'C', 'H' and 'N', and permits inserts only when a corresponding value for the employee's department exists in the DEPARTMENT table. Which of the following CREATE statements will successfully create this table?