Mixed Deck — All 1Z0-071 Topics Flashcards

Which set operator returns all rows from both queries, including duplicate rows?

Answer: UNION ALL

UNION ALL combines result sets from both queries and retains all duplicate rows without performing any deduplication.

Which error occurs when a single-row operator is used with a multi-row subquery result?

Answer: ORA-01427

ORA-01427 'single-row subquery returns more than one row' is raised when = or < is used against a multi-row subquery.

Which type of join condition compares columns using an operator other than equals (=)?

Answer: Non-equijoin

A non-equijoin uses comparison operators like BETWEEN, instead of = to match rows.

What does the ON DELETE CASCADE option on a FOREIGN KEY do?

Answer: Automatically deletes child rows when the parent row is deleted

ON DELETE CASCADE automatically deletes all child rows in the referencing table when the parent row is deleted.

Which DML statement can both insert new rows and update existing rows in a single operation?

Answer: MERGE

MERGE (also called an upsert) matches source rows to target rows and inserts new ones or updates existing ones conditionally.

Which of the following is true about relational databases?

Answer: They use tables to store data, where each table consists of rows and columns.

Relational databases are fundamentally structured around tables, which are composed of rows and columns. Each row represents a single record, and each column represents an attribute of that record. This tabular structure is central to how relational databases store, organize, and manage data, allowing for clear definition and relationships between data entities.

An EMPLOYEES table has 10 rows; a DEPARTMENTS table has 5 rows. A CROSS JOIN returns how many rows?

Answer: 50

A CROSS JOIN (Cartesian product) multiplies all rows: 10 × 5 = 50 rows.

Which DDL command is used to remove a column from an existing table?

Answer: ALTER TABLE ... DROP COLUMN

ALTER TABLE ... DROP COLUMN removes a column and all its data from an existing table.

Which of the following correctly describes how set operators handle NULL values when comparing rows?

Answer: Set operators treat two NULL values as equal when determining row matches

Unlike most Oracle comparisons where NULL does not equal NULL, set operators (UNION, INTERSECT, MINUS) treat two NULL values as equal when determining whether rows match between the two result sets.

What is the effect of issuing ROLLBACK TO SAVEPOINT sp1 in a transaction?

Answer: Undoes changes made after sp1 was set, but keeps sp1 and changes before it

ROLLBACK TO SAVEPOINT reverses DML changes made after the savepoint, preserving the savepoint and earlier changes.

In Oracle SQL, the MINUS operator is the Oracle-specific equivalent of which ANSI SQL standard set operator?

Answer: EXCEPT

Oracle's MINUS operator is functionally equivalent to the ANSI SQL standard EXCEPT operator, both returning rows from the first query not found in the second.

Which Oracle DDL command renames an existing table?

Answer: RENAME old_name TO new_name

The RENAME statement changes the name of a table (or other object) to a new name.

Which view can you query to see all tables owned by the current user in Oracle?

Answer: USER_TABLES

USER_TABLES shows metadata about all tables owned by the currently logged-in Oracle user.

Which statement correctly nests group functions?

Answer: SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id

Oracle allows nesting group functions one level deep when a GROUP BY clause is present.

What does ROLLBACK do in a DML transaction?

Answer: Undoes all changes since the last COMMIT or ROLLBACK

ROLLBACK reverses all uncommitted DML changes, restoring the data to its state at the last COMMIT.

How many SELECT statements can be combined using set operators in a single Oracle SQL compound query?

Answer: There is no practical limit imposed by Oracle

Oracle SQL does not impose a strict practical limit on the number of SELECT statements that can be chained together using set operators in a single compound query.

Which of the following queries correctly places the ORDER BY clause when using set operators?

Answer: SELECT id FROM emp UNION SELECT id FROM dept ORDER BY id;

The ORDER BY clause must appear at the very end of the compound query after the final SELECT statement in a set operator expression.

Which of the following correctly creates a table with a primary key constraint?

Answer: CREATE TABLE t (id NUMBER PRIMARY KEY, name VARCHAR2(50))

Placing PRIMARY KEY after the column definition creates an inline primary key constraint for that column.

Which DDL statement removes all rows from a table but keeps its structure, and cannot be rolled back?

Answer: TRUNCATE TABLE

TRUNCATE TABLE is a DDL command that removes all rows instantly without logging individual row deletions and cannot be rolled back.

Which clause is mandatory in an UPDATE statement to avoid updating every row?

Answer: WHERE

Without a WHERE clause, UPDATE modifies every row in the table; WHERE restricts which rows are changed.