Almost every piece of software you use daily stores information somewhere: your bank stores transactions, an online shop stores orders, a messaging app stores conversations. That "somewhere" is, in the vast majority of cases, a relational database, and the language you use to talk to it is called SQL. This first lesson won't teach you to write complex queries yet: it gives you the mental map you need so that everything that follows fits together. You'll understand what problem SQL solves, how it differs from the programming languages you may already know, how the language is organised internally, and why, fifty years after it was invented, it remains one of the most valued technical skills on the market.

1. What SQL is and what problem it solves
2. SQL is not the same thing as a DBMS
3. A brief history: from Codd's paper to the ANSI/ISO standard
4. The standard versus the dialects
5. SQL is declarative, not imperative
6. The five sublanguages: DDL, DML, DQL, DCL and TCL
7. Where SQL is used today and why it's still essential
8. The course case study: GreenStore
9. Common Mistakes and Tips
10. Exercises
11. Conclusion

SQL stands for Structured Query Language. It is a language specialised in one single thing: describing what data you want from a relational database, and what you want to do with it.

To understand the problem it solves, imagine you manage a shop's orders in a folder full of text files. Every time you want to answer a question as simple as "how much did we bill in France last month?" you would have to:

• Open every file and read it from start to finish.
• Write code that splits the fields on each line.
• Filter by country and by date by hand.
• Add up the amounts while being careful with the decimals.
• Repeat the whole process from scratch for the next question.

And that's without counting the worse problems: what happens if two people write to the same file at once? How do you guarantee that an order isn't left half-saved if the power goes out? How do you stop someone from recording an order for a customer who doesn't exist?

A relational database solves all of that, and SQL is the interface that gives you access to that machinery. With SQL, the question above is a single sentence that the system translates for you into an efficient reading plan:

Don't worry about the syntax yet: just notice that you're describing the result you want, not the steps to get it.

In short, SQL lets you:

This is the number one confusion for beginners. It's worth settling from the start:

• SQL is the language. It is a written specification, just as HTML or English are.
• A DBMS (Database Management System) is the program that understands that language, stores the data on disk, indexes it, handles concurrency and returns results to you.

The useful analogy: SQL is to a DBMS what the English language is to the people who speak it. They all speak English, but each one with their own accent and local expressions.

These are the most common relational DBMSs:

Course decision: every example is written and tested for PostgreSQL 16. When MySQL or SQLite behave differently in something that matters, you'll find a note or a comparison table. We'll never mix dialects without warning you.

Knowing the history helps you understand why the language is the way it is (quirks included).

The important lesson: SQL is not a language frozen in the 1970s. Many of the most powerful tools you'll see in module 10 (window functions, CTEs, JSON) are modern additions and sit at the heart of today's data analysis.

The ANSI/ISO standard defines a common language, but no DBMS implements 100 % of it, and they all add extensions of their own. That's what we call a dialect.

A concrete example: asking for "only the first 5 rows".

Another example: concatenating two strings.

Practical consequence: 80 % of what you learn here will work as-is on any DBMS. The remaining 20 % will need small adjustments. Learning one dialect well (PostgreSQL) and knowing that differences exist is far more useful than trying to learn a "generic SQL" that nobody actually speaks.

This is the idea that people coming from Python, Java or JavaScript struggle with most, and the one that pays off most if you grasp it early.

• In an imperative language you describe the procedure: open this, walk through that, accumulate, check, close.
• In a declarative language you describe the desired result, and the system decides how to get it.

Compare in your head the two ways of answering "give me the customers in Valencia".

Imperative approach (Python-style pseudocode):

Declarative approach (SQL):

In the SQL version there is no loop, no positional index, no accumulator variable. You declare the what; the DBMS's query optimizer decides the how: whether to read the whole table, whether to use an index, in what order to combine tables, whether to parallelise the work across several processors.

The most important mental consequence: in SQL you think in sets, not in rows one at a time. If you catch yourself thinking "and now I go through each order and...", there is almost always a set-based way to express it, shorter and vastly faster.

Careful: the fact that the optimizer decides doesn't mean you can write anything you like. Two queries that return the same result can take 3 milliseconds or 3 minutes. Module 8 is dedicated to precisely that.

SQL is big enough that it's worth splitting it into families of statements. It isn't a formal division in the standard, but it's the classification everyone uses and it'll help you place each module of the course.

One line of each, already using the GreenStore tables you'll meet in lesson 01-06:

An important note about SELECT: many people classify SELECT under DML, because the standard doesn't recognise "DQL" as a separate category. Both versions are acceptable; what matters is that you distinguish reading from writing, because permissions, performance and risks are very different in each case.

• Application backends. Every application with users, orders or content needs persistence. Even if you use an ORM (Hibernate, Django ORM, Prisma, SQLAlchemy), the ORM generates SQL: if you can't read it, you won't know why your page takes eight seconds to load.
• Data analytics. It's the analyst's daily tool: segmenting customers, measuring retention, calculating revenue by channel.
• Business Intelligence. Power BI, Tableau, Metabase, Looker and Superset all lean on SQL, and they all let you (or require you to) write queries by hand for anything non-trivial.
• Data engineering. Modern data warehouses (BigQuery, Snowflake, Redshift, Databricks SQL) are queried with SQL, and tools like dbt consist literally of organising transformations written in SQL.
• Data science and AI. The step before any model is obtaining and cleaning the dataset, and that step almost always starts with a SQL query.

