SQL in a Nutshell – Beginner Edition

01 — FOUNDATIONS

What is SQL?

DEFINITION

SQL (Structured Query Language) is the standard language for communicating with relational databases. It lets you store, retrieve, update, and delete data.

PRONUNCIATION

Say "S-Q-L" or "sequel" — both are accepted. Invented at IBM in the 1970s, it became the universal data query standard.

🗄️

Relational

Data in tables with rows & columns

🔍

Declarative

Say what you want, not how

⚡

Universal

MySQL, PostgreSQL, SQLite, MSSQL

📊

CRUD

Create, Read, Update, Delete

02 — STRUCTURE

Anatomy of a Query

A COMPLETE SQL STATEMENT

SELECT

Keyword

→

name, age

Columns

→

FROM

Keyword

→

users

Table

→

WHERE

Filter

→

age > 18

Condition

→

;

Terminator

SELECT name, age

FROM users

WHERE age > 18

ORDER BY name ASC

LIMIT 10;

CLAUSE EXECUTION ORDER

        ① FROM
      

        ② WHERE
      

        ③ GROUP BY
      

        ④ HAVING
      

        ⑤ SELECT
      

        ⑥ ORDER BY
      

        ⑦ LIMIT
      

💡 Even though you write SELECT first, the database executes FROM first.

03 — COMMANDS

Most Important Commands

The building blocks of every SQL operation.

SELECT

Retrieve data from one or more columns.

SELECT name, email FROM users;

SELECT * FROM products; -- all cols

FROM

Specifies the source table(s) for the query.

SELECT * FROM orders;

SELECT * FROM customers c;

WHERE

Filter rows matching a condition.

SELECT * FROM users

WHERE age > 25 AND active = 1;

ORDER BY

Sort results ascending or descending.

SELECT * FROM products

ORDER BY price DESC;

LIMIT

Cap the number of returned rows.

SELECT * FROM logs

LIMIT 100; -- first 100 rows

INSERT

Add new rows into a table.

INSERT INTO users (name, age)

VALUES ('Alice', 30);

UPDATE

Modify existing row values.

UPDATE users SET age = 31

WHERE name = 'Alice';

DELETE

Remove rows from a table. ⚠️ Always use WHERE!

DELETE FROM users

WHERE id = 42;

CREATE TABLE

Define a new table schema.

CREATE TABLE employees (

id INT PRIMARY KEY,

name VARCHAR(100)

);

04 — CLAUSES & KEYWORDS

Essential Clauses

FILTERING

WHERE AND OR

WHERE city = 'KL' AND age > 18

IN LIKE BETWEEN

WHERE country IN ('MY', 'SG')

WHERE name LIKE 'A%' -- starts with A

WHERE age BETWEEN 20 AND 40

AGGREGATIONS

COUNT()

How many rows

SUM()

Total value

AVG()

Average

MIN / MAX

Range extremes

SELECT dept, COUNT(*) AS total,

AVG(salary) AS avg_pay

FROM employees

GROUP BY dept

HAVING COUNT(*) > 5;

💡 GROUP BY collapses rows. HAVING filters groups (like WHERE but for aggregates).

05 — JOINS

Joining Tables

INNER JOIN

Rows that match in both tables

LEFT JOIN

All from left + matches from right

RIGHT JOIN

Matches from left + all from right

FULL JOIN

All rows from both tables

EXAMPLE — LEFT JOIN

SELECT u.name, o.total

FROM users u

LEFT JOIN orders o ON u.id = o.user_id;

-- Returns all users, even those with no orders (NULL for o.total)

06 — DATA TYPES

Common Data Types

TYPE	STORES	EXAMPLE	NOTE
INT	Whole numbers	42, -7, 1000000	Use for IDs, counts, ages
VARCHAR(n)	Variable-length text	'Alice', '[email protected]'	n = max characters
DECIMAL(p,s)	Precise decimals	99.95, 1234.56	Use for money/prices
DATE	Calendar date	'2025-06-18'	YYYY-MM-DD format
DATETIME	Date + time	'2025-06-18 09:30:00'	Full timestamp
BOOLEAN	True/false	TRUE / FALSE / 1 / 0	Some DBs use TINYINT(1)
TEXT	Long text	'Long description...'	No length limit, slower

07 — BEST PRACTICES

Quick Tips & Best Practices

⚠️

Always use WHERE with DELETE/UPDATE

Without WHERE, you modify or delete every row in the table.

🚫

Avoid SELECT * in production

Name specific columns. It's faster, clearer, and won't break when schema changes.

📌

Use indexes on filter columns

Columns used in WHERE and JOIN should be indexed for performance.

📋

Use aliases for readability

SELECT COUNT(*) AS total_users is cleaner than no alias.

🔤

Uppercase SQL keywords

Convention: SELECT not select. Easier to scan.

💾

Test with SELECT before modifying

Run a SELECT with your WHERE first to verify the target rows before UPDATE/DELETE.

🔢

Use LIMIT when exploring

Add LIMIT 100 while learning to avoid pulling millions of rows accidentally.

📦

Normalize your schema

Store each fact once. Use foreign keys to link tables rather than duplicating data.

08 — SANDBOX

Try It Yourself

Experiment with SQL examples below — edit and run any query.

SQL EDITOR

→ SIMULATED OUTPUT

Click "Run Query" to see simulated results...

SQL in a Nutshell Beginner Edition