SQL Interview

0%Certificate0/136

Join the Community

The Relational Model

Keys and Constraints

Denormalization

The SELECT Statement

Exercise - SELECT Statement

Filtering with WHERE

Exercise - Filtering with WHERE

Exercise - BETWEEN and IN

LIKE and Pattern Matching

Exercise - LIKE and Pattern Matching

NULL Handling and COALESCE

Exercise - NULL Handling and COALESCE

CASE WHEN Expressions

Exercise - CASE WHEN Expressions

Sorting and Pagination

Exercise - Sorting and Pagination

Exercise - DISTINCT

Scalar Functions: Strings, Numbers, Dates

Exercise - Scalar Functions

Exercise - Date and Time

Table and Column Aliases

Exercise - Table and Column Aliases

Aggregate Functions: COUNT, SUM, AVG

Exercise - COUNT, SUM, AVG

MIN, MAX, and Finding Rows with Extreme Values

Exercise - MIN, MAX, and Extreme Values

GROUP BY and HAVING

Exercise - GROUP BY and HAVING

Advanced Grouping: ROLLUP, CUBE, GROUPING SETS

Exercise - Advanced Grouping

String and Conditional Aggregation

Exercise - String and Conditional Aggregation

Introduction to Joins

Exercise - INNER JOIN

Exercise - LEFT JOIN

Exercise - RIGHT JOIN

FULL OUTER JOIN

Exercise - FULL OUTER JOIN

Exercise - CROSS JOIN

Exercise - SELF JOIN

LATERAL JOIN and Join Interview Patterns

Exercise - LATERAL JOIN and Patterns

Subquery Fundamentals

Exercise - Subquery Fundamentals

EXISTS, NOT EXISTS, IN, and NOT IN

Exercise - EXISTS, NOT EXISTS, IN, NOT IN

Correlated Subqueries

Exercise - Correlated Subqueries

Common Table Expressions (CTEs)

Exercise - Common Table Expressions

Exercise - Recursive CTEs

Introduction to Window Functions

Exercise - Introduction to Window Functions

Exercise - ROW_NUMBER

RANK, DENSE_RANK, and Distribution Functions

Exercise - RANK, DENSE_RANK, Distribution

LAG, LEAD, and Row Comparison

Exercise - LAG, LEAD, and Row Comparison

FIRST_VALUE, LAST_VALUE, NTH_VALUE

Exercise - FIRST_VALUE, LAST_VALUE, NTH_VALUE

Window Aggregates: Running Totals and Moving Averages

Gaps and Islands

Exercise - Gaps and Islands

UNION and UNION ALL

Exercise - UNION and UNION ALL

INTERSECT and EXCEPT

Exercise - INTERSECT and EXCEPT

Pivoting and Unpivoting Data

Exercise - Pivoting and Unpivoting

Creating and Modifying Tables

INSERT, UPDATE, DELETE, and UPSERT

Views and Materialized Views

Temporary Tables and Table Expressions

Working with JSON Data

How Indexes Work

Composite, Partial, and Covering Indexes

EXPLAIN and Reading Execution Plans

Query Optimization and Anti-Patterns

What is a Transaction?

ACID Properties

Isolation Levels

Locking Mechanisms

MVCC: Multi-Version Concurrency Control

Entity-Relationship Modeling

Normal Forms in Practice

Denormalization Strategies

Star Schema and Snowflake Schema

SQL Gotchas and Tricky Behavior

Top-N per Group

Exercise - Top-N per Group

Nth Highest / Nth Value

Exercise - Nth Highest / Nth Value

Finding and Removing Duplicates

Exercise - Finding and Removing Duplicates

Consecutive Records and Streaks

Exercise - Consecutive Records and Streaks

Period-over-Period Comparisons

Exercise - Period-over-Period Comparisons

Running Totals and Cumulative Calculations

Exercise - Running Totals and Cumulative

Gaps in Sequences

Exercise - Gaps in Sequences

Funnel Analysis, Cohorts, and Retention

Exercise - Funnel, Cohorts, Retention

Sessionization and Time-Based Grouping

Exercise - Sessionization and Time Grouping

Hierarchical Queries and Tree Traversal

Exercise - Hierarchical Queries

Median and Percentile Calculations

Exercise - Median and Percentile

Overlap Detection and Date Ranges

Exercise - Overlap Detection and Date Ranges

Pivot Patterns and Data Reshaping

Exercise - Pivot Patterns and Data Reshaping

User-Defined Functions (UDFs)

Stored Procedures and Procedural SQL

Users, Roles, and Privileges

Row-Level and Column-Level Security

SQL Injection and Safe Query Construction

SQL Interview

Welcome

Course Roadmap
Course Setup
Join the Community

SQL Foundations

What is SQL?
The Relational Model
Keys and Constraints
Normalization
Denormalization

Querying Data

