SQL Fundamentals & Data Manipulation Flashcards

What does the TRUNCATE TABLE command do that differs from DELETE?

Answer: TRUNCATE is a DDL statement that removes all rows and cannot be rolled back in the same way as DELETE

TRUNCATE is a DDL operation that deallocates table data quickly and, unlike DELETE, implicitly commits and cannot be rolled back.

Which operator tests whether a value falls within an inclusive range in a WHERE clause?

Answer: BETWEEN low AND high

BETWEEN low AND high is inclusive on both ends, equivalent to col >= low AND col <= high.

Which SELECT statement retrieves distinct department IDs from the employees table?

Answer: SELECT DISTINCT department_id FROM employees;

DISTINCT is the standard SQL keyword to eliminate duplicate rows from a result set.

What is the result of: SELECT 100 + NULL FROM dual;

Answer: NULL

Any arithmetic operation involving NULL propagates NULL as the result.

Which SQL keyword is used to sort query results in descending order?

Answer: ORDER BY column DESC

ORDER BY column DESC instructs Oracle to sort the specified column from highest to lowest.

Which constraint ensures that a column value in one table matches a value in a referenced table's primary key?

Answer: FOREIGN KEY constraint

A FOREIGN KEY constraint enforces referential integrity by requiring that the column value exists in the parent table's primary key.

What does the NVL(commission_pct, 0) function do when commission_pct is NULL?

Answer: Returns 0

NVL replaces a NULL value with the specified substitute, so NVL(commission_pct, 0) returns 0 when commission_pct is NULL.