Part II: Retrieving Data with SQL

 Retrieving data is the heart of SQL. In Part II of our beginner-friendly tutorial series, we’ll dive into the four essential techniques that let you extract, filter, summarize, and refine datasets:

• Basic SELECT Queries

• Advanced Filtering and Expressions

• Aggregation and Grouping

• Subqueries and Derived Tables

Mastering these topics will empower you to answer real-world questions, from listing customer orders to calculating monthly sales trends.

Basic SELECT Queries

The SELECT statement is your gateway to any relational database. You’ll learn how to:

• Specify columns and use aliases (SELECT first_name AS fname)

• Retrieve all fields with SELECT * for quick previews

• Limit result sets (LIMIT 10, TOP 5) to speed up testing

• Sort data with ORDER BY (ascending/descending)

Example:

sql

SELECT id, first_name, last_name
FROM customers
ORDER BY last_name ASC;

This simple query fetches a clean, ordered list of customer names in seconds.

Advanced Filtering and Expressions

Once you can pull rows, you’ll want to narrow them down. Advanced filtering covers:

• Logical operators: AND, OR, NOT

• Comparison operators: =, <, >, BETWEEN, IN

• Pattern matching: LIKE '%@gmail.com'

• Null checks: IS NULL, COALESCE

• Calculated fields: SELECT price * quantity AS total_cost

Example:

sql

SELECT *
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-06-30'
  AND status = 'shipped'
  AND customer_email LIKE '%@example.com';

This filters orders placed in H1 2024 by customers and shows only shipped items.

Aggregation and Grouping

To transform rows into insights, use aggregation functions:

• COUNT(), SUM(), AVG(), MIN(), MAX()

• Group data by one or more columns with GROUP BY

• Filter aggregated results using HAVING

Example:

sql

SELECT product_category, SUM(quantity) AS total_sold
FROM sales
GROUP BY product_category
HAVING SUM(quantity) > 1000;

Here, you get categories that sold over 1,000 units—perfect for identifying top performers.

Subqueries and Derived Tables

Complex analyses often require breaking queries into steps. Subqueries let you:

• Nest queries inside SELECT, FROM, or WHERE clauses

• Use EXISTS / NOT EXISTS to test record presence

• Build inline views (derived tables) for cleaner logic

Example:

sql

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

This finds employees earning above their department’s average—insightful for performance reviews or compensation planning.

By the end of Part II, you’ll confidently write queries that retrieve precise datasets, apply rich filters, summarize key metrics, and structure multi-stage analyses. Stay tuned for Part III, where we explore joining tables and combining disparate data sources into unified insights.