IBM DB2 10.1 Fundamentals Set 2

1.

If a table named MY_TAB contains 100 rows and the following statement is executed: What will happen?

The last 5 rows in the table will be deleted. The first 5 rows in the table will be deleted. The statement will fail because a subquery cannot be used with a DELETE statement. The statement will fail because a table name was not specified with the DELETE statement.

2.

Given an EMPLOYEES table and a SALES table, a user wants to produce a list of all employees and their associated revenue, even if no revenue exists. Which SQL statement will produce the desired list?

SELECT employees.name, sales.revenue FROM employees INNER JOIN sales ON employees.id =sales.emp_id

SELECT employees.name, sales.revenue FROM employees INNER JOIN sales ON sales.emp_id =employees.id

SELECT employees.name, sales.revenue FROM sales LEFT OUTER JOIN employees ON employees.id =sales.emp_id

SELECT employees.name, sales.revenue FROM sales RIGHT OUTER JOIN employees ON employees.id= sales.emp_id

3.

Which command will delete all rows from a table without generating log records?

TRIM DROP DELETE TRUNCATE

4.

Which function can be used to obtain values from XML documents that are to be inserted into one or more tables?

XMLTABLE XMLPARSE XMLEXISTS XMLATTRIBUTES

5.

User USER1 wants to retrieve records from a table named EMPLOYEE that satisfy at least one of the following criteria: -The employee's hire date (HIREDATE) is before 1999 and the employee's salary (SALARY) is less than $40,000.00 a year. -The employee has attended university Which SQL statement will accomplish this?

SELECT * FROM employee WHERE (hiredate < '1999-01-01' AND salary < 40000) OR (education = 'University')

SELECT * FROM employee WHERE (hiredate < '1999-01-01') OR (salary < 40000) OR (education = 'University')

SELECT * FROM employee WHERE (hiredate < '1999-01-01' OR (salary < 40000 AND (education = 'University')

SELECT * FROM employee WHERE (hiredate < '1999-01-01' AND salary < 40000 AND (education = 'University')

6.

Which SQL statement will retrieve the employee number (EMPNO), hire date (HIREDATE), and salary (SALARY) for each employee from a table named EMPLOYEE who was hired before 1998 and earns a salary of less than $35,000.00 per year?

SELECT empno, hiredate, salary FROM employee FOR hiredate < '1998-01-01' AND salary < 35000

SELECT empno, hiredate, salary FROM employee WHERE hiredate < '1998-01-01' AND salary < 35000

SELECT empno, hiredate, salary FROM employee WHERE hiredate < '1998-01-01' OR salary < 35000

SELECT empno, hiredate, salary FROM employee FOR hiredate < '1998-01-01' OR salary < 35000

7.

If the following result set is desired: Which SQL statement must be executed?

SELECT lastname, firstnme, salary, job FROM employee ORDER BY 3 FETCH FIRST 5 ROWS ONLY SELECT lastname, firstnme, salary, job FROM employee ORDER BY 3 DESC FETCH FIRST 5 ROWSONLY SELECT lastname, firstnme, salary, job FROM employee ORDER BY 3 FETCH FIRST 5 ROWS SELECT lastname, firstnme, salary, job FROM employee ORDER BY 3 DESC FETCH FIRST 5 ROWS

8.

Which statement about INSERT operations is true?

The INSERT statement is used to insert rows into a table, view, or table function. Inserted values must satisfy the conditions of any check constraints defined on the table. If an INSERT statement omits any column from the inserted row that is defined as NULL or NOT NULLWITH DEFAULT, the statement will fail. If the underlying table of a view being referenced by an INSERT statement has one or more unique indexes,each row inserted does not have to conform to the constraints imposed by those indexes.

9.

Which statement about application-period temporal tables is true?

Consists of explicitly-supplied timestamps and a separate associated history table. Is based on explicitly-supplied timestamps that define the time periods during which data is valid. Is useful when one wants to keep both user-based period information and system-based historicalinformation. Consists of a pair of columns with database-manager maintained values that indicate the period when a rowis current.

10.

Which SQL statement should be used to select the minimum and maximum salaries (SALARY), by job code (JOB), from a table EMPLOYEE?

SELECT job, MIN(salary), MAX(salary) FROM employee GROUP BY job;

SELECT job, MIN(salary), MAX(salary)FROM employee ORDER BY job;

SELECT job, MIN(salary), MAX(salary) FROM employee GROUP BY job, MIN(salary), MAX(salary);

SELECT JOB, MIN(salary), MAX(salary)FROM employee ORDER BY job, MIN(salary), MAX(salary);

IBM DB2 10.1 Fundamentals Set 2

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Congratulations

COMPANY

Products

OTHERS

Partner

IBM DB2 10.1 Fundamentals Set 2

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Congratulations

Detail Form

COMPANY

Products

OTHERS

Partner