SQL
92 bites tagged SQL — interview questions with model answers, and 60-second explainers.
The Query Optimizer: Your Database's Internal GPS
A query optimizer is your database's internal GPS, turning your SQL "what" into the fastest "how." It chooses the best execution plan—like join order or index usage—for every query. The footgun: stale statistics can trick it into picking a slow route.
Query Execution Plan: The Database's Road Map
A query execution plan is the database's internal strategy for fetching your data. It's the recipe it creates before running your SQL. This plan determines whether to use an index or scan a whole table, directly impacting performance.
Surrogate Keys: Stable IDs for Unstable Data
A surrogate key is a meaningless, system-generated ID that never changes, unlike a 'natural' key (like an email) which can. Use it for stable joins between tables. The footgun is exposing these internal IDs in public URLs, which leaks data structure.
Third Normal Form (3NF): Nothing But The Key
Third Normal Form (3NF) dictates that every non-key column must describe 'nothing but the key.' This prevents transitive dependencies, where one non-key column determines another.
Junction Table: Connecting Many to Many
A junction table is a bridge between two other tables, enabling a many-to-many relationship. It's used to connect students to classes or tags to articles. The common footgun is forgetting a unique constraint, allowing duplicate connections.
First Normal Form (1NF): No Nested Data
First Normal Form (1NF) means every table cell holds one single value, not a list. It's the first rule of relational databases, stopping you from storing comma-separated strings. The footgun is stuffing multiple values into one field, which breaks SQL queries.
Boyce-Codd Normal Form (BCNF): Stricter Than 3NF
BCNF is a database design rule stricter than 3NF, ensuring no attribute is determined by anything but a superkey. It's used to eliminate all data redundancies from functional dependencies, but can force trade-offs with dependency preservation.
Codd's 12 Rules: The Relational Database Litmus Test
Codd's 12 Rules are a litmus test for whether a database is truly "relational." They demand that all data and operations be managed purely through the relational model, not through lower-level system access.
Relational Algebra: The Math Behind SQL Queries
Relational algebra is the formal logic behind SQL, treating tables as mathematical sets. It provides a grammar for operations like joins and filters, allowing a database to translate your declarative query into a precise, optimizable execution plan.
Database Views: A Saved Query That Acts Like a Table
A database view is a saved query you interact with like a real table. It simplifies complex joins for applications or restricts data access for security, showing only specific rows or columns.
Database Index: The Phonebook for Your Data
An index is like a book's index, letting you jump to the right row without scanning the whole table. It's essential for columns used in WHERE clauses, but over-indexing slows writes since every index must be updated on data changes.
Foreign Keys: The Glue of Relational Databases
A foreign key is a pointer from one table to another, linking related data like a user to their posts. It enforces referential integrity, ensuring a post can't exist without a valid user. The footgun is forgetting this blocks deletions of referenced records.
Database Normalization: Tidy Tables, Less Redundancy
Database normalization organizes data into smaller, related tables to eliminate redundancy, like separating addresses from orders. It's the standard for transactional (OLTP) systems where data integrity is critical.
The Relational Model: Data as Simple Tables
The relational model organizes data into simple tables (relations) of rows (tuples), forming the foundation of SQL databases. It's used for structured data where consistency is critical, like in banking.
Primary Keys: The Unique ID for Every Row
A primary key is like a social security number for a database row, uniquely identifying it. It's used to reliably fetch records and link related data across tables. The footgun is using a natural key, like an email address, that might change over time.
How a SQL SELECT Query Actually Runs
A SQL SELECT query runs in a different order than you write it. It first builds the dataset with FROM/JOINs and filters it with WHERE, only then computing the final columns in SELECT. This is crucial for debugging.
Apache Hive: SQL on Hadoop
Apache Hive translates SQL-like queries into Hadoop MapReduce jobs, letting you analyze huge datasets with familiar syntax. Don't mistake it for a real-time database; its power is in high-throughput batch processing, not low-latency queries.
dbt: Managing Data Transformations as Code
dbt treats your data transformations as a software project, letting you build, test, and version control your SQL. It's the 'T' in the modern ELT paradigm. Use it to create reliable data models in a warehouse. The footgun: dbt only transforms data.
Google BigQuery: A Serverless Data Warehouse
Think of BigQuery as a massive SQL database you don't have to manage. It's a serverless data warehouse for analyzing huge datasets, like terabytes of application logs. The footgun is treating it like a regular database for real-time transactions.
Star Schema: The Blueprint for Analytics Data
A star schema organizes analytics data with a central fact table (e.g., sales) surrounded by dimension tables (e.g., customers). It's built for fast queries in data warehouses. The footgun is normalizing dimensions, which negates its speed advantage.
Get SQL bites daily.
Five a day, five minutes, offline. With quizzes so it sticks.
Open testing — you’ll join as an early tester.