50-156-702Intermediate SQL
Course Information
Description
The Intermediate SQL course reviews the construction of a SQL query joining multiple tables using both INNER and OUTER joins, subqueries, scalar functions and aggregate functions. Apprentices add, modify and remove data records using DML statements; identify and create appropriate indexes to improve system query performance; and analyze the results for accuracy and completeness as well as review query performance for review and optimization.
Total Credits
2
Course Competencies
  1. Create SQL statements
    Assessment Strategies
    Oral, Written and/or Skill Assessment
    Criteria
    apprentice creates a query using joins
    apprentice analyzes when to use an inner vs. an outer join
    apprentice determines when to use a subquery in building a SQL statement
    apprentice creates a query utilizing scalar functions
    apprentice creates a query utilizing aggregate functions

  2. Manipulate data
    Assessment Strategies
    Oral, Written and/or Skill Assessment
    Criteria
    apprentice inserts a data record
    apprentice modifies a data record
    apprentice removes a data record
    apprentice inserts records based on query result
    apprentice updates records based on query result
    apprentice deletes records based on query result

  3. Analyze query performance
    Assessment Strategies
    Oral, Written and/or Skill Assessment
    Criteria
    apprentice creates clustered indexes
    apprentice creates non-clustered indexes
    apprentice explains the order of execution in a SQL SELECT statement
    apprentice creates queries that are SARGable
    apprentice analyzes query execution plan for index utilization
    apprentice recommends changes to queries or indexes to improve query performance

  4. Evaluate query results
    Assessment Strategies
    Oral, Written and/or Skill Assessment
    Criteria
    apprentice reviews results to ensure extra data is not included
    apprentice reviews results to ensure data is not missing
    apprentice reviews results to ensure that business need is met
    apprentice reviews results for correct data points and completeness