Skip to main content

Part II: Retrieving Data Chapter 3: Basic SELECT Queries

 

Part II: Retrieving Data

Chapter 3: Basic SELECT Queries



Retrieving data is the core skill every SQL user must master. Whether you’re exploring a new dataset, debugging an application, or building reports, the SELECT statement is your primary tool. In this chapter, we’ll explore:

  • The anatomy of a SELECT statement

  • How to choose specific columns and rename them with aliases

  • Filtering rows precisely with the WHERE clause

  • Sorting results using ORDER BY

  • Limiting output for faster testing with LIMIT and TOP

By the end, you’ll have the confidence to write queries that fetch exactly the data you need—no more, no less.

1. Anatomy of the SELECT Statement

Every SQL query starts with the SELECT clause, which defines what you want to see, and the FROM clause, which specifies where that data lives. A simple query looks like this:

sql
SELECT column1, column2
FROM table_name;

Here’s the typical order of clauses in a SELECT statement:

  1. SELECT – List of columns or expressions to return.

  2. FROM – One or more tables or subqueries.

  3. WHERE – Filters rows before they reach the output.

  4. GROUP BY – Groups rows for aggregation (covered in Chapter 5).

  5. HAVING – Filters groups after aggregation (Chapter 5).

  6. ORDER BY – Sorts the final result set.

  7. LIMIT / TOP – Restricts the number of rows returned.

Even if you omit optional parts like WHERE or ORDER BY, SQL internally processes the clauses in the order above. Understanding this logical flow helps you debug unexpected results.

2. Choosing Columns and Renaming with Aliases

2.1 Explicit Column Lists vs. SELECT *

Using SELECT * returns every column in the table:

sql
SELECT *
FROM employees;

Drawbacks of SELECT *:

  • Transfers unnecessary data over the network

  • Breaks if new columns are added or column order changes

  • Makes intent unclear to readers

Instead, list only the columns you need:

sql
SELECT first_name, last_name, hire_date
FROM employees;

This approach:

  • Improves performance by reducing I/O

  • Clarifies which data matters

  • Guards against schema changes

2.2 Column Aliases

Aliases let you rename columns or expressions in your result set for readability:

sql
SELECT
  employee_id        AS id,
  first_name || ' ' || last_name AS full_name,
  salary * 1.10      AS adjusted_salary
FROM employees;

  • AS id simplifies employee_id.

  • Concatenating first and last names gives full_name.

  • Applying a 10% raise yields adjusted_salary.

Aliases are especially helpful when you apply functions or calculations. Always choose descriptive alias names that reflect the data.

3. Filtering Rows Using WHERE

The WHERE clause narrows down which rows appear in your output. You can use comparison, pattern matching, and logical operators to construct precise filters.

3.1 Comparison Operators

sql
SELECT order_id, total_amount
FROM orders
WHERE total_amount > 100.00
  AND status = 'completed';

Common operators:

  • =, <> (not equal)

  • <, >, <=, >=

  • BETWEEN … AND … (inclusive range)

  • IN (…) (set membership)

3.2 Pattern Matching with LIKE

Search strings with % (any sequence) and _ (single character):

sql
SELECT email
FROM customers
WHERE email LIKE '%@gmail.com';

This returns all Gmail addresses. Use ILIKE in PostgreSQL for case-insensitive matching.

3.3 Handling NULL Values

NULL represents missing or unknown data. Comparisons require IS NULL or IS NOT NULL:

sql
SELECT *
FROM employees
WHERE manager_id IS NOT NULL;

Avoid = NULL—it always yields false.

3.4 Combining Conditions

Use AND, OR, and parentheses to build complex filters:

sql
SELECT *
FROM orders
WHERE (status = 'shipped' OR status = 'delivered')
  AND order_date BETWEEN '2025-01-01' AND '2025-06-30';

Parentheses group conditions so that OR is evaluated before AND.

4. Sorting Results with ORDER BY

The default order of rows in a result set is undefined. To impose a meaningful sequence, use ORDER BY:

sql
SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC, total_amount ASC;

  • DESC sorts in descending order (newest first).

  • ASC (default) sorts ascending.

  • You can sort by column position (ORDER BY 2 DESC) but explicit names improve clarity.

Performance tip: Index the columns you sort by to speed up large result sets.

5. Limiting Output for Faster Testing

During development, you often don’t need the full result set. Limiting output speeds up query execution and reduces network load.

DatabaseSyntaxExample
MySQL, PostgreSQL, SQLiteLIMIT <count> [OFFSET <n>]SELECT * FROM users LIMIT 10;
SQL ServerSELECT TOP <count> *SELECT TOP 10 * FROM users;

