KAHIBARO
Discord Login Register ☰
☰

Introduction to SQL

Table of Contents

Why SQL Matters for Backend Developers

When you build backend applications, you almost always need to store and retrieve data. User accounts, orders, blog posts, comments, logs, payments, all of these live in a database.

Relational databases such as PostgreSQL, MySQL, and SQLite dominate backend systems. They all speak a common language called SQL, which stands for Structured Query Language.

SQL is the standard way to:

If you work as a backend developer, you will use SQL regularly, even if you use an ORM in your code.

Key idea: SQL is the standard language used to communicate with relational databases.
If you know SQL, you can work with any relational database.

This chapter gives you a gentle introduction to what SQL is and how you will use it, without going deep into each command, because those will have their own dedicated chapters.

What SQL Is (and Is Not)

SQL as a Query Language

SQL is a declarative language. That means you describe what you want, not how to get it.

Example in plain English:

"Give me all users who registered today."

Example in SQL:

sql 
SELECT *
FROM users
WHERE registration_date = CURRENT_DATE;

You do not say:

The database engine decides all of that.

SQL Is Not a General Programming Language

SQL is powerful, but it is not meant to replace your backend language such as Python or JavaScript.

You cannot use SQL alone to:

Instead, you use SQL from inside your backend application to talk to the database.

A typical backend stack looks like this:

LayerRoleExample
HTTP frameworkHandles requests and responsesFastAPI, Django, Express, Laravel
Application codeBusiness logicPython, JavaScript, Java, Go
DatabaseStores dataPostgreSQL, MySQL, SQLite
SQLLanguage to talk to the databaseSELECT, INSERT, UPDATE, DELETE

The Two Main Uses of SQL

Everyday SQL usage falls into two large categories.

1. Data Definition: Creating and Changing Structure (DDL)

You use SQL to define what tables exist and what columns they have. This is called Data Definition Language (DDL).

Typical DDL commands:

Example: defining a simple users table:

sql 
CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    email       VARCHAR(255) NOT NULL UNIQUE,
    name        VARCHAR(100) NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT NOW()
);

You will learn details of CREATE later in the chapter called CREATE.

2. Data Manipulation: Working with the Rows (DML)

You use SQL to work with the data stored inside tables. This is called Data Manipulation Language (DML).

Typical DML commands:

Example: adding a new user:

sql 
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice');

Example: reading all users:

sql 
SELECT id, email, name
FROM users;

You will study each of these commands in its own chapter in this SQL section.

Basic SQL Concepts: Databases, Tables, and Rows

Databases and Schemas

In relational systems, you organize data like this:

In PostgreSQL, for example:

Backends usually connect to a single database and operate inside one or more schemas.

Tables: Like Spreadsheets in the Database

A table is similar to a spreadsheet:

Example users table:

idemailnamecreated_at
1alice@example.comAlice2024-04-01 10:05:00
2bob@example.comBob2024-04-01 11:10:00

SQL uses data types for columns, for example:

Data types affect what you can store and how the database optimizes it, but their details are covered under database specific chapters.

Rows and Columns: Terminology

You will often hear:

In SQL syntax, you will see:

Simple examples, using the users table:

sql 
-- choose specific columns
SELECT id, email
FROM users;
-- update a column in one row
UPDATE users
SET name = 'Alice Wonderland'
WHERE id = 1;
-- delete one row
DELETE FROM users
WHERE email = 'bob@example.com';

Common SQL Statement Structure

Most SQL commands follow a predictable pattern.

Basic `SELECT` Structure

The most important command is SELECT, which reads data.

General shape:

sql 
SELECT column1, column2, ...
FROM table_name
WHERE some_condition
ORDER BY some_column;

Example:

sql 
SELECT id, email
FROM users
WHERE created_at >= '2024-01-01'
ORDER BY created_at DESC;

Meaning:

Later chapters like SELECT, WHERE, and ORDER BY will explain each part in detail.

SQL Keywords and Case Sensitivity

Typical style rules:

Most relational databases:

For this course, you can safely:

Running SQL: Where and How

Interactive SQL Consoles

Most databases provide a command line client where you can type SQL manually.

Examples:

Example session in PostgreSQL:

sql 
-- create a table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL
);
-- insert a row
INSERT INTO users (email)
VALUES ('alice@example.com');
-- read the table
SELECT * FROM users;

You will see the result printed in your terminal.

GUI Tools

You can also use a graphical tool:

They usually have:

From Your Backend Code

In real applications, SQL is usually executed from your backend code, not by you typing it manually.

For example, in Python with a library:

python 
cursor.execute(
    "SELECT id, email FROM users WHERE id = %s",
    (user_id,)
)
row = cursor.fetchone()

Your Python code prepares and sends SQL to the database, then reads the result. This integration is covered in later chapters on ORMs and database integration.

Basic Read and Write Examples

This section previews the core SQL operations you will learn in detail later.

Creating a Simple Table

