SQL Basics for Data Engineers

SQL (Structured Query Language) is the primary language data engineers use to interact with relational databases and data warehouses. While data engineers also write Python and work with distributed computing frameworks, SQL remains the most frequently used tool in the daily workflow. A data engineer who writes clean, efficient SQL solves problems faster and builds more reliable pipelines than one who avoids it.

What SQL Does

SQL reads data from tables, inserts new records, updates existing ones, deletes records, and creates or modifies table structures. Every relational database and most modern data warehouses support SQL. Learning SQL once lets you work across PostgreSQL, MySQL, Snowflake, BigQuery, Redshift, and dozens of other systems with minimal adjustment.

The Four Core SQL Commands

SELECT: Read Data

SELECT retrieves data from one or more tables. It is the command data engineers use most frequently — for exploring data, building reports, and transforming data into new shapes.

-- Retrieve all columns from the customers table
SELECT * FROM customers;

-- Retrieve specific columns
SELECT customer_id, name, city FROM customers;

-- Filter with WHERE
SELECT customer_id, name, city
FROM customers
WHERE city = 'Mumbai';

-- Sort results
SELECT customer_id, name, city
FROM customers
ORDER BY name ASC;

-- Limit the number of rows returned
SELECT * FROM orders LIMIT 10;

INSERT: Add New Records

INSERT adds new rows to a table. Data pipelines use INSERT to load transformed data into destination tables after processing.

INSERT INTO customers (customer_id, name, email, city)
VALUES ('C004', 'Fatima Ali', 'fatima@mail.com', 'Cairo');

UPDATE: Modify Existing Records

UPDATE changes the value of one or more columns in existing rows. Always include a WHERE clause — without it, UPDATE changes every row in the table.

UPDATE customers
SET city = 'Bangalore'
WHERE customer_id = 'C001';

DELETE: Remove Records

DELETE removes rows from a table. Like UPDATE, always specify a WHERE clause to avoid deleting the entire table accidentally.

DELETE FROM orders WHERE order_date < '2020-01-01';

Filtering with WHERE

The WHERE clause filters rows based on conditions. Data engineers use WHERE constantly to isolate specific time ranges, categories, or record statuses.

-- Multiple conditions with AND
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
  AND status = 'completed'
  AND amount > 1000;

-- Either condition with OR
SELECT * FROM customers
WHERE city = 'Delhi' OR city = 'Mumbai';

-- List membership with IN
SELECT * FROM products
WHERE category IN ('Electronics', 'Appliances', 'Accessories');

-- Pattern matching with LIKE
SELECT * FROM customers
WHERE email LIKE '%@gmail.com';

-- Null checks
SELECT * FROM orders
WHERE shipped_date IS NULL;

Aggregations

Aggregation functions compute a single value from multiple rows. They power every summary report and analytics query.

-- Count total orders
SELECT COUNT(*) AS total_orders FROM orders;

-- Total revenue
SELECT SUM(amount) AS total_revenue FROM orders;

-- Average order value
SELECT AVG(amount) AS avg_order_value FROM orders;

-- Maximum and minimum values
SELECT MAX(amount) AS largest_order,
       MIN(amount) AS smallest_order
FROM orders;

GROUP BY: Aggregate by Category

GROUP BY splits rows into groups and applies an aggregate function to each group separately. This is the foundation of every "total X by Y" analytical query.

-- Total revenue by city
SELECT city, SUM(amount) AS total_revenue
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY city
ORDER BY total_revenue DESC;

Result:
+----------+---------------+
| city     | total_revenue |
+----------+---------------+
| Mumbai   | 1,250,000     |
| Delhi    | 980,000       |
| Bangalore| 740,000       |
+----------+---------------+

HAVING: Filter After Grouping

HAVING filters groups after GROUP BY applies. WHERE filters individual rows before grouping; HAVING filters the grouped results.

-- Cities with total revenue above 500,000 only
SELECT city, SUM(amount) AS total_revenue
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY city
HAVING SUM(amount) > 500000;

Creating and Managing Tables

Data engineers build the tables that pipelines write into and analysts query from. CREATE TABLE defines the structure.

CREATE TABLE daily_sales (
    sale_date   DATE        NOT NULL,
    product_id  VARCHAR(20) NOT NULL,
    region      VARCHAR(50),
    total_units INTEGER     DEFAULT 0,
    revenue     NUMERIC(12, 2),
    PRIMARY KEY (sale_date, product_id)
);

Common SQL Functions for Data Engineers

-- Date functions
SELECT CURRENT_DATE;                       -- today's date
SELECT DATE_TRUNC('month', order_date)     -- truncate to month start
FROM orders;

-- String functions
SELECT UPPER(name), LOWER(email),
       TRIM(name), LENGTH(email)
FROM customers;

-- Type casting
SELECT CAST(amount AS INTEGER) FROM orders;

-- Conditional logic
SELECT
  order_id,
  amount,
  CASE
    WHEN amount >= 5000 THEN 'High Value'
    WHEN amount >= 1000 THEN 'Medium Value'
    ELSE 'Low Value'
  END AS order_tier
FROM orders;

SQL in Data Engineering Pipelines

Data engineers embed SQL inside pipeline code to transform data as it moves through stages. A Python pipeline might extract records from a source database using a SQL SELECT, then insert cleaned records into a destination using a SQL INSERT. A dbt pipeline consists entirely of SQL SELECT statements that the tool converts into CREATE TABLE AS SELECT commands running inside the warehouse.

Summary

SQL is the most essential language in a data engineer's toolkit. SELECT, INSERT, UPDATE, and DELETE cover the fundamental operations. WHERE, GROUP BY, HAVING, and aggregate functions power analytical queries. Data engineers use SQL to build transformation logic, query source systems, and define the table structures that pipelines write into. Fluency in SQL directly improves the speed and quality of every data engineering task.

Previous lesson

Back to course

Next lesson