Unit 1: Data, Databases, and SQL

  • Define and explain the purpose of data, and the most widely used data types.
  • Understand the hierarchy of units used to calculate data size.
  • Define and explain the purpose of databases.
  • Understand relational vs. non-relational databases.
  • Use design and ethics principles to guide database use, including referential integrity and ACID.
  • Learn about the Structured Query Language (SQL) and its history.
  • Understand the main differences between various SQL flavors: PostgreSQL, MySQL, and SQLite.
  • Learn further details about PostgreSQL, which will be used in this course.
  • Examine the structure of a SQL database.
  • Use the SELECT statement to display all columns of a table.
  • Understand that SQL is case-insensitive.
  • Understand the purpose of the keyword NULL and SQL’s three-valued logic.
  • Order query results using ORDER BY.
  • Limit the number of results using the keyword LIMIT.
  • Use the SELECT statement to only display one column of interest.
  • Use the SELECT statement to display two or more columns.
  • Change the order of the displayed columns if needed.
  • Skip some rows in the output using the keyword OFFSET.
  • Order multiple columns at the same time.
  • Filter the results of queries using the keyword WHERE.
  • Access a table in a schema by typing schema.table.
  • Remove duplicate results with SELECT DISTINCT.
  • Use aggregate functions COUNT(), MIN(), MAX(), SUM() and AVG().
  • Narrow down queries using WHERE … LIKE … and WHERE … BETWEEN …
  • Combine multiple conditions in the search using the keywords AND, OR, NOT.

Unit 2: SQL Practice

  • Practice basic SQL queries by exploring the Northwind database and in particular the table Employees.
  • Practice basic SQL queries by exploring the table Products of the Northwind database.
  • Practice basic SQL queries by exploring the table Products of the Northwind database.
  • Use SQL for calculations
  • Use the WHERE … IN … clause.
  • Practice basic SQL queries by exploring the table Order Details of the Northwind database.
  • Create new columns and name them using the keyword AS
  • Group results for better readability using the GROUP BY … and GROUP BY … HAVING … clauses.
  • Practice basic SQL queries by exploring the Customers table of the Northwind database.

Unit 3: Data Visualization, Analysis, and Reporting

  • Basic and advanced operations with Excel spreadsheets.
  • Data visualization (functions, graphs, bar charts, pie charts, histograms, scatter plots, etc).
  • Basis statistical concepts.
  • Linear regression analysis.
  • Visualize regression plots.
  • Correlation, R and R squared.
  • Visualize correlation matrices and heat maps.
  • Fundamentals of statistical hypothesis testing.
  • Design components for reports and dashboards.
  • Report cover page.
  • Design elements.
  • Documentation elements.
  • Static vs. dynamic reports.
  • Ad-hoc/one-time report.
  • Self-service/on demand.
  • Recurring reports.
  • Tactical/research reports.