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?

Which of the following statements eliminates all but one of each set of duplicate rows in the DEPT
column in the STAFF table?

Given the following tables:
CONTINENTS
ID NAME COUNTRIES
1 Antarctica 0
2 Africa 53
3 Asia 47
4 Australia 14
5 Europe 43

6 North America 23
7 South America 12
REGION
ID LOCATION
1 East
2 West
How many rows would be returned using the following statement?
SELECT location FROM continents, region