SQL Statements

Click any row below to jump to a worked example of that statement.

Statement Reference Examples

INSERT

Add a new row to the employees table.

INSERT INTO employees (name, department_id, salary)
VALUES ('Alice', 1, 85000);

UPDATE

Modify rows that match a WHERE clause.

UPDATE employees
SET salary = 90000
WHERE name = 'Alice';

DELETE

Remove rows that match a WHERE clause.

DELETE FROM employees
WHERE department_id = 3;

ALTER TABLE

Add a new column to an existing table.

ALTER TABLE employees
ADD COLUMN bonus INTEGER DEFAULT 0;

TRUNCATE

Remove all rows efficiently — faster than DELETE, no per-row logging, but typically not transactional.

TRUNCATE TABLE staging_events;

UNION / UNION ALL

Combine result sets. UNION deduplicates; UNION ALL keeps duplicates and is faster.

SELECT name, 'employee' AS role FROM employees
UNION
SELECT name, 'contractor' AS role FROM contractors
ORDER BY name;

CREATE INDEX

Add a B-tree index to speed up lookups on a frequently filtered column.

CREATE INDEX idx_employees_dept
  ON employees (department_id);

DISTINCT

Return unique values only.

SELECT DISTINCT department_id
FROM employees;

HAVING

Filter after aggregation. WHERE filters rows; HAVING filters groups.

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 100000;

LIMIT / OFFSET

Page through a result set. Always pair with ORDER BY for stable pagination.

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 10 OFFSET 20;

ORDER BY

Sort the result set. Default is ascending; use DESC for descending.

SELECT name, salary
FROM employees
ORDER BY salary DESC, name ASC;

EXISTS

Test whether a subquery returns any rows. Often faster than IN on large subqueries because the engine short-circuits on the first match.

SELECT e.name
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM bonuses b
    WHERE b.employee_id = e.employee_id
);

RENAME

Rename a column (syntax varies by dialect; this is the ANSI/PostgreSQL form).

ALTER TABLE employees
RENAME COLUMN name TO full_name;

CREATE VIEW

Define a virtual table backed by a SELECT. The view re-runs its query on every read; use a materialized view if you need cached results.

CREATE VIEW high_earners AS
SELECT name, department_id, salary
FROM employees
WHERE salary >= 80000;

DROP VIEW

Remove a view. The underlying tables are not affected.

DROP VIEW IF EXISTS high_earners;

1. Using JOIN:

   Write a query to retrieve employee names along with their department names from employees and departments tables.

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

   +-------------+------------------+
   | Name        | Department Name  |
   +-------------+------------------+
   | Alice       | Engineering      |
   | Bob         | Marketing        |
   | Charlie     | HR               |
   +-------------+------------------+

2. GROUP BY and Aggregate Functions:

   Find the total salary paid in each department.

   SELECT department_id, SUM(salary) AS total_salary
   FROM employees
   GROUP BY department_id;

   +---------------+---------------+
   | Department ID | Total Salary  |
   +---------------+---------------+
   | 1             | 150000        |
   | 2             | 220000        |
   | 3             | 180000        |
   +---------------+---------------+

3. HAVING Clause:

   Retrieve departments that have a total salary greater than 100,000.

   SELECT department_id, SUM(salary) AS total_salary
   FROM employees
   GROUP BY department_id
   HAVING total_salary > 100000;

   +--------------+--------------+
   | Department ID | Total Salary |
   +--------------+--------------+
   | 2            | 220000       |
   | 3            | 180000       |
   +--------------+--------------+

4. CASE Statement:

   Write a query to categorize employees as High, Medium, or Low salary based on their salary values.

   SELECT name,
          salary,
          CASE
              WHEN salary >= 80000 THEN 'High'
              WHEN salary >= 50000 THEN 'Medium'
              ELSE 'Low'
          END AS salary_category
   FROM employees;

   +---------+--------+------------------+
   | Name    | Salary | Salary Category  |
   +---------+--------+------------------+
   | Alice   | 85000  | High             |
   | Bob     | 60000  | Medium           |
   | Charlie | 40000  | Low              |
   +---------+--------+------------------+

