SUBJECT / SQL

SQL and Data Engineering

Queries, data models, and production database work.

249 topics / 50 articles ready / 2 course paths

SQL - Basic to Production Engineering

249 headings

Getting Started5 topics

Introduction to SQLFoundational Overview of SQLRead
Why SQL ExistsTheoretical Breakdown of DatabasesRead
How Databases WorkTheoretical Breakdown of DatabasesRead
Database SystemsTheoretical Breakdown of DatabasesRead
Installation and ToolsSQL Setup GuideRead

Core Foundations12 topics

Database & Table BasicsDatabase FundamentalsRead
SQL Basics and CommandsBreakdown of Table BasicsRead
Working with Databases in SQLBreakdown of Table BasicsRead
SQL Data TypesBreakdown of Table BasicsRead
Creating and Managing Tables in SQLBreakdown of Table BasicsRead
Primary KeyBreakdown of Table BasicsRead
Foreign KeyBreakdown of Table BasicsRead
Constraints in SQLBreakdown of Table BasicsRead
NULL vs 0 vs Empty StringBreakdown of Table BasicsRead
DDL vs DMLBreakdown of Table BasicsRead
Query LifecycleBreakdown of Table BasicsRead
Indexing in SQLBreakdown of Table BasicsRead

Database Design9 topics

Database Design FundamentalsPrinciples of Data Modeling & Database DesignPlanned
Importance, Raw Data and PainConceptual Breakdown of DB DesignPlanned
Core Concepts (Overview)Conceptual Breakdown of DB DesignPlanned
Mental Model (First Principle)Conceptual Breakdown of DB DesignPlanned
Schema DesignConceptual Breakdown of DB DesignPlanned
Entity and AttributesConceptual Breakdown of DB DesignPlanned
Relationships, Cardinality and OptionalityConceptual Breakdown of DB DesignPlanned
Everything About KeysConceptual Breakdown of DB DesignPlanned
Normalisation and their FormsConceptual Breakdown of DB DesignPlanned

Querying Essentials19 topics

Query FundamentalsData Retrieval FundamentalsRead
SELECT AND FROMData Selection ProtocolsRead
WHEREData Selection ProtocolsRead
Comparison OperatorsData Selection ProtocolsRead
Logical OperatorsData Selection ProtocolsRead
Arithmetic OperatorsData Selection ProtocolsRead
ORDER BY and LIMITData Selection ProtocolsRead
DISTINCT and AS (Aliases)Data Selection ProtocolsRead
Big CountriesProblems Level 1 (Query Fundamentals)Read
Profitable Customers in 2021Problems Level 1 (Query Fundamentals)Read
Odd Non-Boring MoviesProblems Level 1 (Query Fundamentals)Read
Filtering EssentialsAdvanced Filtering TechniquesRead
IS NULL vs. IS NOT NULL, IN, and NOT INConceptual Breakdown of FiltersRead
BETWEEN and NOT BETWEENConceptual Breakdown of FiltersRead
LIKE and NOT LIKEConceptual Breakdown of FiltersRead
Filter Records Excluding a Specific PatternProblem Level 1 (Filtering Essentials)Read
Find Records Excluding a Given Set of ValuesProblem Level 1 (Filtering Essentials)Read
Find Salaries Outside the Expected RangeProblem Level 1 (Filtering Essentials)Read
Non-Referred CustomersProblem Level 1 (Filtering Essentials)Read

Aggregation and Analysis14 topics

Data SummarizationData Aggregation & SummarizationRead
Fundamentals of GROUP BYConceptual Breakdown of AggregatesRead
Basic Aggregate Functions (MIN, MAX, SUM, AVG)Conceptual Breakdown of AggregatesRead
COUNT FunctionsConceptual Breakdown of AggregatesRead
HAVING Clause (Basics, HAVING with COUNT)Conceptual Breakdown of AggregatesRead
First Login AnalysisProblems Level 1 (Data Summarization)Read
Employee Work Time SummaryProblems Level 1 (Data Summarization)Read
Unique Subjects per TeacherProblems Level 1 (Data Summarization)Read
User Follower CountProblems Level 1 (Data Summarization)Read
CRM Automotive Sales AnalysisProblems Level 1 (Data Summarization)Read
Highest Order Placing CustomerProblems Level 1 (Data Summarization)Read
Frequent Actor-Director DuosProblems Level 2 (Data Summarization)Read
Large ClassesProblems Level 2 (Data Summarization)Read
Email DuplicatesProblems Level 2 (Data Summarization)Read

