Coming Soon

Expected availability announced soon

This course is in active development. Preview the scope below and create a free account to be notified the moment it goes live.

Notify me

General Knowledge Coming Soon

SQL and Databases

The course teaches core SQL commands, aggregation, joins, subqueries, set operations, and window functions, emphasizing correct, efficient query writing and normalized schema design using ANSI SQL standards.

Who Should Take This

It is ideal for aspiring data analysts, junior developers, or business professionals who have little to no experience with relational databases and want to build a solid foundation in SQL. Learners aim to write reliable queries, analyze data effectively, and design clean, normalized data models for real‑world applications.

What's Included in AccelaStudy® AI

Adaptive Knowledge Graph

Practice Questions

Lesson Modules

Console Simulator Labs

Exam Tips & Strategy

20 Activity Formats

Course Outline

27 learning goals

1 Basic Queries

1 topic

SELECT, Filter, and Sort

Write SELECT statements to retrieve specific columns and all columns from a table, apply column aliases with AS, use DISTINCT to eliminate duplicates, and sort results with ORDER BY (ASC/DESC, multiple columns).
Filter rows using WHERE with comparison operators, BETWEEN, IN, LIKE with wildcards (% and _), IS NULL/IS NOT NULL, and combine conditions using AND, OR, NOT with correct precedence.
Use LIMIT and OFFSET for pagination, apply expressions and CASE WHEN in the SELECT clause for computed columns, and use COALESCE and NULLIF to handle null values in query results.

2 Aggregation and Grouping

1 topic

Aggregate Functions and GROUP BY

Apply aggregate functions (COUNT, SUM, AVG, MIN, MAX) to compute summary statistics, using COUNT(*) vs COUNT(column) and understanding how NULL values are handled by each aggregate.
Group rows using GROUP BY with one or multiple columns, filter groups using HAVING (distinguishing it from WHERE), and explain the SQL logical execution order (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY).

3 Joins

1 topic

Join Types and Multi-Table Queries

Write INNER JOIN queries to combine rows from two or more tables on matching key columns, using table aliases and qualifying ambiguous column names with table prefixes.
Explain and apply LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN, predicting which rows appear in results when there are non-matching keys and how NULL values fill unmatched columns.
Write self-joins to relate rows within the same table (e.g., employee-manager relationships) and CROSS JOINs to generate Cartesian products, identifying when each join type is appropriate.
Combine joins with aggregation to produce summary reports across multiple tables, such as counting orders per customer or computing total revenue by product category.

4 Subqueries and Set Operations

2 topics

Subqueries

Write scalar subqueries in the WHERE clause and SELECT list, column subqueries with IN, ANY, and ALL operators, and derived tables (subqueries in FROM) with proper aliasing.
Write correlated subqueries that reference the outer query and use EXISTS/NOT EXISTS for semi-joins and anti-joins, explaining the row-by-row execution model and when correlated subqueries outperform joins.

Set Operations and CTEs

Combine result sets using UNION, UNION ALL, INTERSECT, and EXCEPT, explaining the column compatibility requirement and when to use UNION ALL for performance over UNION.
Write Common Table Expressions (WITH clause) for named temporary result sets that improve query readability, and implement recursive CTEs to traverse hierarchical data (org charts, category trees, bill of materials).

5 Window Functions

1 topic

Window Function Syntax and Applications

Apply ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE) with OVER(PARTITION BY ... ORDER BY ...) to assign row numbers, ranks, and percentile buckets within partitions.
Use LAG and LEAD window functions to access values from previous and subsequent rows within a partition, solving problems like calculating period-over-period changes and identifying consecutive sequences.
Apply aggregate window functions (SUM, AVG, COUNT over windows) with frame specifications (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) to compute running totals, moving averages, and cumulative distributions.

6 Data Manipulation

1 topic

INSERT, UPDATE, DELETE

Write INSERT statements for single and multiple rows, INSERT from SELECT, UPDATE with WHERE conditions and joined tables, and DELETE with WHERE, explaining the difference between DELETE and TRUNCATE.
Implement upsert patterns using INSERT ON CONFLICT (PostgreSQL) or MERGE (SQL Server/Oracle) to handle insert-or-update logic atomically, choosing the right approach for idempotent data loading.

7 Schema Design and Normalization

2 topics

Table Design and Constraints