5. Self-JOIN:

   Find all employees who share the same manager.

   SELECT e1.name AS Employee, e2.name AS Manager
   FROM employees e1
   JOIN employees e2
     ON e1.manager_id = e2.employee_id;

   +----------+----------+
   | Employee | Manager  |
   +----------+----------+
   | Alice    | Bob      |
   | Charlie  | Bob      |
   | Dave     | Alice    |
   +----------+----------+

6. Window Functions:

   Rank employees based on their salaries within each department.

   SELECT name, department_id, salary,
          RANK() OVER (PARTITION BY department_id
                       ORDER BY salary DESC) AS rank
   FROM employees;

   +---------+--------------+--------+------+
   | Name    | Department ID| Salary | Rank |
   +---------+--------------+--------+------+
   | Alice   | 1            | 85000  | 1    |
   | Bob     | 1            | 60000  | 2    |
   | Charlie | 2            | 90000  | 1    |
   +---------+--------------+--------+------+

7. CTE (Common Table Expression):

   Use a CTE to find employees who earn above the department’s average salary.

   WITH dept_avg AS (
       SELECT department_id, AVG(salary) AS avg_salary
       FROM employees
       GROUP BY department_id
   )
   SELECT e.name, e.salary, d.avg_salary
   FROM employees e
   JOIN dept_avg d
     ON e.department_id = d.department_id
   WHERE e.salary > d.avg_salary;

   +---------+--------+------------+
   | Name    | Salary | Avg Salary |
   +---------+--------+------------+
   | Alice   | 85000  | 70000      |
   | Charlie | 90000  | 60000      |
   +---------+--------+------------+

8. Recursive CTE:

   Write a query to generate a hierarchical list of employees and their managers.

   WITH RECURSIVE emp_hierarchy AS (
       SELECT employee_id, name, manager_id, 1 AS level
       FROM employees
       WHERE manager_id IS NULL
   
       UNION ALL
   
       SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
       FROM employees e
       JOIN emp_hierarchy eh
         ON e.manager_id = eh.employee_id
   )
   SELECT * FROM emp_hierarchy
   ORDER BY level;

   +-------------+--------+------------+-------+
   | Employee ID | Name   | Manager ID | Level |
   +-------------+--------+------------+-------+
   | 1           | Alice  | NULL       | 1     |
   | 2           | Bob    | 1          | 2     |
   | 3           | Charlie| 2          | 3     |
   +-------------+--------+------------+-------+

9. Subquery:

   Retrieve employees whose salary is above the average salary in the company.

   SELECT name, salary
   FROM employees
   WHERE salary > (SELECT AVG(salary) FROM employees);

   +---------+--------+
   | Name    | Salary |
   +---------+--------+
   | Alice   | 85000  |
   | Charlie | 90000  |
   +---------+--------+

10. Handling NULLs:

    Write a query to list employees and replace NULL values in the bonus column with 0.

    SELECT name, COALESCE(bonus, 0) AS bonus
    FROM employees;

    +---------+-------+
    | Name    | Bonus |
    +---------+-------+
    | Alice   | 1000  |
    | Bob     | 0     |
    | Charlie | 500   |
    +---------+-------+

WITH: Common Table Expressions

Select Positive and Negative Image Samples

WITH ordered_images AS (
    -- Order images by score descending and ascending, assigning row numbers --
    SELECT image_id, score,
           ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num_desc,
           ROW_NUMBER() OVER (ORDER BY score ASC) AS row_num_asc
    FROM unlabeled_image_predictions
),
positive_samples AS (
    -- Select every 3rd image from the highest scores, starting from the first row --
    SELECT image_id, 1 AS weak_label
    FROM ordered_images
    WHERE row_num_desc % 3 = 1
    ORDER BY row_num_desc
    LIMIT 10000
),
negative_samples AS (
    -- Select every 3rd image from the lowest scores, starting from the first row --
    SELECT image_id, 0 AS weak_label
    FROM ordered_images
    WHERE row_num_asc % 3 = 1
    ORDER BY row_num_asc
    LIMIT 10000
)
-- Combine positive and negative samples and order by image_id --
SELECT image_id, weak_label
FROM positive_samples

UNION ALL

SELECT image_id, weak_label
FROM negative_samples

ORDER BY image_id;

Output

+----------+------------+
| image_id | weak_label |
+----------+------------+
|    1001  |      1     |
|    1003  |      0     |
|    1005  |      1     |
|    1007  |      0     |
|    1009  |      1     |
+----------+------------+