Introduction to SQL

Welcome to the 24-chapter SQL for Beginners series on Scriptly. Across these chapters you will go from your first SELECT to joins, subqueries, window functions and indexes, following one small online shop from an empty folder to a working application. This first chapter answers the opening questions: what SQL is, why a declarative query language has outlived every fashion in programming, what a relational database actually stores, and how the dialects in the wild differ from the SQLite engine this series hands you first.

What SQL is

SQL stands for Structured Query Language. It was invented at IBM in the 1970s for the System R project, standardised by ANSI and ISO, and is now spoken by every serious relational database: SQLite, MySQL, PostgreSQL, Microsoft SQL Server and Oracle. Everybody writes “SQL”; half the industry says “sequel” and half spells the four letters out — both are accepted, and you will hear both in interviews.

The syntax is deliberately close to English — SELECT city FROM customers WHERE country = 'India' reads as a sentence — and keywords are spelled out in full rather than abbreviated. That plainness is a large part of why SQL outlived the query languages invented alongside it.

• Declarative — you describe the rows you want and leave the how to the engine: no loops, no counters, no hints about where to search.
• Set-based — one statement acts on a whole set of rows at once. “Loop over the array and filter it” is exactly the habit SQL replaces.
• Standardised, then forked — the core grammar is portable, and each vendor adds its own functions, pagination tricks and upserts on top of it.
• Two halves — DDL (CREATE, ALTER, DROP) shapes the tables; DML (SELECT, INSERT, UPDATE, DELETE) moves the data. This series lives inside SELECT until chapter 7.
• Not a programming language — a query computes an answer from tables; loops, user interfaces and network calls belong to the application around it.

It helps to name the families early, because every SQL statement you ever meet belongs to one of them: queries (SELECT), data changes (INSERT, UPDATE, DELETE), schema changes (CREATE, ALTER, DROP), permissions (GRANT, REVOKE) and transaction control (BEGIN, COMMIT, ROLLBACK). Five families, and this series touches all five by chapter 21.

One more piece of vocabulary: a relational database management system, or RDBMS, is the program that stores the tables, runs your SQL and keeps the data intact when the power goes out. In this series the RDBMS is SQLite; its transactional side gets a whole chapter of its own later on.

Why learn raw SQL

Spreadsheets and ORMs will happily move rows around for you, right up until the question gets interesting. Then there is no substitute:

• Frameworks leak — every Laravel, Django or Rails project eventually needs one custom join, one report or one slow query fixed, and the escape hatch is always raw SQL.
• Reporting is SQL — “revenue by country by month” is a three-line GROUP BY here and a twenty-line method in application code.
• Interviews ask it — SQL turns up in a large share of data and backend interviews, and it is one of the few skills that transfers unchanged between employers.
• It is small — a working vocabulary is about thirty keywords, so you can be competent in a weekend and fluent in a season.
• It is stable — the SQL you learn now behaves like the SQL of 1995 and will still behave that way long after today’s framework has been retired.

There is a selfish reason too: this is one of the fastest gaps to close in the whole of software. A developer who can read a slow query, write a window function and normalise a schema is doing work that a colleague with a query builder cannot, and the distance between the two is a few evenings rather than a degree.

Where SQL runs

The language travels well. Anywhere rows are stored in tables, you will find SQL or something deliberately shaped like it:

• Relational databases — MySQL and PostgreSQL behind much of the web, SQL Server and Oracle behind most enterprises, SQLite behind most single-user applications.
• Analytics warehouses — BigQuery, Snowflake and Redshift speak SQL because analysts never stopped asking for it.
• Spreadsheets and dashboards — Power Query, Google Sheets add-ons and BI tools such as Metabase all emit SQL under the hood.
• Embedded and mobile — every iPhone and Android phone carries a SQLite database for settings, messages and app state.
• Big data engines — Spark SQL, DuckDB and Hive run the same grammar over files that never lived in a database at all.

Learn it once, and each of those becomes another place you can work without learning a second query language.

Tables, rows and columns

A relational database stores data in tables. Each table has a fixed set of columns with declared types, and a growing set of rows, one per record. This series runs on a small shop: six customers, six products, six orders and eight order lines. Two properties matter more than they look: rows have no order of their own — the arrangement below is simply how one query printed them — and columns are fixed when the table is created, so a value that has not arrived yet is stored as NULL rather than by adding a column. The customers table on the way out of a query:

id  first_name  last_name  city       country
--  ----------  ---------  ---------  ---------
1   Aarav       Sharma     Mumbai     India
2   Priya       Nair       Delhi      India
3   Lena        Fischer    Berlin     Germany
4   Marc        Tremblay   Toronto    Canada
5   Wei         Ling       Singapore  Singapore
6   Amara       Ochieng    Nairobi    Kenya

Every column also carries a declared type — TEXT for names, INTEGER for ids and stock, and dates written as text in the YYYY-MM-DD layout you are about to see. SQL is looser about types than a compiled language: SQLite in particular will store a word in an integer column without complaining, so picking sound types and constraints is a subject of its own in chapter 8.

Two structural ideas are already at work. Every row carries an id — a primary key that names it uniquely, so two customers may share a city but never an id. And orders.customer_id points back at customers.id: a foreign key, the link that lets a query put the shop back together after it was split across four tables. Chapter 9, Keys and Referential Integrity, is devoted to those links.

