WGU D427 OBJECTIVE ASSESSMENT EXAM– QUESTIONS AND
ANSWERS | VERIFIED AND WELL DETAILED ANSWERS PLUS
RATIONALES | GUARANTEED PASS | LATEST EXAM UPDATE | EXAM
PREP | STUDY GUIDE | PRACTICE TEST| DOWNLOAD INSTANT PDF
1. Which of the following SQL clauses is used to filter rows based on a specified condition
before any grouping or aggregation occurs in a query?
A. HAVING
B. WHERE
C. GROUP BY
D. ORDER BY
The WHERE clause filters individual rows prior to any grouping or aggregation operations.
In contrast, the HAVING clause is used exclusively to filter groups after aggregate functions
have been applied.
2. Which database normalization form ensures that a table is in Second Normal Form and
that all non-key attributes are fully dependent only on the primary key, thereby removing
transitive dependencies?
A. First Normal Form
B. Second Normal Form
C. Third Normal Form
D. Boyce-Codd Normal Form
Third Normal Form (3NF) requires that a table is in 2NF and contains no transitive
dependencies, meaning non-key columns depend solely on the primary key rather than on
other non-key columns.
3. When designing a relational database schema for an online retail system, which
constraint should be applied to a column to guarantee that no two rows contain duplicate
values while allowing null values depending on business rules?
A. FOREIGN KEY
B. CHECK
C. UNIQUE
,D. NOT NULL
The UNIQUE constraint ensures all values in a column are distinct across rows. Unlike a
primary key, a unique constraint typically permits a single null value depending on the
specific database management system implementation.
4. What is the primary purpose of a database index in a relational database management
system?
A. To enforce referential integrity between parent and child tables
B. To accelerate data retrieval operations at the cost of additional storage and slower write performance
C. To encrypt sensitive column data at rest automatically
D. To automatically normalize unformatted spreadsheets during import
Correct Answer: B. To accelerate data retrieval operations at the cost of additional storage
and slower write performance Indexes create structured lookup paths that significantly
speed up SELECT queries, though they require extra disk space and can overhead INSERT,
UPDATE, and DELETE operations.
5. Which SQL statement structure correctly adds a new column named birth_date of type
DATE to an existing table called employees?
A. MODIFY TABLE employees ADD COLUMN birth_date DATE;
B. ALTER TABLE employees ADD birth_date DATE;
C. UPDATE TABLE employees INSERT birth_date DATE;
D. CHANGE TABLE employees COLUMN birth_date DATE;
The ALTER TABLE statement combined with the ADD clause is the standard SQL syntax for
introducing new columns into an existing table structure.
6. In transactional database systems, what property of ACID ensures that either all
operations within a transaction commit successfully or the entire transaction is rolled back,
leaving the database unchanged?
A. Atomicity
B. Consistency
C. Isolation
D. Durability
, Atomicity treats a transaction as a single indivisible unit of work. If any part of the transaction
fails, all preceding changes are aborted and rolled back.
7. Which type of database join returns all records when there is a match in either the left or
the right table, filling with NULL values where no match exists?
A. INNER JOIN
B. LEFT JOIN
C. RIGHT JOIN
D. FULL OUTER JOIN
A FULL OUTER JOIN combines the results of both left and right outer joins, returning all
rows from both tables and matching them where conditions are met, or inserting NULLs
otherwise.
8. An administrator needs to grant read-only access to a specific table named
financial_records to a database user named analyst_user. Which SQL command
accomplishes this?
A. GRANT SELECT ON financial_records TO analyst_user;
B. PERMIT READ ON financial_records TO analyst_user;
C. ALLOW SELECT FROM financial_records USER analyst_user;
D. ASSIGN ROLE reader ON financial_records TO analyst_user;
The standard Data Control Language (DCL) command GRANT SELECT provides read-only
privileges on a specific table to a designated database user or role.
9. When evaluating database performance, an execution plan reveals a full table scan on a
large table containing millions of rows. Which remediation strategy is most appropriate if
queries frequently filter by a specific unindexed column?
A. Convert the table into a non-relational document store
B. Create an index on the frequently filtered column
C. Disable transaction logging for the database instance
D. Drop the primary key constraint to reduce overhead
Adding an index on the filtered column allows the database query optimizer to use an index
scan instead of inspecting every row via a full table scan.
ANSWERS | VERIFIED AND WELL DETAILED ANSWERS PLUS
RATIONALES | GUARANTEED PASS | LATEST EXAM UPDATE | EXAM
PREP | STUDY GUIDE | PRACTICE TEST| DOWNLOAD INSTANT PDF
1. Which of the following SQL clauses is used to filter rows based on a specified condition
before any grouping or aggregation occurs in a query?
A. HAVING
B. WHERE
C. GROUP BY
D. ORDER BY
The WHERE clause filters individual rows prior to any grouping or aggregation operations.
In contrast, the HAVING clause is used exclusively to filter groups after aggregate functions
have been applied.
2. Which database normalization form ensures that a table is in Second Normal Form and
that all non-key attributes are fully dependent only on the primary key, thereby removing
transitive dependencies?
A. First Normal Form
B. Second Normal Form
C. Third Normal Form
D. Boyce-Codd Normal Form
Third Normal Form (3NF) requires that a table is in 2NF and contains no transitive
dependencies, meaning non-key columns depend solely on the primary key rather than on
other non-key columns.
3. When designing a relational database schema for an online retail system, which
constraint should be applied to a column to guarantee that no two rows contain duplicate
values while allowing null values depending on business rules?
A. FOREIGN KEY
B. CHECK
C. UNIQUE
,D. NOT NULL
The UNIQUE constraint ensures all values in a column are distinct across rows. Unlike a
primary key, a unique constraint typically permits a single null value depending on the
specific database management system implementation.
4. What is the primary purpose of a database index in a relational database management
system?
A. To enforce referential integrity between parent and child tables
B. To accelerate data retrieval operations at the cost of additional storage and slower write performance
C. To encrypt sensitive column data at rest automatically
D. To automatically normalize unformatted spreadsheets during import
Correct Answer: B. To accelerate data retrieval operations at the cost of additional storage
and slower write performance Indexes create structured lookup paths that significantly
speed up SELECT queries, though they require extra disk space and can overhead INSERT,
UPDATE, and DELETE operations.
5. Which SQL statement structure correctly adds a new column named birth_date of type
DATE to an existing table called employees?
A. MODIFY TABLE employees ADD COLUMN birth_date DATE;
B. ALTER TABLE employees ADD birth_date DATE;
C. UPDATE TABLE employees INSERT birth_date DATE;
D. CHANGE TABLE employees COLUMN birth_date DATE;
The ALTER TABLE statement combined with the ADD clause is the standard SQL syntax for
introducing new columns into an existing table structure.
6. In transactional database systems, what property of ACID ensures that either all
operations within a transaction commit successfully or the entire transaction is rolled back,
leaving the database unchanged?
A. Atomicity
B. Consistency
C. Isolation
D. Durability
, Atomicity treats a transaction as a single indivisible unit of work. If any part of the transaction
fails, all preceding changes are aborted and rolled back.
7. Which type of database join returns all records when there is a match in either the left or
the right table, filling with NULL values where no match exists?
A. INNER JOIN
B. LEFT JOIN
C. RIGHT JOIN
D. FULL OUTER JOIN
A FULL OUTER JOIN combines the results of both left and right outer joins, returning all
rows from both tables and matching them where conditions are met, or inserting NULLs
otherwise.
8. An administrator needs to grant read-only access to a specific table named
financial_records to a database user named analyst_user. Which SQL command
accomplishes this?
A. GRANT SELECT ON financial_records TO analyst_user;
B. PERMIT READ ON financial_records TO analyst_user;
C. ALLOW SELECT FROM financial_records USER analyst_user;
D. ASSIGN ROLE reader ON financial_records TO analyst_user;
The standard Data Control Language (DCL) command GRANT SELECT provides read-only
privileges on a specific table to a designated database user or role.
9. When evaluating database performance, an execution plan reveals a full table scan on a
large table containing millions of rows. Which remediation strategy is most appropriate if
queries frequently filter by a specific unindexed column?
A. Convert the table into a non-relational document store
B. Create an index on the frequently filtered column
C. Disable transaction logging for the database instance
D. Drop the primary key constraint to reduce overhead
Adding an index on the filtered column allows the database query optimizer to use an index
scan instead of inspecting every row via a full table scan.
