1Z0-006 Database Concepts & Terminology

Question 1

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

Accepted Answer

DISTINCT

Answer

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:

Accepted Answer

Each row has a unique, non‑NULL identifier

Answer

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?

Accepted Answer

Second Normal Form

Answer

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:

Accepted Answer

VARCHAR

Answer

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?

Accepted Answer

DBWn

Answer

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.

(1Z0-006) Oracle Database Foundations Associate Practice Test

(1Z0-006) Oracle Database Foundations Associate Practice Test

1Z0-006 Database Concepts & Terminology