Data EngineeringBeginner12 min readUpdated March 2025

SQL Fundamentals for Data Engineers

SQL is the lingua franca of data. This article covers relational concepts and the core statements — SELECT, WHERE, GROUP BY and ORDER BY — that every data engineer uses daily to query and shape data.

What is SQL?

SQL (Structured Query Language) is a declarative language for managing and querying relational databases. You describe *what* data you want, and the database engine decides *how* to retrieve it.

Data engineers use SQL to explore source systems, validate pipeline output, build transformations, and serve data to analysts.

Relational Building Blocks

Relational databases organize data into related tables:

Table — A collection of rows with a fixed set of typed columns.
Row (record) — A single entry, such as one customer or one order.
Primary key — A column (or set of columns) that uniquely identifies each row.
Foreign key — A column that references the primary key of another table, linking them together.
Schema — The logical grouping and definition of tables, columns, and types.

Querying with SELECT

The SELECT statement is the foundation of SQL. Clauses are written in a fixed order, but logically evaluated as FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

sql

-- Top 5 customer countries by revenue in 2024
SELECT
  c.country,
  COUNT(DISTINCT o.order_id) AS total_orders,
  SUM(o.amount)              AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
  AND o.order_date <  '2025-01-01'
  AND o.status = 'COMPLETED'
GROUP BY c.country
HAVING SUM(o.amount) > 10000
ORDER BY revenue DESC
LIMIT 5;

Modifying Data (DML) and Structure (DDL)

Beyond reading data, you will create tables and change their contents:

sql

-- DDL: define a table
CREATE TABLE customers (
  customer_id  INT PRIMARY KEY,
  full_name    VARCHAR(120) NOT NULL,
  country      VARCHAR(60),
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- DML: insert, update, delete
INSERT INTO customers (customer_id, full_name, country)
VALUES (1, 'Asha Verma', 'India');

UPDATE customers SET country = 'USA' WHERE customer_id = 1;

DELETE FROM customers WHERE customer_id = 1;

Common Pitfalls

Avoid these frequent mistakes when writing SQL for pipelines:

Using SELECT * in production queries — it breaks when columns change and wastes I/O.
Forgetting that NULL is not equal to anything, including NULL — use IS NULL.
Running UPDATE or DELETE without a WHERE clause.
Filtering on a computed expression of a column, which can prevent index use.

Key Takeaways

SQL is declarative: describe the result, not the procedure.
Primary and foreign keys define relationships between tables.
Logical evaluation order differs from written order.
Always be explicit about columns and filters in production queries.

Contact Us

Have a question or feedback? Fill out the form below or reach us directly at support@nvaitraining.com