How SQL Queries Work

SQL (Structured Query Language) retrieves, inserts, updates, and deletes data in databases. Understanding the basics helps you write effective queries.

Databases, Tables, and Rows

A database is a collection of organized data. Inside it are tables (like spreadsheets), and inside tables are rows (records) and columns (fields).

Database: purple_hrm
    ├─ Table: employees
    │   ├─ Columns: employee_id, first_name, last_name, salary, ...
    │   └─ Rows: (101, John, Smith, 50000), (102, Jane, Doe, 55000), ...
    ├─ Table: departments
    │   ├─ Columns: department_id, department_name, ...
    │   └─ Rows: (10, Sales), (20, Engineering), ...
    └─ Table: jobs
        ├─ Columns: job_id, job_title, min_salary, max_salary
        └─ Rows: ...

The Basic Query Structure

Most queries follow this pattern:

SELECT (which columns?)
FROM (which table?)
WHERE (which rows?)
ORDER BY (sort how?)

You’re asking: “Get me these columns from this table, but only rows that match these conditions, sorted like this.”

SELECT: Retrieving Data

SELECT specifies which columns you want to see.

SELECT first_name, last_name
FROM employees

This retrieves only the first and last names. You could also use SELECT * to get all columns.

WHERE: Filtering Rows

WHERE narrows results to rows matching your conditions.

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000

This returns employees earning more than 50,000. Without WHERE, you get all rows.

Joining Tables: Combining Data

Tables are related. An employee belongs to a department. A department is in a location. JOIN connects these tables.

SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id

The ON clause specifies how tables connect (matching column values).

Without JOIN, you’d have no way to see which department each employee works in—the tables would be separate.

Aggregating Data: Summarizing

Sometimes you don’t want individual rows—you want summaries: “How many employees?”, “What’s the average salary?”, “Which product sold most?”

SELECT COUNT(*) as total_employees
FROM employees

COUNT(*) counts all rows. Other aggregation functions include SUM(), AVG(), MIN(), MAX().

Grouping: Organizing Aggregates

GROUP BY combines related rows before aggregating.

SELECT department_id, COUNT(*) as employee_count
FROM employees
GROUP BY department_id

This counts employees per department. Without GROUP BY, you’d get one total count for the entire company.

Data Types: What Kind of Value?

Every column stores a specific type of data:

• VARCHAR(50) = Text up to 50 characters
• INT = Whole numbers
• DECIMAL(10,2) = Numbers with decimals (10 total digits, 2 after decimal)
• DATE = Dates
• CHAR(2) = Fixed-length text (always 2 characters)

Types ensure data integrity and enable proper comparisons.

Keys and Relationships: Connecting Tables

Primary Key uniquely identifies each row. No two rows have the same primary key.

employee_id INT PRIMARY KEY

Foreign Key links to another table’s primary key.

FOREIGN KEY (department_id) REFERENCES departments(department_id)

Foreign keys enforce relationships. You can’t create an employee record in a department that doesn’t exist.

NULL Values: Missing Data

A column might have no value. SQL calls this NULL. It’s not zero or empty string—it’s “unknown” or “missing.”

WHERE outservice_date IS NULL       # Has no value
WHERE outservice_date IS NOT NULL   # Has a value

NULL is tricky. NULL = NULL returns false (unknown equals unknown?). Always use IS NULL or IS NOT NULL.

INSERT: Adding Data

INSERT adds new rows.

INSERT INTO employees (first_name, last_name, salary)
VALUES ('Alice', 'Johnson', 60000)

Column order and value order must match. All required columns must have values.

UPDATE: Changing Data

UPDATE modifies existing rows.

UPDATE employees
SET salary = 62000
WHERE employee_id = 101

Always include a WHERE clause to specify which rows to change. Without it, every row updates.

DELETE: Removing Data

DELETE removes rows.

DELETE FROM employees
WHERE employee_id = 101

Like UPDATE, always use WHERE or you’ll delete everything.

Indexes: Making Queries Fast

Indexes help databases find data quickly. Without indexes, the database must scan every row. With an index on employee_id, lookups are instant.

You rarely create indexes manually—the database does it, but it’s good to know they exist.

Constraints: Rules for Data

Constraints enforce data quality:

NOT NULL                   # Column must have a value
UNIQUE                     # No duplicates
PRIMARY KEY                # Unique and not null
FOREIGN KEY                # Must reference another table
CHECK (age >= 0)           # Value must meet condition
DEFAULT 'Unknown'          # Use this if no value provided

Constraints prevent bad data from entering the database.

Aliases: Shorthand Names

Aliases give tables and columns shorter names for easier reading.

SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id

e is an alias for employees, d for departments. Instead of typing employees.first_name, you use e.first_name.

Query Execution Order

SQL executes queries in a specific order, not necessarily left-to-right:

1. FROM - Find the table(s)
2. WHERE - Filter rows
3. GROUP BY - Organize rows
4. SELECT - Choose columns
5. ORDER BY - Sort results

Understanding this order helps explain why some queries fail or return unexpected results.