SQL SELECT Statement

Introduction

In this chapter, we will learn how to use the SELECT statement in SQL. The SELECT statement is used to retrieve data from a database. It is one of the most commonly used commands in SQL, as it allows you to query and fetch data from one or more tables. This chapter will cover the syntax, various options, and examples to help you understand how to use the SELECT statement effectively.

Sample Data

Before we dive into the examples, let's create a sample table and insert some data. We will use this table for our examples.

Create a Sample Table

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    hire_date DATE,
    salary DECIMAL(10, 2)
);

Insert Sample Data

INSERT INTO employees (first_name, last_name, email, hire_date, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', '2020-01-15', 50000.00),
('Sita', 'Patel', 'sita.patel@example.com', '2019-03-23', 60000.00),
('Arjun', 'Singh', 'arjun.singh@example.com', '2018-07-11', 70000.00),
('Priya', 'Sharma', 'priya.sharma@example.com', '2021-05-19', 55000.00),
('Anil', 'Mehta', 'anil.mehta@example.com', '2020-11-02', 52000.00);

Data in employees Table

id	first_name	last_name	email	hire_date	salary
1	Ramesh	Kumar	ramesh.kumar@example.com	2020-01-15	50000.00
2	Sita	Patel	sita.patel@example.com	2019-03-23	60000.00
3	Arjun	Singh	arjun.singh@example.com	2018-07-11	70000.00
4	Priya	Sharma	priya.sharma@example.com	2021-05-19	55000.00
5	Anil	Mehta	anil.mehta@example.com	2020-11-02	52000.00

Syntax for SELECT Statement

The SELECT statement is used to query data from a table. Here is the basic syntax:

SELECT column1, column2, ...
FROM table_name;

• column1, column2, ...: The columns you want to retrieve data from.
• table_name: The name of the table from which to retrieve the data.

Examples

Select All Columns

To retrieve all columns from a table, you can use the * wildcard.

SELECT * FROM employees;

Output

id	first_name	last_name	email	hire_date	salary
1	Ramesh	Kumar	ramesh.kumar@example.com	2020-01-15	50000.00
2	Sita	Patel	sita.patel@example.com	2019-03-23	60000.00
3	Arjun	Singh	arjun.singh@example.com	2018-07-11	70000.00
4	Priya	Sharma	priya.sharma@example.com	2021-05-19	55000.00
5	Anil	Mehta	anil.mehta@example.com	2020-11-02	52000.00

Select Specific Columns

To retrieve specific columns from a table, specify the column names.

SELECT first_name, last_name, email FROM employees;

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com
Sita	Patel	sita.patel@example.com
Arjun	Singh	arjun.singh@example.com
Priya	Sharma	priya.sharma@example.com
Anil	Mehta	anil.mehta@example.com

Using Column Aliases

Column aliases are used to rename a column in the result set. This can make the output more readable.

SELECT first_name AS FirstName, last_name AS LastName, email AS EmailAddress FROM employees;

Output

FirstName	LastName	EmailAddress
Ramesh	Kumar	ramesh.kumar@example.com
Sita	Patel	sita.patel@example.com
Arjun	Singh	arjun.singh@example.com
Priya	Sharma	priya.sharma@example.com
Anil	Mehta	anil.mehta@example.com

Using Table Aliases

Table aliases are used to give a table a temporary name, often to make queries easier to write and read, especially when joining tables.

SELECT e.first_name, e.last_name, e.email
FROM employees AS e;

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com
Sita	Patel	sita.patel@example.com
Arjun	Singh	arjun.singh@example.com
Priya	Sharma	priya.sharma@example.com
Anil	Mehta	anil.mehta@example.com

Using DISTINCT Keyword

The DISTINCT keyword is used to return only distinct (different) values.

SELECT DISTINCT salary FROM employees;

Output

salary
50000.00
60000.00
70000.00
55000.00
52000.00

Using Functions in SELECT

SQL provides various functions that can be used in the SELECT statement to perform calculations on data.

Example of Using Aggregate Functions

1. COUNT: Returns the number of rows that match a specified criterion.

   SELECT COUNT(*) AS NumberOfEmployees FROM employees;
   

   Output

   NumberOfEmployees
   5

2. SUM: Returns the total sum of a numeric column.

   SELECT SUM(salary) AS TotalSalary FROM employees;
   

   Output

   TotalSalary
   287000.00

3. AVG: Returns the average value of a numeric column.

   SELECT AVG(salary) AS AverageSalary FROM employees;
   

   Output

   AverageSalary
   57400.00

4. MIN: Returns the smallest value of a selected column.

   SELECT MIN(salary) AS MinimumSalary FROM employees;
   

   Output

   MinimumSalary
   50000.00

5. MAX: Returns the largest value of a selected column.

   SELECT MAX(salary) AS MaximumSalary FROM employees;
   

   Output

   MaximumSalary
   70000.00

Concatenating Columns

You can concatenate columns to create a full name from first and last names.

SELECT CONCAT(first_name, ' ', last_name) AS FullName, email FROM employees;

Output

FullName	email
Ramesh Kumar	ramesh.kumar@example.com
Sita Patel	sita.patel@example.com
Arjun Singh	arjun.singh@example.com
Priya Sharma	priya.sharma@example.com
Anil Mehta	anil.mehta@example.com

Conclusion

The SELECT statement is used for retrieving data from a database. This chapter covered the basic syntax, selecting specific columns, using aliases, the DISTINCT keyword, aggregate functions, and concatenating columns. Understanding how to use the SELECT statement effectively is crucial for querying and manipulating data in your database.