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

How should the following UDF be invoked in order to convert US currency values stored in the
EXPENSES table into Canadian currency?
CREATE FUNCTION getratews11 (
country1 VARCHAR(100),
country2 VARCHAR(100) )
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
EXTERNAL ACTION NOT DETERMINISTIC
BEGIN
…
END

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

Given the following two tables:
TAB1
C1 C2
__ __________
1 Antarctica
2 Africa

3 Asia
4 Australia
TAB2
CX CY
__ _____________
5 Europe
6 North America
7 South America
Which of the following SQL statements will insert all rows found in table TAB2 into table TAB1?

Given the following table and XML data stored in the CONTACTINFO column:
CREATE TABLE clients(
id INT PRIMARY KEY NOT NULL,
name VARCHAR(50),
status VARCHAR(10),
contactinfo XML)
<Client>
<Address>
<street>25 EastCreek</street>
<city>Toronto</city>
<prov-state>Ontario</prov-state>
<pcode-zip>M8X-3T6</pcode-zip>
</Address>
<phone>
<work>4165551358</work>
<home>9051112222</home>
<fax>9051112222</fax>
</phone>
<preferredcontact>by fax</preferredcontact>
</Client>

What is the result of the following XQuery expression?
for $y in db2-fn:xmlcolumn(‘CLIENTS.CONTACTINFO’)/Client//fax return $y

Given the following two tables:
NAMES
—————————–
NAME NUMBER
———- ——-
Wayne Gretzky 99
Jaromir Jagr 68
Bobby Orr 4
Bobby Hull 23
Mario Lemieux 66
POINTS
—————————–
NAME POINTS
———- ——
Wayne Gretzky 244
Bobby Orr 129
Brett Hull 121
Mario Lemieux 189
Joe Sakic 94
How many rows would be returned using the following statement?
SELECT name FROM names, points

57 Given table T1 has column I1 containing the following data:
I1
—
1234
If the following sequence of SQL statements is applied within a single unit of work:
UPDATE t1 SET i1 = 3 WHERE i1 = 2; S
AVEPOINT s1 ON ROLLBACK RETAIN CURSORS;
UPDATE t1 SET i1 = 5 WHERE i1 = 3;
SAVEPOINT s2 ON ROLLBACK RETAIN CURSORS;
INSERT INTO t1 (i1) VALUES (6);
ROLLBACK TO SAVEPOINT s1;
UPDATE t1 SET i1 = 2 WHERE i1 = 4;
COMMIT;
What is the expected sequence of values returned from?
SELECT i1 FROM t1 ORDER BY i1

Given the following CREATE TABLE statement:

CREATE TABLE EMPLOYEE
(EMPNO CHAR(3) NOT NULL,
FIRSTNAME CHAR(20) NOT NULL,
MIDINIT CHAR(1),
LASTNAME CHAR(20) NOT NULL,
SALARY DECIMAL(10, 2))
Which of the following will retrieve the rows that have a missing value in the MIDINIT column?

Given the following two tables:
TAB1
———————-
COL_1 COL_2
—– —–
A 10
B 12
C 14
TAB2
———————-
COL_A COL_B
—– —–
A 21
C 23
D 25
Assuming the following results are desired:
COL_1 COL_2 COL_A COL_B
A 10 A 21
B 12 – –
C 14 C 23

– – D 25
Which of the following joins will produce the desired results?