Set Operations9 topics

Merging Query ResultsSet Theory & Result MergingPlanned
UNIONConceptual Breakdown of UnionsPlanned
UNION ALLConceptual Breakdown of UnionsPlanned
Difference Between UNION and UNION ALLConceptual Breakdown of UnionsPlanned
IntersectionConceptual Breakdown of UnionsPlanned
Combine Active and Archived UsersProblems Level 1 (Merging Query Results)Planned
Merge Recent Orders from Multiple SourcesProblems Level 1 (Merging Query Results)Planned
Combine Sales Records Without DeduplicationProblems Level 1 (Merging Query Results)Planned
Reshape Products DataProblems Level 1 (Merging Query Results)Planned

SQL Joins35 topics

Joins Deep DiveJoin FundamentalsPlanned
RIGHT JOINConceptual breakdown of JoinsPlanned
ON vs. WHEREConceptual breakdown of JoinsPlanned
LEFT JOINConceptual breakdown of JoinsPlanned
INNER JOINConceptual breakdown of JoinsPlanned
FULL OUTER JOINConceptual breakdown of JoinsPlanned
CROSS JOINConceptual breakdown of JoinsPlanned
IMPLICIT JOINConceptual breakdown of JoinsPlanned
SELF JOINConceptual breakdown of JoinsPlanned
NATURAL JOINConceptual breakdown of JoinsPlanned
Employees and Their DepartmentsProblems Level 1 (Joins Deep Dive)Planned
Customer Orders OverviewProblems Level 1 (Joins Deep Dive)Planned
Employees With Confirmed Salary RecordsProblems Level 1 (Joins Deep Dive)Planned
Generate All Possible User-Category PairsProblems Level 1 (Joins Deep Dive)Planned
Employees With or Without Salary RecordsProblems Level 1 (Joins Deep Dive)Planned
Match Employees With Their SalariesProblems Level 1 (Joins Deep Dive)Planned
Employees Earning More Than Their ManagerProblems Level 1 (Joins Deep Dive)Planned
Students Enrolled in CoursesProblems Level 1 (Joins Deep Dive)Planned
Sales AnalysisProblems Level 2 (Joins Deep Dive)Planned
Minimum Distance Between PointsProblems Level 2 (Joins Deep Dive)Planned
Suspended AccountsProblems Level 2 (Joins Deep Dive)Planned
Find Team Size for Each EmployeeProblems Level 2 (Joins Deep Dive)Planned
Average Experience by ProjectProblems Level 2 (Joins Deep Dive)Planned
Warehouse Stock ManagerProblems Level 2 (Joins Deep Dive)Planned
Table Join OperationProblems Level 3 (Joins Deep Dive)Planned
Inactive CustomersProblems Level 3 (Joins Deep Dive)Planned
Students Enrolled in Non-Existent DepartmentsProblems Level 3 (Joins Deep Dive)Planned
Low Bonus EmployeesProblems Level 3 (Joins Deep Dive)Planned
Available Seat StreaksProblems Level 3 (Joins Deep Dive)Planned
Visitors Without TransactionsProblems Level 3 (Joins Deep Dive)Planned
A & B Buyers Without CProblems Level 3 (Joins Deep Dive)Planned
Product Selling Price ReportProblems Level 3 (Joins Deep Dive)Planned
Updated Bank BalancesProblems Level 3 (Joins Deep Dive)Planned
Most Frequent TravellersProblems Level 3 (Joins Deep Dive)Planned
Suggested PagesProblems Level 3 (Joins Deep Dive)Planned

Subqueries25 topics

