How to Retrieve Data from a Single Table in SQL

Retrieving data from a single table is the foundation of SQL. In this tutorial, we’ll explore the basic SELECT statement and learn how to apply filtering, sorting, and functions to make data retrieval more precise and useful.

Table of Contents

Understanding the SELECT Statement

The SELECT statement is used to specify which columns of data you want to retrieve from a table. Here’s the simplest form:

SELECT column1, column2, ...
FROM table_name;

Example: Imagine we have a table called Employees with columns EmployeeID, FirstName, LastName, and Salary. To retrieve all employee names, we would write:

SELECT FirstName, LastName
FROM Employees;

This query returns only the FirstName and LastName columns for every row in the Employees table.

Retrieving All Columns

If you want to retrieve every column from the table, use the asterisk *:

SELECT *
FROM Employees;

The above query retrieves all columns and rows from the Employees table.

Filtering Data with WHERE

To get specific data, use the WHERE clause to filter results. The WHERE clause allows you to specify conditions.

Example: Retrieve employees with a salary above $50,000

SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

You can use comparison operators like =, !=, <, >, <=, and >= in WHERE conditions, as well as logical operators AND, OR, and NOT.

Sorting Data with ORDER BY

The ORDER BY clause sorts results by one or more columns in ascending (ASC) or descending (DESC) order.

Example: To list employees by salary in descending order:

SELECT FirstName, LastName, Salary
FROM Employees
ORDER BY Salary DESC;

If you omit DESC, SQL assumes ASC by default.

Using Aliases with AS

Aliases temporarily rename a column in the results, improving readability.

Example: Let’s retrieve FirstName and LastName as a single column labeled “Full Name.”

SELECT FirstName || ' ' || LastName AS "Full Name"
FROM Employees;

This returns a “Full Name” column combining FirstName and LastName.

Using Functions to Transform Data

SQL has various functions for manipulating data in a single table. Here are some common ones:

COUNT: Counts the number of rows.

SELECT COUNT(*) AS "Total Employees"
FROM Employees;

SUM: Adds values in a numeric column.

SELECT SUM(Salary) AS "Total Salaries"
FROM Employees;

AVG: Finds the average value.

SELECT AVG(Salary) AS "Average Salary"
FROM Employees;

Limiting Results with LIMIT

Use LIMIT to restrict the number of rows returned.

Example: Retrieve the top 5 highest-paid employees:

SELECT FirstName, LastName, Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 5;

Conclusion

This tutorial covered the essentials of retrieving data from a single table in SQL. With SELECT, WHERE, ORDER BY, and functions, you can control what data you see, filter it precisely, and transform it for meaningful insights. Practice these skills on a sample database, and you’ll be ready to tackle more complex queries quickly.