Why has nothing replaced it? Its death was announced with the NoSQL movement of 2010, and the opposite happened: NoSQL systems ended up adding SQL-like query layers. The reasons it has endured:

1. It's based on mathematics, not on fashion: relational algebra is still valid.
2. It's portable as a skill: you change company, DBMS or decade, and your knowledge transfers.
3. It separates the what from the how: engines improve their performance without forcing you to rewrite your queries.
4. It's the common language between development, analytics and the business.

From here until the final project, every example and exercise in the course will use the same database: that of GreenStore, a fictional organic products online shop.

• Headquarters: Valencia (Spain).
• Catalogue: organic food, natural cosmetics, sustainable home, drinks, personal hygiene and supplements.
• Markets: Spain, Portugal and France.
• Operations: it sells through the web (orders with no sales rep assigned) and by phone (orders handled by a sales rep on the team). It works with suppliers from several countries, manages stock, accepts customer reviews and processes returns.

Its database has nine tables: categories, suppliers, products, customers, employees, orders, order_lines, reviews and returns. You'll see it in detail, with its diagram and its load script, in lesson 01-06.

Always working on the same case has a huge advantage for self-study: you won't spend energy understanding a new context in every lesson, and you'll be able to compare how the same question is answered with better and better tools.

Here are some of the business questions you can't answer today and will handle comfortably by the end of the course:

• Confusing SQL with "the database". Sentences like "we use SQL" are ambiguous. The correct thing is "we use PostgreSQL" (the DBMS) "and we query it with SQL" (the language).
• Believing there's a single SQL. Copying a query from Stack Overflow written for MySQL and expecting it to work on PostgreSQL is a constant source of frustration. Always check which engine what you're copying was written for.
• Thinking row by row. The most expensive mental mistake. If your first instinct is "I go through each order", stop: in SQL you operate on complete sets.
• Assuming SQL is "a thing for old databases". It's exactly the other way round: the rise of analytics and data engineering has multiplied demand for it over the last decade.
• Tip: learn one dialect thoroughly. Mastering PostgreSQL and knowing the main differences is worth more than a superficial knowledge of five engines.
• Tip: always write your queries by hand at the beginning. Assistants and automatic generators are useful later; at the start they stop you from internalising the mental model.

These exercises are conceptual: you don't need anything installed yet.

Classify each of these sentences by indicating which sublanguage it belongs to (DDL, DML, DQL, DCL or TCL) and briefly justify why:

1. Adding a phone column to the customers table.
2. Recording a new order for customer 7.
3. Finding out how many products there are in the "Drinks" category.
4. Undoing every change made since the start of the operation in progress.
5. Letting the analyst user read the orders table but not modify it.
6. Removing the returns table completely, structure included.

A colleague tells you: "I wrote the query as SELECT TOP 10 * FROM products and PostgreSQL gives me a syntax error. Clearly PostgreSQL doesn't comply with the SQL standard."

Explain in three or four sentences why their reasoning is wrong and how they should frame the problem.

Translate into plain English, in a single sentence, what this query declares. Don't try to describe the steps the engine takes, but the result being asked for:

Solution 1

Notice the nuance in point 6: deleting the rows of returns would be DML (DELETE), but deleting the table is DDL (DROP). The same everyday verb, two different families.

Solution 2

The reasoning fails on two counts. First, SELECT TOP 10 is not part of the ANSI/ISO standard: it's an extension of SQL Server's own, so PostgreSQL isn't breaking anything by rejecting it. Second, the standard limits rows with FETCH FIRST 10 ROWS ONLY, and PostgreSQL does accept that, in addition to its usual form LIMIT 10. The right move would be to ask "what is this dialect's syntax for limiting rows?" instead of assuming that the syntax you happen to know is the standard. In practice, no DBMS implements the full standard and they all add extensions.

Solution 3

"Give me the name and the price of the products that are active and cost less than 10 euros, starting with the most expensive."

Notice what does not appear in that sentence: no loop, no traversal, no decision about indexes. That is exactly what it means for SQL to be declarative. The query doesn't say how to find those rows —the optimizer will decide that— only which ones you want and in what order you expect them.

In this lesson you've built the conceptual framework of the course:

• SQL is a declarative, standardised language for defining, manipulating, querying and controlling data in relational databases; it describes what you want, not how to get it.
• SQL (the language) and a DBMS (the program) are different things: PostgreSQL, MySQL, SQLite, SQL Server and Oracle all speak SQL, each with its own dialect.
• The ANSI/ISO standard has defined a common core since 1986, but no engine implements it completely: that's why the course fixes PostgreSQL 16 as its reference.
• The language is organised into five families —DDL, DML, DQL, DCL and TCL— which map onto the modules you'll work through.
• SQL is essential today in backends, analytics, BI and data engineering, and nothing has replaced it.
• GreenStore will be your testing ground from beginning to end.

In the next lesson, Setting Up Your SQL Environment, you'll move from theory to practice: you'll install PostgreSQL (with instructions for Linux, macOS and Windows, plus the recommended Docker option), learn to connect with the psql client, get to know the most common graphical clients and create the greenstore database you'll use throughout the course.