Everything about SubqueriesMastering SubqueriesPlanned
Introduction to Subqueries (IN)Conceptual Breakdown of Nested LogicPlanned
EXISTS and NOT EXISTSConceptual Breakdown of Nested LogicPlanned
Correlated SubqueriesConceptual Breakdown of Nested LogicPlanned
Contest Participation RateProblems Level 1 (Everything about Subqueries)Planned
High-Report ManagersProblems Level 1 (Everything about Subqueries)Planned
All-Product BuyersProblems Level 1 (Everything about Subqueries)Planned
Highest Non-Repeating NumberProblems Level 1 (Everything about Subqueries)Planned
Find the First Device Logged In by Each PlayerProblems Level 1 (Everything about Subqueries)Planned
Salespersons Without RED OrdersProblems Level 1 (Everything about Subqueries)Planned
Employees with the Highest Salary in Each DepartmentProblems Level 2 (Everything about Subqueries)Planned
Find the Most Recent Order for Each ProductProblems Level 2 (Everything about Subqueries)Planned
Find Transactions with Maximum Amount Per DayProblems Level 2 (Everything about Subqueries)Planned
Incomplete Employee RecordsProblems Level 2 (Everything about Subqueries)Planned
Find Quiet Students in All ExamsProblems Level 2 (Everything about Subqueries)Planned
Top Grade per StudentProblems Level 2 (Everything about Subqueries)Planned
Swap Consecutive SeatsProblems Level 3 (Everything about Subqueries)Planned
Safe Investment CountriesProblems Level 3 (Everything about Subqueries)Planned
Tennis Grand Slam WinnersProblems Level 3 (Everything about Subqueries)Planned
Football Team ScoresProblems Level 3 (Everything about Subqueries)Planned
Order Count per CustomerProblems Level 3 (Everything about Subqueries)Planned
Boolean Expression EvaluatorProblems Level 3 (Everything about Subqueries)Planned
Immediate First Orders PercentageProblems Level 3 (Everything about Subqueries)Planned
Find All Employees Reporting to the Head of the CompanyProblems Level 3 (Everything about Subqueries)Planned
Orphan EmployeesProblems Level 3 (Everything about Subqueries)Planned

Data Modification and Schema Evolution10 topics

Editing Data and TablesData ManipulationPlanned
INSERTTable Alteration CommandsPlanned
UPSERTTable Alteration CommandsPlanned
UPDATETable Alteration CommandsPlanned
DELETETable Alteration CommandsPlanned
ALTERTable Alteration CommandsPlanned
TRUNCATETable Alteration CommandsPlanned
DELETE vs. TRUNCATE vs. DROPTable Alteration CommandsPlanned
System SettingsProblems Level 1 (Editing Data and Tables)Planned
Employee SalaryProblems Level 1 (Editing Data and Tables)Planned

Data Storage, Keys, and Query Optimization5 topics

Storage, Keys and Query PerformanceHow Storage and Keys Impact QueriesPlanned
Where Rows Actually LiveBreakdown of Storage, Keys, and Query PerformancePlanned
How a B+ Tree Is Built with DataBreakdown of Storage, Keys, and Query PerformancePlanned
Why the Wrong Primary Key Can Quietly Destroy YouBreakdown of Storage, Keys, and Query PerformancePlanned
Index Strategy at ScaleBreakdown of Storage, Keys, and Query PerformancePlanned

Query Performance5 topics

Performance & DebuggingPerformance EngineeringPlanned
Raw Data Setup and Stored ProceduresBreakdown of Performance & DebuggingPlanned
Debugging Queries with EXPLAINBreakdown of Performance & DebuggingPlanned
Query PerformanceBreakdown of Performance & DebuggingPlanned
Debugging CorrectnessBreakdown of Performance & DebuggingPlanned

Transactions and Access Control11 topics

Permissions and Transactions Part-1Access Control Part 1Planned
Privileges and RolesBreakdown of Transactions and Access Part 1Planned
GRANTSBreakdown of Transactions and Access Part 1Planned
GRANT ALL and WITH GRANT OPTIONBreakdown of Transactions and Access Part 1Planned
ALTER USERBreakdown of Transactions and Access Part 1Planned
REVOKEBreakdown of Transactions and Access Part 1Planned
Permissions and Transactions Part-2Access Control Part 2Planned
Connection, Connection Pool and CommitBreakdown of Transactions and Access Part 2Planned
RollbackBreakdown of Transactions and Access Part 2Planned
SavepointBreakdown of Transactions and Access Part 2Planned
Internal Working of Transactions, Timeout and DeadlockBreakdown of Transactions and Access Part 2Planned

Functions (Math and Conditional)25 topics

