DataBurst Data Engineering Wiki - Roadmap Step-by-Step: https://wiki.databurst.tech/docs/roadmap/ - Programming Language: https://wiki.databurst.tech/docs/roadmap/programming-language/ - Python: https://wiki.databurst.tech/docs/roadmap/programming-language/python/ - Java: https://wiki.databurst.tech/docs/roadmap/programming-language/java/ - Scala: https://wiki.databurst.tech/docs/roadmap/programming-language/scala/ - Go: https://wiki.databurst.tech/docs/roadmap/programming-language/go/ - Rust: https://wiki.databurst.tech/docs/roadmap/programming-language/rust/ - Operating System: https://wiki.databurst.tech/docs/roadmap/operating-system/ - Linux: https://wiki.databurst.tech/docs/roadmap/operating-system/linux/ - Windows: https://wiki.databurst.tech/docs/roadmap/operating-system/windows/ - Networking: https://wiki.databurst.tech/docs/roadmap/networking/ - Protocols: https://wiki.databurst.tech/docs/roadmap/networking/protocols/ - TCP/IP: https://wiki.databurst.tech/docs/roadmap/networking/protocols/tcp/ - DNS: https://wiki.databurst.tech/docs/roadmap/networking/protocols/dns/ - HTTP: https://wiki.databurst.tech/docs/roadmap/networking/protocols/http/ - HTTPS: https://wiki.databurst.tech/docs/roadmap/networking/protocols/https/ - SSH: https://wiki.databurst.tech/docs/roadmap/networking/protocols/ssh/ - UDP: https://wiki.databurst.tech/docs/roadmap/networking/protocols/udp/ - Concepts: https://wiki.databurst.tech/docs/roadmap/networking/concepts/ - Firewalls: https://wiki.databurst.tech/docs/roadmap/networking/concepts/firewalls/ - VPN: https://wiki.databurst.tech/docs/roadmap/networking/concepts/vpn/ - General Software Engineering Skills: https://wiki.databurst.tech/docs/roadmap/general-software-engineering-skills/ - OAuth: https://wiki.databurst.tech/docs/roadmap/general-software-engineering-skills/oauth/ - JWT: https://wiki.databurst.tech/docs/roadmap/general-software-engineering-skills/jwt/ - GraphQL: https://wiki.databurst.tech/docs/roadmap/general-software-engineering-skills/graphql/ - REST: https://wiki.databurst.tech/docs/roadmap/general-software-engineering-skills/rest/ - gRPC: https://wiki.databurst.tech/docs/roadmap/general-software-engineering-skills/grpc/ - Caching: https://wiki.databurst.tech/docs/roadmap/general-software-engineering-skills/caching/ - Web Frameworks and API Development: https://wiki.databurst.tech/docs/roadmap/web-frameworks-and-api-development/ - Python: https://wiki.databurst.tech/docs/roadmap/web-frameworks-and-api-development/python/ - Go: https://wiki.databurst.tech/docs/roadmap/web-frameworks-and-api-development/go/ - Java: https://wiki.databurst.tech/docs/roadmap/web-frameworks-and-api-development/java/ - Version Control System: https://wiki.databurst.tech/docs/roadmap/version-control-system/ - git: https://wiki.databurst.tech/docs/roadmap/version-control-system/git/ - Version Control System Hosting: https://wiki.databurst.tech/docs/roadmap/version-control-system-hosting/ - Github: https://wiki.databurst.tech/docs/roadmap/version-control-system-hosting/github/ - Gitlab: https://wiki.databurst.tech/docs/roadmap/version-control-system-hosting/gitlab/ - BitBucket: https://wiki.databurst.tech/docs/roadmap/version-control-system-hosting/bitbucket/ - Distributed Systems Concepts: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/ - CAP: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/cap/ - PACELC: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/pacelc/ - Sharding: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/sharding/ - Clock: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/clock/ - ACID: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/acid/ - Replication: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/replication/ - Distributed Transactions: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/transaction/ - Indexing: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/indexing/ - Batch and Stream Processing: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/batch-and-stream-processing/ - Consistency vs Eventual Consistency: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/consistency-vs-eventual-consistency/ - Normalization or Denormalization: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/normalization-or-denormalization/ - SQL Fundamentals: https://wiki.databurst.tech/docs/roadmap/sql-fundamentals/ - Basic SQL: https://wiki.databurst.tech/docs/roadmap/sql-fundamentals/basic-sql/ - Advanced SQL: https://wiki.databurst.tech/docs/roadmap/sql-fundamentals/advanced-sql/ - Databases: https://wiki.databurst.tech/docs/roadmap/databases/ - Relational: https://wiki.databurst.tech/docs/roadmap/databases/relational/ - NoSQL: https://wiki.databurst.tech/docs/roadmap/databases/nosql/ - Columnar: https://wiki.databurst.tech/docs/roadmap/databases/nosql/columnar/ - Document Based: https://wiki.databurst.tech/docs/roadmap/databases/nosql/document-based/ - Key-Value: https://wiki.databurst.tech/docs/roadmap/databases/nosql/key-value/ - Time Series: https://wiki.databurst.tech/docs/roadmap/databases/nosql/time-series/ - Vector: https://wiki.databurst.tech/docs/roadmap/databases/nosql/vector/ - Graph: https://wiki.databurst.tech/docs/roadmap/databases/nosql/graph/ - NewSQL: https://wiki.databurst.tech/docs/roadmap/databases/nosql/newsql/ - Data Modeling: https://wiki.databurst.tech/docs/roadmap/data-modeling/ - Star Schema (Ralph Kimball): https://wiki.databurst.tech/docs/roadmap/data-modeling/star-schema/ - Snowflake: https://wiki.databurst.tech/docs/roadmap/data-modeling/snowflake/ - Data Vault: https://wiki.databurst.tech/docs/roadmap/data-modeling/data-vault/ - Data Management Architecture: https://wiki.databurst.tech/docs/roadmap/data-management-architecture/ - Relational Data Warehouse: https://wiki.databurst.tech/docs/roadmap/data-management-architecture/relational-data-warehouse/ - Data Lake: https://wiki.databurst.tech/docs/roadmap/data-management-architecture/data-lake/ - Modern Data Warehouse: https://wiki.databurst.tech/docs/roadmap/data-management-architecture/modern-data-warehouse/ - Data Fabric: https://wiki.databurst.tech/docs/roadmap/data-management-architecture/data-fabric/ - Data Mart: https://wiki.databurst.tech/docs/roadmap/data-management-architecture/data-mart/ - Data Lakehouse: https://wiki.databurst.tech/docs/roadmap/data-management-architecture/data-lakehouse/ - Data Mesh: https://wiki.databurst.tech/docs/roadmap/data-management-architecture/data-mesh/ - Data Integration: https://wiki.databurst.tech/docs/roadmap/data-integration/ - ETL: https://wiki.databurst.tech/docs/roadmap/data-integration/etl/ - ELT: https://wiki.databurst.tech/docs/roadmap/data-integration/elt/ - CDC: https://wiki.databurst.tech/docs/roadmap/data-integration/cdc/ - Reverse ETL: https://wiki.databurst.tech/docs/roadmap/data-integration/reverse-etl/ - Storage: https://wiki.databurst.tech/docs/roadmap/storage/ - Block Storage: https://wiki.databurst.tech/docs/roadmap/storage/block-storage/ - Object Storage: https://wiki.databurst.tech/docs/roadmap/storage/object-storage/ - File Formats and Serialization: https://wiki.databurst.tech/docs/roadmap/file-formats-and-serialization/ - File Formats: https://wiki.databurst.tech/docs/roadmap/file-formats-and-serialization/file-formats/ - CSV: https://wiki.databurst.tech/docs/roadmap/file-formats-and-serialization/file-formats/csv/ - JSON: https://wiki.databurst.tech/docs/roadmap/file-formats-and-serialization/file-formats/json/ - Parquet: https://wiki.databurst.tech/docs/roadmap/file-formats-and-serialization/file-formats/parquet/ - XML: https://wiki.databurst.tech/docs/roadmap/file-formats-and-serialization/file-formats/xml/ - Serialization: https://wiki.databurst.tech/docs/roadmap/file-formats-and-serialization/serialization/ - Avro: https://wiki.databurst.tech/docs/roadmap/file-formats-and-serialization/serialization/avro/ - Protocol Buffers: https://wiki.databurst.tech/docs/roadmap/file-formats-and-serialization/serialization/protobuf/ - Query Engine: https://wiki.databurst.tech/docs/roadmap/query-engine/ - Apache Hive: https://wiki.databurst.tech/docs/roadmap/query-engine/hive/ - SparkSQL: https://wiki.databurst.tech/docs/roadmap/query-engine/sparksql/ - Trino: https://wiki.databurst.tech/docs/roadmap/query-engine/trino/ - Presto: https://wiki.databurst.tech/docs/roadmap/query-engine/presto/ - Data Architecture: https://wiki.databurst.tech/docs/roadmap/data-architecture/ - Kappa: https://wiki.databurst.tech/docs/roadmap/data-architecture/kappa/ - Lambda: https://wiki.databurst.tech/docs/roadmap/data-architecture/lambda/ - Data Processing: https://wiki.databurst.tech/docs/roadmap/data-processing/ - Pipeline Orchestration: https://wiki.databurst.tech/docs/roadmap/pipeline-orchestration/ - Messaging and Streaming Platform: https://wiki.databurst.tech/docs/roadmap/messaging-and-streaming-platform/ - Data Governance: https://wiki.databurst.tech/docs/roadmap/data-governance/ - Data Visualization: https://wiki.databurst.tech/docs/roadmap/data-visualization/ - Containerization: https://wiki.databurst.tech/docs/roadmap/containerization/ - Docker: https://wiki.databurst.tech/docs/roadmap/containerization/docker/ - Podman: https://wiki.databurst.tech/docs/roadmap/containerization/podman/ - containerd: https://wiki.databurst.tech/docs/roadmap/containerization/containerd/ - Container Orchestration: https://wiki.databurst.tech/docs/roadmap/container-orchestration/ - Kubernetes: https://wiki.databurst.tech/docs/roadmap/container-orchestration/kubernetes/ - Docker Swarm: https://wiki.databurst.tech/docs/roadmap/container-orchestration/docker-swarm/ - Apache Mesos: https://wiki.databurst.tech/docs/roadmap/container-orchestration/apache-mesos/ - CI/CD: https://wiki.databurst.tech/docs/roadmap/cicd/ - Infrastructure as Code: https://wiki.databurst.tech/docs/roadmap/infrastructure-as-code/ - Configuration Management: https://wiki.databurst.tech/docs/roadmap/infrastructure-as-code/configuration-management/ - Infrastructure Provisioning: https://wiki.databurst.tech/docs/roadmap/infrastructure-as-code/infrastructure-provisioning/ - Observability: https://wiki.databurst.tech/docs/roadmap/observability/ - Logging: https://wiki.databurst.tech/docs/roadmap/observability/logging/ - Monitoring: https://wiki.databurst.tech/docs/roadmap/observability/monitoring/ - Tracing: https://wiki.databurst.tech/docs/roadmap/observability/tracing/
$ psql -c "SELECT ..."
# Basic SQL **SQL** lets you describe the rows you want from one or more tables, and the database works out how to fetch them. A query is built from a few clauses: `FROM` says which tables, `WHERE` filters rows, `GROUP BY` collapses rows into groups, `SELECT` picks the output columns and `ORDER BY` sorts. Learn these clauses, joins, and how `NULL` behaves, and you can answer most everyday questions about data. The examples use one small table: ```sql CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, customer_id BIGINT NOT NULL, city TEXT, status TEXT, -- 'paid', 'cancelled', ... amount NUMERIC(10,2), ordered_at TIMESTAMP ); ``` ## Reading data ```sql SELECT order_id, city, amount -- which columns FROM orders -- which table WHERE status = 'paid' AND amount > 100 -- which rows ORDER BY amount DESC -- sort LIMIT 10; -- how many ``` Useful filters: `IN ('Tehran', 'Shiraz')`, `BETWEEN 100 AND 500`, `LIKE 'Teh%'`, `IS NULL`. Use `SELECT DISTINCT city` to remove duplicate rows, and `CASE WHEN amount > 500 THEN 'large' ELSE 'small' END` for conditional values. ## The order SQL actually runs in You write `SELECT` first, but the database evaluates it almost last. Knowing the **logical processing order** explains most beginner errors. {{< fig kicker="$ EXPLAIN (logically)" title="Written order vs evaluation order" >}} SELECT city, COUNT(*) AS orders FROM orders WHERE status = 'paid' GROUP BY city HAVING COUNT(*) > 1 ORDER BY orders DESC LIMIT 1; 1 1 · FROM orders8 rows 2 2 · WHERE status = 'paid'6 rows 3 3 · GROUP BY city3 groups 4 4 · HAVING COUNT(*) > 12 groups 5 5 · SELECT (alias "orders")2 rows 6 7 6–7 · ORDER BY, LIMIT 1Tehran · 3
  1. FROM (and any joins) runs first: it builds the full set of input rows. Here, 8 orders.
  2. WHERE filters individual rows. 2 cancelled orders drop out. It cannot use aggregates or SELECT aliases: they don't exist yet.
  3. GROUP BY collapses rows into one row per city: Tehran 3, Shiraz 2, Isfahan 1.
  4. HAVING filters groups, so it can use aggregates. Isfahan, with one paid order, is removed.
  5. SELECT computes the output columns. Only now does the alias orders exist.
  6. ORDER BY runs after SELECT, so it can use the alias. LIMIT keeps the first row: Tehran with 3 paid orders.
{{< /fig >}} This is the logical order. The optimizer may physically execute things differently (push filters into scans, reorder joins), but the result must be as if it followed these steps. ## Joins A join combines rows from two tables where a condition matches, usually a foreign key equal to a primary key. {{< fig kicker="$ SELECT c.name, o.order_id FROM customers c ? JOIN orders o ON o.customer_id = c.id" title="INNER, LEFT and FULL OUTER JOIN on the same two tables" >}}
customers.idname
1Ali
2Sara
3Reza
order_idcustomer_id
o11
o21
o32
o49
nameorder_idreturned by
Alio1INNER · LEFT · FULL
Alio2INNER · LEFT · FULL
Sarao3INNER · LEFT · FULL
RezaNULLLEFT · FULL: customer with no orders
NULLo4FULL (and RIGHT): order whose customer 9 does not exist
  1. Three customers, four orders. Ali has two orders, Reza has none, and order o4 points to a customer id that doesn't exist.
  2. INNER JOIN returns only matching pairs. Ali appears twice because he has two orders: joins can multiply rows.
  3. LEFT JOIN keeps every customer. Reza has no match, so the order columns are NULL.
  4. FULL OUTER JOIN also keeps unmatched rows from the right side: o4 appears with a NULL customer. RIGHT JOIN is a LEFT JOIN with the tables swapped.
{{< /fig >}} Two more: `CROSS JOIN` pairs every row with every row (use deliberately, for example with a calendar table), and a **self join** joins a table to itself (employees and their managers). Anti-joins ("customers with no orders") are written as `LEFT JOIN ... WHERE o.order_id IS NULL` or `WHERE NOT EXISTS (...)`. ## Aggregation: GROUP BY and HAVING ```sql SELECT city, COUNT(*) AS orders, COUNT(DISTINCT customer_id) AS customers, SUM(amount) AS revenue, AVG(amount) AS avg_order FROM orders WHERE status = 'paid' GROUP BY city HAVING SUM(amount) > 1000 ORDER BY revenue DESC; ``` Every column in `SELECT` must either be in `GROUP BY` or wrapped in an aggregate. `WHERE` filters rows before grouping, `HAVING` filters groups after. ## NULL: the unknown value `NULL` means "unknown", and SQL uses three-valued logic: comparisons with NULL are neither true nor false but **unknown**, and `WHERE` keeps only rows where the condition is true. - `city = NULL` is never true. Use `city IS NULL`. - `COUNT(*)` counts rows; `COUNT(city)` counts non-null cities. `SUM` and `AVG` ignore NULLs. - `NOT IN (subquery)` returns nothing if the subquery contains a NULL. Prefer `NOT EXISTS`. - `COALESCE(city, 'unknown')` replaces NULL with a default. ## Changing data (CRUD) ```sql INSERT INTO orders (order_id, customer_id, city, status, amount, ordered_at) VALUES (9001, 42, 'Tehran', 'paid', 80.00, '2026-01-15 10:30'); UPDATE orders SET status = 'cancelled' WHERE order_id = 9001; DELETE FROM orders WHERE status = 'cancelled' AND ordered_at < '2025-01-01'; ``` Always write the `WHERE` of an `UPDATE` or `DELETE` first, and run it as a `SELECT` to see which rows it touches. Wrap risky changes in a transaction (`BEGIN; ... COMMIT;` or `ROLLBACK;`). Table structure is defined with DDL: `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE`, plus constraints (`PRIMARY KEY`, `FOREIGN KEY`, `NOT NULL`, `UNIQUE`, `CHECK`). ## Common mistakes - Using a `SELECT` alias in `WHERE` (it doesn't exist yet; see the figure). - Joining on a non-unique key and silently multiplying rows, then summing them. Check row counts before and after a join. - Filtering the right table of a `LEFT JOIN` in `WHERE`, which turns it into an inner join. Put the condition in `ON` instead. - `= NULL` instead of `IS NULL`, and `NOT IN` with NULLs. - `SELECT *` in production queries: wasteful on columnar engines and breaks when columns change. ## Learning Resources ### Books - [Learning SQL, 3rd Edition](https://www.oreilly.com/library/view/learning-sql-3rd/9781492057604/): Alan Beaulieu - [Sams Teach Yourself SQL in 10 Minutes](https://www.amazon.com/SQL-Minutes-Sams-Teach-Yourself/dp/0672336073): Ben Forta, short and hands-on ### Interactive - [SQLBolt](https://sqlbolt.com/) - [Select Star SQL](https://selectstarsql.com/) - [SQLZoo](https://www.sqlzoo.net/wiki/SQL_Tutorial) - [Mode SQL Tutorial](https://mode.com/sql-tutorial) ### Docs - [PostgreSQL Tutorial: The SQL Language](https://www.postgresql.org/docs/current/tutorial-sql.html) - [PostgreSQL: Joins Between Tables](https://www.postgresql.org/docs/current/tutorial-join.html)