Data Manipulation, Optimization, and Query Techniques
Text Functions
- : Removes leading and trailing spaces.
- : Removes all non-printable characters.
- : Returns a number of characters from the left side of the field.
- : Returns a number of characters from the right side of the field.
- : Returns a number of characters from the middle of the field.
- : Converts field to uppercase.
- : Converts field to lower case.
- : Capitalizes each word.
Combining Data Fields
- The CONCATENATE function can be used to combine data fields in Microsoft Excel.
- Columns can also be merged in Excel Power Query.
Parsing Strings for Information
- Text to Columns and Delimiter can be used in Microsoft Excel to break apart a product number into fields.
- Split Columns in Microsoft Power Query can be used to create individual names.
Date Functions
- : Returns a number 1 through 7 to designate the day of the week.
- : Returns the week number of the year (1 through 52).
- : Returns a number 1-12 to designate the month of the year.
- : Calculates the number of days between two dates (start and end).
- : Calculates the number of business days between a start and end date.
- : Uses the computer system time to provide the current date and time.
- : Provides the current date without the time.
Logical Functions and Conditional Formatting
- : Specifies a logical test to perform.
- : Checks multiple conditions and returns a value corresponding to the first TRUE condition.
- : Returns TRUE if all arguments are TRUE.
- : Returns TRUE if any argument is TRUE.
- : Returns the logical value TRUE.
- : Returns the logical value FALSE.
- : Returns a specified value if a formula results in an error; otherwise, returns the formula's result.
- : Reverses the logic of its argument.
Aggregation and the Basic Types of Aggregate Functions
- : Adds all records together to produce a total.
- : Counts all records as individual lines to produce a record count.
- : Counts all unique records in a column, even if a value appears multiple times.
- : Totals all values in a column and divides by the count of values.
- : Returns the largest value in a column.
- : Returns the smallest value in a column.
System Functions
- Reporting tools often include system functions to avoid manual tracking of information.
- Examples include page numbers, file paths, and refresh dates.
Use Common Techniques for Query Optimization
Filtering Data
- A simple SELECT statement in SQL:
SELECT ORDERID, ORDERDATE, ORD_AMOUNT FROM Orders WHERE ORDERDATE > 1/1/2020Parameterization
- Parameters can be used to optimize data load time and filter data according to specified criteria.
Indexing Data
- Indexes/Keys are used to optimize data retrieval.
Temporary Tables
- Temporary tables are stored in Tempdb in Microsoft SQL Server Management Studio.
Sub Querying and Subsets of Information
- A subquery is a query within another query.
Query Execution Plan
- A query execution plan provides a visual representation of the steps taken to execute a query.
- Types of query execution plans include estimated and actual plans.
Lab Activity
- Types of labs:
- Assisted labs guide you step-by-step through tasks.
- Applied labs set goals with limited guidance.
- Complete lab
- Submit all items for grading and check each progress box
- Select “Grade Lab” from final page
- Save lab
- Select the hamburger menu and select “Save”
- Save up to two labs in progress for up to 7 days
- Cancel lab without grading
- Select the hamburger menu and select “End”