Latest Certified Success Dumps Download

CISCO, MICROSOFT, COMPTIA, HP, IBM, ORACLE, VMWARE
000-730 Real Tests (May 2017)

[Free] 2017(May) Ensurepass Pass4sure IBM 000-730 Real Tests 151-160

May 4, 2017

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 151

Given the following table definition:

 

SALES

 

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

 

INVOICE_NO CHAR(20) NOT NULL

 

SALES_DATE DATE

 

SALES_PERSON VARCHAR(25)

 

REGION CHAR(20)

 

SALES_AMT DECIMAL(9,2)

 

Which of the following queries will return SALES information, sorted by SALES_PERSON, from A to Z, and SALES_DATE, from most recent to earliest?

 

A.

SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales SORT BY sales_person, sales_date DESC

B.

SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales SORT BY sales_person DESC, sales_date

C.

SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales ORDER BY sales_person, sales_date DESC

D.

SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales ORDER BY sales_person DESC, sales_date

 

Correct Answer: D

 

 

QUESTION 152

Given the following statement:

 

SELECT hyear, AVG(salary)

 

FROM (SELECT YEAR(hiredate) AS hyear, salary

 

FROM employee WHERE salary > 30000)

 

GROUP BY hyear

 

Which of the following describes the result if this statement is executed?

 

A.

The statement will return the year and average salary for all employees that have a salary greater than $30,000, sorted by year.

B.

The statement will return the year and average salary for all employees hired within a given year that have a salary greater than $30,000.

C.

The statement will return the year and average salary for all years that every employee hired had a salary greater than $30,000.

D.

The statement will return the year and average salary for all years that any employee had a salary greater than $30,000.

 

Correct Answer: B

 

 

QUESTION 153

Which two of the following statements are true about the HAVING clause?

 

A.

The HAVING clause is used in place of the WHERE clause.

B.

The HAVING clause uses the same syntax as the WHERE clause.

C.

The HAVING clause can only be used with the GROUP BY clause.

D.

The HAVING clause accepts wildcards.

E.

The HAVING clause uses the same syntax as the IN clause.

 

Correct Answer: BC

 

 

QUESTION 154

Given the following table definitions:

 

DEPARTMENT

 

————————-

 

DEPTNO CHAR(3)

 

DEPTNAME CHAR(30)

 

MGRNO INTEGER

 

ADMRDEPT CHAR(3)

 

EMPLOYEE

 

————————-

 

EMPNO INTEGER

 

FIRSTNAME CHAR(30)

 

MIDINIT CHAR

 

LASTNAME CHAR(30)

 

WORKDEPT CHAR(3)

 

Which of the following statements will list every employee number and last name, along with the employee number and last name of their manager, including employees that have not been assigned to a manager?

 

A.

SELECT e.empno, e.lastname, m.empno, m.lastname FROM employee e LEFT INNER JOIN department INNER JOIN employee m ON mgrno=m.empno ON e.workdept=deptno

B.

SELECT e.empno, e.lastname, m.empno, m.lastname FROM employee e LEFT OUTER JOIN department INNER JOIN employee m ON mgrno=m.empno ON e.workdept=deptno

C.

SELECT e.empno, e.lastname, m.empno, m.lastname FROM employee e RIGHT OUTER JOIN department INNER JOIN employee m ON mgrno=m.empno ON e.workdept=deptno

D.

SELECT e.empno, e.lastname, m.empno, m.lastname FROM employee e RIGHT INNER JOIN department INNER JOIN employee m ON mgrno=m.empno ON e.workdept=deptno

 

Correct Answer: B

 

 

 

 

QUESTION 155

Given the following table:

 

EMPLOYEE

 

EMPID NAME INSTRUMENT

 

— ————— —–

 

1 Jagger, Mick 01

 

2 Richards, Keith 02

 

3 Wood, Ronnie 02

 

4 Watts, Charlie 03

 

5 Jones, Darryl 04

 

6 Leavell, Chuck 05

 

