SQL Fundamentals
SQL Fundamentals teaches the relational model, schema design, and ANSI‑standard queries—from SELECT filtering to joins, aggregations, and window functions—while emphasizing performance best practices for reliable data handling.
Who Should Take This
Data analysts, business intelligence developers, and junior engineers who are new to relational databases and seek to write clean, efficient SQL will benefit. The course suits professionals with basic spreadsheet or query experience aiming to build solid querying skills, understand schema design, and improve data‑driven decision making.
What's Included in AccelaStudy® AI
Adaptive Knowledge Graph
Practice Questions
Lesson Modules
Console Simulator Labs
Exam Tips & Strategy
20 Activity Formats
Course Outline
68 learning goals
1
Relational Model and Schema Design
3 topics
Relational Model Concepts
- Identify the components of the relational model including tables, rows, columns, primary keys, and foreign keys and describe how they represent real-world entities and relationships
- Describe the properties of primary keys including uniqueness and NOT NULL constraints and explain the difference between natural keys and surrogate keys
- Explain how foreign keys enforce referential integrity between tables and describe the behavior of ON DELETE CASCADE, SET NULL, and RESTRICT actions
- Describe entity-relationship modeling concepts including entities, attributes, and cardinality (one-to-one, one-to-many, many-to-many) and how they map to table structures
Data Types and Table Creation
- Identify SQL data types including INTEGER, DECIMAL, VARCHAR, CHAR, DATE, TIMESTAMP, and BOOLEAN and describe appropriate use cases for each
- Implement CREATE TABLE statements with column definitions, data types, NOT NULL constraints, DEFAULT values, UNIQUE constraints, and CHECK constraints
- Implement ALTER TABLE statements to add, drop, and modify columns and constraints on existing tables
- Implement GENERATED ALWAYS AS columns for computed values and IDENTITY or SERIAL columns for auto-incrementing primary keys
Normalization
- Describe first, second, and third normal forms and explain how normalization reduces data redundancy and update anomalies
- Analyze a denormalized table schema and apply normalization rules to decompose it into properly normalized tables with appropriate foreign key relationships
- Evaluate the trade-offs between normalization for data integrity and denormalization for query performance in read-heavy versus write-heavy workloads
- Implement CREATE VIEW to define virtual tables from stored queries and explain how views simplify complex queries and support access control
2
SELECT Queries and Filtering
3 topics
Basic SELECT Syntax
- Describe the syntax and execution order of a SELECT statement including FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, and LIMIT clauses
- Implement SELECT queries with column selection, column aliases using AS, and DISTINCT to retrieve unique values from one or more columns
- Implement calculated columns and expressions in SELECT using arithmetic operators, string concatenation, and CASE expressions for conditional output
- Implement CASE expressions with searched and simple syntax for conditional logic within SELECT, WHERE, and ORDER BY clauses
WHERE Clause and Ordering
- Implement WHERE clause filtering using comparison operators, BETWEEN, IN, LIKE with wildcards, and IS NULL to restrict result sets based on conditions
- Implement compound WHERE conditions using AND, OR, and NOT logical operators with proper parenthetical grouping to express complex filter criteria
- Implement ORDER BY with single and multiple columns, ASC and DESC directions, and NULLS FIRST/LAST to control result set ordering
- Implement LIMIT and OFFSET (or FETCH FIRST / OFFSET ROWS) for pagination and explain the performance implications of large offset values
Built-in Functions
- Implement built-in scalar functions including UPPER, LOWER, TRIM, LENGTH, SUBSTRING, COALESCE, and NULLIF for data transformation within queries
- Implement date and time functions including CURRENT_DATE, CURRENT_TIMESTAMP, DATE_PART, DATE_TRUNC, and interval arithmetic for temporal data queries
- Implement CAST and implicit type conversion between data types and analyze how type mismatches in WHERE clauses can prevent index usage
- Analyze how NULL values propagate through arithmetic operations, comparisons, and aggregate functions and implement COALESCE and NULLIF to handle NULL safely
3
Joins and Subqueries
3 topics
Join Types and Multi-Table Queries
- Describe the types of joins including INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN and explain how each combines rows from two tables
- Implement INNER JOIN and LEFT JOIN queries using ON conditions to combine data from related tables based on foreign key relationships
- Implement multi-table joins chaining three or more tables and use table aliases to write readable queries against complex relational schemas
- Implement self-joins to query hierarchical or self-referencing data such as employee-manager relationships or category trees
- Analyze FULL OUTER JOIN and CROSS JOIN use cases and evaluate when each is appropriate for data reconciliation and generating Cartesian products
Subqueries
- Implement scalar subqueries in SELECT and WHERE clauses that return a single value for comparison or calculated column output
- Implement correlated subqueries that reference columns from the outer query and explain how they execute once per outer row
- Implement EXISTS and NOT EXISTS subqueries to test for the presence or absence of related rows and compare their behavior to IN and NOT IN
- Analyze when to use a JOIN versus a subquery and evaluate the readability and performance implications of each approach for common query patterns
- Implement derived tables (subqueries in the FROM clause) and compare their scoping rules and readability with Common Table Expressions
Set Operations and CTEs
- Implement UNION, UNION ALL, INTERSECT, and EXCEPT to combine result sets from multiple queries and explain the distinct-versus-all behavior of each
- Implement Common Table Expressions (CTEs) using WITH clauses to break complex queries into readable named subquery blocks
- Implement recursive CTEs using WITH RECURSIVE for hierarchical data traversal such as organizational charts, bill-of-materials, or category trees
4
Aggregation and Grouping
3 topics
Aggregate Functions and GROUP BY
- Describe aggregate functions COUNT, SUM, AVG, MIN, and MAX and explain how they collapse multiple rows into a single result value
- Implement GROUP BY to partition result sets into groups and apply aggregate functions to compute summary statistics per group
- Implement HAVING to filter grouped results based on aggregate conditions and explain how HAVING differs from WHERE in execution order
- Implement COUNT(*) versus COUNT(column) versus COUNT(DISTINCT column) and explain how each handles NULL values differently
Advanced Grouping
- Implement COUNT with DISTINCT to count unique values and use conditional aggregation with CASE inside aggregate functions to compute pivoted summaries
- Implement GROUP BY with ROLLUP and CUBE to generate subtotals and grand totals across multiple grouping dimensions
- Analyze grouping errors such as selecting non-aggregated columns not in GROUP BY and evaluate query correctness against SQL standard grouping rules
Window Functions
- Describe window functions and the OVER clause including PARTITION BY and ORDER BY and explain how they compute values across related rows without collapsing the result set
- Implement ROW_NUMBER, RANK, DENSE_RANK, and NTILE window functions to assign ordinal positions within partitions for ranking and pagination
- Implement LAG, LEAD, FIRST_VALUE, and LAST_VALUE window functions to access values from preceding and following rows for trend analysis and gap detection
- Implement running totals, moving averages, and cumulative aggregates using SUM and AVG with window frame specifications (ROWS BETWEEN)
- Analyze the difference between window functions and GROUP BY and evaluate when to use each for computing aggregates while retaining individual row detail
5
Data Modification
3 topics
INSERT, UPDATE, and DELETE
- Implement INSERT statements for single rows, multiple rows, and INSERT INTO ... SELECT to populate tables from query results
- Implement UPDATE statements with WHERE conditions to modify existing rows and use subqueries or joins in UPDATE to reference values from other tables
- Implement DELETE statements with WHERE conditions and explain the difference between DELETE, TRUNCATE, and DROP in terms of rollback capability and performance
- Implement INSERT with ON CONFLICT (PostgreSQL) or ON DUPLICATE KEY (MySQL) syntax for upsert operations on tables with unique constraints
Transactions
- Describe the ACID properties of database transactions and explain how BEGIN, COMMIT, and ROLLBACK maintain data consistency
- Implement transactions that group multiple DML operations and use SAVEPOINT for partial rollback within a transaction
- Analyze scenarios where concurrent transactions can cause dirty reads, non-repeatable reads, and phantom reads and describe how isolation levels address each anomaly
- Implement explicit locking with SELECT FOR UPDATE to prevent concurrent modification of critical rows within a transaction
Upsert and Constraint Interactions
- Implement MERGE or UPSERT patterns to insert new rows or update existing rows based on matching conditions in a single atomic statement
- Evaluate the impact of foreign key constraints on INSERT, UPDATE, and DELETE operations and analyze how cascading actions propagate changes across related tables
6
Indexing and Performance Basics
2 topics
Index Types and Creation
- Describe what indexes are, how B-tree indexes store sorted data, and explain how indexes accelerate lookups at the cost of additional write overhead and storage
- Implement CREATE INDEX statements for single-column and multi-column indexes and explain how column order in composite indexes affects query optimization
- Describe the difference between clustered and non-clustered indexes, unique indexes, and partial indexes and identify when each type is appropriate
- Implement covering indexes and expression-based indexes and analyze how including additional columns in an index enables index-only scans
Query Execution Plans
- Describe the purpose of EXPLAIN and EXPLAIN ANALYZE and identify key metrics in an execution plan including sequential scan, index scan, nested loop, hash join, and sort operations
- Analyze execution plans to identify full table scans, missing indexes, and suboptimal join strategies and recommend index additions to improve query performance
- Evaluate common query anti-patterns that prevent index usage including functions on indexed columns, leading wildcards in LIKE, implicit type conversions, and OR conditions on different columns
- Describe the difference between cost-based and rule-based query optimization and explain how table statistics influence the optimizer's choice of execution plan
- Implement ANALYZE or UPDATE STATISTICS commands to refresh table statistics and evaluate how stale statistics lead to suboptimal query plans
Hands-On Labs
15 labs
~385 min total
Console Simulator
Practice in a simulated cloud console or Python code sandbox — no account needed. Each lab runs entirely in your browser.
Scope
Included Topics
- ANSI SQL relational model concepts, schema design and normalization, data types, table creation and modification, SELECT queries with filtering and ordering, joins (inner, outer, cross, self), subqueries and CTEs, set operations, aggregate functions, GROUP BY and HAVING, window functions, INSERT/UPDATE/DELETE operations, transactions and ACID properties, indexing fundamentals, and query execution plan analysis
Not Covered
- Vendor-specific SQL dialects (PL/SQL, T-SQL, PL/pgSQL)
- Stored procedures, functions, and triggers
- Database administration (backup, replication, user management)
- NoSQL databases and document stores
- Object-relational mapping (ORM) frameworks
- Database migration tools
- Distributed databases and sharding
- Full-text search engines
Ready to master SQL Fundamentals?
Adaptive learning that maps your knowledge and closes your gaps.
Subscribe to Access