sql 
CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    price_cents INTEGER NOT NULL,
    in_stock    BOOLEAN NOT NULL DEFAULT TRUE
);

What this does:

Inserting Data

sql 
INSERT INTO products (name, price_cents, in_stock)
VALUES ('Coffee Mug', 1299, TRUE);

Insert multiple rows in one statement:

sql 
INSERT INTO products (name, price_cents, in_stock)
VALUES
    ('T-Shirt', 1999, TRUE),
    ('Sticker Pack', 499, TRUE),
    ('Hoodie', 3999, FALSE);

You will later see the INSERT chapter where this is covered in depth.

Reading Data with `SELECT`

Get all products:

sql 
SELECT * FROM products;

Get only names and prices:

sql 
SELECT name, price_cents
FROM products;

Filter products that are in stock:

sql 
SELECT name, price_cents
FROM products
WHERE in_stock = TRUE;

Sort by price, highest first:

sql 
SELECT name, price_cents
FROM products
ORDER BY price_cents DESC;

The chapters SELECT, WHERE, and ORDER BY will show many more patterns like this.

Updating Data

Mark a product as out of stock:

sql 
UPDATE products
SET in_stock = FALSE
WHERE name = 'Coffee Mug';

Increase all prices by 10 percent:

sql 
UPDATE products
SET price_cents = price_cents * 1.10;

The UPDATE chapter will explore safe patterns to avoid changing the wrong rows.

Deleting Data

Remove a product:

sql 
DELETE FROM products
WHERE name = 'Sticker Pack';

Remove all products that are out of stock:

sql 
DELETE FROM products
WHERE in_stock = FALSE;

The DELETE chapter will show how to use WHERE carefully to avoid deleting too much.

SQL and Relational Thinking

Relationships between Tables

Relational databases are built around the idea of relationships between tables.

Example tables:

A basic orders table might look like:

sql 
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    user_id     INTEGER NOT NULL,
    total_cents INTEGER NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT NOW()
);

Here:

Later chapters on JOINs, Foreign Keys, and Relationships will show how to:

Joining Data from Multiple Tables

One of SQLs most powerful features is the JOIN.

Example: you want to see orders with user emails:

sql 
SELECT
    orders.id,
    users.email,
    orders.total_cents
FROM orders
JOIN users ON orders.user_id = users.id;

This gives you a combined view of two tables. The JOINs chapter explains different join types and use cases in detail.

SQL Grammar: Statements, Clauses, and Expressions

To read SQL documentation and error messages, it helps to know the basic grammar words.

Statements

A statement is one complete command. For example:

sql 
SELECT * FROM users;

Each statement usually ends with a semicolon ;.

Other examples of statements:

Clauses

A statement is built from one or more clauses. In a SELECT statement, common clauses are:

Example:

sql 
SELECT email, COUNT(*) AS order_count      -- SELECT clause
FROM orders                                -- FROM clause
JOIN users ON orders.user_id = users.id    -- JOIN clause
WHERE orders.created_at >= '2024-01-01'    -- WHERE clause
GROUP BY email                             -- GROUP BY clause
ORDER BY order_count DESC;                 -- ORDER BY clause

Each clause has a specific role, which you will see in the dedicated chapters.

Expressions

Within clauses, you use expressions. These can be:

Example:

sql 
SELECT name, price_cents * 1.15 AS price_with_tax
FROM products
WHERE price_cents > 1000;

Here, price_cents * 1.15 is an expression.

SQL and Standards: Differences Between Databases

SQL is standardized, but each database has its own dialect and features.

Common points:

Differences can include:

In this course:

How SQL Fits into Backend Development

Typical Backend Workflow with SQL

A backend that uses SQL usually does the following:

  1. Define the schema with CREATE TABLE and other DDL statements.
  2. Insert initial data, for example an admin user or configuration.
  3. In response to API calls:
    • Use SELECT to read from tables.
    • Use INSERT when creating new items.
    • Use UPDATE when modifying existing items.
    • Use DELETE for removal.
  4. Over time:
    • Evolve the schema using ALTER TABLE.
    • Migrate data when requirements change.

All of these operations are usually wrapped in transactions, which you will study in the Transactions chapter.

ORMs and SQL

Many backends use an ORM so you write code like:

python 
user = User(email="alice@example.com")
session.add(user)
session.commit()

Instead of writing raw SQL by hand.

However, the ORM translates that down to SQL such as:

sql 
INSERT INTO users (email)
VALUES ('alice@example.com');

To use an ORM effectively, you must understand SQL concepts:

Summary and What Comes Next

In this introduction you learned that:

Core mental model:

  1. Store data in tables with columns and rows.
  2. Use SQL to define tables and insert / read / update / delete rows.
  3. Combine data from tables using joins.

In the next chapters, you will explore each main SQL operation in detail, starting with how to create tables and insert data, then how to query, filter, aggregate, and join data efficiently.

Views: 8

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3106 / 24h
Impressum / Imprint
Datenschutz / Data Policy