-
Create SQL SELECT queries that filter and sort table data
Assessment Strategies
Skill demonstration in lab, quiz, SQL report generation project
Criteria
Write SELECT statements that retrieve any or all columns from a table
Utilize WHERE clauses that filter data on simple and compound conditions
Sort result rows based on one or more columns using ORDER BY
Eliminate duplicate rows of results using DISTINCT
Appropriately utilize aliases to rename result columns
Create readable queries that accurately match desired results
-
Create SQL SELECT queries to join records from multiple tables
Assessment Strategies
Skill demonstration in lab, quiz, SQL report generation project
Criteria
Write SELECT statements that retrieve records from two or more tables using foreign key relationships
Retrieve matching and unmatched records using outer joins
Apply compound join conditions on non-key fields to link records in more complex ways
Nest joins when necessary to correctly combine inner and outer joins
Utilize self-joins for multiple matches and recursive relationships
Utilize table aliases to disambiguate commonly named fields
-
Utilize SQL functions and expressions to transform data values
Assessment Strategies
Skill demonstration in lab, quiz, SQL report generation project
Criteria
Combine multiple values and extract components of string values
Calculate numerical values using arithmetic operators
Analyze components of individual dates or combinations of dates
Utilize CASE to create new values based on the condition of other values
Transform numerical and date values to match a specific format
Apply different techniques for managing missing values in expressions
Research and apply DBMS vendor-specific functions appropriately
-
Produce queries that report filtered aggregates using GROUP BY and HAVING
Assessment Strategies
Skill demonstration in lab, quiz, SQL report generation project
Criteria
Write SELECT statements that summarize multiple rows of data into individual values
Calculate single values across multiple rows using aggregate functions
Define single and multi-level grouping criteria using GROUP BY
Utilize HAVING to filter group rows using aggregate values
-
Produce queries that combine rows from multiple relations
Assessment Strategies
Skill demonstration in lab, exploratory data analysis project
Criteria
Write queries that utilize SQL set theory functions to merge rows from multiple SELECT statements
Utilize UNION and UNION ALL to combine multiple relations with and without duplicate rows
Apply vendor-specific techniques for performing set intersection and set difference
-
Calculate values for a row based on a set of related values using windowing
Assessment Strategies
Skill demonstration in lab, exploratory data analysis project
Criteria
Write SELECT queries that calculate sub-aggregates, cumulative aggregates and ranks (top-n)
Apply both general aggregate and windowing functions to groups of related rows defined by OVER()
Divide windows into subsegments with criteria defined using PARTITION BY
Utilize ORDER BY() to calculate cumulative aggregates, recognizing difference from general ORDER BY
Identify appropriate use cases for windowing vs. aggregation with GROUP BY
-
Produce layered queries that combine results from multiple queries
Assessment Strategies
Skill demonstration in lab, exploratory data analysis project
Criteria
Write scalar and list subqueries to calculate individual or list values within a query
Utilize correlated subqueries to connect subquery rows to the outer query
Test for existence or nonexistence of row data within a query using EXISTS
Create common table expressions (CTEs) to define intermediate result tables in complex queries
Utilize views to create virtual tables defined by queries
Decompose larger data analysis problems into combinations of smaller queries
Identify equivalent queries that do and do not utilize subqueries Write readable queries by utilizing subqueries only when necessary
-
Identify and design relational database structures
Assessment Strategies
Skill demonstration in lab, quiz, database creation project
Criteria
Identify functional dependencies in a dataset to define an optimal set of data themes
Create schemas that eliminate data redundancy through normalization
Properly recognize that a database schema is normalized (in 3rd normal form)
Describe the strengths and weaknesses of transactional (OLTP) and analytical (OLAP) schemas
-
Create and manage database objects
Assessment Strategies
Skill demonstration in lab, quiz, database creation project
Criteria
Define database objects, such as tables, to match a database design
Create tables, and columns with a table, with appropriate names
Remove unneeded tables using DROP
Modify the names of existing tables and table columns
Define appropriate primary keys, including composite and surrogate keys
Improve query performance by creating indexes on tables
Write scripts for deploying database schemas across multiple environments
-
Define structural rules that constrain data
Assessment Strategies
Skill demonstration in lab, quiz, database creation project
Criteria
Create a database schema with rules enforced to help reject poorly formed data
Select the most appropriate datatype for a range of data values
Appropriately enforce requirement of column values
Define rules to reject duplicate values or ranges of values from a column
Enforce referential integrity of foreign key values
-
Manipulate and maintain table data
Assessment Strategies
Skill demonstration in lab, quiz, database creation project
Criteria
Insert single rows of static data into a table
Insert rows of data into a table by querying one or more tables
Update columns within existing tables rows that meet simple or complex criteria
Remove rows of data from a table, possibly limited to those meeting a given criteria
Appropriately apply DELETE and TRUNCATE by understanding the difference between the two commands
Combine multiple data manipulation queries into a single unit of work using transactions