5.1 LIMIT and OFFSET

sql
SELECT id, name
FROM products
ORDER BY name
LIMIT 5 OFFSET 10;

  • Returns rows 11 through 15 after sorting by name.

5.2 TOP

sql
SELECT TOP 5 *
FROM products
ORDER BY name;

Choose the syntax your RDBMS supports. Limiting rows is essential when exploring large tables or testing new queries.

6. Putting It All Together: Practical Examples

Example A: Top 5 High-Value Customers

sql
SELECT
  c.customer_id AS id,
  c.first_name || ' ' || c.last_name AS customer,
  SUM(o.total_amount) AS lifetime_spend
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, customer
ORDER BY lifetime_spend DESC
LIMIT 5;

This query finds the top five customers by total spend.

Example B: Recent Low-Stock Products

sql
SELECT
  p.product_id,
  p.product_name,
  i.stock_level
FROM products p
JOIN inventory i ON p.product_id = i.product_id
WHERE i.stock_level < 20
ORDER BY i.stock_level ASC;

Lists products running low on stock, sorted by urgency.

7. Best Practices and Tips

  • Specify columns: Avoid SELECT * to improve performance and clarity.

  • Use aliases: Make your result sets easier to read and maintain.

  • Filter early: Push conditions into WHERE to reduce data processed.

  • Index sorting columns: Speed up ORDER BY on large tables.

  • Limit during development: Use LIMIT/TOP to iterate quickly.

  • Test your filters: Preview row counts with SELECT COUNT(*) before fetching full data.

By mastering these basic SELECT techniques—choosing the right columns, filtering precisely, sorting intentionally, and limiting output—you’ll build a strong foundation for all subsequent SQL skills. In Chapter 4, we’ll dive deeper into advanced filtering and expressions to handle even more complex querying scenarios.

Labels:

Comments

Post a Comment

Popular posts from this blog

Alfred Marshall – The Father of Modern Microeconomics

  Welcome back to the blog! Today we explore the life and legacy of Alfred Marshall (1842–1924) , the British economist who laid the foundations of modern microeconomics . His landmark book, Principles of Economics (1890), introduced core concepts like supply and demand , elasticity , and market equilibrium — ideas that continue to shape how we understand economics today. Who Was Alfred Marshall? Alfred Marshall was a professor at the University of Cambridge and a key figure in the development of neoclassical economics . He believed economics should be rigorous, mathematical, and practical , focusing on real-world issues like prices, wages, and consumer behavior. Marshall also emphasized that economics is ultimately about improving human well-being. Key Contributions 1. Supply and Demand Analysis Marshall was the first to clearly present supply and demand as intersecting curves on a graph. He showed how prices are determined by both what consumers are willing to pay (dem...
Post a Comment
Continue reading

Unlocking South America's Data Potential: Trends, Challenges, and Strategic Opportunities for 2025

  Introduction South America is entering a pivotal phase in its digital and economic transformation. With countries like Brazil, Mexico, and Argentina investing heavily in data infrastructure, analytics, and digital governance, the region presents both challenges and opportunities for professionals working in Business Intelligence (BI), Data Analysis, and IT Project Management. This post explores the key data trends shaping South America in 2025, backed by insights from the World Bank, OECD, and Statista. It’s designed for analysts, project managers, and decision-makers who want to understand the region’s evolving landscape and how to position themselves for impact. 1. Economic Outlook: A Region in Transition According to the World Bank’s Global Economic Prospects 2025 , Latin America is expected to experience slower growth compared to global averages, with GDP expansion constrained by trade tensions and policy uncertainty. Brazil and Mexico remain the largest economies, with proj...
Post a Comment
Continue reading

Kickstart Your SQL Journey with Our Step-by-Step Tutorial Series

  Welcome to Data Analyst BI! If you’ve ever felt overwhelmed by rows, columns, and cryptic error messages when trying to write your first SQL query, you’re in the right place. Today we’re launching a comprehensive SQL tutorial series crafted specifically for beginners. Whether you’re just starting your data career, pivoting from another field, or simply curious about how analysts slice and dice data, these lessons will guide you from day zero to confident query builder. In each installment, you’ll find clear explanations, annotated examples, and hands-on exercises. By the end of this series, you’ll be able to: Write efficient SQL queries to retrieve and transform data Combine multiple tables to uncover relationships Insert, update, and delete records safely Design robust database schemas with keys and indexes Optimize performance for large datasets Ready to master SQL in a structured, step-by-step way? Let’s explore the full roadmap ahead. Wh...
Post a Comment
Continue reading