SQLFoundation
Master all SQL fundamentals from data types to subqueries and transactions. This comprehensive course covers everything all database professionals must know about SQL.
Free Preview Available
Watch the first 2 minutes of every lesson to explore SQL fundamentals. This is our most comprehensive SQL course with hands-on exercises and quizzes.
What Makes This Course Special
- Interactive quizzes and assignments
- Downloadable workbooks and exercises
- Real-world examples and use cases
- PostgreSQL-specific features and syntax
Course Lessons
SQL Fundamentals
This foundational lecture introduces you to the SQL language structure and its fundamental command categories that form the backbone of all database interactions. You'll learn how SQL organizes commands into five distinct categories: DDL (Data Definition Language) for database structure management, DML (Data Manipulation Language) for data operations, DQL (Data Query Language) for data retrieval, DCL (Data Control Language) for access control, and TCL (Transaction Control Language) for transaction management. The lesson emphasizes SQL's declarative nature, where you specify what data you want rather than how to retrieve it, allowing PostgreSQL's query planner to determine the most efficient execution strategy.
All About Data Types - Parts I, II, and III
Part A introduces the fundamental concepts of data types and their critical importance in database design. You'll explore the underlying mechanisms of how computers store and represent data. Part B explores PostgreSQL's numeric and text data types, providing essential knowledge for effective database design and data storage optimization. Finally, Parc C covers PostgreSQL's temporal data types, specialized data types, and type conversion mechanisms essential for comprehensive database development.
Basic Database Object Creation
This lesson establishes the foundation of PostgreSQL's organizational hierarchy by exploring databases, schemas, and tablespaces. You'll learn how to create databases using the CREATE DATABASE command and understand key parameters like OWNER, TEMPLATE, ENCODING, and TABLESPACE. The lesson covers PostgreSQL's template system, including the roles of template0 (pristine template) and template1 (customizable default template), and how to create custom templates for standardizing database deployments.
DDL - Table Creation
This lesson focuses on the practical aspects of creating tables in PostgreSQL, building upon the database object foundation from the previous lesson. You'll master the CREATE TABLE syntax, learn about schema qualification for organizing tables within specific schemas, and explore temporary tables with their various ON COMMIT behaviors (DROP, DELETE ROWS, PRESERVE ROWS). The lesson covers essential table creation concepts including proper naming conventions, session-specific table behavior, and when temporary tables provide performance advantages for complex operations.
DDL - Sequence, Key and Index Creation
This lesson completes the foundation of PostgreSQL database objects by covering sequences, keys, and indexes - essential components for data integrity and performance optimization. You'll master PostgreSQL sequences as special database objects that generate unique numeric values, learning the CREATE SEQUENCE syntax with parameters like INCREMENT BY, START WITH, MINVALUE, MAXVALUE, CACHE, and CYCLE options. The lesson covers sequence functions (nextval, currval, lastval, setval) and explains the session-based caching behavior that improves concurrency while potentially creating gaps in sequence values.
DDL - Basic Database Object Management
This lesson explores the essential operations for managing PostgreSQL's fundamental container objects after creation. You'll learn how to modify databases using ALTER DATABASE operations including renaming, changing ownership, setting configuration parameters, and understanding the parameter hierarchy from global defaults to session-level settings. The lesson covers safe database removal practices with DROP DATABASE, emphasizing backup recommendations and the distinction between regular drops and forced removal with active connections.
DDL - Table Management
This lesson provides comprehensive coverage of PostgreSQL table structure management and column operations, essential skills for maintaining and evolving database schemas. You'll master table-level operations including renaming with ALTER TABLE RENAME TO, understanding the impact on dependent objects like views and functions that reference table names. The lesson covers ownership transfers, tablespace changes for performance optimization, and safe table removal using DROP TABLE with CASCADE and RESTRICT options, including performance implications for large tables.
DDL - Constraint, Sequence and Index Management
This comprehensive lesson completes the DDL command series by covering advanced database object management including constraints, sequences, and indexes. You'll master constraint management through ALTER TABLE operations, learning to add various constraint types (primary key, unique, check, foreign key) with special attention to the NOT VALID option for large tables and subsequent validation techniques. The lesson covers constraint modification strategies, removal with CASCADE options, and the critical management of primary and foreign key relationships with their performance implications.
DML - Data Insertion
This lesson introduces PostgreSQL's data insertion capabilities, starting with fundamental INSERT operations for single and multiple rows. You'll master essential techniques including using DEFAULT values, expressions, and functions within INSERT statements, along with the powerful RETURNING clause to retrieve information about inserted data. The lesson covers advanced insertion scenarios such as INSERT INTO...SELECT for transferring data between tables and upsert operations using INSERT ON CONFLICT to handle duplicate key situations gracefully.
DML - Data Modification (UPDATE and MERGE)
This lesson provides comprehensive coverage of PostgreSQL's data modification capabilities through UPDATE and MERGE operations. You'll master UPDATE command fundamentals including single and multiple column updates, conditional modifications with WHERE clauses, and complex scenarios involving subqueries and self-joins. The lesson covers advanced UPDATE techniques such as using table aliases, the USING clause for multi-table operations, and the RETURNING clause for retrieving modified data. Special attention is given to concurrent update considerations and error handling strategies essential for production environments.
DML - Data Removal (DELETE and TRUNCATE)
This lesson explores PostgreSQL's data removal capabilities through DELETE and TRUNCATE operations, emphasizing safe and efficient techniques for removing data. You'll master DELETE command fundamentals including conditional deletes with WHERE clauses, multi-table deletes using the USING clause, and handling foreign key relationships with various ON DELETE options. The lesson covers advanced DELETE techniques such as batched deletes for large datasets, using RETURNING clauses for verification, and performance considerations for bulk deletion operations.
DQL - Querying Basics
This foundational lesson introduces the essential components of PostgreSQL's Data Query Language (DQL), focusing on the SELECT statement and fundamental querying techniques. Students learn to retrieve and manipulate data using a gaming leaderboard database, exploring how to select specific columns, eliminate duplicates with DISTINCT and DISTINCT ON, and apply various filtering conditions through the WHERE clause. The lesson emphasizes practical applications while building a solid understanding of query anatomy and data projection principles.
DQL - Querying Special Features
This lesson explores PostgreSQL's advanced querying capabilities, beginning with core variables and functions that provide essential system and session information. Students learn the distinction between SQL-standard functions (like `current_timestamp`) and PostgreSQL-specific functions (like `version()`), understanding syntax conventions and their practical applications. The lesson covers system information functions, session context functions, and connection details, teaching students how to access help documentation and query system catalogs for comprehensive function information.
DQL - Data Type Operations (String, Numbers and Temporal Types)
This comprehensive lesson delves into PostgreSQL's extensive data type manipulation capabilities, starting with string operations essential for text processing. Students master concatenation techniques using both the `||` operator and `CONCAT` functions, learning critical differences in NULL handling. The lesson progresses through essential string functions including `LENGTH`, `UPPER`, `LOWER`, `SUBSTRING`, and the `TRIM` family, providing students with tools for data cleaning and standardization. Advanced text manipulation covers `REPLACE`, `SPLIT_PART`, and PostgreSQL's powerful regular expression capabilities using operators like `~` and functions like `REGEXP_REPLACE`.
DQL - Data Type Operations (Null Handling and Type Conversions)
This lesson focuses on two critical aspects of data manipulation in PostgreSQL: robust NULL value handling and safe type conversions. Students master the `COALESCE` function for providing default values and creating fallback chains, learning to handle missing data gracefully in production applications. The lesson covers `NULLIF` for converting specific values to NULL, and demonstrates powerful patterns that combine both functions to create robust data handling solutions that prevent query failures and improve user experience.
DQL - Basic Data Aggregation
This lesson introduces the fundamental concept of data aggregation in PostgreSQL, marking a significant shift from retrieving individual records to generating meaningful summary insights. Students learn how aggregation transforms detailed data into actionable business intelligence by changing the dimensionality of result sets. The lesson covers core aggregate functions including COUNT variations, SUM, AVG, MIN, MAX, and statistical functions like STDDEV and VARIANCE, with practical examples using an e-commerce database to demonstrate real-world applications such as calculating total revenue, average order values, and inventory statistics.
DQL - Understanding Table Joins
This comprehensive lesson explores one of the most powerful features of relational databases: the ability to combine related data from multiple tables through join operations. Students learn the conceptual foundation of joins, understanding how PostgreSQL creates Cartesian products and applies join conditions to filter meaningful relationships. The lesson covers all major join types including INNER JOIN for matching records only, and the complete family of OUTER JOINs (LEFT, RIGHT, and FULL) for including unmatched data, with practical examples using an e-commerce database to demonstrate real-world scenarios like finding customers without orders or identifying data integrity issues.
DQL - SET Operations
This lesson introduces PostgreSQL's powerful set operations based on mathematical set theory, enabling students to combine and compare result sets from different queries in sophisticated ways. Students learn the fundamental set operations of UNION, INTERSECT, and EXCEPT, along with their ALL variations that handle duplicates differently. The lesson demonstrates practical applications using both e-commerce and CRM system integration scenarios, showing how UNION combines customer and contact data, INTERSECT identifies overlapping records for data quality analysis, and EXCEPT finds differences between datasets for gap analysis and opportunity identification.
DQL - Basic Subqueries
This final foundational DQL lesson introduces subqueries as PostgreSQL's most flexible feature for nesting queries within other queries, completing students' essential SQL toolkit. Students learn to classify subqueries by their return types (scalar, row, and table subqueries) and understand their strategic placement in different parts of SQL statements including WHERE, SELECT, and FROM clauses. The lesson demonstrates practical applications such as finding products above average prices, creating dynamic thresholds, and building derived tables for complex analysis, with comprehensive examples using the e-commerce database to solve real business problems.
DCL - Creating Roles and Granting Permissions
This lesson introduces Data Control Language and PostgreSQL's comprehensive security model for managing database access. Students learn to create and configure database roles, which serve as the foundation of PostgreSQL's unified approach to users and groups, where every database participant is represented as a role with specific attributes and capabilities.
DCL - Managing Roles and Revoking Privileges
This lesson completes the exploration of Data Control Language by focusing on the comprehensive lifecycle management of PostgreSQL roles and the precise control of privilege revocation. Students master the ALTER ROLE command for modifying role attributes including authentication settings, resource limitations, and password policies, while learning proper techniques for role removal that account for object ownership and dependency management.
TCL - Transaction Fundamentals
This lesson completes the SQL Foundation section by introducing Transaction Control Language, the essential framework for maintaining data integrity during complex database operations. Students learn to understand transactions as logical units of work that ensure all-or-nothing execution, mastering the fundamental concepts of atomicity, consistency, and isolation that underpin reliable database systems.
Unlock Advanced Learning
This comprehensive SQL course is available to premium members. Start with our free courses:
- Database Fundamentals (FREE)
- Environment Setup (FREE)
- "Postgres Everywhere" eBook (FREE)
- Community access
Then upgrade to access SQL Foundation