Getting Started with SQL: SQL for Developers

Every app needs to store data. Whether it’s user profiles, product catalogs, or chat messages, databases are where it all lives. SQL is the language you use to talk to them.

---

What is a Database?

A database is an organized collection of data stored electronically. The most common type is a relational database, where data is stored in tables that relate to each other.

Relational Database Structure

Database: my_app
├── Table: users
│   ├── id (PK) | name | email | created_at
│   ├── 1       | Abdu | abdu@email.com | 2026-01-15
│   └── 2       | Fira | fira@email.com | 2026-02-20
│
├── Table: posts
│   ├── id (PK) | user_id (FK) | title | content | published_at
│   ├── 1       | 1            | Hello | ...     | 2026-03-01
│   └── 2       | 1            | World | ...     | 2026-03-05
│
└── Table: comments
    ├── id (PK) | post_id (FK) | user_id (FK) | text | created_at
    ├── 1       | 1            | 2            | Nice! | 2026-03-02
    └── 2       | 1            | 1            | Ty!  | 2026-03-02

Key Terms

Term	Meaning
Table	Like a spreadsheet, stores data in rows and columns
Row	One record (e.g., one user)
Column	One attribute (e.g., name, email)
Primary Key (PK)	Unique identifier for each row
Foreign Key (FK)	Links to a primary key in another table
Schema	Structure/blueprint of your database

Popular Relational Databases

Database	Best For
PostgreSQL	Complex queries, reliability, open source
MySQL	Web apps, WordPress, widely supported
SQLite	Mobile apps (Android/iOS), small projects
SQL Server	Enterprise, .NET ecosystem

---

SQL Basics

SQL (Structured Query Language) has four main operations, often called CRUD:

Operation	SQL	What
Create	INSERT	Add new data
Read	SELECT	Query data
Update	UPDATE	Modify data
Delete	DELETE	Remove data

---

Creating Tables

Before storing data, define the structure:

CREATE TABLE users (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) NOT NULL UNIQUE,
    age         INTEGER,
    is_active   BOOLEAN DEFAULT TRUE,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Common Data Types

SQL Type	What	Example
INTEGER	Whole numbers	42, 0, -7
VARCHAR(n)	Text up to n chars	'hello'
TEXT	Unlimited text	Long descriptions
BOOLEAN	True/false	TRUE, FALSE
FLOAT / REAL	Decimal numbers	3.14
TIMESTAMP	Date and time	'2026-05-29 10:30:00'

Constraints

NOT NULL       -- Must have a value
UNIQUE         -- No duplicates allowed
DEFAULT value  -- Default if not specified
PRIMARY KEY    -- Unique + not null (one per table)
FOREIGN KEY    -- Must exist in another table
CHECK (cond)   -- Must satisfy condition

Relationships

