D191 - Advanced Data Management

(WGU-D191.AJ1)/ISBN:WGU-D-191.A-J1-

This course includes
Lessons
TestPrep
Hands-On Labs
D191 - Advanced Data Management

Lessons

10+ Lessons | 40+ Flashcards | 40+ Glossary of terms

TestPrep

Hands-On Labs

87+ LiveLab | 00+ Minutes

Need guidance and support? Click here to check our Instructor Led Course.

Here's what you will learn

Download Course Outline

Lessons 1: Welcome to Advanced Data Management

  • About this Course
  • About the Labs in this Course
  • Learning Resources

Lessons 2: OPTIONAL: Understanding and Describing Data

  • Introduction
  • The World of Data
  • Methods of Descriptive Statistics
  • Statistical Significance Testing
  • Summary

Lessons 3: The Basics of SQL for Analytics

  • Introduction
  • Relational Databases and SQL
  • Basic Data Types of SQL
  • Reading Tables: The SELECT Query
  • Creating Tables
  • Updating Tables
  • Deleting Data and Tables
  • SQL and Analytics
  • Summary

Lessons 4: SQL for Data Preparation

  • Introduction
  • Assembling Data
  • Transforming Data
  • Summary

Lessons 5: Aggregate Functions for Data Analysis

  • Introduction
  • Aggregate Functions
  • Aggregate Functions with GROUP BY
  • The HAVING Clause
  • Using Aggregates to Clean Data and Examine Data Quality
  • Summary

Lessons 6: OPTIONAL: Window Functions for Data Analysis

  • Introduction
  • Window Functions
  • Statistics with Window Functions
  • Summary

Lessons 7: Importing and Exporting Data

  • Introduction
  • The COPY Command
  • Using R with Our Database
  • Using Python with Our Database
  • Best Practices for Importing and Exporting Data
  • Summary

Lessons 8: OPTIONAL: Analytics Using Complex Data Types

  • Introduction
  • Date and Time Data Types for Analysis
  • Performing Geospatial Analysis in Postgres
  • Using Array Data Types in Postgres
  • Using JSON Data Types in Postgres
  • Text Analytics Using Postgres
  • Summary

Lessons 9: Performant SQL

  • Introduction
  • Database Scanning Methods
  • Performant Joins
  • Functions and Triggers
  • Summary

Lessons 10: OPTIONAL: Using SQL to Uncover the Truth – a Case Study

  • Introduction
  • Case Study
  • Summary

Hands-on LAB Activities

Welcome to Advanced Data Management

  • Sandbox Lab
  • PostgreSQL

The Basics of SQL for Analytics

  • Conceptual Schema - ER Model
  • Physical Schema - ER Model
  • Creating a Database
  • Creating a Database
  • Create Table
  • Display Table and Table Structure
  • Creating a Table
  • Creating a Table to Store Encryption Keys
  • Create Table
  • Display Table and Table Structure
  • Creating a Table
  • Creating a Table to Store Login Information
  • Retrieving Data from a Table
  • Retrieving Specific Column Data from a Table
  • Selecting Specific Columns from a Table
  • Retrieving Data from a Table
  • Retrieving Specific Column Data from a Table
  • Update Data in a Table
  • Updating Data in a Table
  • Updating Specific Data in a Table
  • Adding Data in the Table
  • Update Data in a Table
  • Adding New Data in a Table
  • Updating Data in a Table
  • Updating Specific Data in a Table
  • Adding Data in the Table
  • Adding New Data in a Table
  • Copying Data from One Table to Another Table
  • Copying Data from One Table to Another Table
  • Selecting Specific Columns from a Table
  • Adding a Column in a Table
  • Modifying a Column in a Table
  • Adding a Comment
  • Inserting Data from One Table to Another
  • Adding a New Column in a Table
  • Using ALTER Command to Modify a Column in a Table
  • Adding a Column in a Table to Store timestamp
  • Modifying a Column in a Table to Include a Comment
  • Adding a Column in a Table
  • Modifying a Column in a Table
  • Adding a Comment
  • Inserting Data from One Table to Another
  • Adding a New Column in a Table
  • Using ALTER Command to Modify a Column in a Table
  • Adding a Column in a Table to Store Timestamp
  • Modifying a Column in a Table to Include a Comment
  • Dropping a Database
  • Delete Rows
  • Deleting Specific Data from a Table
  • Delete Rows
  • Deleting Specific Data from a Table
  • Deleting a Table
  • Deleting All the Data from a Table
  • Deleting a Column from a Table
  • Deleting a Table
  • Deleting All the Data from a Table
  • Deleting a Column from a Table

SQL for Data Preparation

  • Joining Tables
  • Joining Tables
  • Using DISTINCT Keyword
  • Using DISTINCT Keyword

Aggregate Functions for Data Analysis

  • Using COUNT Function in GROUP BY Clause
  • Using CONCAT Function
  • Using CONCAT Function
  • Using ORDER BY Clause
  • Displaying Data in Alphabetical Order
  • Using GROUP BY Clause
  • Using ORDER BY Clause
  • Displaying Data in Alphabetical Order
  • Using GROUP BY Clause
  • Using COUNT Function in GROUP BY Clause
  • Using Operators in GROUP BY Clause
  • Using Operators in GROUP BY Clause
  • Normalizing the Unnormalized Model to 1NF
  • Normalizing from 1NF to 2NF
  • Normalizing the 2NF to 3NF

Importing and Exporting Data

  • Create a View
  • Creating a View
  • Creating a View Using UNION Operator
  • Create a View
  • Creating a View
  • Creating a View Using the UNION Operator

Performant SQL

  • Create an Index
  • Create an Index
  • Creating a Function