The SELECT Statement
Exercise - SELECT Statement
Filtering with WHERE
Exercise - Filtering with WHERE
BETWEEN and IN
Exercise - BETWEEN and IN
LIKE and Pattern Matching
Exercise - LIKE and Pattern Matching
NULL Handling and COALESCE
Exercise - NULL Handling and COALESCE
CASE WHEN Expressions
Exercise - CASE WHEN Expressions
Sorting and Pagination
Exercise - Sorting and Pagination
DISTINCT
Exercise - DISTINCT
Scalar Functions: Strings, Numbers, Dates
Exercise - Scalar Functions
Date and Time
Exercise - Date and Time
Table and Column Aliases
Exercise - Table and Column Aliases

Aggregations

Aggregate Functions: COUNT, SUM, AVG
Exercise - COUNT, SUM, AVG
MIN, MAX, and Finding Rows with Extreme Values
Exercise - MIN, MAX, and Extreme Values
GROUP BY and HAVING
Exercise - GROUP BY and HAVING
Advanced Grouping: ROLLUP, CUBE, GROUPING SETS
Exercise - Advanced Grouping
String and Conditional Aggregation
Exercise - String and Conditional Aggregation

Joins

Introduction to Joins
INNER JOIN
Exercise - INNER JOIN
LEFT JOIN
Exercise - LEFT JOIN
RIGHT JOIN
Exercise - RIGHT JOIN
FULL OUTER JOIN
Exercise - FULL OUTER JOIN
CROSS JOIN
Exercise - CROSS JOIN
SELF JOIN
Exercise - SELF JOIN
LATERAL JOIN and Join Interview Patterns
Exercise - LATERAL JOIN and Patterns

Subqueries & CTEs

Subquery Fundamentals
Exercise - Subquery Fundamentals
EXISTS, NOT EXISTS, IN, and NOT IN
Exercise - EXISTS, NOT EXISTS, IN, NOT IN
Correlated Subqueries
Exercise - Correlated Subqueries
Common Table Expressions (CTEs)
Exercise - Common Table Expressions
Recursive CTEs
Exercise - Recursive CTEs

Window Functions

Introduction to Window Functions
Exercise - Introduction to Window Functions
ROW_NUMBER
Exercise - ROW_NUMBER
RANK, DENSE_RANK, and Distribution Functions
Exercise - RANK, DENSE_RANK, Distribution
LAG, LEAD, and Row Comparison
Exercise - LAG, LEAD, and Row Comparison
FIRST_VALUE, LAST_VALUE, NTH_VALUE
Exercise - FIRST_VALUE, LAST_VALUE, NTH_VALUE
Window Aggregates: Running Totals and Moving Averages
Gaps and Islands
Exercise - Gaps and Islands

Set Operations

UNION and UNION ALL
Exercise - UNION and UNION ALL
INTERSECT and EXCEPT
Exercise - INTERSECT and EXCEPT
Pivoting and Unpivoting Data
Exercise - Pivoting and Unpivoting

DDL, DML & Views

Creating and Modifying Tables
INSERT, UPDATE, DELETE, and UPSERT
Views and Materialized Views
Temporary Tables and Table Expressions
Working with JSON Data

Performance

How Indexes Work
Composite, Partial, and Covering Indexes
EXPLAIN and Reading Execution Plans
Query Optimization and Anti-Patterns

Transactions & Concurrency

What is a Transaction?
ACID Properties
Isolation Levels
Locking Mechanisms
Deadlocks
MVCC: Multi-Version Concurrency Control

Data Modeling

Entity-Relationship Modeling
Normal Forms in Practice
Denormalization Strategies
Star Schema and Snowflake Schema

Interview Patterns

SQL Gotchas and Tricky Behavior
Top-N per Group
Exercise - Top-N per Group
Nth Highest / Nth Value
Exercise - Nth Highest / Nth Value
Finding and Removing Duplicates
Exercise - Finding and Removing Duplicates
Consecutive Records and Streaks
Exercise - Consecutive Records and Streaks
Period-over-Period Comparisons
Exercise - Period-over-Period Comparisons
Running Totals and Cumulative Calculations
Exercise - Running Totals and Cumulative
Gaps in Sequences
Exercise - Gaps in Sequences
Funnel Analysis, Cohorts, and Retention
Exercise - Funnel, Cohorts, Retention
Sessionization and Time-Based Grouping
Exercise - Sessionization and Time Grouping
Hierarchical Queries and Tree Traversal
Exercise - Hierarchical Queries
Median and Percentile Calculations
Exercise - Median and Percentile
Overlap Detection and Date Ranges
Exercise - Overlap Detection and Date Ranges
Pivot Patterns and Data Reshaping
Exercise - Pivot Patterns and Data Reshaping

Procedural SQL

User-Defined Functions (UDFs)
Stored Procedures and Procedural SQL
Triggers

Database Security

Users, Roles, and Privileges
Row-Level and Column-Level Security
SQL Injection and Safe Query Construction

Exercise - Sessionization and Time Grouping

Premium Content

Subscribe to unlock full access to this content and more premium articles.

Sessionization and T...Hierarchical Queries...