Data Manipulation, Optimization, and Query Techniques

Text Functions

  • =TRIM()=TRIM(): Removes leading and trailing spaces.
  • =CLEAN()=CLEAN(): Removes all non-printable characters.
  • =LEFT()=LEFT(): Returns a number of characters from the left side of the field.
  • =RIGHT()=RIGHT(): Returns a number of characters from the right side of the field.
  • =MID()=MID(): Returns a number of characters from the middle of the field.
  • =UPPER()=UPPER(): Converts field to uppercase.
  • =LOWER()=LOWER(): Converts field to lower case.
  • =PROPER()=PROPER(): 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

  • WEEKDAY([Date])WEEKDAY([Date]): Returns a number 1 through 7 to designate the day of the week.
  • WEEKNUM([Date])WEEKNUM([Date]): Returns the week number of the year (1 through 52).
  • MONTH([Date])MONTH([Date]): Returns a number 1-12 to designate the month of the year.
  • DATEDIFF()DATEDIFF(): Calculates the number of days between two dates (start and end).
  • NETWORKDAYS()NETWORKDAYS(): Calculates the number of business days between a start and end date.
  • NOW()NOW(): Uses the computer system time to provide the current date and time.
  • TODAY()TODAY(): Provides the current date without the time.

Logical Functions and Conditional Formatting

  • IF()IF(): Specifies a logical test to perform.
  • IFS()IFS(): Checks multiple conditions and returns a value corresponding to the first TRUE condition.
  • AND()AND(): Returns TRUE if all arguments are TRUE.
  • OR()OR(): Returns TRUE if any argument is TRUE.
  • TRUE()TRUE(): Returns the logical value TRUE.
  • FALSE()FALSE(): Returns the logical value FALSE.
  • IFERROR()IFERROR(): Returns a specified value if a formula results in an error; otherwise, returns the formula's result.
  • NOT()NOT(): Reverses the logic of its argument.

Aggregation and the Basic Types of Aggregate Functions

  • SUM()SUM(): Adds all records together to produce a total.
  • COUNT()COUNT(): Counts all records as individual lines to produce a record count.
  • DISTINCTCOUNT()DISTINCT COUNT(): Counts all unique records in a column, even if a value appears multiple times.
  • AVERAGE()AVERAGE(): Totals all values in a column and divides by the count of values.
  • MAX()MAX(): Returns the largest value in a column.
  • MIN()MIN(): 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/2020
    
  • Parameterization

    • 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”