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 161

Given the following data:

 

TAB1

 

C1 C2

 

– —

 

200 abc

 

250 abc

 

150 def

 

300 ghi

 

175 def

 

If the following query is executed:

 

WITH subset (col1, col2) AS

 

(SELECT c1, c2 FROM tab1 WHERE
c1 > 150)

 

SELECT col2, SUM(col1) AS col1_sum

 

FROM subset

 

GROUP BY col2

 

ORDER BY col2

 

Which of the following result data sets will be produced?

 

A.

COL2 COL1_SUM

— ——–

abc 200

abc 250

def 175

ghi 300

4 record(s) selected.

B.

COL2 COL1_SUM

— ——–

abc 450

def 175

ghi 300

3 record(s) selected.

C.

COL2 COL1_SUM

— ——–

abc 450

def 325

ghi 300

3 record(s) selected.

D.

COL2 COL1_SUM

— ——–

abc 450

abc 450

def 175

def 175

ghi 300

5 record(s) selected.

 

Correct Answer: B

 

 

 

 

 

 

 

 

QUESTION 162

Given the following table definitions:

 

TABLE1

 

———————————-

 

ID INT

 

NAME CHAR(30)

 

PERSON INT

 

CITIES INT

 

TABLE2

 

———————————-

 

ID INT

 

LASTNAME CHAR(30)

 

Which of the following statements will remove all rows in table TABLE1 that have matching PERSONs in table TABLE2?

 

A.

DELETE FROM table1 WHERE id IN (SELECT id FROM table2)

B.

DELETE FROM table1 WHERE id IN (SELECT person FROM table2)

C.

DELETE FROM table1 WHERE person IN (SELECT id FROM table2)

D.

DELETE FROM table1 WHERE person IN (SELECT person FROM table2)

 

Correct Answer: C

 

 

QUESTION 163

Given the following two tables:

 

NAMES

 

NAME NUMBER

 

———- ————–

 

Wayne Gretzky 99

 

Jaromir Jagr 68

 

Bobby Orr 4

 

Bobby Hull 23

 

Brett Hull 16

 

Mario Lemieux 66

 < /span>

Mark Messier 11

 

POINTS

 

NAME POINTS

 

———- ————–

 

Wayne Gretzky 244

 

Jaromir Jagr 168

 

Bobby Orr 129

 

Brett Hull 121

 

Mario Lemieux 189

 

Joe Sakic 94

 

Which of the following statements will display the player name, number, and points for all players that have scored points?

 

A.

SELECT p.name, n.number, p.points FROM names n INNER JOIN points p ON n.name = p.name

B.

SELECT p.name, n.number, p.points FROM names n LEFT OUTER JOIN points p ON n.name = p.name

C.

SELECT p.name, n.number, p.points FROM names n RIGHT OUTER JOIN points p ON n.name = p.name

D.

SELECT p.name, n.number, p.points FROM names n FULL OUTER JOIN points p ON n.name = p.name

 

Correct Answer: C

 

 

QUESTION 164

Given the following table definitions:

 

EMPLOYEES

 

———————————————–

 

EMPID INTEGER

 

NAME CHAR(20)

 

DEPTID CHAR(3)

 

SALARY DECIMAL(10,2)

 

COMMISSION DECIMAL(8,2)

 

DEPARTMENTS

 

———————————————–

 

DEPTNO INTEGER

 

DEPTNAME CHAR(20)

 

Which of the following statements will produce a result data set that satisfies all of these conditions:

 

> Displays the total number of employees in each department

 

>> Displays the corresponding department name for each department ID

 

>> Sorted by department employee count, from greatest to least

 

A.

SELECT *, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 DESC

B.

SELECT deptname, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 DESC

C.

SELECT deptname, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 ASC

D.

SELECT deptname, COUNT(*) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2

 

Correct Answer: B

 

 

QUESTION 165

Given the following table:

 

CURRENT_EMPLOYEES

 

————————————-

 

EMPID INTEGER NOT NULL

 

NAME CHAR(20)

 

SALARY DECIMAL(10,2)

 

PAST_EMPLOYEES

 

————————————-

 

EMPID INTEGER NOT NULL

 

NAME CHAR(20)

 

SALARY DECIMAL(10,2)

 

Assuming both tables contain data, which of the following statements will NOT successfully add data to table CURRENT_EMPLOYEES?

 

