7. SELECT Querying Deep Dive · Data Engineering

SELECT Querying Deep Dive

SELECT is the command you will use most often. The early lessons used SELECT * to inspect a full table, but real queries usually choose specific columns, filter rows, sort results, and limit output.

Start with a sample table

sql

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  city VARCHAR(100),
  country VARCHAR(50),
  total_spend DECIMAL(10, 2)
);

INSERT INTO customers
VALUES
  (1, "John", "Doe", "Springfield", "USA", 250.00),
  (2, "Jane", "Smith", "Metropolis", "USA", 1200.50),
  (3, "Emily", "Davis", "Gotham", "USA", 740.25),
  (4, "Aarav", "Sharma", "Mumbai", "India", 980.00),
  (5, "Sara", "Wilson", "Austin", "USA", 80.00);

Select specific columns

Prefer explicit columns when you know what you need.

sql

SELECT first_name, last_name, city
FROM customers;

SELECT * is fine for quick exploration, but explicit columns make production queries clearer and reduce unnecessary data transfer.

Aliases

Aliases rename columns in the result.

sql

SELECT
  first_name AS first,
  last_name AS last,
  total_spend AS lifetime_value
FROM customers;

You can also create calculated columns.

sql

SELECT
  first_name,
  total_spend,
  total_spend * 0.10 AS reward_credit
FROM customers;

DISTINCT

DISTINCT removes duplicate result rows.

sql

SELECT DISTINCT country
FROM customers;

Use it when you need unique values, not when you are hiding duplicates caused by a broken join.

WHERE filters

WHERE keeps only rows that match a condition.

sql

SELECT *
FROM customers
WHERE total_spend >= 500;

Combine conditions with AND and OR.

sql

SELECT *
FROM customers
WHERE country = "USA"
  AND total_spend >= 500;

SELECT *
FROM customers
WHERE city = "Mumbai"
   OR city = "Austin";

Use parentheses when the logic mixes AND and OR.

sql

SELECT *
FROM customers
WHERE country = "USA"
  AND (city = "Austin" OR city = "Gotham");

LIKE, BETWEEN, and IN

LIKE matches patterns. % means any number of characters.

sql

SELECT *
FROM customers
WHERE last_name LIKE "S%";

BETWEEN filters an inclusive range.

sql

SELECT *
FROM customers
WHERE total_spend BETWEEN 100 AND 1000;

IN checks whether a value appears in a list.

sql

SELECT *
FROM customers
WHERE city IN ("Austin", "Mumbai", "Gotham");

ORDER BY

Sort results with ORDER BY.

sql

SELECT first_name, last_name, total_spend
FROM customers
ORDER BY total_spend DESC;

Use multiple sort columns when ties matter.

sql

SELECT *
FROM customers
ORDER BY country ASC, total_spend DESC;

LIMIT

LIMIT returns only the first N rows after filtering and sorting.

sql

SELECT first_name, last_name, total_spend
FROM customers
ORDER BY total_spend DESC
LIMIT 3;

Always pair LIMIT with ORDER BY when you expect deterministic top-N results.