Database Concepts · SQL Basics · Advanced SQL

What you'll learn

• 1Explain why JSON files fail at scale
• 2Distinguish relational (SQL) from NoSQL databases
• 3Design tables with proper primary keys
• 4Apply basic normalization (1NF, 2NF, 3NF) — at a high level
• 5Run SELECT queries with WHERE, ORDER BY, LIMIT
• 6INSERT new rows safely
• 7UPDATE and DELETE with WHERE
• 8Use comparison and logical operators correctly
• 9Use INNER, LEFT, RIGHT, FULL JOIN appropriately
• 10Aggregate with COUNT, SUM, AVG, MIN, MAX
• 11Filter aggregated results with HAVING
• 12Speed up queries with indexes

Database Concepts

Understand why we need databases, the difference between relational and NoSQL, and core concepts: tables, rows, columns, primary keys, and normalization.

1. Why a Database?

• Concurrency — many writers without losing data
• Querying — fast lookups, joins, aggregations
• Integrity — types, constraints, transactions
• Durability — survive crashes and power loss

2. Relational vs NoSQL

3. Tables, Rows, Columns

users table
┌────┬──────────┬───────────────────────┐
│ id │ name     │ email                 │
├────┼──────────┼───────────────────────┤
│  1 │ Alice    │ alice@example.com     │
│  2 │ Bob      │ bob@example.com       │
└────┴──────────┴───────────────────────┘

• Each row = one entity (a user, a task, etc.)
• Each column = one attribute with a type
• Primary key = a unique identifier (often id)
• Foreign key = a column that references another table's PK

4. Normalization (Quick Take)

• 1NF — atomic values (no "comma, separated, lists" in a cell)
• 2NF — every non-key column depends on the whole PK
• 3NF — no transitive dependencies (no derived columns)
• Rule of thumb: model 1-to-many and many-to-many with separate tables joined by FK

SQL Basics

Learn the four core SQL operations (CRUD) plus filtering, sorting, and limiting. Practice on a sample SQLite database.

1. SELECT

SELECT * FROM users;
SELECT name, email FROM users;

SELECT name FROM users
WHERE age >= 18 AND city = 'Seoul'
ORDER BY name ASC
LIMIT 10;

2. INSERT

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

-- Multiple rows
INSERT INTO users (name, email) VALUES
  ('Bob',   'bob@example.com'),
  ('Carol', 'carol@example.com');

3. UPDATE & DELETE

UPDATE users SET age = 31 WHERE id = 1;

DELETE FROM users WHERE id = 2;

4. Operators

Advanced SQL

Combine data across tables with JOINs, summarize with GROUP BY, filter aggregates with HAVING, and speed things up with indexes.

1. JOINs

-- Get each task with the owner's name
SELECT t.id, t.title, u.name AS owner
FROM tasks t
JOIN users u ON t.user_id = u.id;

-- LEFT JOIN keeps tasks even without an owner
SELECT t.id, t.title, u.name AS owner
FROM tasks t
LEFT JOIN users u ON t.user_id = u.id;

• INNER JOIN — only rows that match in both
• LEFT JOIN — all from the left, plus matches
• RIGHT JOIN — all from the right, plus matches (less common)
• FULL OUTER JOIN — all from both

2. GROUP BY and HAVING

-- Number of tasks per user
SELECT user_id, COUNT(*) AS task_count
FROM tasks
GROUP BY user_id;

-- Users with more than 5 tasks
SELECT user_id, COUNT(*) AS c
FROM tasks
GROUP BY user_id
HAVING c > 5;

WHERE filters rows before grouping; HAVING filters groups after aggregation.

3. Subqueries & EXISTS

-- Users who have at least one open task
SELECT name FROM users
WHERE EXISTS (
  SELECT 1 FROM tasks t
  WHERE t.user_id = users.id AND t.done = 0
);

-- The most expensive product
SELECT * FROM products
WHERE price = (SELECT MAX(price) FROM products);

4. Indexes

CREATE INDEX idx_tasks_user_id ON tasks(user_id);
CREATE UNIQUE INDEX idx_users_email ON users(email);

• Index columns used in WHERE, JOIN, and ORDER BY
• Indexes speed up reads — but slow down writes and use disk
• Use EXPLAIN to verify the query is using your index