Oracle 1Z0-071

Front 1

DELETE with a Correlated Subquery

Back 1

The DELETE statement can be used with a correlated subquery in the WHERE clause to determine which rows to delete from a given table. The syntax is similar to the correlated subquery syntax for SELECT and UPDATE statements.

Front 2

Correlated Subquery can be used with what type of statements?

Back 2

SELECT, UPDATE, DELETE

Front 3

EXISTS and NOT EXISTS Operators

Back 3

The EXISTS keyword tests for the existence of any rows in a subquery. If no rows are found, the answer is FALSE. Otherwise, the subquery returns TRUE. NOT EXISTS reverses the results.

Front 4

WITH Clause

Back 4

You can use the keyword WITH to assign a name to a subquery block. Once the name is assigned, you can reference the name from elsewhere in the query. WITH is considered a clause of the SELECT statement.

Front 5

WITH Clause locations

Back 5

WITH can define one subquery factoring clause; it must be defined before the SELECT statement.

Front 6

When is WITH statement not recognized?

Back 6

The WITH statement name isn't recognized within the subquery itself but is recognized in most every other location in the overall query.

Front 7

Single-Row Subqueries

Back 7

single-row subquery is a subquery that returns one row. That's the parent query's way of saying that it is expecting one—and only one—row from the subquery.

Front 8

What error will occur when a parent query expect a single row but receives multiple rows?

Back 8

SQL Error: ORA-01427: single-row subquery returns more than one row*Can be fixed by using things like MAX, or WHERE filters on fields, or using ROWNUM filter

Front 9

What are the wildcard characters used With LIKE?

Back 9

_ The underscore is a wildcard character representing a single character. % The percent sign is a wildcard character representing one or more values.

Front 10

Multiple-Row Subqueries

Back 10

A multiple-row subquery may return more than one row of answers to the parentquery.

Front 11

Subqueries and IN

Back 11

IN allows the subquery to return multiple rows. The presence of the keyword IN directs the parent query to allow the subquery to be a multiple-row subquery

Front 12

Greater Than and Less Than and using subqueries

Back 12

You can use greater-than or less-than comparison operators with single-row subqueries but not multirow subqueries unless those operators are combined with ALL, ANY, or SOME.

Front 13

Comparison Conditions: IN

Back 13

Compares a subject value to a set of zero or more values. Returns TRUE if the subject value equals any of the values in the set. Returns FALSE if the subquery returns no rows.

Front 14

Comparison Conditions: NOT

Back 14

Used with IN to reverse the result. Returns TRUE if the subquery returns no rows. WARNING: Be careful here. If any one or more of the subquery rows returns NULL, the result of the subquery will be "no rows selected."

Front 15

Comparison Conditions: ANY

Back 15

Used in combination with single-row comparison conditions (such as = or >) to compare a subject value with a multirow subquery. Returns TRUE if the subject value finds a match consistent with the comparison operator in any of the rows returned by the subquery. Returns FALSE if the subquery returns no rows

Front 16

Comparison Conditions: SOME

Back 16

Same as ANY. Used in combination with single-row comparison conditions (such as = or >) to compare a subject value with a multirow subquery. Returns TRUE if the subject value finds a match consistent with the comparison operator in any of the rows returned by the subquery. Returns FALSE if the subquery returns no rows

Front 17

Comparison Conditions: ALL

Back 17

Used in combination with single-row comparison conditions to compare a subject value with a multirow subquery. Returns TRUE if the subject value finds a match consistent with the comparison operator in all of the rows returned by the subquery. Returns FALSE if the subquery returns no rows.

Front 18

Subquery

Back 18

A subquery is a SELECT statement that exists within a larger SQL statement. Subqueries can be included in a SELECT, INSERT, UPDATE, MERGE, or DELETE statement. Subqueries can also be used in a CREATE TABLE statement. Subqueries can be used in WHERE clauses of SELECT, UPDATE, and DELETEstatements. They can be used in the UPDATE . . . SET clause and the INSERT list of values. Some subqueries may be able to substitute for any expression almost anywhere an expression is accepted, including the SELECT list of a SELECT statement. Subqueries can perform multiple-step queries in a single SQL statement. Theycan be used to reference lookup information from a given query. They can populate a table at the time of creation in a CREATE TABLE statement. They are used to create views. There are many types of subqueries, including single-row, multiple-row,multiple-column, scalar, and correlated.

Front 19

Correlated Subquery

Back 19

Correlated subqueries contain conditions in the subquery that connect rows of data with rows in the parent query, much like a join might do

Front 20

Single-row Subquery

Back 20

Single-row subqueries return one row of data to the parent query. Multiple-rowsubqueries can return anywhere from zero to one to more than one row.

Front 21

Multiple-column subquery

Back 21

Multiple-column subqueries are compared to rows in the parent query using multiple columns at once. Scalar subqueries return one row and one column's worth of data at all times.

Front 22

Parent Query

Back 22

The outer SQL statement is called the parent. The outermost level is the top level.

Front 23

EXISTS and NOT EXISTS Operators

Back 23

The EXISTS keyword tests for the existence of any rows in a subquery. If no rows are found, the answer is FALSE. Otherwise, the subquery returns TRUE. NOT EXISTS reverses the results.