Mastering the Core of SQL

1. SELECT – Choosing What to See

The SELECT clause tells the database what columns or values you want to retrieve. Think of it as your shopping list, you’re specifying exactly what you want from the data.

Example:

SELECT first_name, last_name, email
FROM customers;

This will show only the first_name, last_name, and email columns from the customers table.

Tip: You can also use SELECT * to pull all columns, but it’s best practice to select only what you need.

2. FROM – Where the Data Comes From

The FROM clause tells SQL which table (or tables) you’re pulling data from. Without FROM, SQL won’t know where to look.

Example:

SELECT *
FROM orders;

This grabs all the data from the orders table.

3. WHERE – Filtering the Data

The WHERE clause lets you filter your results based on conditions. It’s like telling SQL, “Only bring me the rows that match these rules.”

Example:

SELECT *
FROM orders
WHERE order_date >= '2024-01-01';

This shows only orders placed after January 1st, 2024.

You can use operators like =, >, <, LIKE, IN, and BETWEEN to build powerful filters.

4. GROUP BY – Organising into Categories

GROUP BY is used when you want to aggregate data (like sums, counts, or averages) based on categories. Instead of looking at raw rows, you summarise them into groups.

Example:

SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id;

This query shows how many orders each customer has made.

Anytime you use an aggregate function (COUNT, SUM, AVG, etc.), you’ll often need GROUP BY.

Tip: You can also GROUP BY ALL or GROUP BY 1, 2

5. HAVING – Filtering Groups

Here’s where people often get confused: if WHERE filters rows, then HAVING filters groups. You use it alongside GROUP BY when you want to apply conditions to aggregated results.

Example:

SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

This shows only customers who placed more than 5 orders.

Remember:

• Use WHERE for filtering individual rows.
• Use HAVING for filtering aggregated results.

Pulling It All Together

Let’s combine everything into one query:

SELECT customer_id, SUM(order_amount) AS total_spent
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
HAVING SUM(order_amount) > 1000;

What does this do?

• SELECT: Choose customer ID and total amount spent.
• FROM: Look in the orders table.
• WHERE: Only consider orders from 2024 onwards.
• GROUP BY: Summarise by customer.
• HAVING: Show only customers who spent more than $1,000.

Final Thoughts

Mastering these five SQL clauses gives you the power to answer most day-to-day data questions. They’re the backbone of querying, and once you’re comfortable with them, you’ll be ready to tackle more advanced SQL concepts like JOINs, subqueries, and window functions.

Think of it this way:

• SELECT = What do you want to see?
• FROM = Where is it coming from?
• WHERE = Which rows do you want?
• GROUP BY = How should the rows be grouped?
• HAVING = Which groups do you want?

With these tools in your pocket, you’re already speaking the language of data.