We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningFoundation · Database Fundamentals
Structured Query Language for managing relational databases. Tables, rows, columns, and powerful joins to query related data.
In short
SQL (Structured Query Language) is the standard language for storing, reading, and changing data in relational databases, where data lives in tables made of rows and columns. You write declarative statements like SELECT, INSERT, UPDATE, and DELETE that describe the result you want, and the database engine figures out how to fetch it.
In short
SQL (Structured Query Language) is the standard language for storing, reading, and changing data in relational databases, where data lives in tables made of rows and columns. You write declarative statements like SELECT, INSERT, UPDATE, and DELETE that describe the result you want, and the database engine figures out how to fetch it.
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers SQL across a run of lessons, not one page. These 4 alone are about 105 minutes of step-by-step reading, every one with a quiz.
Database model comparison, relational vs non-relational data stores.
Distributed SQL databases that promise the scalability of NoSQL with the ACID guarantees of traditional SQL. CockroachDB, Google Spanner, and the NewSQL movement.
How Apache Ignite runs SQL on a partitioned cache. Measured tests show a query with the key stays fast, and one without it slows as the cluster grows.
Why parameterized queries stop SQL injection by keeping SQL code and user data apart. Shows code examples and what happens inside the database.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with SQL vs NoSQLpay once,
yours for life
SQL is a language, not a database. The same language works across PostgreSQL, MySQL, Microsoft SQL Server, Oracle, and SQLite, with small dialect differences between them. It was created at IBM in the 1970s based on Edgar Codd's relational model and became an ANSI standard in 1986.
Data in a SQL database is organized into tables. A table for users might have columns like id, email, and created_at, and each row is one user. Relationships between tables are expressed with keys: a primary key uniquely identifies a row, and a foreign key in one table points at the primary key of another. An orders table holding a user_id foreign key is how you connect an order back to the customer who placed it.
SQL is declarative. You say what you want, not how to get it. When you write SELECT name FROM users WHERE country = 'India', you never specify whether to scan the whole table or use an index. The query planner inside the database decides that for you based on statistics and available indexes.
When you send a query, the engine parses it, checks that the tables and columns exist, and hands it to the query planner. The planner generates several possible execution plans, estimates the cost of each using stored statistics about table sizes and value distributions, and picks the cheapest one. You can see this plan yourself by prefixing a query with EXPLAIN.
The biggest performance lever is the index. Without one, finding rows where email = 'a@b.com' means reading every row in the table, a full table scan. With a B-tree index on the email column, the engine walks a sorted tree structure and finds the match in roughly log(n) steps. On a table with 10 million rows that is the difference between reading 10 million rows and reading around 24.
Joins are how SQL combines rows from multiple tables. A query that pulls each order along with its customer name joins the orders and users tables on user_id. Internally the engine may use a nested loop, a hash join, or a merge join depending on table sizes and indexes. Most SQL databases also wrap groups of statements in transactions that give ACID guarantees, so a money transfer that debits one account and credits another either fully commits or fully rolls back.
Reach for SQL when your data has a clear structure, relationships matter, and correctness is non-negotiable. Banking ledgers, e-commerce orders, inventory, and user accounts are the classic fit because transactions and constraints stop the data from drifting into invalid states. A UNIQUE constraint on email guarantees you never get two accounts on the same address, enforced by the database itself rather than hoped for in application code.
The trade-off is schema rigidity and horizontal scaling. You define columns and types up front, and changing them on a huge live table can be slow. Relational databases also scale up (bigger machine) more easily than they scale out (many machines), which is why high-write workloads sometimes move to NoSQL stores. That said, modern Postgres comfortably handles tens of thousands of transactions per second on a single node, so most applications never hit that wall.
A common mistake is fearing joins. Joins are what relational databases are built and optimized for. Avoiding them by duplicating data across tables usually creates more bugs than it prevents, because now the same fact lives in two places and can disagree.
PostgreSQL
Open-source SQL database used by Instagram, Reddit, and Twitch to store core relational data with strong transactional guarantees.
MySQL
Powers the relational layer at Facebook, YouTube, and Booking.com, often sharded across many servers for scale.
Amazon Aurora
AWS managed database speaking the MySQL and PostgreSQL SQL dialects, with storage that scales automatically under the hood.
SQLite
An embedded SQL engine that ships inside every Android phone, iPhone, and web browser to store local app data in a single file.
It is a query language, not a general-purpose programming language. It is declarative and built for working with data in relational databases. You can't write a full application in plain SQL, though procedural extensions like PL/pgSQL add loops and variables.
SQL is the language. MySQL is a specific database product that you talk to using SQL. PostgreSQL, SQL Server, and Oracle are other products that all speak SQL with minor dialect differences.
Choose NoSQL when your data is unstructured or schema-less, when you need to scale writes across many servers, or when you don't need multi-row transactions. Choose SQL when relationships and correctness matter, such as financial or order data.
The most common cause is a missing index, forcing a full table scan. Run EXPLAIN on the query to see the plan, and add an index on the columns used in WHERE and JOIN conditions. Avoid SELECT star on wide tables and watch for queries inside loops.
ACID is four transaction guarantees: Atomicity (all changes succeed or none do), Consistency (the database moves only between valid states), Isolation (concurrent transactions don't corrupt each other), and Durability (committed changes survive a crash).
NewSQL Databases
Distributed SQL databases that promise the scalability of NoSQL with the ACID guarantees of traditional SQL. CockroachDB, Google Spanner, and the NewSQL movement
Apache Ignite
How Apache Ignite runs SQL on a partitioned cache. Measured tests show a query with the key stays fast, and one without it slows as the cluster grows.
Parameterized Queries
Why parameterized queries stop SQL injection by keeping SQL code and user data apart. Shows code examples and what happens inside the database.
Prepared Statements
Pre-compiled SQL templates that combine security with performance, parse once, execute many times
Database Views
A view and the query it is defined as produced identical plans, character for character. A materialised view answered in 0.017 ms what took 94.929 ms to compute, and is a cache with every problem that implies
NoSQL
Databases that don't use traditional table-based relational models. Includes document stores, key-value, graph, and column-family databases.
Database
An organized collection of data that can be easily accessed, managed, and updated. The backbone of almost every application.
ACID
Four guarantees for database transactions: Atomicity (all or nothing), Consistency (valid states only), Isolation (no interference), Durability (changes persist).
BASE
An alternative to ACID for distributed systems: Basically Available, Soft state, Eventually consistent. Trades strong consistency for availability.
Sharding
Splitting a database into smaller pieces (shards) distributed across multiple servers. Each shard holds a subset of the data.
Replication
Keeping copies of the same data on multiple servers. Improves read performance and provides fault tolerance if one server goes down.
This page explains the idea. The "SQL vs NoSQL" lesson builds it up step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
Open the SQL vs NoSQL lessoncourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about SQL in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
