1z0-071 Sample Questions & Answers

The ORDER BY Clause can be used in a subquery.

A subquery can be used in the FROM clause of a SELECT statement.

If a subquery returns NULL, the main query may still return rows.

A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.

Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.

It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace.

It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.

It enables sessions to store a query block permanently in memory and use it to create complex queries.

It enables sessions to store the results of a query permanently.

You can use a column alias in the GROUP BY clause.

Using the WHERE clause after the GROUP BY clause excludes rows after creating groups.

The GROUP BY clause is mandatory if you are using an aggregating function in the SELECT clause.

Using the WHERE clause before the GROUP BY clause excludes rows before creating groups.

If the SELECT clause has an aggregating function, then columns without an aggregating function in the SELECT clause should be included in the GROUP BY clause.

SELECT last_name, city FROM members WHERE state - MO' AND state ='MI';

SELECT last_name, city FROM members WHERE state LIKE 'M%';

SELECT last_name, city FROM members WHERE state IN ('MO', 'Ml');

SELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI';

INSERT INTO transaction VALUES ('AT, DEFAULT, TO_DATE(DEFAULT+10));

INSERT INTO transaction VALUES ('AT, DEFAULT, TO_DATE('SYSDATE+10'));

INSERT INTO transaction (trn_ID, end_date) VALUES ('AT, '10-DEC-2014');

INSERT INTO transaction (trn_ID, start_date, end_date) VALUES ('AT,, '10-DEC-2014');

The minimum column width that can be specified for a varchar2 data type column is one.

Only one LONG column can be used per table.

TIMESTAMP data type column stores only time values with fractional seconds.

The BLOB data type column is used to store binary data in an operating system file.

The value for a CHAR data type column is blank-padded to the maximum defined column width.

It would not execute because the entire WHERE clause is not enclosed within parentheses.

It would execute but would return no rows.

It would not execute because the same column has been used twice with the AND logical operator.

It would execute and return the desired result.

The table prefix is missing for the column names in the SELECT clause.

The NATURAL JOIN clause is missing the USING clause.

The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.

The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.

1 row

an error

0 rows

2 rows