Create tables with appropriate data types (INTEGER, VARCHAR, TEXT, DATE, TIMESTAMP, BOOLEAN, NUMERIC), define PRIMARY KEY (single and composite), and apply NOT NULL, UNIQUE, DEFAULT, and CHECK constraints.
Define FOREIGN KEY constraints to establish relationships between tables, choose between CASCADE, SET NULL, and RESTRICT referential actions for ON DELETE and ON UPDATE, and use ALTER TABLE to modify existing schemas.
Create and use views (CREATE VIEW) for query encapsulation and access control, and explain the concept of materialized views for precomputed query results with tradeoffs in freshness vs performance.

Normalization

Identify functional dependencies in a relation and apply normalization rules to decompose tables through 1NF, 2NF, 3NF, and BCNF, explaining the anomalies each normal form eliminates (insertion, update, deletion anomalies).
Evaluate when to denormalize a schema for read performance, adding redundant columns or summary tables, and explain the tradeoffs between normalized (write-optimized, consistent) and denormalized (read-optimized, potentially inconsistent) designs.

8 Indexes and Transactions

2 topics

Indexes and Query Performance

Explain how B-tree indexes accelerate lookups and range scans, create single-column and composite indexes, and predict which queries benefit from a given index based on the leftmost prefix rule.
Evaluate the tradeoff between read performance gains from indexes and write performance costs (insert/update/delete overhead), and identify when covering indexes eliminate the need for table lookups.

Transactions and Concurrency

Explain the four ACID properties (Atomicity, Consistency, Isolation, Durability) and use BEGIN, COMMIT, and ROLLBACK to group SQL statements into atomic units of work.
Describe the four SQL isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) and the concurrency anomalies each permits (dirty reads, non-repeatable reads, phantom reads), choosing the appropriate level for a given consistency requirement.

Scope

Included Topics

SQL query fundamentals: SELECT, FROM, WHERE, ORDER BY, LIMIT/OFFSET, DISTINCT, column aliases, and basic expressions in the SELECT clause.
Filtering and operators: comparison operators, BETWEEN, IN, LIKE (wildcards), IS NULL/IS NOT NULL, AND/OR/NOT, and operator precedence.
Aggregation: COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING, and the distinction between WHERE (row-level) and HAVING (group-level) filtering.
Joins: INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN, CROSS JOIN, self-joins, join conditions (ON clause), natural joins, and joining multiple tables in a single query.
Subqueries: scalar subqueries, column subqueries with IN/ANY/ALL, correlated subqueries, EXISTS/NOT EXISTS, subqueries in FROM (derived tables), and subqueries in SELECT.
Set operations: UNION, UNION ALL, INTERSECT, EXCEPT, and their requirements for compatible column lists.
Window functions: OVER clause, PARTITION BY, ORDER BY within OVER, ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, SUM/AVG/COUNT as window aggregates, and frame specifications (ROWS BETWEEN).
Data manipulation: INSERT (single and multi-row), UPDATE with WHERE and joins, DELETE with WHERE, UPSERT patterns (INSERT ON CONFLICT / MERGE), and TRUNCATE.
Schema design: CREATE TABLE, data types (INTEGER, VARCHAR, TEXT, DATE, TIMESTAMP, BOOLEAN, NUMERIC/DECIMAL), PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, DEFAULT, CHECK constraints, and ALTER TABLE.
Normalization: first normal form (1NF), second normal form (2NF), third normal form (3NF), Boyce-Codd normal form (BCNF), functional dependencies, identifying normalization violations, and denormalization tradeoffs.
Indexes: B-tree indexes, CREATE INDEX, composite indexes, covering indexes, unique indexes, index selection for common query patterns, and the tradeoff between read performance and write overhead.
Transactions: ACID properties (Atomicity, Consistency, Isolation, Durability), BEGIN/COMMIT/ROLLBACK, isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE), and common anomalies (dirty reads, non-repeatable reads, phantom reads).
Common Table Expressions (CTEs): WITH clause, recursive CTEs for hierarchical data traversal, and using CTEs for query readability and reuse.
Views: CREATE VIEW, updatable views, materialized views conceptually, and using views for access control and query simplification.

Not Covered

Database administration: backup/restore, replication, clustering, sharding, and performance tuning at the DBA level.
NoSQL databases (MongoDB, Redis, Cassandra, DynamoDB) and non-relational data modeling.
ORM frameworks (SQLAlchemy, Django ORM, Hibernate, ActiveRecord) and application-level database access patterns.
Vendor-specific SQL extensions and proprietary features (PL/pgSQL, T-SQL, PL/SQL) beyond standard SQL.
Database internals: query optimizer implementation, storage engines, WAL, MVCC implementation details.
Data warehousing, ETL pipelines, OLAP cubes, and star/snowflake schema design beyond basic normalization.

SQL and Databases is coming soon

Adaptive learning that maps your knowledge and closes your gaps.

Create Free Account to Be Notified