WGU Data-Management-Foundations Exam Dumps

To add a primary key constraint after table creation, we use the ALTER TABLE statement.

Example Usage:

sql

ALTER TABLE Employees

ADD CONSTRAINT PK_Employees PRIMARY KEY (EmpID);

This adds a primary key to the EmpID column after the table was created.

Why Other Options Are Incorrect:

Option B (CREATE TABLE) (Incorrect): Used for defining constraints during table creation, not after.

Option C (UPDATE) (Incorrect): Modifies row values, not constraints.

Option D (INSERT INTO) (Incorrect): Used to add data to a table, not modify constraints.

Thus, the correct answer is ALTER TABLE, as it modifies table structure to add a primary key constraint.

The BETWEEN keyword in SQL is used to filter values within a specified range. It is particularly useful for numeric and date-based queries.

Syntax of BETWEEN:

sql

SELECT * FROM Employees WHERE Salary BETWEEN 50000 AND 100000;

Retrieves all employees whose salary is between $50,000 and $100,000 (inclusive).

Why Other Options Are Incorrect:

Option A (LIKE) (Incorrect): Used for pattern matching with wildcards (%, _). Example:

sql

SELECT * FROM Customers WHERE Name LIKE 'A%';

Option B (IN) (Incorrect): Used to match a value in a specified set, but not a range. Example:

sql

SELECT * FROM Employees WHERE Department IN ('HR', 'Finance', 'IT');

Option C (OR) (Incorrect): Used for logical conditions, but does not check a range. Example:

sql

SELECT * FROM Products WHERE Price < 10 OR Price > 50;

Thus, the correct choice is BETWEEN for filtering values within a range.

Binary search is an algorithm that finds a search value by repeatedly dividing the search space into two halves. It is commonly used in indexed searches.

Example Usage in Databases:

B-Trees and B+ Trees (used in database indexing) apply binary search to navigate the index efficiently.

If searching for ID = 50 in a sorted list of IDs, binary search:

Splits the list into two halves.

Checks the middle value.

Eliminates half of the dataset.

Repeats until the value is found.

Why Other Options Are Incorrect:

Option A (Table scan) (Incorrect): Reads every row, much slower than binary search.

Option C (Index scan) (Incorrect): Uses indexes but does not necessarily apply binary search.

Option D (Fan-out) (Incorrect): Describes branching in B-Trees, not searching.

Thus, the correct answer is Binary search, as it repeatedly splits the index in two.

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.

The HAVING clause is used in SQL to filter grouped results generated by the GROUP BY clause. Unlike WHERE, which filters individual rows before grouping, HAVING filters after aggregation has been performed.

Example Usage:

sql

SELECT Department, AVG(Salary) AS AvgSalary

FROM Employees

GROUP BY Department

HAVING AVG(Salary) > 50000;

This query first groups employees by Department, calculates the average salary per department, and then filters only those departments where the average salary is greater than 50,000.

Why Other Options Are Incorrect:

Option A (REPLACE) (Incorrect): Used for string substitution, not filtering.

Option C (WITH) (Incorrect): Used in Common Table Expressions (CTEs), not for filtering.

Option D (WHERE) (Incorrect): Used for row-level filtering before aggregation, but it cannot be used on aggregate functions like SUM() or AVG().

Thus, HAVING is the correct answer for filtering after grouping.