Expanding Queries

1.

In a left outer join, what happens to the rows in the left table without a matching row in the right table?

Choose the correct answer.

Rows in the left table without a matching row in the right table are copied to the query result. The missing values from the right table are filled with NULL values.

Rows in the left table without a matching row in the right table are NOT copied to the query result.

Rows in the left table without a matching row in the right table are copied to the query result. The missing values from the right table are filled with a blank if the data type is a character, or with a zero if it is numeric.

This will result as an error.

2.

In a SELECT with an OVER() clause, which of the following statements are true?

There are two correct answers.

PARTITIONED BY specifies the order of the rows in the result set.

ORDER BY specifies the order of the rows in the result set.

An OVER() clause with ORDER BY cannot contain PARTITIONED BY.

The SELECT statement cannot return NULL values.

The PARTITION BY clause is optional.

3.

Which of the following is correct when combining the result sets of multiple queries using UNION?

There are three correct answers.

The individual result sets must have the same number of columns.

The corresponding result columns must have compatible data types.

The individual result sets must have the same number of rows.

The column names of the resulting output table are based on the first SELECT statement.

4.

What is the difference between GROUP BY CUBE and GROUP BY ROLLUP?

Choose the correct answer.

GROUP BY CUBE is not part of the ANSI SQL-99 standard.

GROUP BY CUBE aggregates in every dimension.

GROUP BY ROLLUP cannot be used in a SELECT statement.

5.

If the current row has a value of 5000 and the three preceding rows have a value (in order) of 1000, 1500, and 3000, which rows would be included in ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING?

Choose the correct answer.

1500

1000 and 1500

1500 and 3000

1000, 1500, 3000, and 5000

6.

What kind of subquery can be evaluated independently of the outer query?

Choose the correct answer.

Correlated subqueries

Uncorrelated subqueries

Both correlated and uncorrelated subqueries

7.

If the current row has a value of 5000 and the three preceding rows have a value (in order) of 1000, 1500, and 3000, which rows would be included in RANGE BETWEEN 3500 PRECEDING AND CURRENT ROW?

Choose the correct answer.

1000, 1500, and 3000

1000, 1500, 3000, and 5000

1500, 3000, and 5000

3000 and 5000

5000

Knowledge quiz

In a left outer join, what happens to the rows in the left table without a matching row in the right table?

In a SELECT with an OVER() clause, which of the following statements are true?

Which of the following is correct when combining the result sets of multiple queries using UNION?

What is the difference between GROUP BY CUBE and GROUP BY ROLLUP?

If the current row has a value of 5000 and the three preceding rows have a value (in order) of 1000, 1500, and 3000, which rows would be included in ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING?

What kind of subquery can be evaluated independently of the outer query?

If the current row has a value of 5000 and the three preceding rows have a value (in order) of 1000, 1500, and 3000, which rows would be included in RANGE BETWEEN 3500 PRECEDING AND CURRENT ROW?