SQL Essentials Online Course
This course teaches you SQL from the ground up, starting with the relational model, schemas, data types, and table structures. You’ll set up a local Postgres database, practice creating tables, inserting data, and running queries with filtering and ordering. As you progress, you’ll explore advanced concepts like aggregations, joins, subqueries, unions, and window functions. By the end, you’ll be confident in applying SQL to real-world projects, integrating it with Python, and excelling in technical interviews or on the job.
Who should take this course?
This course is designed for beginners, students, and professionals who want to build a solid foundation in SQL for managing and querying databases. It’s also ideal for data analysts, developers, and business professionals seeking to work with structured data effectively.
What you will learn
- Understand SQL databases
- Understand the relational model
- Learn to insert data into SQL databases
- Extract data from SQL databases
- Learn about aggregations, subqueries, joins, and unions
- Learn about different window functions
Course Outline
Fundamentals and Setup
- Intro to Relational Databases
- Data Types and Schemas
- [Mac] Setting Up a Local PostgreSQL Server
- [Windows] Setting Up a Local PostgreSQL Server
- [Mac] SQL Editor Postico
- SQL Editor DataGrip
- SQL Editor CLI (Command Line Interface)
- Creating and Removing a Database
Databases, Tables, and Basic Queries
- Creating Schemas
- Creating Tables
- Altering Tables
- Enumerated Types
- Inserting Values
- Running an SQL Script
- Reading Data
- Creating Tables Using Query Results
- Aliases and Ordering
Conditionals and Arithmetic
- Conditional Filtering Direct Comparisons
- Conditional Filtering Grouped Comparisons
- Joining Conditionals
- Negating Conditionals
- Filtering Null Values
- Creating New Columns and Arithmetic
- Datatype Conversions
- Logic Statements
Dates, Times, and Manipulating Data
- Manipulating Strings
- String Positional Information
- String Subsets and Concatenations
- String Replacements
- Date and Time
- Date and Time Intervals
- Comments
- Aggregation Functions
- Grouping and Filtering Aggregations
Joins, Unions, and Subqueries
- Inner Joins
- Left, Right, and Outer Joins
- Self Joins
- Unions
- Subqueries
- CTEs
Window Functions
- Window Functions and Aliases
- Row Numbers and Ranks
- Using Other Rows with Window Functions
- NTiles
- Understanding Query Performance
Exercises
- Exercise 1
- Exercise 2
- Exercise 3
- Exercise 4
- Exercise 5