WGU Data Management - Foundations Exam Questions

Answer : D

A valid SELECT statement in SQL follows this basic syntax:

sql

SELECT column1, column2

FROM table_name

WHERE condition;

The correct option D follows this syntax correctly.

Why Other Options Are Incorrect:

Option A (Incorrect): SQL does not use double quotes (') around column/table names unless explicitly required in some databases.

Option B (Incorrect): The WHERE clause must appear after the FROM clause.

Option C (Incorrect): ALL is not a valid keyword in standard SQL queries.

Thus, Option D follows the correct SQL syntax.

Answer : A

The UNION keyword in SQL is used to combine the results of two or more SELECT queries into a single result set while removing duplicate rows.

Example:

sql

SELECT Name FROM Employees

UNION

SELECT Name FROM Managers;

Option A (Correct): UNION combines results from multiple queries into one set, removing duplicates.

Option B (Incorrect): MERGE is not a valid SQL keyword for combining result sets (it is used in some database systems for data merging).

Option C (Incorrect): INTEGRATE is not a SQL keyword.

Option D (Incorrect): CONSOLIDATE is not an SQL keyword.

Answer : C

This query performs a join operation where records from the Make table and Model table are combined based on the condition Make.ModelID = Model.ID. This condition tests for equality, which is the definition of an EQUIJOIN.

Types of Joins in SQL:

EQUIJOIN (Correct Answer):

Uses an equality operator (=) to match rows between tables.

Equivalent to an INNER JOIN ON condition.

Example:

sql

SELECT *

FROM Employees

JOIN Departments ON Employees.DeptID = Departments.ID;

NON-EQUIJOIN (Incorrect):

Uses comparison operators other than = (e.g., <, >, BETWEEN).

Example:

sql

SELECT *

FROM Employees e

JOIN Salaries s ON e.Salary > s.MedianSalary;

SELF JOIN (Incorrect):

A table is joined with itself using table aliases.

Example:

sql

SELECT e1.Name, e2.Name AS Manager

FROM Employees e1

JOIN Employees e2 ON e1.ManagerID = e2.ID;

CROSS JOIN (Incorrect):

Produces Cartesian product (each row from Table A combines with every row from Table B).

Example:

sql

SELECT *

FROM Employees

CROSS JOIN Departments;

Thus, since our given query uses an equality condition (=) to join two tables, it is an EQUIJOIN.

Answer : C

A Transaction Manager ensures ACID (Atomicity, Consistency, Isolation, Durability) properties in database transactions. It manages concurrent transactions, ensuring no conflicts occur and logs modifications to support recovery mechanisms.

Option A (Incorrect): Query optimization is managed by the query processor, not the transaction manager.

Option B (Incorrect): The transaction manager is a component of the database architecture but is not composed of the entire system (query processor, storage manager, etc.).

Option C (Correct): The transaction manager logs transactions like INSERT, UPDATE, and DELETE, ensuring consistency and recoverability.

Option D (Incorrect): The storage manager is responsible for translating queries into file system commands.

Answer : A

In databases, primary keys are often set to auto-increment so that new rows automatically receive unique values. However, one common error is manually inserting a value into an auto-incremented primary key column, which overrides the automatic numbering and may cause conflicts.

Example of Auto-Increment Setup:

sql

CREATE TABLE Users (

UserID INT AUTO_INCREMENT PRIMARY KEY,

Username VARCHAR(50)

);

Incorrect Insert (Error-Prone Approach):

sql

INSERT INTO Users (UserID, Username) VALUES (100, 'Alice');

This manually overrides the auto-increment, which can lead to duplicate key errors.

Correct Insert (Avoiding Errors):

sql

INSERT INTO Users (Username) VALUES ('Alice');

The database assigns UserID automatically, preventing conflicts.

Why Other Options Are Incorrect:

Option B (Failing to set a numeric value) (Incorrect): The database automatically assigns values when AUTO_INCREMENT is used.

Option C (Designating multiple primary keys) (Incorrect): While incorrect, most databases will prevent this at creation time.

Option D (Forgetting to specify which is the auto-increment column) (Incorrect): If AUTO_INCREMENT is set, the database handles numbering automatically.

Thus, the most common error is Inserting a value and overriding auto-increment, which can cause duplicate key errors and data inconsistencies.

Answer : A

The LTRIM() function in SQL removes leading spaces (spaces at the beginning of a string) while keeping spaces at the end.

Example Usage:

sql

SELECT LTRIM(' Hello World') AS TrimmedText;

Output:

bash

'Hello World'

Why Other Options Are Incorrect:

Option B (LEFT) (Incorrect): Used for extracting a portion of a string. Example:

sql

SELECT LEFT('Hello World', 5); -- Output: 'Hello'

Option C (TRIM) (Incorrect): Removes both leading and trailing spaces, not just leading ones. Example:

sql

SELECT TRIM(' Hello World '); -- Output: 'Hello World'

Option D (REPLACE) (Incorrect): Replaces occurrences of one substring with another but does not specifically remove spaces.

Thus, the correct answer is LTRIM(), which removes only leading spaces.

Answer : A

The ON keyword specifies the field used by an index when creating it in SQL.

Example Usage:

sql

CREATE INDEX idx_employee_name

ON Employees(Name);

Here, an index idx_employee_name is created on the Name column.

This improves query performance when filtering by Name.

Why Other Options Are Incorrect:

Option B (IN) (Incorrect): Used in queries to match values in a set, not for indexing.

Option C (UNIQUE) (Incorrect): Ensures a column has unique values but does not define an index field.

Option D (CHECK) (Incorrect): Used for validating column values, not for indexing.

Thus, the correct answer is ON, as it defines the column on which an index is created.