If the following query is executed:

 

SELECT name,

 

CASE WHEN instrument = ’01’ THEN ‘HARMONICA’

 

WHEN instrument = ’02’ THEN ‘GUITAR’

 

WHEN instrument = ’03’ THEN ‘DRUMS’

 

ELSE ‘UNKNOWN’

 

END AS instrument

 

FROM employee

 

What will be the results?

 

A.

NAME INSTRUMENT

————– ——–

Jagger, Mick HARMONICA

Richards, Keith GUITAR

Wood, Ronnie GUITAR

Watts, Charlie DRUMS

Jones, Darryl ERROR

Leavell, Chuck ERROR

B.

NAME INSTRUMENT

—————– ——–

Jagger, Mick HARMONICA

Richards, Keith GUITAR

Wood, Ronnie GUITAR

Watts, Charlie DRUMS

Jones, Darryl 04

Leavell, Chuck 05

C.

NAME INSTRUMENT

—————– ——–

Jagger, Mick HARMONICA

Richards, Keith GUITAR

Wood, Ronnie GUITAR

Watts, Charlie DRUMS

Jones, Darryl UNKNOWN

Leavell, Chuck UNKNOWN

D.

NAME INSTRUMENT

—————– ——–

Jagger, Mick HARMONICA

Richards, Keith GUITAR

Wood, Ronnie GUITAR

Watts, Charlie DRUMS

Jones, Darryl –

Leavell, Chuck –

 

Correct Answer: C

 

 

QUESTION 156

Given the following UPDATE statement:

 

UPDATE employees SET workdept =

 

(SELECT deptno FROM department WHERE deptno = ‘A01’)

 

WHERE workdept IS NULL

 

Which of the following describes the result if this statement is executed?

 

A.

The statement will fail because an UPDATE statement cannot contain a subquery.

B.

The statement will only succeed if the data retrieved by the subquery does not contain multiple records.

C.

The statement will succeed; if the data retrieved by the subquery contains multiple records, only the first record will be used to perform the update.

D.

The statement will only succeed if every record in the EMPLOYEES table has a null value in the WORKDEPT column.

 

Correct Answer: B

 

 

QUESTION 157

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 158

Given the following table definition:

 

SALES

 

————————-

 

SALES_DATE DATE

 

SALES_PERSON CHAR(20)

 

REGION CHAR(20)

 

SALES INTEGER

 

Which of the following SQL statements will remove all rows that had a SALES_DATE in the year 1995?

 

A.

DELETE * FROM sales WHERE YEAR(sales_date) = 1995

B.

DELETE FROM sales WHERE YEAR(sales_date) = 1995

C.

DROP * FROM sales WHERE YEAR(sales_date) = 1995

D.

DROP FROM sales WHERE YEAR(sales_date) = 1995

 

Correct Answer: B

 

 

QUESTION 159

Given the following table definition:

 

EMPLOYESS

 

————————-

 

EMP ID INTEGER

 

NAME CHAR(20)

 

DEPT CHAR(10)

 

SALARY DECIMAL (10, 2)

 

COMMISSION DECIMAL (8, 2)

 

Assuming the DEPT column contains the values `ADMIN’, `PRODUCTION’, and `SALES’, which of the following statements will produce a result data set in which all ADMIN department employees are grouped together, all PRODUCTION department employees are grouped together, and all SALES department employees are grouped together?

 

A.

SELECT name, dept FROM employees ORDER BY dept

B.

SELECT name, dept FROM employees GROUP BY dept

C.

SELECT name, dept FROM employees GROUP BY ROLLUP (dept)

D.

SELECT name, dept FROM employees GROUP BY CUBE (dept)

 

Correct Answer: A

 

 

QUESTION 160

The following SQL statement:

 

DELETE FROM tab1 WHERE CURRENT OF csr1 WITH RR

 

Is used to perform which type of delete operation?

 

A.

Positioned

B.

Searched

C.

Embedded

D.

Dynamic

 

Correct Answer: A

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