🗄️ SQL & Databases

The one query shape to master

Most of SQL is variations on this. Learn the order and you can read almost anything:

SELECT column1, column2      -- which columns you want
FROM table_name              -- from which table
WHERE condition              -- filter rows
GROUP BY column1             -- bucket rows together
HAVING condition             -- filter those buckets
ORDER BY column2 DESC        -- sort the result
LIMIT 10;                    -- cap the rows

The database runs them roughly in the order FROM to WHERE to GROUP BY to HAVING to SELECT to ORDER BY to LIMIT, which is why you cannot use a SELECT alias in WHERE. Small detail, common interview gotcha.

Everyday queries you will actually write

Filtering and sorting:

SELECT name, salary
FROM employees
WHERE department = 'Engineering' AND salary > 50000
ORDER BY salary DESC;

Counting and grouping (how many employees per department):

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
ORDER BY headcount DESC;

Aggregates (average salary per department, only where it beats 60k):

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

Inserting, updating, deleting:

INSERT INTO employees (name, department, salary)
VALUES ('Asha', 'Engineering', 72000);

UPDATE employees SET salary = 80000 WHERE name = 'Asha';

DELETE FROM employees WHERE name = 'Asha';

Joins, the part everyone fears (they are simple)

A join combines rows from two tables using a shared column. Picture two tables, employees and departments, linked by a department id.

SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

Interview favorite, find employees with no department:

SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;

Concepts interviews love

• Primary key - uniquely identifies each row. Foreign key - points to a primary key in another table (how tables relate).
• Normalization - splitting data so each fact lives in one place, avoiding duplication and update bugs (aim for 3NF in most apps).
• Index - like a book's index, it makes reads on a column much faster (at a small cost to writes). Add them to columns you filter or join on a lot.
• ACID & transactions - a transaction groups steps so they all succeed or all roll back (think bank transfer).
• SQL vs NoSQL - SQL for structured, related data with strong consistency; NoSQL (like MongoDB) for flexible, fast-changing, or huge-scale data.

Practice tasks (do these)

Reading SQL is easy; writing it is the skill. Set up a free playground and try:

1. Find the top 5 highest-paid employees.
2. Count how many employees are in each department.
3. List departments with an average salary above 60k.
4. Join employees to departments and show each person's department name.
5. Find employees who are not assigned to any department.
6. Find the second-highest salary (a classic interview question).

-- Second highest salary, one clean way:
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Which database should you learn?

Start with SQL (PostgreSQL or SQLite). The relational thinking transfers everywhere, and most jobs expect it.

Free places to practice

• SQLBolt - interactive lessons, no setup.
• PostgreSQL Exercises - practice against a real dataset.
• HackerRank SQL - graded problems, interview style.
• DB Fiddle - a free online SQL playground.

What to do next