10156125SQL Database Programming
Course Information
Description
Presents relational database concepts and teaches beginning to intermediate Structured Query Language (SQL) using cloud-hosted relational databases. Students learn techniques for extracting and transforming data from relational databases in increasingly complex ways. Students also learn to create robust structures and to store and manipulate the data within them. Demonstrations and hands-on practice reinforce the fundamental concepts.
Total Credits
3
Course Competencies
  1. 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

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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