WGU D427 Data Management Applications Study Guide (PDF) | SQL Exam Prep 2026

WGU D427 Data Management Applications Study Guide |
Actual verified Study complete Solutions | A+ Graded | 2026
Updates | 100% correct


Literals
Explicit values that are string, numeric or binary must be surrounded by single quotes or double quotes
When you use an INSERT INTO code you will put ‘ ‘ (single quotes) for your values, EXCEPT INTEGERS (dates
you will put ‘ ‘)
Ex: The Movie table has the following columns:
Title – variable-length string
Genre – variable-length string
RatingCode – variable-length string
The following data needs to be added to the Movie table:




INSERT INTO Movie (Title, Genre, RatingCode)
VALUES (‘Pride and Prejudice’, ‘Romance’, ‘G’);



SQL Sublanguages
Data Definition Language (DDL) Remember DDL/CAD (I want to drop my CAD)
Defines the structure & manages the database
CREATE, ALTER, DROP (CAD)
Data Query Language (DQL)
Retrieves data from the database
SELECT
Data Manipulation Language (DML) Remember DML/IUD (I want to delete my IUD)
Manipulates data stored in a database
INSERT, UPDATE, DELETE (IUD)
Data Control Language (DCL)
Controls database user access
GRANT, REVOKE (GR)
Data Transaction Language (DTL)
Manages database transactions
COMMIT, SAVEPOINT, ROLLBACK (CSR) Attributes

,is columns in a table
Distinct pieces of info that describe the data. Break down the fragment into separate categories of info that
could be columns in a table. Each category corresponds to one attribute. Each unique piece of info is a separate
attribute that would be in its own column
Ex: San Francisco, CA 94410 USA
How many attributes are present in the address fragment? 4
Ex: Portland, OR 97212
How many attributes are present in the address fragment? 3

Data Types
Integer – represent whole positive & negative numbers
TINYINT – 1 byte, Signed range: -128-127, Unsigned range: 0-255
SMALLINT – 2 bytes, Signed range: -32,768 -32,767, Unsigned range: 0-65,535
MEDIUMINT – 3 bytes, Signed range: -8,388,608-8,388,607, Unsigned range: 0-16,777,215
INTEGER or INT – 4 bytes, Signed range: -2,147,483,648-2,147,483,647, Unsigned range: 0-4,294,967,296
BIGINT – 8 bytes, Signed range: -2^63-2^63 – 1, Unsigned range: 0- 2^64 -1
Decimal – represent numbers with fractional values
DECIMAL(M,D) – exact decimal # where M= # of significant digits, D= # of digits after decimal point
FLOAT – 4 bytes (approximate decimal #)
Ex: Which data type represents numbers with fractional values? Decimal
Ex: What data type is used to hold numbers like this: 25.55? Decimal or Numeric
Character – represent textual characters
CHAR(N) – fixed-length string of length
VARCHAR(N) – variable-length string with max N characters
Date & Time – represent date, time or both
DATE – format: YYYY-MM-DD
Time – format: hh:mm:ss
DATETIME – format: YYYY-MM-DD hh:mm:ss
Ex: Which data type will store “2022-01-10 14:22:12” as a temporal value without loss of info? DATETIME
Ex: Which data type can be designated to allow for storage of dates? TIMESTAMP/DATE
UNSIGNED – datatype when you only want positive numbers
Ex: INT UNSIGNED – positive integer
Ex: DECIMAL (x,y) UNSIGNED – positive decimal value with x being total digits before & after the decimal & y
being just the digits after the decimal
SIGNED – datatype when you only want negative numbers



SELECT Statement
The SELECT statement is used to select data from a database
The data returned is stored in a result table, called the result-set
SELECT Syntax
SELECT column1, column2, ...
FROM table_name;

,Ex: Write a statement that will just pull the last_name, first_name & coordinator_phone from the coordinator
table
SELECT last_name, first_name, coordinator_phone
FROM coordinator;
If you want to select all the fields available in the table, use the following syntax:
SELECT *
FROM table_name;
Ex: The database contains a table named Book. Write a SQL query to return all data from the Book table
without directly referencing any column names? SELECT *
FROM Book;

SELECT Statement DISTINCT Clause
The SELECT DISTINCT statement is used to return only distinct (different) values
Inside a table, a column often contains many duplicate values; and sometimes you only want to list the
different (distinct) values.
SELECT DISTINCT Syntax
SELECT DISTINCT column1, column2, ...
FROM table_name;
Ex: Write a query that will return all of the cities from the Customer table. Do not include duplicates, list each
city only once?
SELECT DISTINCT City
FROM Customer;
Ex: Write a statement to pull ActivityID & ActivityName in the Registration table. Eliminate duplicates in the
result set?
SELECT DISTINCT ActivityID, ActivityName
FROM Registration;
EX: Write a SQL query to output the unique RatingCode values & the number of movies with each rating value
from the Movie table as RatingCodeCount. Sort the results by the RatingCode in alphabetical order. Ensure
your result set returns the columns in the order indicated
SELECT DISTINCT RatingCode, COUNT(*) AS RatingCode
FROM Movie
GROUP BY RatingCode
ORDER BY RatingCode ASC;

SELECT Statement WHERE Clause
The WHERE clause is used to filter records & used to extract only those records that fulfill a specified condition
NOTE: The WHERE clause is not only used in SELECT statements, it is also used in UPDATE, DELETE, etc.!
WHERE Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition; (WHERE [designated column] = [designated value])
Ex: The Book table has the following columns:
ID – int, primary key, auto_increment
Title – variable-length string

, Genre – variable-length string
Year – int
Write a SQL query to retrieve the Title & Genre values for all records in the Book table with a Year value of
2020. Ensure your result set returns the columns in the order indicated?
SELECT Title, Genre
FROM Book
WHERE Year = 2020;
Ex: The Movie table has the following columns:
ID – int, primary key
Title – variable-length string
Genre – variable-length string
RatingCode – variable-length string
Year – int
Write a SQL query to return how many movies have a Year value of 2019 in the Movie table? (YOU DO NOT
USE A GROUP BY because you only have one category WHERE Year=2019)
SELECT COUNT (*)
FROM Movie
WHERE Year = 2019;
Ex: Write a SQL statement to retrieve all of the Customers from Seattle from the Customer table?
SELECT *
FROM Customer
WHERE City = ‘Seattle’;

SELECT Statement WHERE Clause with Logical Operators
The following operators can be used in the WHERE clause:
= Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> Not equal. Note: In some versions of SQL this operator may be written as !=
BETWEEN - Between a certain range
LIKE - Search for a pattern
IN - To specify multiple possible values for a column
The WHERE clause can be combined with AND, OR, and NOT operators
The AND and OR operators are used to filter records based on more than one condition:
The AND operator displays a record if all the conditions separated by AND are TRUE.
The OR operator displays a record if any of the conditions separated by OR is TRUE.
The NOT operator displays a record if the condition(s) is NOT TRUE
Ex: Write a query to list customers from Chicago or Seattle from the Customer table
SELECT *
FROM Customer
WHERE City = ‘Chicago’ OR City = ‘Seattle’;