1Z0-006 Fundamental Database Concepts 5 — Questions and Answers
Question 1: What is the purpose of the ROLLBACK command in SQL?
- To permanently save transaction changes
- To undo all changes made since the last COMMIT (Correct answer)
- To delete all rows in a table
- To reset a sequence to its starting value
Correct answer: To undo all changes made since the last COMMIT
ROLLBACK undoes all data modifications made during the current transaction, restoring the database to its state at the last COMMIT or SAVEPOINT.
Question 2: In First Normal Form (1NF), what requirement must be met?
- All non-key attributes must depend on the whole primary key
- Each column must contain atomic (indivisible) values with no repeating groups (Correct answer)
- There must be no transitive dependencies
- Every attribute must depend directly on the primary key
Correct answer: Each column must contain atomic (indivisible) values with no repeating groups
1NF requires that each table cell contain a single, indivisible value and that there are no repeating groups or arrays within a row.
Question 3: Which SQL aggregate function counts only non-NULL values in a column?
- COUNT(*)
- COUNT(column_name) (Correct answer)
- SUM(column_name)
- NVL(column_name, 0)
Correct answer: COUNT(column_name)
COUNT(column_name) counts only non-NULL values in the specified column, while COUNT(*) counts all rows including those with NULLs.
Question 4: What is a database index, and what is its primary trade-off?
- A backup copy of a table; trade-off is storage space
- A data structure that speeds up reads; trade-off is slower writes and extra storage (Correct answer)
- A constraint that enforces uniqueness; trade-off is query complexity
- A view that caches query results; trade-off is data staleness
Correct answer: A data structure that speeds up reads; trade-off is slower writes and extra storage
An index speeds up SELECT queries by providing fast data lookup paths, but it slows down INSERT, UPDATE, and DELETE operations because the index must also be maintained.
Question 5: What does 'referential integrity' mean in a relational database?
- Every table must have a primary key defined
- Foreign key values must match existing primary key values in the referenced table or be NULL (Correct answer)
- All column values must be stored in a normalized form
- Index entries must always match the actual table data
Correct answer: Foreign key values must match existing primary key values in the referenced table or be NULL
Referential integrity ensures that a foreign key value in a child table must either match an existing primary key value in the parent table or be NULL.
Question 6: Which SQL set operator returns only the rows that appear in both result sets?
- UNION
- UNION ALL
- INTERSECT (Correct answer)
- MINUS
Correct answer: INTERSECT
INTERSECT returns only the distinct rows that exist in both the first and second query result sets.
Question 7: What is the key difference between a database and a database management system (DBMS)?
- A database is software; a DBMS is the raw data stored on disk
- A database is an organized collection of data; a DBMS is the software that manages and provides access to that data (Correct answer)
- A DBMS is a type of database optimized for large datasets
- There is no difference; the terms are interchangeable
Correct answer: A database is an organized collection of data; a DBMS is the software that manages and provides access to that data
A database is the structured collection of data, while a DBMS (like Oracle) is the software system that stores, retrieves, and manages that data.
1Z0-006 Fundamental Database Concepts 5
What is the purpose of the ROLLBACK command in SQL?