{"title":"What is SQL?","description":null,"content":"Almost every application that works with structured data, from music streaming apps to banking platforms, relies on SQL to communicate with its database.\n\nSQL has been around since the 1970s. Despite decades of new technologies attempting to replace it, it continues to be the standard way to interact with relational databases.\n\nIn this chapter, we’ll build a clear mental model of SQL. We’ll start with what SQL is and why it’s declarative rather than imperative. Then we’ll walk through the different types of SQL commands, and finally, look under the hood to understand what actually happens inside a database engine when a query runs.\n\n---\n\n# Why SQL Still Runs the World\n\nSQL first appeared in the 1970s. It predates Java, Python, JavaScript, Go, and every other language most engineers use today. And it's outlived every technology that tried to replace it.\n\nIn the early 2010s, NoSQL databases like MongoDB and Cassandra gained massive popularity. The pitch was compelling: ditch the rigid table structure, scale horizontally, move fast. Many teams did exactly that. But something interesting happened. Most of those NoSQL databases eventually added SQL-like query interfaces. Cassandra got CQL. MongoDB added an aggregation framework that looks suspiciously like SQL pipelines. Google's Spanner, built for planet-scale distributed storage, speaks SQL natively.\n\nThe industry kept circling back to SQL because it solves a fundamental problem well: giving humans a readable, standardized way to ask questions about data.\n\nSQL also crosses role boundaries in a way few technologies do. Backend engineers write it to power APIs. Data engineers use it to build ETL pipelines. Data scientists query it for feature engineering. Analysts build dashboards with it. Product managers run ad-hoc queries to answer business questions.\n\nIn interviews, SQL shows up for backend roles, full-stack roles, data engineering screens, and even some frontend take-home projects that involve database schemas.\n\n---\n\n# What SQL Actually Is\n\nBefore SQL existed, working with data was painful. Applications stored information in flat files, each with its own proprietary format. If you wanted to find all customers in California, you'd write a program that opened the file, read it line by line, parsed the fields, checked the state column, and collected the matches. Change the file format and you'd rewrite the program. Move to a different storage system and you'd rewrite it again. The application logic and the data storage were tightly coupled.\n\nIn 1970, Edgar F. Codd, a researcher at IBM, published a paper that changed everything. His key insight was simple but powerful: separate the logical view of data from its physical storage. Let users describe the data they want using a high-level language, and let the system figure out how to retrieve it efficiently. This idea became the foundation of relational databases, and SQL became the language built to talk to them.\n\nSQL stands for **Structured Query Language**. It's a domain-specific language, not a general-purpose programming language. You can't build a web server with SQL or write a mobile app in it. But for its one job, communicating with relational databases, it's remarkably effective.\n\nHere's what a SQL query looks like. Say we want to find all StreamFlow users based in the US:\n\n\n```sql\nSELECT username, email, country\nFROM users\nWHERE country = 'US';\n```\n\n\nThree lines. No loops, no file handling, no parsing logic. You described the result you wanted and the database returned it.\n\n---\n\n# Declarative vs Imperative\n\nMost programming languages are imperative. You write step-by-step instructions: open this file, loop through the rows, check this condition, add matches to a list, return the list. You control the entire process.\n\nSQL works differently. It's declarative. You describe the result you want and the database engine decides how to get it.\n\nHere's a concrete comparison. Suppose we want to find all premium users in StreamFlow.\n\n**Imperative approach (Python):**\n\n\n```python\npremium_users = []\nfor user in load_all_users():\n if user[\"subscription_tier\"] == \"premium\":\n premium_users.append(user[\"username\"])\n```\n\n\n**Declarative approach (SQL):**\n\n\n```sql\nSELECT username\nFROM users\nWHERE subscription_tier = 'premium';\n```\n\n\nBoth produce the same result. But the SQL version doesn't tell the database *how* to find those users. Should it scan every row? Use an index on `subscription_tier`? Read from a cache? That's the database engine's decision, not yours.\n\nThis matters because the database engine has information you don't. It knows how many rows the table has, whether an index exists on the column you're filtering, how the data is distributed across disk, and what's already cached in memory. When you describe what you want, the engine can choose the fastest path using all of that context. When you dictate the steps, you lock it into your approach, which is often slower.\n\nThink of it like ordering at a restaurant. You say \"I'll have the pasta.\" You don't walk into the kitchen and say \"Boil 2 liters of water, wait 8 minutes, add 200 grams of penne, drain, heat olive oil in a pan...\" The chef knows how to make pasta better than you do. SQL works the same way: you order the result, the database engine is the chef.\n\nThis is possible because every SQL query passes through an optimizer before it runs. The optimizer evaluates different strategies for getting your result and picks the cheapest one. We'll see exactly how that works later in this chapter.\n\n\n```mermaid\nflowchart TB\n subgraph Declarative\n\t\tdirection TB\n B1[Describe Result]:::green --> B2[Database Optimizes
and Executes]:::green\n B2 --> B3[Return]:::green\n end\n\t\n subgraph Imperative\n\t\tdirection TB\n A1[Load All Data]:::orange --> A2[Loop Through Rows]:::orange\n A2 --> A3[Check Condition]:::orange\n A3 --> A4[Build Result List]:::orange\n A4 --> A5[Return]:::orange\n end\t\n\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\nFive steps versus two. That's the fundamental trade-off: imperative gives you control, declarative gives you efficiency. For data operations, declarative almost always wins.\n\n---\n\n# The Four Types of SQL Commands\n\nSQL isn't just SELECT statements. Every SQL command falls into one of four categories, each with a different job.\n\n**DDL (Data Definition Language)** builds and modifies the structure of your database. Think of it as the architect’s toolkit. It determines which tables exist, what columns they contain, and how they relate to each other.\n\n\n```sql\nCREATE TABLE artists (\n artist_id SERIAL PRIMARY KEY,\n name VARCHAR(100) NOT NULL,\n country VARCHAR(2),\n verified BOOLEAN DEFAULT FALSE\n);\n```\n\n\nOther DDL commands include `ALTER TABLE` (modify an existing table's structure) and `DROP TABLE` (delete a table entirely).\n\n**DML (Data Manipulation Language)** works with the actual data inside those structures. This is where you'll spend 90% of your time, and it's the focus of most interview questions.\n\n\n```sql\n-- Insert a new artist\nINSERT INTO artists (name, country, verified)\nVALUES ('Luna Nova', 'BR', TRUE);\n```\n\n\n\n```sql\n-- Find tracks longer than 5 minutes\nSELECT title, duration_seconds\nFROM tracks\nWHERE duration_seconds > 300;\n```\n\n\nDML also includes `UPDATE` (modify existing rows) and `DELETE` (remove rows).\n\n**DCL (Data Control Language)** manages permissions: who can read which tables, who can modify data, who can create new structures.\n\n\n```sql\nGRANT SELECT ON streams TO analyst_role;\nREVOKE DELETE ON users FROM intern_role;\n```\n\n\n**TCL (Transaction Control Language)** groups multiple operations into atomic units. Either all of them succeed, or none of them do.\n\n\n```sql\nBEGIN;\nUPDATE payments SET status = 'refunded' WHERE payment_id = 4821;\nUPDATE payments SET refund_amount = 9.99 WHERE payment_id = 4821;\nCOMMIT;\n```\n\n\nIf something fails between `BEGIN` and `COMMIT`, you can `ROLLBACK` to undo everything. This prevents your database from ending up in a half-updated state.\n\nHere's how these four categories fit together:\n\n\n```mermaid\nflowchart TD\n SQL[SQL Commands]:::cyan\n\n DDL[DDL
Data Definition]:::orange\n DML[DML
Data Manipulation]:::green\n DCL[DCL
Data Control]:::teal\n TCL[TCL
Transaction Control]:::teal\n\n DDL_cmds[CREATE, ALTER, DROP]:::orange\n DML_cmds[SELECT, INSERT,
UPDATE, DELETE]:::green\n DCL_cmds[GRANT, REVOKE]:::teal\n TCL_cmds[BEGIN, COMMIT,
ROLLBACK]:::teal\n\n SQL --> DDL --> DDL_cmds\n SQL --> DML --> DML_cmds\n SQL --> DCL --> DCL_cmds\n SQL --> TCL --> TCL_cmds\n\n classDef cyan fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n```\n\n\nDML is by far the most commonly used category. Most day-to-day database work involves querying and modifying data, which is why the bulk of this course focuses on DML operations.\n\n---\n\n# SQL in the Modern Tech Stack\n\nSQL isn’t limited to database administration. It shows up across almost every layer of modern software.\n\n#### Web Applications\n\nMost backend frameworks rely on SQL behind the scenes. Whether you’re using Django, Rails, Spring Boot, or Express, your code eventually turns into SQL queries, even if you’re working through an ORM in Python or JavaScript.\n\nWhen something gets slow, you usually end up inspecting or writing raw SQL to fix it. Understanding what’s happening at that level often turns hours of debugging into minutes.\n\n#### Data Engineering\n\nSQL is the backbone of most data pipelines. ETL workflows use it to move and transform data across systems. Modern data warehouses like Snowflake, BigQuery, and Amazon Redshift are built around SQL.\n\nEven tools like Apache Spark and dbt lean heavily on SQL as their primary interface.\n\n#### Machine Learning\n\nBefore any model training begins, most of the work happens in SQL. Feature engineering involves querying datasets, joining tables, computing aggregates, and validating data quality.\n\nIn practice, SQL is what shapes the data before it ever reaches Python or a machine learning framework.\n\n#### Analytics and Business Intelligence\n\nDashboards, reports, and ad-hoc analysis all depend on SQL. Tools like Metabase, Looker, and Tableau generate SQL queries under the hood.\n\nAnalysts who can write SQL directly tend to move much faster than those relying only on visual interfaces.\n\n---\n\n# How a SQL Query Gets Executed\n\nWhen you run a SQL query, the database doesn’t simply “find the data.” It processes your request through a pipeline of three stages: parsing, optimization, and execution.\n\nUnderstanding this flow gives you a much clearer mental model of how SQL works, and why the declarative approach is so powerful.\n\nLet’s walk through what happens step by step.\n\n\n```sql\nSELECT username\nFROM users\nWHERE country = 'US';\n```\n\n\n\n```mermaid\nflowchart LR\n Q[SQL Query]:::cyan --> P[Parser]:::orange\n P --> O[Optimizer]:::orange\n O --> E[Executor]:::green\n E --> R[Results]:::cyan\n\n classDef cyan fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\n### Stage 1: The Parser\n\nThe parser acts like a strict grammar checker.\n\nIt reads your SQL query and validates everything:\n\n- Is the syntax correct?\n- Do the referenced tables exist?\n- Are the column names valid?\n\nIf anything is off, the query fails immediately with an error. No data is touched.\n\nIf everything looks good, the parser converts your SQL into an internal structure called a **parse tree**. This is a structured representation of your query that the database can actually work with.\n\n### Stage 2: The Optimizer\n\nThis is where the real intelligence comes in, and it's the reason declarative SQL works so well.\n\nThe optimizer takes the parse tree and figures out the most efficient way to execute your query. It doesn’t just follow one path, it evaluates multiple possible strategies.\n\nFor example:\n\n- Scan the entire table and filter rows\n- Use an index to directly jump to matching rows\n\nIt estimates the cost of each approach using internal statistics like:\n\n- Total number of rows\n- Distribution of values in columns\n- Available indexes\n\nThen it picks the fastest plan.\n\nOn a large table, this decision can make a massive difference. An index scan might be thousands of times faster than a full table scan, and the optimizer knows that because it understands your data.\n\nThis is exactly why SQL is declarative. You describe *what* you want, and the database figures out *how* to get it efficiently.\n\n### Stage 3: The Executor\n\nOnce the optimizer chooses the best plan, the executor takes over.\n\nIt actually runs the query:\n\n- Reads data from disk or memory\n- Applies filters (like `country = 'US'`)\n- Selects the required columns\n- Builds the final result set\n\nFinally, it sends the results back to your application.\n\n---\n\nYou write a simple query. Behind the scenes, the database:\n\n- Validates the syntax\n- Evaluates multiple execution strategies\n- Chooses the most efficient one\n- Reads and processes the data\n- Returns the result\n\nAll of this happens in milliseconds. That’s the power of SQL. You focus on the question, and the database handles the complexity of execution.","pageType":"sql"}

What is SQL?

Ashish Pratap Singh

Get Premium