A number of applications issue the following SQL statement:
SELECT d.deptno, e.empno, e.salary FROM department d INNER JOIN employee e ON d.deptno
= e.deptno
A database administrator wishes to store this query within the database. Which of the following
database objects can be used to accomplish this?

Given the following statements:
CREATE TABLE tab1 (col1 INT);
CREATE TABLE tab2 (col1 INT);
CREATE TRIGGER trig1 AFTER UPDATE ON tab1
REFERENCING NEW AS new1
FOR EACH ROW MODE DB2SQL
INSERT INTO tab2 VALUES(new1.col1);

INSERT INTO tab1 VALUES(2),(3);
What is the result of the following query?
SELECT count(*) FROM tab2;

Given the following function:
CREATE FUNCTION emplist ()
RETURNS TABLE ( id CHAR(6)
, firstname VARCHAR(12)
, lastname VARCHAR(15) )
LANGUAGE SQL
BEGIN ATOMIC
RETURN
SELECT EMPNO, FIRSTNME, LASTNAME
FROM EMPLOYEE
WHERE WORKDEPT IN (‘A00’, ‘B00’);
END
How can this function be used in an SQL statement?

Given the following query:
SELECT last_name, first_name, age, hire_date

FROM employee
WHERE age > 40
Which of the following clauses must be added to return the rows sorted by AGE, oldest first, and
by LAST_NAME, from A to Z?

Which of the following will DELETE all of the rows from table T03?

Given the following table definitions:
EMPLOYEE
ID NAME DEPTID
— —— ———
01 Smith 10
02 Bossy 20
03 Peterson 20
04 Goss 30
05 Pape 40
06 Avery 50
07 O’Neal 60
08 Carter 50
DEPARTMENT
ID DEPTNAME
— ————–
05 Hardware

10 Kitchen
20 Shoes
30 Toys
40 Electronics
50 Automotive
and the following query:
SELECT e.id, d.deptname
FROM employee e, department d
WHERE e.deptid = d.id AND e.id > 4
Which of the following queries will produce the same result set as the query above?

Which of the following is a feature of a unit of work?

Consider the following table called EMPLOYEES:
ID FIRSTNAME LASTNAME JOB LEVEL
————————————————————
1 Paul Jones DBA 2
2 George Baker MGR 1
3 Roger Melvin CLERK 3
4 Jim Smith MGR 1
5 Kevin Street CLERK 3
6 Chris Eaton MGR 1
If the following SQL statement is executed, how many rows will be deleted? DELETE FROM
employees WHERE 1 = 1

Given the following query:
SELECT quantity,

CASE WHEN itemcode = ‘099’ THEN ‘SILVER’
WHEN itemcode = ‘788’ THEN ‘GOLD’
WHEN itemcode = ‘899’ THEN ‘PLATINUM’
ELSE ‘ERROR’
END
FROM supplier
What will be the result of the query if the following data is evaluated by the CASE expression?
SUPPLIER
——————————————
QUANTITY ITEMCODE
3 099
4 099
1 788
1 899
5 009
3 788
1 899

In which of the following situations should correlation names be used?