SQL Essential Training: Query, Analyze, and Explore Relational Data

Course details

Embark on an intensive journey to elevate your data querying and database management skills. Explore modern data systems, unveiling the power of SQL with a foundational deep dive into relational database management and SQL syntax. Unearth the art of composing optimized SQL queries, including filtering, sorting, and joining data to extract meaningful insights. Gain proficiency in managing and manipulating data across complex tables, ensuring your skills align with current industry demands. Discover how to seamlessly integrate AI into your SQL workflow, understanding its pitfalls and enhancing your data analysis with machine learning techniques. Transform theoretical knowledge into practical expertise as you engage in realistic, problem-solving scenarios using a sample SQLite database environment. This course is ideal for aspiring data analysts, business professionals, and career switchers eager to become proficient in SQL and database environments. Whether you are an analyst, ops professional, or developer, this course will help to equip you with the durable, enterprise-ready skills to be able to make data-driven decisions confidently.

Concepts

Introduction

PixelMetrics - Solving a real business problem with SQL and AI

Modern Data Systems

PixelMetrics scenario - What data do we even have
Relational database theory
The SQLite database engine
AI workflow spotlight - When AI misreads a schema

The SQL Stack

WSDA Music - Meet the sample database

The SQLite Database Environment

Load the sample database file
Getting familiar with a database
The browse data area
The query writing area
AI workflow spotlight - What does AI miss when exploring a schema

Compose Queries

Queries
Query commenting
Query composition
Query composition best practices
Column custom names
Sorting query results
Limiting query results
Solution - Sales team contact directory
AI workflow spotlight - Write it yourself first, then ask AI to improve it

Discover Insights in Data

Types of SQL operators
Filter and analyze numeric data
BETWEEN and IN operators
Filter and analyze text data
Search records without an exact match
Filter and analyze using dates
Filter records based on more than one condition
Logical operator OR
Brackets and order
IF THEN logic with CASE
Solution - Filtering records with multiple operators
AI workflow spotlight - Can AI write a CASE statement a stakeholder would trust

Access Data from Multiple Tables

JOINs explained
How tables share a relationship, part 1
How tables share a relationship, part 2
Simplifying JOINs
Types of JOINs
The INNER JOIN
The LEFT JOIN
The RIGHT JOIN
Tables and entity relationship diagrams
Joining many tables
Solution - Invoice line item detail report
AI workflow spotlight - When AI gets the JOIN wrong

SQL Functions

PixelMetrics scenario - The data needs cleaning before it can tell a story
Calculate with functions
String, date, and aggregate function types
Connecting strings
Separating text
UPPER and LOWER string functions
Date functions
Aggregate functions
Nesting functions
Solution - Formatted staff contact list

Grouping

Group query results
Filter with a grouped condition
Group with the WHERE clause
Group with the HAVING clause
Group with WHERE and HAVING
Group by many fields
Solution - Active market revenue report
AI workflow spotlight - Turning a GROUP BY result into a stakeholder decision

Nesting Queries

Subqueries and aggregate functions
SELECT clause subquery
Aggregated subqueries
Non-aggregate subqueries
IN clause subquery
DISTINCT clause subquery
Solution - Get invoices above the company average

Stored Queries

View introduction
Create a view
Edit a view
Join views
Delete views
Solution - Create reusable employee directory
AI workflow spotlight - Using AI as a view-writing partner

Add, Modify, and Delete Data

Analysis and administration
Insert data
Update data
Delete data
Solution - Genre table maintenance
AI workflow spotlight - Reviewing AI-generated DML before it touches real data

Capstone - Integrate AI into Your SQL Workflow

Prepare for the capstone
PixelMetrics capstone - End-to-end AI-assisted SQL workflow

Scenario - Mock SQL Interview

Prepare for the Modern SQL Interview
Non-technical interview practice

Conclusion

Next steps - Your career as an AI-enabled data analyst