WGU D427 Data Management Applications Exam | Objective Assessment | 150+ Verified Questions with Answers & Rationales | Complete Study Guide for MySQL Database Management

This is a comprehensive and fully updated exam preparation guide for WGU D427 - Data Management Applications, specifically designed for the academic year. This document contains an extensive collection of 150+ verified exam questions and key concepts, complete with correct answers and detailed rationales to ensure a deep understanding of SQL fundamentals, database design, MySQL syntax, and data management principles. This resource covers all critical domains of the WGU D427 Objective Assessment: SQL Fundamentals & Query Execution: SELECT Statements and Clause Order of Execution WHERE Clause (Filtering rows before grouping) ORDER BY Clause (Default ascending order) GROUP BY Clause and Aggregate Functions HAVING Clause (Filtering groups after aggregation) COUNT(*) vs COUNT(column_name) (Null handling) LIKE Operator with Wildcards (% and _) JOIN Types (INNER, LEFT, RIGHT, FULL OUTER, SELF JOIN) UNION vs UNION ALL (Duplicate handling) Subqueries (Correlated, non-correlated, in WHERE clause) LIMIT Clause (Row restriction, pagination) DISTINCT Keyword (Duplicate removal) IS NULL Operator (Null comparison) BETWEEN Operator (Range filtering) IN Operator (List matching) Mathematical Operators (Division, Modulo) String Functions (LENGTH, UPPER, LOWER, CONCAT, SUBSTRING) Date Functions (NOW, CURDATE, CURTIME) Date Literal Format (YYYY-MM-DD) Data Types, Constraints & Table Creation: Data Types: INT, VARCHAR, CHAR, DATE, DECIMAL, TINYINT, TEXT CHAR vs VARCHAR (Fixed vs variable storage) INT UNSIGNED (Positive values only) DECIMAL Precision and Scale (DECIMAL(3,2) = 1 digit before, 2 after) PRIMARY KEY Constraint (Unique, not null) FOREIGN KEY Constraint (Referential integrity) UNIQUE Constraint (Unique values) NOT NULL Constraint (No null values) CHECK Constraint (Value conditions) DEFAULT Constraint (Default values) AUTO_INCREMENT Attribute (Sequential numbers) CREATE TABLE Statement ALTER TABLE Statement (ADD, MODIFY, CHANGE, DROP) DROP TABLE vs TRUNCATE TABLE (Structure vs data only) RENAME TABLE CREATE INDEX (Single and composite) DROP INDEX CREATE VIEW DROP VIEW CREATE DATABASE / CREATE SCHEMA SHOW DATABASES / SHOW TABLES DESCRIBE / DESC (Table structure) Composite Primary Key (Multiple columns) ON DELETE CASCADE ON UPDATE CASCADE RESTRICT Referential Integrity Action Integrity & Relational Rules: Entity Integrity (Primary key cannot be null, must be unique) Referential Integrity (Foreign key must match primary key or be null) Domain Integrity (Column values must adhere to data type) User-Defined Integrity (Business rules via CHECK constraints) Relational Algebra Operations (SELECT σ, PROJECT π, JOIN ⋈, UNION ∪, DIFFERENCE -) Transitive Dependencies (Non-key depends on non-key) Partial Dependencies (Non-key depends on part of composite key) Normalization & Database Design: First Normal Form (1NF) - No repeating groups, atomic values Second Normal Form (2NF) - 1NF + no partial dependencies Third Normal Form (3NF) - 2NF + no transitive dependencies Boyce-Codd Normal Form (BCNF) - Every determinant is a candidate key Candidate Key (Minimal set that uniquely identifies each row) Surrogate Key (Artificial, system-generated key) Composite Key (Multiple columns combined as primary key) Functional Dependency (One column uniquely determines another) Insertion Anomalies Update Anomalies Deletion Anomalies Denormalization (Adding redundancy for performance) Atomic Values (Cannot be divided into smaller parts) DDL & DML Commands: INSERT INTO (Single and multiple rows) UPDATE ... SET ... WHERE DELETE FROM ... WHERE SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT CRUD Operations (Create, Read, Update, Delete) GRANT (User privileges) REVOKE (Remove privileges) Transactions & Stored Procedures: Transaction (Sequence of SQL statements executed as single unit) COMMIT (Permanently save changes) ROLLBACK (Undo changes) SAVEPOINT (Named point to rollback to) START TRANSACTION / BEGIN / BEGIN WORK Default Transaction Isolation Level (MySQL - REPEATABLE READ) ACID Properties (Atomicity, Consistency, Isolation, Durability) CREATE PROCEDURE (Stored procedure syntax) DROP PROCEDURE Practical Query Scenarios: Finding duplicates with GROUP BY and HAVING Finding nth highest/lowest with LIMIT and OFFSET Correlated subqueries for per-group comparisons Counting and grouping by categories Updating specific rows based on conditions Deleting rows with WHERE filters Inserting multiple rows at once Average, maximum, minimum calculations with aggregate functions Range queries with BETWEEN Pattern matching with LIKE Null handling with IS NULL View creation and usage Table and column aliasing with AS Each question is presented in a clear, easy-to-read format followed by the correct answer and a concise rationale that explains the underlying database and SQL principles. This study guide is ideal for exam preparation, reinforcing data management concepts, and mastering the critical thinking skills required for WGU D427 success.