Numeric and NULL FunctionsScalar & Conditional FunctionsPlanned
ROUND() and ABS()Numeric and Null LogicPlanned
GREATEST(), LEAST(), and IF NULL()Numeric and Null LogicPlanned
NULL Handling in SQL: IS NULL, IS NOT NULL, IF NULL(), and COALESCE()Numeric and Null LogicPlanned
Call Count Between PairsProblems Level 1 (Numeric and NULL Functions)Planned
Case Conditional LogicLogic & ConditionsPlanned
CASE BasicsBoolean and Branching LogicPlanned
CASE Practical ExamplesBoolean and Branching LogicPlanned
Advanced CASE UsageBoolean and Branching LogicPlanned
Valid Triangle CheckProblems Level 1 (Case Conditional Logic)Planned
Instant Food DeliveryProblems Level 1 (Case Conditional Logic)Planned
Special Bonus CalculationProblems Level 1 (Case Conditional Logic)Planned
Node ClassificationProblems Level 1 (Case Conditional Logic)Planned
Apples vs OrangesProblems Level 1 (Case Conditional Logic)Planned
Query Quality AnalysisProblems Level 1 (Case Conditional Logic)Planned
String FunctionsText & StringsPlanned
CONCAT() vs. CONCAT_WS()String & Pattern MechanicsPlanned
LOWER() / UPPER()String & Pattern MechanicsPlanned
TRIM() / LTRIM() / RTRIM()String & Pattern MechanicsPlanned
LENGTH() vs CHAR_LENGTH()String & Pattern MechanicsPlanned
LEFT() / RIGHT() / SUBSTRING()String & Pattern MechanicsPlanned
LOCATE() / INSTR()String & Pattern MechanicsPlanned
REPLACE()String & Pattern MechanicsPlanned
Pattern Matching with LIKEString & Pattern MechanicsPlanned
Exceeding Tweet LengthProblems Level 1 (String Functions)Planned

CTEs and Temp Structures6 topics

CTEs and Temporary TablesWorking with CTEsPlanned
WITH and AS (CTEs)Conceptual Breakdown of CTEsPlanned
Non-Recursive CTEsConceptual Breakdown of CTEsPlanned
Recursive CTEs for HierarchiesConceptual Breakdown of CTEsPlanned
Most Frequently Ordered Product(s) for Each CustomerProblems Level 1 (CTEs and Temporary Tables)Planned
Find Missing Subtasks for Each TaskProblems Level 1 (CTEs and Temporary Tables)Planned

Dates and Time11 topics

Dates: Functions and FilteringWorking with DatesPlanned
Date and Time Part Extraction Using YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), and SECOND()Conceptual Breakdown of Date and TimePlanned
Filtering with Date RangesConceptual Breakdown of Date and TimePlanned
Finding First and Latest EventsConceptual Breakdown of Date and TimePlanned
Calculating Date Differences (DATEDIFF, TIMESTAMPDIFF)Conceptual Breakdown of Date and TimePlanned
Current Date and TimeConceptual Breakdown of Date and TimePlanned
Latest 2020 LoginProblem Level 1 (Dates Functions and Filtering)Planned
Kid-Friendly Movies in Last MonthProblem Level 1 (Dates Functions and Filtering)Planned
Warmer DaysProblem Level 1 (Dates Functions and Filtering)Planned
Restaurant Payment TrendsProblem Level 1 (Dates Functions and Filtering)Planned
Inactive SellersProblem Level 1 (Dates Functions and Filtering)Planned

Window Functions17 topics

Everything about Window FunctionsMastering Window FunctionsPlanned
Core Mental Model (OVER, PARTITION BY, ORDER BY)Breakdown of SQL Window FunctionsPlanned
Ranking FunctionsBreakdown of SQL Window FunctionsPlanned
Offset FunctionsBreakdown of SQL Window FunctionsPlanned
Window FramesBreakdown of SQL Window FunctionsPlanned
Value Window FunctionsBreakdown of SQL Window FunctionsPlanned
Distribution HelpersBreakdown of SQL Window FunctionsPlanned
Named WindowsBreakdown of SQL Window FunctionsPlanned
Real-Life Use CasesBreakdown of SQL Window FunctionsPlanned
Follow-up Game ActivityProblem Level 1 (Window Functions)Planned
Track Continuous Periods of Task Failures and SuccessesProblem Level 1 (Window Functions)Planned
Find the Largest Window Between User VisitsProblem Level 1 (Window Functions)Planned
Top 3 Salaries per DepartmentProblem Level 1 (Window Functions)Planned
Top Ratings in Feb 2020Problem Level 1 (Window Functions)Planned
Find Continuous Ranges in LogsProblem Level 1 (Window Functions)Planned
Most Experienced Employees in Each ProjectProblem Level 1 (Window Functions)Planned
Most Recent Three Orders for Each CustomerProblem Level 1 (Window Functions)Planned

JSON9 topics

