Free Practice Questions for WGU Data-Management-Foundations Exam

Anindex scanis a database operation thatquickly locates table blocksusing anindexrather than scanning the entire table. It significantly improves query performance.

Example Usage:

sql

SELECT * FROM Employees WHERE EmployeeID = 105;

If EmployeeID isindexed, the database uses anindex scantoquickly find the record.

Why Other Options Are Incorrect:

Option A (Binary search) (Incorrect):A searching algorithm, but not a database operation.

Option B (Table scan) (Incorrect):Scans theentire table, which isslowerthan an index scan.

Option C (Fan-out) (Incorrect):Refers tobranching in B-Treesbut doesnot locate table blocks directly.

Thus, the correct answer isIndex scan, as it efficiently locates table blocks.

Bydatabase design conventions, theprimary key is usually placed in the first columnof a table to make it easy to identify and reference.

Example Usage:

sql

CREATE TABLE Employees (

EmpID INT PRIMARY KEY, -- First column (convention)

Name VARCHAR(50),

Salary DECIMAL(10,2)

);

EmpID is placed as the first columnfor clarity and quick access.

Why Other Options Are Incorrect:

Option A (In the table header) (Incorrect):Table headers onlydisplay column names, they do not contain values.

Option C (In the top row) (Incorrect):Thetop row contains data, not theprimary key definition.

Option D (In the last visible column) (Incorrect):While technically possible,placing a primary key at the endisuncommonin database design.

Thus, the correct answer isIn the first column, as this is the standard convention in relational databases.

Thesecond step in implementing relationshipsis definingone-to-one (1:1) relationshipsbetween entities.

Example Usage:

Example of a 1:1 relationship:

sql

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

Name VARCHAR(50)

);

CREATE TABLE EmployeeDetails (

EmpID INT PRIMARY KEY,

Address VARCHAR(255),

FOREIGN KEY (EmpID) REFERENCES Employees(EmpID)

);

Here, eachemployee has exactly one detail record, creating a1:1 relationship.

Why Other Options Are Incorrect:

Option A (Implement weak entities) (Incorrect):Weak entities rely on aforeign keyand are implementedlater.

Option C (Implement subtype entities) (Incorrect):Subtypes arespecial casesandnot implemented in the second step.

Option D (Specify cascade) (Incorrect):Cascade rules (ON DELETE, ON UPDATE)are defined duringforeign key implementation, not in the second step.

Thus, the correct answer isImplement one-one relationships, as it is thenext logical stepafter defining entities.

Atangible entityis a real-world object that can bemeasured and storedin a database.

Example Usage:

In an inventory system,tangible entitiesinclude:

Products, Orders, Customers

Why Other Options Are Incorrect:

Option A (Logical entity) (Incorrect):Exists logically butmay not have a physical presence(e.g.,views, categories).

Option C (Virtual entity) (Incorrect):Existsonly in queries or reports, not stored as real data.

Option D (Conceptual entity) (Incorrect):Abstract idea used indesign modeling, not astored entity.

Thus, the correct answer isTangible entity, as it representsmeasurable, real-world objects.

Analiasis used in SQL to give atemporaryname to a table or column within a query. It makes queries more readable and helps in cases where a table needs to be referenced multiple times (e.g., in aself-join).

Example Usage:

sql

SELECT e.Name, d.DepartmentName

FROM Employees AS e

JOIN Departments AS d

ON e.DeptID = d.ID;

Here,Employees is aliased as eandDepartments as d, making the query shorter and clearer.

Why Other Options Are Incorrect:

Option A (HAVING) (Incorrect):Used to filter grouped results, not create aliases.

Option B (NEW) (Incorrect):Not a valid SQL keyword for aliasing.

Option D (UNION) (Incorrect):Combines result sets but does not rename tables.

Thus, the correct answer isALIAS, which allows fortemporary naming of tables or columns.

Aforeign keyis a column (or a set of columns) that establishes arelationship between two tablesby referencing theprimary key of another table.

Example Usage:

sql

CREATE TABLE Departments (

DeptID INT PRIMARY KEY,

DeptName VARCHAR(50)

);

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

Name VARCHAR(50),

DeptID INT,

FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)

);

DeptID in Employees is a foreign key, referencing DeptID in Departments.

Ensuresreferential integrity→ DeptID in Employeesmust existin Departments.

Why Other Options Are Incorrect:

Option A (Super key) (Incorrect):Asuper keyis any set of columns that uniquely identifies a row, but itdoes not reference another table.

Option B (Simple key) (Incorrect):Asimple keyis asingle-column primary key, not a reference to another table.

Option C (Composite key) (Incorrect):Acomposite keyconsists ofmultiple columnsbut does not necessarily reference another table.

Thus, the correct answer isForeign key, as it establishes aconnection between two tables.

TheHAVINGclause is used in SQL to filtergrouped resultsgenerated by the GROUP BY clause. Unlike WHERE, which filters individual rowsbeforegrouping, HAVING filtersafter aggregationhas 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 theaverage salary per department, and then filters onlythose 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 inCommon Table Expressions (CTEs), not for filtering.

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

Thus,HAVING is the correct answerfor filtering after grouping.

TheLTRIM()function in SQL removesleading 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):Removesboth 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 doesnot specifically remove spaces.

Thus, the correct answer isLTRIM(), which removes onlyleading spaces.