-- One-to-many: users have many posts
CREATE TABLE posts (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id     INTEGER NOT NULL,
    title       VARCHAR(200) NOT NULL,
    content     TEXT,
    published   BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Many-to-many: using a junction table
CREATE TABLE post_tags (
    post_id INTEGER,
    tag_id  INTEGER,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (tag_id) REFERENCES tags(id)
);

---

INSERT: Adding Data

-- Single row
INSERT INTO users (name, email, age)
VALUES ('Abdu Ar Rahman', 'abdu@email.com', 28);

-- Multiple rows
INSERT INTO users (name, email, age) VALUES
    ('Fira', 'fira@email.com', 26),
    ('Ahmad', 'ahmad@email.com', 30),
    ('Sari', 'sari@email.com', 25);

---

SELECT: Querying Data

This is the command you’ll use most.

Basic Queries

-- Everything from a table
SELECT * FROM users;

-- Specific columns
SELECT name, email FROM users;

-- With a condition
SELECT * FROM users WHERE age >= 25;

-- Multiple conditions
SELECT * FROM users WHERE age >= 25 AND is_active = TRUE;

Filtering with WHERE

-- Comparison operators
WHERE age = 28
WHERE age > 25
WHERE age != 30
WHERE age BETWEEN 20 AND 30

-- Pattern matching
WHERE name LIKE 'Ab%'         -- Starts with 'Ab'
WHERE email LIKE '%@gmail.com' -- Ends with '@gmail.com'
WHERE name LIKE '%ah%'         -- Contains 'ah'

-- Multiple values
WHERE role IN ('admin', 'editor', 'moderator')

-- NULL handling
WHERE phone IS NULL
WHERE phone IS NOT NULL

Sorting & Limiting

-- Sort ascending (default)
SELECT * FROM users ORDER BY name ASC;

-- Sort descending
SELECT * FROM users ORDER BY created_at DESC;

-- Pagination
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;  -- Page 3 (21-30)

---

Aggregation

Summarize data across rows:

-- Count rows
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE is_active = TRUE;

-- Math operations
SELECT AVG(age) FROM users;
SELECT MIN(age), MAX(age) FROM users;
SELECT SUM(price) FROM orders WHERE user_id = 1;

GROUP BY

Group rows and aggregate per group:

-- Users per age group
SELECT age, COUNT(*) as user_count
FROM users
GROUP BY age;

-- Posts per user
SELECT user_id, COUNT(*) as post_count
FROM posts
GROUP BY user_id
ORDER BY post_count DESC;

-- Having (filter after grouping)
SELECT user_id, COUNT(*) as post_count
FROM posts
GROUP BY user_id
HAVING post_count >= 3;

WHERE vs HAVING

Clause	Filters	When
WHERE	Individual rows	Before grouping
HAVING	Groups	After grouping

SELECT user_id, COUNT(*) as total
FROM posts
WHERE published = TRUE          -- Filter rows first
GROUP BY user_id
HAVING COUNT(*) >= 3;           -- Then filter groups

---

JOIN: Combining Tables

Joins let you query data from multiple related tables in one query.

Types of Joins

INNER JOIN:  Only matching rows from both tables
LEFT JOIN:   All rows from left + matching from right
RIGHT JOIN:  All rows from right + matching from left
FULL JOIN:   All rows from both tables

INNER JOIN (Most Common)

-- Get posts with their author names
SELECT
    posts.title,
    posts.published_at,
    users.name AS author
FROM posts
INNER JOIN users ON posts.user_id = users.id;

Result:

title	published_at	author
Hello	2026-03-01	Abdu Ar Rahman
World	2026-03-05	Abdu Ar Rahman

LEFT JOIN

-- All users, even those without posts
SELECT
    users.name,
    COUNT(posts.id) AS post_count
FROM users
LEFT JOIN posts ON users.id = posts.user_id
GROUP BY users.id;

Result:

name	post_count
Abdu Ar Rahman	2
Fira	0
Ahmad	3

Multiple Joins

-- Posts with author and comments
SELECT
    posts.title,
    users.name AS author,
    comments.text AS comment,
    commenters.name AS commenter
FROM posts
INNER JOIN users ON posts.user_id = users.id
INNER JOIN comments ON comments.post_id = posts.id
INNER JOIN users AS commenters ON comments.user_id = commenters.id;

---

UPDATE: Modifying Data

-- Update specific rows (ALWAYS use WHERE!)
UPDATE users SET name = 'Abdu' WHERE id = 1;

-- Update multiple columns
UPDATE users
SET is_active = FALSE, email = 'old@email.com'
WHERE created_at < '2025-01-01';

-- Update with a calculation
UPDATE products SET price = price * 1.1 WHERE category = 'premium';

Warning: UPDATE without a WHERE clause updates every row in the table. Always double-check before running.

---

DELETE: Removing Data

-- Delete specific rows
DELETE FROM users WHERE is_active = FALSE;

-- Delete by ID
DELETE FROM posts WHERE id = 42;

-- Delete all rows (keeps table structure)
DELETE FROM temp_data;

-- Delete table entirely
DROP TABLE temp_data;

Warning: DELETE without WHERE removes all rows. DROP TABLE deletes the entire table. There is no undo in standard SQL.

---

Real-World Query Patterns

Search with Pagination

-- Page 1 (items 1-10)
SELECT id, title, created_at
FROM posts
WHERE published = TRUE
  AND (title LIKE '%flutter%' OR content LIKE '%flutter%')
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;

Dashboard Stats

SELECT
    (SELECT COUNT(*) FROM users) AS total_users,
    (SELECT COUNT(*) FROM users WHERE is_active = TRUE) AS active_users,
    (SELECT COUNT(*) FROM posts WHERE published = TRUE) AS published_posts,
    (SELECT COUNT(*) FROM posts WHERE published = FALSE) AS draft_posts;

Top N by Category

-- Top 3 most active users
SELECT users.name, COUNT(posts.id) AS post_count
FROM users
LEFT JOIN posts ON users.id = posts.user_id
GROUP BY users.id
ORDER BY post_count DESC
LIMIT 3;

---

SQL in Your App

Parameterized Queries (Prevent SQL Injection)

// BAD — SQL injection vulnerability!
var query = "SELECT * FROM users WHERE email = '$email'";

// GOOD — parameterized
var query = "SELECT * FROM users WHERE email = ?";
var result = await db.query('users', where: 'email = ?', whereArgs: [email]);

Never concatenate user input into SQL strings. Always use parameterized queries. This is the #1 security rule for databases.

ORMs (Object-Relational Mapping)

Instead of raw SQL, many apps use ORMs:

Platform	ORM	Raw SQL
Flutter	drift, sqflite	sqflite package
Node.js	Prisma, TypeORM	pg, mysql2
Python	SQLAlchemy, Django ORM	psycopg2
Kotlin	Room	SQLiteOpenHelper

---

Quick Reference

Task	SQL
Create table	CREATE TABLE name (...)
Insert data	INSERT INTO table (cols) VALUES (...)
Query all	SELECT * FROM table
Query with filter	SELECT * FROM table WHERE cond
Sort results	ORDER BY col ASC/DESC
Limit results	LIMIT n OFFSET m
Count	SELECT COUNT(*) FROM table
Group + count	GROUP BY col HAVING cond
Join tables	INNER JOIN table2 ON cond
Update rows	UPDATE table SET col = val WHERE cond
Delete rows	DELETE FROM table WHERE cond

---

What’s Next?

With SQL fundamentals in place, you’re ready to learn Dart programming, the language you’ll use to build Flutter apps that interact with databases.

Bismillah, happy querying! 🚀