Ensurepass
2017 May IBM Official New Released 000-730 Q&As
100% Free Download! 100% Pass Guaranteed!
http://www.ensurepass.com/000-730.html

DB2 9 Family Fundamentals

QUESTION 121

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?

 

A.

SELECT TABLE(EMPLIST()) FROM EMPLOYEE

B.

SELECT TABLE(EMPLIST()) AS t FROM EMPLOYEE

C.

SELECT EMPLIST(id, firstname, lastname) FROM EMPLOYEE

D.

SELECT id, firstname, lastname FROM TABLE(EMPLIST()) AS t

 

Correct Answer: D

 

 

QUESTION 122

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?

 

A.

SORT BY age ASC, last_name

B.

SORT BY age DESC, last_name

C.

ORDER BY age DESC, last_name

D.

ORDER BY age ASC, last_name

 

Correct Answer: C

 

 

QUESTION 123

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

 

A.

DELETE * FROM TABLE T03

B.

DELETE ALL FROM T03

C.

DELETE * FROM T03

D.

DELETE FROM T03

 

Correct Answer: D

 

 

QUESTION 124

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?

 

A.

SELECT e.id, d.deptname

FROM employee e, department d

WHERE e.id > 4

B.

SELECT e.id, d.deptname

FROM employee e

INNER JOIN

department d

ON e.deptid = d.id

WHERE e.id > 4

C.

SELECT e.id, d.deptname

FROM employee e

< font style="font-size: 10pt" color="#000000">FULL OUTER JOIN

department d

ON e.id = d.id

WHERE e.id > 4

D.

SELECT e.id, d.deptname

FROM employee e

LEFT OUTER JOIN

department d

ON e.deptid = d.id

WHERE e.id > 4

UNION ALL

SELECT e.id, d.deptname

FROM employee e

RIGHT OUTER JOIN

department d

ON e.deptid = d.id

WHERE e.id > 4

 

Correct Answer: B

 

 

QUESTION 125

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

 

A.

It applies to a single data server.

B.

It is a recoverable sequence of operations.

C.

Its value can be queried from the system catalog tables.

D.

It begins when the application connects to the data server.

 

Correct Answer: B

QUESTION 126

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

 

A.

0

B.

1

C.

3

D.

6

 

Correct Answer: D

 

 

QUESTION 127

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

&n
bsp;

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

 

A.

3,SILVER

4,GOLD

1,SILVER

1,GOLD

5,PLATINUM

3,SILVER

1,GOLD

B.

3,SILVER

4,SILVER

1,GOLD

1,PLATINUM

5,ERROR

3,GOLD

1,PLATINUM

C.

3,SILVER

4,SILVER

1,PLATINUM

1,ERROR

5,SILVER

3,GOLD

1,PLATINUM

D.

3,SILVER

4,SILVER

1,GOLD

1,GOLD

5,SILVER

3,ERROR

1,PLATINUM

 

Correct Answer: B

 

 

QUESTION 128

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

 

A.

A table referenced in the FROM clause has no indexed column.

B.

The table referenced in the FROM clause has more than 200 columns.

C.

Two or more tables in the FROM clause have identical column names.

D.

The FROM clause contains two or more tables in the SELECT statement.

 

Correct Answer: C

QUESTION 129

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

 

A.

SELECT UNIQUE dept FROM staff

B.

SELECT DISTINCT dept FROM staff

C.

SELECT (dept) UNIQUE FROM staff

D.

SELECT (dept) DISTINCT FROM staff

 

Correct Answer: B

 

 

QUESTION 130

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

 

A.

2

B.

7

C.

9

D.

14

 

Correct Answer: D

100% Free Download!
—Download Free Demo:000-730 Demo PDF
100% Pass Guaranteed!
Download 2017 Ensurepass 000-730 Full Exam PDF and VCE Q&As:303
—Get 10% off your purchase! Copy it:TJDN-947R-9CCD [2017.05.01-2017.05.31]

Ensurepass ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF + VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

2017 Ensurepass IT Certification PDF and VCE