JSON in SQLWorking with JSONPlanned
Dummy Data SetupBreakdown of JSON in SQLPlanned
JSON Insertion + Data LoadingBreakdown of JSON in SQLPlanned
JSON Read / Query CommandsBreakdown of JSON in SQLPlanned
JSON Arrays and SearchBreakdown of JSON in SQLPlanned
JSON UpdatesBreakdown of JSON in SQLPlanned
JSON Output BuildersBreakdown of JSON in SQLPlanned
Performance and IndexingBreakdown of JSON in SQLPlanned
Upsert Power FeaturesBreakdown of JSON in SQLPlanned

Database Scaling and Production Systems6 topics

Scaling And Production OperationsProduction Scaling FundamentalsPlanned
Scaling ReadsBreakdown of Scaling And Production OperationsPlanned
ShardingBreakdown of Scaling And Production OperationsPlanned
Distributed IDsBreakdown of Scaling And Production OperationsPlanned
Zero-Downtime Schema ChangesBreakdown of Scaling And Production OperationsPlanned
Partitioning & Data LifecycleBreakdown of Scaling And Production OperationsPlanned

Interview Situational Questions (Easy)6 topics

Introduction to Situation-Based QuestionsPlanned
Situation-1Planned
Situation-2Planned
Situation-3Planned
Situation-4Planned
Situation-5Planned

Interview Situational Questions (Medium)5 topics

Situation-6Planned
Situation-7Planned
Situation-8Planned
Situation-9Planned
Situation-10Planned

Interview Situational Questions (Hard)5 topics

Situation-11Planned
Situation-12Planned
Situation-13Planned
Situation-14Planned
Situation-15Planned

SQL - 75

75 headings

Querying and Filtering5 topics

Aggregation and Grouping9 topics

Set Operations and Combined Results6 topics

Reshape Products DataPlanned
Incomplete Employee RecordsPlanned
Safe Investment CountriesPlanned
Tennis Grand Slam WinnersPlanned
Football Team ScoresPlanned
Top Ratings in Feb 2020Planned

Self Joins and Relationship Queries6 topics

Employees Earning More Than Their ManagerPlanned
Minimum Distance Between PointsPlanned
Suspended AccountsPlanned
Suggested PagesPlanned
High-Report ManagersPlanned
Boolean Expression EvaluatorPlanned

Joins and Aggregation7 topics

Sales AnalysisPlanned
Average Experience by ProjectPlanned
Warehouse Stock ManagerPlanned
Table Join OperationPlanned
Low Bonus EmployeesPlanned
Updated Bank BalancesPlanned
Most Frequent TravellersPlanned

Window Functions4 topics

Find Team Size for Each EmployeePlanned
Restaurant Payment TrendsPlanned
Follow-up Game ActivityPlanned
Find the Largest Window Between User VisitsPlanned

Subqueries and Missing Records9 topics

Inactive CustomersPlanned
Students Enrolled in Non-Existent DepartmentsPlanned
Visitors Without TransactionsPlanned
A & B Buyers Without CPlanned
All-Product BuyersPlanned
Salespersons Without RED OrdersPlanned
Find Quiet Students in All ExamsPlanned
Orphan EmployeesPlanned
Inactive SellersPlanned

Consecutive Sequences and Ranges3 topics

Available Seat StreaksPlanned
Track Continuous Periods of Task Failures and SuccessesPlanned
Find Continuous Ranges in LogsPlanned

Date and Time Analysis6 topics

Product Selling Price ReportPlanned
Order Count per CustomerPlanned
Immediate First Orders PercentagePlanned
Latest 2020 LoginPlanned
Kid-Friendly Movies in Last MonthPlanned
Warmer DaysPlanned

Conditional Logic and Calculations8 topics

Contest Participation RatePlanned
Swap Consecutive SeatsPlanned
Call Count Between PairsPlanned
Valid Triangle CheckPlanned
Instant Food DeliveryPlanned
Special Bonus CalculationPlanned
Apples vs OrangesPlanned
Query Quality AnalysisPlanned

Ranking and Top N Queries9 topics

Find the First Device Logged In by Each PlayerPlanned
Employees with the Highest Salary in Each DepartmentPlanned
Find the Most Recent Order for Each ProductPlanned
Find Transactions with Maximum Amount Per DayPlanned
Top Grade per StudentPlanned
Most Frequently Ordered Product(s) for Each CustomerPlanned
Top 3 Salaries per DepartmentPlanned
Most Experienced Employees in Each ProjectPlanned
Most Recent Three Orders for Each CustomerPlanned

CTEs and Hierarchies3 topics

Find All Employees Reporting to the Head of the CompanyPlanned
Node ClassificationPlanned
Find Missing Subtasks for Each TaskPlanned