SQL SELECT ... FROM ...: A Beginner's Guide

Structured Query Language (SQL) is a powerful tool used for managing and manipulating relational databases. One of the fundamental SQL statements is SELECT, which allows you to retrieve data from a database table. In this beginner's guide, we'll dive into the SELECT statement, explaining its syntax and providing detailed examples with outputs to help you get started.

Basic Syntax of the SELECT Statement

The basic syntax of the SELECT statement is as follows:

SELECT column1, column2, ... 
FROM table_name 
WHERE condition;        

• SELECT: This keyword indicates that you want to retrieve data from the specified table.
• column1, column2, ...: Here, you list the columns from which you want to fetch data. You can use * to select all columns.
• FROM: Specifies the table from which you want to retrieve data.
• WHERE: Allows you to filter the rows based on a specified condition. This part is optional but very useful for refining your results.

Note 1: Before we start in querying the tables, we need to create tables first. The following article can help explain how to create tables

Note 2: We will be using https://www.db-fiddle.com/ for practice and examples.?

Here is the code to create the table used in the examples below (Please, add it to db-fiddle):

CREATE TABLE employees (
  `ID` INTEGER,
  `FirstName` VARCHAR(4),
  `LastName` VARCHAR(7),
  `JobTitle` VARCHAR(9),
  `Salary` INTEGER
);

INSERT INTO employees
  (`ID`, `FirstName`, `LastName`, `JobTitle`, `Salary`)
VALUES
  ('1', 'John', 'Doe', 'Manager', '60000'),
  ('2', 'Jane', 'Smith', 'Analyst', '50000'),
  ('3', 'Bob', 'Johnson', 'Developer', '55000');        

Retrieving Data from a Single Table

Let's start with a simple example using a hypothetical employees table:

Example 1: Selecting All Columns from the employees Table

SELECT *
FROM employees;        

In this example, we are selecting all columns from the employees table. The * symbol acts as a wildcard, retrieving all columns. Here's what the output might look like:

+----+-----------+---------+-----------+--------+
| ID | FirstName | LastName|  JobTitle | Salary |
+----+-----------+---------+-----------+--------+
| 1  | John      | Doe     | Manager   | 60000  |
| 2  | Jane      | Smith   | Analyst   | 50000  |
| 3  | Bob       | Johnson | Developer | 55000  |
+----+-----------+---------+-----------+--------+
        

Example 2: Selecting Specific Columns from the employees Table

SELECT FirstName, LastName, Salary
FROM employees;
        

In this example, we are selecting only the FirstName, LastName, and Salary columns from the employees table. The output will display only these selected columns:

+-----------+---------+--------+
| FirstName | LastName| Salary |
+-----------+---------+--------+
| John      | Doe     | 60000  |
| Jane      | Smith   | 50000  |
| Bob       | Johnson | 55000  |
+-----------+---------+--------+
        

Practice Problems

Let's have some more practice problems. For the following problems, please use the following schema and copy it to df-fiddle (Full code can be found here )

Problem1:

return all titles in the books table

Solution

-- Return all titles from the books table
SELECT title
FROM books;        

Problem2:

Now, select both author and title

Solution:

SELECT author, title
FROM books;        

Problem3:

Select all fields from books

Solution:

-- Select all fields from the books table
SELECT *
FROM books;        

Adding Aliases

In SQL, you can add an alias to a column name using the AS keyword in your SELECT statement. An alias is a custom name that you assign to a column, and it can make your query results more readable and meaningful. Here's how you can use SELECT ... AS ... to add aliases to column names:

SELECT column_name AS alias_name
FROM table_name;
        

Let's break down the syntax:

• column_name: This is the name of the column you want to select.
• AS: This keyword is used to indicate that you're defining an alias for the column.
• alias_name: This is the custom name you want to give to the column, and it will appear as the column name in the query result.

Here are some examples to illustrate how to use aliases in SQL:

Example 1: Adding an Alias to a Single Column

Suppose you have a table called employees, and you want to add an alias to the Salary column to make it more descriptive:

SELECT Salary AS AnnualSalary
FROM employees;
        

In this query, we're selecting the Salary column from the employees table and giving it an alias of AnnualSalary. The result will display the AnnualSalary column:

+--------------+
| AnnualSalary |
+--------------+
| 60000        |
| 50000        |
| 55000        |
+--------------+
        

Example 2: Adding Aliases to Multiple Columns

You can also add aliases to multiple columns in a single query:

SELECT FirstName AS First_Name, LastName AS Last_Name
FROM employees;
        

In this example, we're selecting the FirstName and LastName columns from the employees table and assigning them aliases First_Name and Last_Name. The result will display these alias names:

+-----------+----------+
| First_Name| Last_Name|
+-----------+----------+
| John      | Doe      |
| Jane      | Smith    |
| Bob       | Johnson  |
+-----------+----------+
        

Example 3: Using Aliases with Expressions

