SkillData AnalystData ScientistData EngineerAI/ML EngineerForward Deployed EngineerSoftware Engineer (Backend)DevOps / Platform EngineerBlockchain Developer
Library · language
SQL
Ask precise questions of tables of data. The one skill that appears in almost every data job description, and in many software and AI ones too.
SQL is how you talk to relational databases and warehouses such as PostgreSQL, MySQL, SQL Server, BigQuery, Snowflake and DuckDB. The dialects differ in small ways; the ideas on this shelf work in all of them.
Every example here runs on real public data with DuckDB, a free database that needs no server and reads CSV and Parquet files straight from a URL.
- 1Selecting, filtering and aggregatingWhat is SQL? Databases, tables and your first query
- 2Selecting, filtering and aggregatingSQL SELECT, WHERE and ORDER BY on real taxi trips
- 3Selecting, filtering and aggregatingSQL NULL values: IS NULL, COALESCE and three-valued logic
- 4Selecting, filtering and aggregatingSQL data types and CAST: numbers, text, dates
- 5Selecting, filtering and aggregatingSQL LIKE, IN and BETWEEN with wildcards and ranges
- 6Selecting, filtering and aggregatingSQL CASE WHEN: conditional logic and buckets
- 7Selecting, filtering and aggregatingSQL GROUP BY, aggregate functions and HAVING
- 8Joins, subqueries and set operationsSQL INNER JOIN vs LEFT JOIN with real examples
- 9Joins, subqueries and set operationsSQL FULL OUTER JOIN, CROSS JOIN and SELF JOIN
- 10Joins, subqueries and set operationsSQL join fan-out: avoid double counting rows
- 11Joins, subqueries and set operationsSQL subqueries: IN, EXISTS and correlated subqueries
- +33 more beginner lessons
- 1Window functionsSQL ROW_NUMBER, RANK and DENSE_RANK: top N per group
- 2Window functionsSQL running total, moving average and LAG
- 3Window functionsSQL percentiles and median: PERCENTILE_CONT and NTILE
- 4Window functionsRemove duplicate rows in SQL with ROW_NUMBER
- 5CTEs, dates, strings and analysis patternsSQL CTE (WITH clause): write readable queries
- 6CTEs, dates, strings and analysis patternsSQL date functions: DATE_TRUNC, EXTRACT and intervals
- 7CTEs, dates, strings and analysis patternsSQL cohort analysis and customer retention
- 8CTEs, dates, strings and analysis patternsSQL string functions: TRIM, SUBSTRING, REPLACE and regex
- 9CTEs, dates, strings and analysis patternsSQL pivot: rows to columns and unpivot
- 10CTEs, dates, strings and analysis patternsSQL funnel analysis: conversion rates step by step
- 11Relational design and safe changesDatabase normalization: 1NF, 2NF, 3NF with examples
- +33 more intermediate lessons
- 1Query plans and performanceSQL EXPLAIN: read query plans and use indexes
- 2Query plans and performanceSQL composite indexes and covering indexes
- 3Query plans and performanceHash join vs nested loop vs merge join
- 4Query plans and performanceSQL query optimization: rewrite slow queries
- 5Query plans and performanceSQL isolation levels, locks and deadlocks
- 1BeginnerAn orders REST API with Spring Boot and PostgreSQL
- 2BeginnerFrom a vague ask to a pilot plan
- 3BeginnerMonthly data quality report for taxi trips
- 4BeginnerNYC taxi demand in SQL
- 5BeginnerRetail star schema in SQL
- 6IntermediateCustomer cohort retention analysis
- 7IntermediateE-commerce star schema and DAX dashboard
- 8IntermediateFind and fix a slow endpoint with measurements
- 9IntermediateMarketplace warehouse with history
- 10IntermediateReliable order events with Kafka and the outbox pattern
- 11IntermediateTaxi analytics marts with dbt
- 12AdvancedA data question agent with guardrails and an eval suite
- 13AdvancedAutomated taxi operations pipeline and dashboard
- 14AdvancedChange a live database schema with zero downtime
- 15AdvancedIncremental warehouse loads with late and corrected data
Further reading
- DuckDB documentation ↗docs · free
The free, serverless database used in most SQL examples on UniMitra.
- NYC TLC trip record data ↗dataset · free · NYC Open Data public data (TLC terms of use)
Monthly Parquet files of every yellow and green taxi trip in New York City.
- UCI Online Retail II ↗dataset · free · CC BY 4.0
Two years of order lines from a UK online gift wholesaler, used across the data shelves.