The orders table makes that relationship concrete — six rows, and a column that points at a customer rather than repeating a name:

id  customer_id  order_date  status
--  -----------  ----------  ---------
1   1            2026-01-12  paid
2   1            2026-01-20  shipped
3   2            2026-02-03  cancelled
4   3            2026-02-11  shipped
5   5            2026-02-25  paid
6   6            2026-03-02  paid

Customer 1 appears twice, so somebody placed two of the six orders — and only a join back to customers can tell you it was Aarav. Hold that thought until chapter 10.

Ask for one column and you get one column; ask for two and the rows come back as pairs. Here is the same table cut down to two fields:

SELECT first_name, city FROM customers;

Expected output:

Aarav|Mumbai
Priya|Delhi
Lena|Berlin
Marc|Toronto
Wei|Singapore
Amara|Nairobi

The vertical bars are sqlite3’s default field separator, which the next chapter replaces with proper headers. Six rows in, six rows out: the result of a query is another table, which is why SQL is called a closed language — you can build a question whose answer looks exactly like its ingredients.

Declarative: say what, not how

Here is a fuller question — the first names of everyone in India, alphabetised by surname:

SELECT first_name, last_name
FROM customers
WHERE country = 'India'
ORDER BY last_name;

Expected output:

Priya|Nair
Aarav|Sharma

Notice what is missing: no loop, no comparison algorithm, no instruction about which index to use. You described the answer and the engine chose a plan. The imperative version needs a loop, a condition, a sort and a result list — forty lines of Python or TypeScript instead of four of SQL — and all forty have to be rewritten the moment the question changes. The database this statement queries is built in the next chapter; you will run it there exactly as written.

That bargain has a price: you give up control of how. When a query runs slowly the lever you pull is a different one — an index, a rewritten join, a better schema — and chapter 19, Indexes and Query Performance, is about exactly that.

There is a second way to lose the plot, by the way: writing a query so clever that nobody can read it a month later. SQL rewards plain statements — one clause per line, keywords in capitals, tables named in full — and chapter 24 collects the rules that keep your queries readable long after you wrote them.

One language, several dialects

SQL is standardised, but vendors compete on the edges. These are the differences you will meet first:

Every query in this series is plain SQL that runs on SQLite — an engine that lives in a single file, needs no server to install, and already ships with macOS and most Linux distributions. Where a dialect diverges, the chapter you are reading flags it at the moment it matters, so the habits you build here stay portable to a server database later.

Notice how small the differences are for everyday queries: a SELECT with a WHERE clause and an ORDER BY runs unchanged on all three engines. The divergences live at the edges — pagination, string handling, the moment you insert a row — so the core is worth learning properly and the edges are then a checklist rather than a retraining.

How this series teaches SQL

The order is deliberate: read first, then write, then design. Nothing is assumed beyond comfort with a terminal.

1. Chapters 2–6 — install SQLite, meet SELECT, filter with WHERE, sort and limit, then summarise with GROUP BY.
2. Chapters 7–9 — change data with INSERT, UPDATE and DELETE, choose types and constraints, and wire tables together with keys.
3. Chapters 10–16 — combine data: joins, subqueries, set operations, common table expressions and window functions.
4. Chapters 17–21 — work with strings, dates and CASE, then look underneath at indexes, views and transactions.
5. Chapters 22–24 — design a schema, put SQL into a real application, and adopt the habits that keep it readable.

Every example runs against the same six-customer shop, with fixed ids, prices and dates, so a result you see in chapter 6 still matches the rows you insert in chapter 2. Nothing is renamed halfway through, and every output block in this series was produced by running the query directly above it.

The chapters share a shape, too: one idea at a time, worked examples against the shop, a list of the mistakes people actually make, and a five-item checklist. The checklists are not decoration — typing a query out by hand is what turns recognition into recall, so keep a terminal beside you rather than reading on the train.

They are also independent enough to revisit. Each chapter states what it builds on in its opening paragraph, so six months from now you can come back for the page on IN, HAVING or LIMIT without rereading the sequence from the start.

Common mistakes

• Learning “SQL” from one product’s tutorial — CONCAT(), TOP and clever LIMIT placements are dialect habits rather than the language; learn the standard core first and the vendor notes second.
• Treating a table like a spreadsheet — rows have no guaranteed order and the column list is fixed when the table is created; the grid you see is an output formatting choice, not the storage.
• Expecting imperative control flow — queries contain no loops and no if statements; conditions live in WHERE and, later, in CASE expressions.
• Confusing DDL with DML — CREATE changes a table’s shape and INSERT changes its contents; reaching for the wrong family of statements is how tables get emptied.
• Skipping straight to the ORM — an ORM emits exactly this language, so reading it means you can debug and tune what your framework quietly generates behind your back.
• Memorising syntax without data — keywords stick only once you have run them against rows you care about, which is why every chapter here hands you a database before it hands you theory.

Practice checklist

1. Define table, row, column, primary key and foreign key in your own words, using the customers grid above as your example.
2. Type the India query by hand and predict its two output rows before you press Enter.
3. Explain out loud why SELECT first_name FROM customers; needs no loop.
4. Write the four DML statements from memory, plus one question each one answers.
5. Find out which database your favourite application uses, then note one way its dialect differs from the table above.

What's next?

A query language is no good without something to run it on. Next you install SQLite, create the four shop tables and load the sample rows that every later chapter queries: Setting Up SQLite and Your First Query.