You can also use aliases with expressions or calculations. For instance, if you want to calculate the total salary of employees and provide an alias for the result:

SELECT SUM(Salary) AS Total_Salary
FROM employees;
        

In this query, we're calculating the sum of the Salary column and giving it an alias of Total_Salary. The result will display the alias as the column name:

+-------------+
| Total_Salary|
+-------------+
| 165000      |
+-------------+
        

Adding aliases to columns can make your query results more meaningful and easier to understand, especially when dealing with complex queries or calculations. It's a useful practice for improving the readability of your SQL queries and results.

Selecting Distinct Records

In SQL, the DISTINCT keyword is used in a SELECT statement to eliminate duplicate values from the result set. When you use DISTINCT, it instructs the database to return only unique values for the specified columns. This can be particularly useful when you want to retrieve a list of unique values from one or more columns in a table. Here's how you can use DISTINCT:

SELECT DISTINCT column1, column2, ...
FROM table_name;
        

Let's break down the usage of DISTINCT:

• column1, column2, ...: These are the columns for which you want to retrieve distinct values. You can specify one or more columns.
• table_name: This is the name of the table from which you want to retrieve data.

Here are some examples to illustrate the use of DISTINCT:

DISTINCT Example 1: Retrieving Distinct Values from a Single Column

Suppose you have a table called countries with a column named continent, and you want to retrieve a list of distinct continents:

SELECT DISTINCT continent
FROM countries;        

In this query, we're selecting the continent column from the countries table, and the DISTINCT keyword ensures that only unique continent values are returned:

+-----------+
| continent |
+-----------+
| Asia      |
| Europe    |
| Africa    |
| North America |
| South America |
| Australia |
+-----------+
        

DISTINCT Example 2: Retrieving Distinct Values from Multiple Columns

You can also use DISTINCT with multiple columns. For instance, if you have a table called employees with FirstName and LastName columns, and you want to retrieve a list of distinct full names:

SELECT DISTINCT FirstName, LastName
FROM employees;
        

In this query, we're selecting both FirstName and LastName columns, and DISTINCT ensures that only unique combinations of first names and last names are returned:

+-----------+----------+
| FirstName | LastName |
+-----------+----------+
| John      | Doe      |
| Jane      | Smith    |
| Bob       | Johnson  |
| Mary      | Johnson  |
+-----------+----------+
        

DISTINCT Example 3: Using DISTINCT with Expressions

You can also use DISTINCT with expressions or calculations. For example, if you want to retrieve a list of distinct age groups based on the Age column in a table:

SELECT DISTINCT CASE
                   WHEN Age < 30 THEN 'Under 30'
                   WHEN Age >= 30 AND Age < 50 THEN '30-49'
                   ELSE '50 and over'
               END AS Age_Group
FROM employees;
        

In this query, we're using a CASE expression to categorize employees into age groups, and DISTINCT ensures that only unique age group values are returned:

+-----------+
| Age_Group |
+-----------+
| Under 30  |
| 30-49     |
| 50 and over |
+-----------+
        

Using the DISTINCT keyword is a handy way to retrieve unique values from one or more columns in your database, helping you to analyze and understand your data more effectively.

WHERE Example: Using the WHERE Clause to Filter Data

SELECT FirstName, LastName
FROM employees
WHERE Salary > 55000;
        

In this example, we use the WHERE clause to filter rows where the Salary is greater than 55000. The output will display only the FirstName and LastName of employees meeting this condition:

+-----------+---------+
| FirstName | LastName|
+-----------+---------+
| John      | Doe     |
| Bob       | Johnson |
+-----------+---------+
        

Retrieving Data from Multiple Tables (JOIN)

Often, you'll need to retrieve data from multiple tables. SQL allows you to do this using the JOIN clause. Let's consider two hypothetical tables: employees and departments.

Example 4: Inner Join between employees and departments Tables

SELECT employees.FirstName, employees.LastName, departments.DepartmentName
FROM employees
INNER JOIN departments
ON employees.DepartmentID = departments.DepartmentID;
        

In this example, we are performing an inner join between the employees and departments tables on the DepartmentID column. The output will display the first and last names of employees along with their corresponding department names:

+-----------+---------+------------------+
| FirstName | LastName| DepartmentName   |
+-----------+---------+------------------+
| John      | Doe     | Sales            |
| Jane      | Smith   | Marketing        |
| Bob       | Johnson | Development      |
+-----------+---------+------------------+
        

More details about JOINs can be found in the following article :

Conclusion

The SELECT statement is a fundamental SQL command that allows you to retrieve data from one or more tables. With the ability to specify which columns to retrieve and filter data using the WHERE clause, you can tailor your queries to meet specific requirements. Additionally, using JOIN, you can combine data from multiple tables to gain more insights from your database.

As a beginner, mastering the SELECT statement is a crucial step towards becoming proficient in SQL. Practice and experimentation with various queries will help you build your SQL skills and unlock the full potential of your relational database.