Data-Management-Foundations WGU Data Management – Foundations Exam Question and Answers

TheALTER TABLEstatement is used to modify an existing database table structure. One common clause isCHANGE, which allows renaming a column and modifying its data type.

Example:

sql

ALTER TABLE Employees CHANGE COLUMN OldName NewName VARCHAR(50);

Option A (Incorrect):DELETE is used to removerows, not alter table structure.

Option B (Correct):CHANGE is avalid clausefor renaming and modifying columns in MySQL and some other databases.

Option C (Incorrect):STOP is not a valid SQL keyword for altering tables.

Option D (Incorrect):AGGREGATE refers to functions like SUM() and AVG(), not table alterations.

Thefirst stepin database analysis isidentifying entities, which are thereal-world objectsthat need to be represented in the database.

Example Usage:

In a school system, the mainentitiescould be:

Students, Courses, Instructors

Why Other Options Are Incorrect:

Option A (Determine cardinality) (Incorrect):Determining relationships comesafteridentifying entities.

Option C (Draw an ER diagram) (Incorrect):ER diagramsvisualizeentities but arenot the first step.

Option D (Implement attributes) (Incorrect):Attributes aredefined afterentities are identified.

Thus, the correct answer isIdentify entities, asentities form the foundation of the databasemodel.

Non-relational databases(also calledNoSQL databases) are designed for handlingbig dataandunstructured dataefficiently. They are optimized forhorizontal scaling, making them ideal forlarge-scale distributed systems.

Option A (Correct):Non-relational databases areoptimized for big data, handling massive volumes of data across distributed architectures.

Option B (Incorrect):NoSQL databases donotuse SQL as their primary query language. They often use JSON-based queries (e.g., MongoDB).

Option C (Incorrect):Transaction-heavy applications requireACID compliance, which relational databases (SQL) handle better than NoSQL databases.

Option D (Incorrect):NoSQL databases usedocument, key-value, graph, or column-family storage models, nottables, columns, and rowslike relational databases.

In databases, primary keys are oftenset to auto-incrementso that new rows automatically receive unique values. However,one common error is manually inserting a value into an auto-incremented primary key column, whichoverrides the automatic numberingand 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');

Thismanually overrides the auto-increment, which can lead toduplicate key errors.

Correct Insert (Avoiding Errors):

sql

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

Thedatabase assigns UserID automatically, preventing conflicts.

Why Other Options Are Incorrect:

Option B (Failing to set a numeric value) (Incorrect):The databaseautomatically assignsvalues when AUTO_INCREMENT is used.

Option C (Designating multiple primary keys) (Incorrect):Whileincorrect, 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 isInserting a value and overriding auto-increment, which can causeduplicate key errors and data inconsistencies.

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.

TheONkeyword specifies the field used by anindexwhen creating it in SQL.

Example Usage:

sql

CREATE INDEX idx_employee_name

ON Employees(Name);

Here, an indexidx_employee_nameis createdon the Name column.

Thisimproves query performancewhen 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 doesnot define an index field.

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

Thus, the correct answer isON, as it defines thecolumn on which an index is created.

Thecorrect syntaxfor deleting records from a table in SQL is:

sql

DELETE FROM table_name WHERE condition;

This deletesonly the rowsthat match the condition.

Example Usage:

sql

DELETE FROM Employees WHERE Salary < 30000;

Deletesall employees earning less than $30,000.

Why Other Options Are Incorrect:

Option A (Incorrect):Missing FROMkeyword. The correct syntax is DELETE FROM table_name.

Option C (Partially Correct):DELETE FROM table_name;deletes all rows, but it lacks a WHERE clause.

Option D (Incorrect):DELETE *is not validin SQL. The correct command is just DELETE FROM.

Thus, the correct answer isDELETE FROM table_name WHERE condition;.

Asimple primary keyconsists ofonly one columnthat uniquely identifies each row in a table.

Example Usage:

sql

CREATE TABLE Students (

StudentID INT PRIMARY KEY,

Name VARCHAR(50)

);

StudentIDis asimple primary keybecause it consists of only one field.

Why Other Options Are Incorrect:

Option B (Partition) (Incorrect):Refers topartitioned tables, which divide data for performance reasons butare not related to primary keys.

Option C (Stable) (Incorrect):This is not a recognized term in database keys.

Option D (Meaningless) (Incorrect):Primary keys are oftenmeaningless (e.g., auto-incremented IDs), but this isnot a term used to describe their structure.

Thus, the correct answer isSimple, as asingle-field primary keyis referred to as asimple primary key.

In MySQL, theBIGINTdata type is a64-bit integerthat requires8 bytes (64 bits) of storage. It is used to store large numerical values beyond the range of INT (4 bytes).

Option A (Incorrect):1 byte corresponds toTINYINT, which can store values from -128 to 127.

Option B (Incorrect):3 bytes is not a standard integer storage size in MySQL.

Option C (Incorrect):4 bytes corresponds toINT, which has a range of -2,147,483,648 to 2,147,483,647.

Option D (Correct):BIGINT takes8 bytesand supports a massive range of numbers from -2^63 to 2^63 -1.

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.

In SQL, aprimary key is explicitly defined using the PRIMARY KEY keywordwhen creating a table.

Example Usage:

sql

CREATE TABLE Products (

ProductID INT PRIMARY KEY,

Name VARCHAR(100),

Price DECIMAL(10,2)

);

Here,PRIMARY KEY is the SQL keyword that designates ProductID as the primary key.

Why Other Options Are Incorrect:

Option A (Formula in SQL) (Incorrect):SQLdoes not use formulas to define primary keys.

Option C (Bold typeface) (Incorrect):SQL syntax does not rely on text formatting.

Option D (Diamond symbol) (Incorrect):ER diagramsmight use symbols, but SQLdoes not use diamonds to indicate keys.

Thus, the correct answer isSQL keyword, as primary keys are explicitly defined using PRIMARY KEY.

MongoDB is aNoSQL (non-relational) databasethat isopen-sourceand supportsdocument-oriented storage. It allows for flexible schema design and is optimized for big data applications.

Option A (Correct):MongoDB is aNoSQL databasewith an open-sourceAGPLlicense, making it both free to use and non-relational.

Option B (Incorrect):SQL Serveris arelational database(RDBMS) developed by Microsoft and isnot open-source.

Option C (Incorrect):MySQLisopen-sourcebut is arelational database(RDBMS), not a NoSQL system.

Option D (Incorrect):Oracle Databaseisrelationalandproprietary(not open-source).

TheBETWEENkeyword in SQL is used tofilter 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;

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

Why Other Options Are Incorrect:

Option A (LIKE) (Incorrect):Used forpattern matchingwith wildcards (%, _). Example:

sql

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

Option B (IN) (Incorrect):Used tomatch a value in a specified set, butnot a range. Example:

sql

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

Option C (OR) (Incorrect):Used forlogical conditions, but doesnot check a range. Example:

sql

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

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

When performing aJOIN operationin MySQL, the ON clause specifies thejoining condition, defining which columns from both tables should be matched.

Example:

sql

SELECT Employees.Name, Departments.DepartmentName

FROM Employees

JOIN Departments ON Employees.DepartmentID = Departments.ID;

Option A (Incorrect):AS is used foraliasing tables and columns, not for specifying join conditions.

Option B (Incorrect):JOIN defines the type of join (INNER JOIN, LEFT JOIN, etc.), butdoes not specify the columns.

Option C (Correct):The ON clause is used tospecify the join conditionbetween two tables.

Option D (Incorrect):AND is used in filtering conditions, not for joining tables.

TheORDER BYclause in SQL is used tosort query resultsinascending (ASC) or descending (DESC) order.

Example Usage:

sql

SELECT Name, Salary FROM Employees ORDER BY Salary DESC;

This retrieves all employees, sorted bysalary in descending order.

Why Other Options Are Incorrect:

Option A (BETWEEN) (Incorrect):Used for filtering ranges butdoes not order results.

Option B (DISTINCT) (Incorrect):Removes duplicate rows butdoes not control order.

Option D (LIKE) (Incorrect):Used forpattern matching, not sorting.

Thus,ORDER BY is the correct choicefor defining the sequence of query results.