Free WGU Data-Management-Foundations Practice Exam with Questions & Answers

TheABS()function in SQL returns theabsolute valueof a given number, effectively measuring itsdistance from zero.

Example Usage:

sql

SELECT ABS(-50), ABS(50);

Result:

50 | 50

This function ensures that numbers arealways positive, regardless of their original sign.

Why Other Options Are Incorrect:

Option A (CONCAT) (Incorrect):Used tocombine strings(not numbers).

Option B (LOWER) (Incorrect):Converts text tolowercase, not numerical operations.

Option C (FROM) (Incorrect):Part of SELECT FROM queries,not a function.

Thus, the correct choice isABS(), which computes the absolute value of a number.

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.

When a VIEW is created in SQL, users mayinsert or updatedata through that view. However, if a row is inserted or updated in such a way that itviolates the conditionof the VIEW’s WHERE clause, it can lead to inconsistencies.

Topreventsuch unwanted modifications,SQL provides the WITH CHECK OPTION clause.

How WITH CHECK OPTION Works:

Ensures thatany new data (INSERT/UPDATE) still fits within the defined constraints of the VIEW.

If a user tries to insert or update a row that would not appear in the VIEW,the operation isrejected.

Example:

sql

CREATE VIEW HighSalaryEmployees AS

SELECT * FROM Employees WHERE Salary > 50000

WITH CHECK OPTION;

Now, if someone attempts:

sql

INSERT INTO HighSalaryEmployees (ID, Name, Salary)

VALUES (101, 'Alice', 30000);

Thisfailsbecause 30000 does not satisfy Salary > 50000.

Why Other Options Are Incorrect:

Option B (Incorrect):JOIN VIEWS isnot a valid SQL clause.

Option C (Incorrect):MATERIALIZED VIEW refers tostored viewsin some databases, but it doesnotreject incorrect inserts/updates.

Option D (Incorrect):BASE TABLE refers to theoriginal table from which a VIEW is created.

Thus, the correct answer is WITH CHECK OPTION, whichensures that only valid data modifications occur.

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.

Aquery processoris responsible forquery optimization and executionin a database management system (DBMS). It analyzes SQL statements, optimizes execution plans, and ensures efficient retrieval of data.

Option A (Correct):Thequery processoroptimizes queries by analyzing metadata from thesystem catalogto determine the best execution strategy.

Option B (Incorrect):Logging transactionsbefore applying changes is the responsibility of thetransaction manager.

Option C (Incorrect):Translating instructions intofile system commandsis handled by thestorage manager, not the query processor.

Option D (Incorrect):While the query processor helps retrieve results, thedatabaseengineandAPI layerare responsible for returning results to applications.

Toadd a primary key constraint after table creation, we use theALTER TABLEstatement.

Example Usage:

sql

ALTER TABLE Employees

ADD CONSTRAINT PK_Employees PRIMARY KEY (EmpID);

Thisaddsa primary key to the EmpID columnafter the table was created.

Why Other Options Are Incorrect:

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

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

Option D (INSERT INTO) (Incorrect):Used toadd datato a table, not modify constraints.

Thus, the correct answer isALTER TABLE, as itmodifies table structure to add a primary key constraint.

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.

Aunary relationship(also known as arecursive relationship) occurs when an entityrelates to itself.

Example Usage:

Employees and Managers:

sql

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

Name VARCHAR(50),

ManagerID INT,

FOREIGN KEY (ManagerID) REFERENCES Employees(EmpID)

);

Here, ManagerIDreferences another Employee→ aunary (self-referential) relationship.

Why Other Options Are Incorrect:

Option A (Modality) (Incorrect):Describesoptional vs. mandatoryrelationships, not self-referencing.

Option C (Cardinality) (Incorrect):Defines how many instances relate,not the type of relationship.

Option D (Binary) (Incorrect):Binary relationships involvetwo different entities, not self-referencing.

Thus, the correct answer isUnary, as it describesrelationships within the same entity type.

In SQL,literalsrepresent explicit values such asnumbers, strings, or binary datadirectly written into queries. For example:

SELECT * FROM Employees WHERE Salary > 50000;

Here, 50000 is anumeric literal.

Option A (Correct):Literalsare explicit values used in SQL queries, such as 123, 'John Doe', and TRUE.

Option B (Incorrect):Commentsare non-executable text used for documentation within SQL code, typically denoted by -- or /* ... */.

Option C (Incorrect):Identifiersare names oftables, columns, or other database objects, such as EmployeeID.

Option D (Incorrect):Keywordsare reserved words in SQL (e.g., SELECT, FROM, WHERE) that define operations and syntax.

Anaggregate functionperforms acalculation over multiple rowsand returns asingle value. Examples includeSUM(), AVG(), MAX(), MIN(), and COUNT()in SQL.

Option A (Correct):Aggregate functions compute values over aset of rows, like summingtotal sales or averaging grades.

Option B (Incorrect):Selecting rows that appear in one table but not another is done usingset operations (EXCEPT or MINUS in SQL).

Option C (Incorrect):Eliminating columns is done using thePROJECToperation orSELECT with specific columns.

Option D (Incorrect):Combining rows from two tables refers to aJOIN operation, not aggregation.