1Z0-006 Fundamental Database Concepts 3 — Questions and Answers
Question 1: Which SQL clause filters rows AFTER a GROUP BY aggregation has been performed?
- WHERE
- HAVING (Correct answer)
- FILTER
- QUALIFY
Correct answer: HAVING
HAVING filters groups produced by GROUP BY, whereas WHERE filters individual rows before grouping occurs.
Question 2: What does DCL (Data Control Language) consist of in SQL?
- SELECT and FROM statements
- CREATE, ALTER, and DROP statements
- GRANT and REVOKE statements (Correct answer)
- INSERT, UPDATE, and DELETE statements
Correct answer: GRANT and REVOKE statements
DCL includes GRANT (to give privileges) and REVOKE (to remove privileges), controlling user access to database objects.
Question 3: Which type of JOIN returns all rows from the left table and matching rows from the right table, with NULLs for non-matching right rows?
- INNER JOIN
- RIGHT OUTER JOIN
- LEFT OUTER JOIN (Correct answer)
- CROSS JOIN
Correct answer: LEFT OUTER JOIN
A LEFT OUTER JOIN returns all rows from the left table plus matching rows from the right table; unmatched right-table columns appear as NULL.
Question 4: What is data redundancy in a database, and why is it problematic?
- Storing backups of data; it wastes storage but is always required
- Storing the same data in multiple places; it leads to inconsistency and update anomalies (Correct answer)
- Encrypting data multiple times; it slows query performance
- Indexing data on multiple columns; it complicates query planning
Correct answer: Storing the same data in multiple places; it leads to inconsistency and update anomalies
Data redundancy means the same data is stored in multiple locations, causing update anomalies where changing one copy may leave other copies out of sync.
Question 5: In a relational database, what is a candidate key?
- Any foreign key that can optionally reference a parent table
- Any minimal set of attributes that can uniquely identify a row (Correct answer)
- The primary key chosen from among several foreign keys
- An index created to speed up common queries
Correct answer: Any minimal set of attributes that can uniquely identify a row
A candidate key is any minimal superkey — a set of attributes that uniquely identifies rows, from which the primary key is chosen.
Question 6: What is the difference between DELETE and TRUNCATE in Oracle SQL?
- DELETE removes the table structure; TRUNCATE removes only rows
- DELETE can be rolled back and fires triggers; TRUNCATE is DDL and cannot be easily rolled back (Correct answer)
- DELETE is faster than TRUNCATE for large tables
- DELETE requires a WHERE clause; TRUNCATE does not allow one
Correct answer: DELETE can be rolled back and fires triggers; TRUNCATE is DDL and cannot be easily rolled back
DELETE is a DML statement that can be rolled back and fires row-level triggers, while TRUNCATE is DDL that bypasses rollback segments and is much faster.
Question 7: Which constraint ensures that a column value in one table matches a value in a specified column of another table?
- CHECK constraint
- UNIQUE constraint
- FOREIGN KEY constraint (Correct answer)
- NOT NULL constraint
Correct answer: FOREIGN KEY constraint
A FOREIGN KEY constraint enforces referential integrity by requiring that column values match existing values in the referenced parent table's column.
1Z0-006 Fundamental Database Concepts 3
Which SQL clause filters rows AFTER a GROUP BY aggregation has been performed?