1Z0-006 Database Concepts & Terminology — Questions and Answers

Question 1: Which SQL clause is used to remove duplicate rows from a query result?

GROUP BY
UNIQUE
DISTINCT (Correct answer)
ORDER BY

Correct answer: DISTINCT

The `DISTINCT` keyword in SQL is specifically used with the `SELECT` statement to retrieve only unique rows from a query result. It effectively eliminates any duplicate entries, ensuring that each returned row is unique based on the selected columns. Other clauses like `GROUP BY` are for aggregation, and `ORDER BY` is for sorting, not for removing duplicates.

Question 2: In a relational database, a PRIMARY KEY constraint ensures that:

A column may contain duplicate values
The column is always indexed but can be NULL
Each row has a unique, non‑NULL identifier (Correct answer)
Rows are physically stored in sorted order

Correct answer: Each row has a unique, non‑NULL identifier

A `PRIMARY KEY` constraint is a fundamental concept in relational databases, designed to uniquely identify each record in a table. It enforces two critical rules: entity integrity (no `NULL` values) and uniqueness (no duplicate values) for the column(s) it comprises. This ensures that every row can be unambiguously referenced and maintains data integrity.

Question 3: Which normal form eliminates partial dependencies on a composite primary key?

First Normal Form
Second Normal Form (Correct answer)
Third Normal Form
Boyce‑Codd Normal Form

Correct answer: Second Normal Form

Second Normal Form (2NF) addresses the issue of partial dependencies, which occur when a non-key attribute is dependent on only a part of a composite primary key. To achieve 2NF, these partially dependent attributes are moved to a new table with the relevant part of the composite key as its primary key. This process reduces data redundancy and improves data integrity within the database.

Question 4: The ANSI SQL data type that stores variable‑length character strings up to a defined limit is:

CHAR
VARCHAR (Correct answer)
CLOB
NCHAR

Correct answer: VARCHAR

The `VARCHAR` (or `VARCHAR2` in Oracle) data type is used to store variable-length character strings, meaning it only uses the amount of storage necessary for the actual data, up to a specified maximum length. In contrast, `CHAR` stores fixed-length strings, padding with spaces if shorter, while `CLOB` is for very large character objects. `VARCHAR` is ideal for efficient storage of text data of varying sizes.

Question 5: Which Oracle background process writes modified database buffers from the SGA to data files?

LGWR
DBWn (Correct answer)
CKPT
PMON

Correct answer: DBWn

The Database Writer (DBWn) background process is responsible for writing modified data blocks from the System Global Area (SGA) buffer cache to the database's data files on disk. This process ensures that changes made in memory are eventually persisted, maintaining data integrity and freeing up buffer space for new data. Other processes like LGWR handle redo logs, and CKPT handles checkpoints.

Question 6: A FOREIGN KEY constraint enforces:

Entity integrity on the parent table
Unique values in a column
Referential integrity between tables (Correct answer)
Automatic index creation only

Correct answer: Referential integrity between tables

A `FOREIGN KEY` constraint establishes a link between two tables, ensuring that values in the foreign key column(s) of the child table match values in the primary key (or unique key) of the parent table. This mechanism enforces referential integrity, preventing actions that would destroy links between tables and ensuring data consistency across the database. It is crucial for maintaining relationships between data.

Question 7: What does the acronym DML stand for in SQL?

Data Modeling Lab
Database Management Logic
Data Manipulation Language (Correct answer)
Distributed Memory Layer

Correct answer: Data Manipulation Language

DML stands for Data Manipulation Language, which is a subset of SQL commands used for managing data within database objects. Common DML statements include `SELECT` (retrieve data), `INSERT` (add new data), `UPDATE` (modify existing data), and `DELETE` (remove data). These commands interact directly with the data itself, rather than defining or controlling the database structure.

Question 8: In Oracle, which structure maps logical database blocks to physical file locations?

Redo log
Extent map in segment header (Correct answer)
Control file
Parameter file

Correct answer: Extent map in segment header

In Oracle, a segment (e.g., a table or index) is composed of extents, which are contiguous blocks of data. The segment header contains an extent map that tracks the physical file and block locations of all the extents belonging to that segment. This map is crucial for the database to efficiently locate and access the data stored within a segment, linking logical data structures to their physical storage.

Question 9: Which command permanently saves all outstanding transactions in an Oracle database?

ROLLBACK
SAVEPOINT
COMMIT (Correct answer)
FLASHBACK

Correct answer: COMMIT

The `COMMIT` command in SQL is used to make all changes performed within the current transaction permanent in the database. Once committed, the changes cannot be undone by a `ROLLBACK` statement, and they become visible to other database users. This ensures data integrity and consistency by finalizing a set of operations, making them a permanent part of the database.

1Z0-006 Database Concepts & Terminology

Which SQL clause is used to remove duplicate rows from a query result?