A.

INSERT INTO current_employees (empid) VALUES (10)

B.

INSERT INTO current_employees VALUES (10, `JAGGER’, 85000.00)

C.

INSERT INTO current_employees SELECT empid, name, salary FROM past_employees WHERE empid = 20

D.

INSERT INTO current_employees (name, salary) VALUES (SELECT name, salary FROM past_employees WHERE empid = 20)

 

Correct Answer: D

 

 

QUESTION 166

Given the following table:

 

STOCK

 

————————-

 

CATEGORY CHAR(1)

 

PARTNO CHAR(12)

 

DESCRIPTION VARCHAR(40)

 

QUANTITY INTEGER

 

PRICE DEC(7,2)

 

If items are indicated to be out of stock by setting DESCRIPTION to NULL and QUANTITY and PRICE to zero, which of the following statements updates the STOCK table to indicate that all

 

items except those with CATEGORY of `S’ are temporarily out of stock?

 

A.

UPDATE stock SET description = ‘NULL’, quantity = 0, price = 0 WHERE category `S’

B.

UPDATE stock SET description = NULL, SET quantity = 0, SET price = 0 WHERE category `S’

C.

UPDATE stock SET (description, quantity, price) = (`null’, 0, 0) WHERE category `S’

D.

UPDATE stock SET (description, quantity, price) = (NULL, 0, 0) WHERE category `S’

 

Correct Answer: D

 

 

QUESTION 167

Given the following SQL statements:

 

CREATE TABLE tab1 (col1 INTEGER)

 

INSERT INTO tab1 VALUES (NULL)

 

INSERT INTO tab1 VALUES (1)

 

CREATE TABLE tab2 (col2 INTEGER)

 

INSERT INTO tab2 VALUES (NULL)

 

INSERT INTO tab2 VALUES (1)

 

INSERT INTO tab2 VALUES (2)

 

What will be the result when the following statement is executed?

 

SELECT * FROM tab1 WHERE col1 IN (SELECT col2 FROM tab2)

 

A.

COL1

1 record(s) selected.

B.

COL1

NULL

2 record(s) selected.

C.

COL1

2 record(s) selected.

D.

COL1

1 record(s) selected.

 

Correct Answer: A

 

 

QUESTION 168

Given the following table definition:

 

SALES

 

——————————————–

 

INVOICE_NO CHAR(20) NOT NULL

 

SALES_DATE DATE

 

SALES_PERSON CHAR(20)

 

REGION CHAR(20)

 

SALES INTEGER

 

If the following SELECT statement is executed, which of the following describes the order of the rows in the result data set produced?

 

SELECT * FROM sales

 

A.

The rows are sorted by INVOICE_NO in ascending order.

B.

The rows are sorted by INVOICE_NO in descending order.

C.

The rows are ordered based on when they were inserted into the table.

D.

The rows are not sorted in any particular order.

 

Correct Answer: D

 

 

QUESTION 169

Given the following tables:

 

YEAR_2006

 

EMPID NAME

——————————–

 

1 Jagger, Mick

 

2 Richards, Keith

 

3 Wood, Ronnie

 

4 Watts, Charlie

 

5 Jones, Darryl

 

6 Leavell, Chuck

 

YEAR_1962

 

EMPID NAME

 

——————————–

 

1 Jagger, Mick

 

2 Richards, Keith

 

3 Jones, Brian

 

4 Wyman, Bill

 

5 Chapman, Tony

 

6 Stewart, Ian

 

If the following SQL statement is executed, how many rows will be returned?

 

SELECT name FROM year_2006

 

UNION

 

SELECT name FROM year_1962

 

A.

0

B.

6

C.

10

D.

12

 

Correct Answer: C

 

 

QUESTION 170

Which of the following best describes a unit of work?

 

A.

It is a recoverable sequence of operations whose point of consistency is established when a connection to a database has been established or when a mechanism known as asavepoint is created.

B.

It is a recoverable sequence of operations whose current point of consistency can be determined by querying the system catalog tables.

C.

It is a recoverable sequence of operations whose point of consistency is established when an executable SQL statement is processed after a connection to a database has been established or a previous transaction has been terminated.

D.

It is a recoverable sequence of operations whose point of consistency is only established if a mechanism known as a savepoint is created.

 

Correct Answer: C

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