D465 DATA APPLICATIONS STUDY GUIDE COMPLETE LATEST UPDATED WESTERN GOVERNORS’ UNIVERSITY

Organizing data for analysis
The 4 Phases of analysis
1. Organize data
2. Format and adjust data
3. Ask for input
4. Transform data—means identifying relationships and finding patterns
Sorting & Filtering
Format for better analysis
Data Validation: Allows you to control what can and can’t be entered in your work.
• Add dropdown lists with predetermined options to choose from
• Create custom check boxes
• Protect structured data and formulas
Conditional formatting- a spreadsheet tool that changes how cells appear when values meet specific conditions.
Using SQL
Order by sorts the query in Ascending order by default.
DESC makes it descending order
ascending order represents numbers from lowest to highest
Descending order is highest to lowest
To filter use the WHERE clause AND
creates sub Filters
CONCAT and CONCATENATE Join 2 or more text strings
SQL Functions
COUNT returns the number of rows in a specified range.
COUNT DISTINCT only returns the distinct values in a specified range. This means it removes both duplicate rows
containing the same data and null values from the result set.
SQL functions are tools built into SQL to facilitate performing calculations
AVG() function to calculate the average salary of employees
Subquery, also called an inner or nested query, is a SQL query that is nested inside a larger
query
HAVING clause filters the results of a SQL query based on conditions applied after the grouping
CASE provides conditional logic in SQL queries, similar to an 'if-else' structure in programming
languages
IF performs a simple conditional test and returns a value depending on the outcome
COUNT performs a simple conditional test and returns a value depending on the outcome