SQL Tutorial: SELECT Statement & WHERE Clause with Examples | Operators in SQL

Introduction

Structured Query Language (SQL) is a powerful tool used for managing and manipulating databases. Among its various functionalities, the SELECT statement and WHERE clause are fundamental components that enable users to retrieve and filter data from databases. This tutorial will provide a comprehensive guide on the SELECT statement and WHERE clause, including examples and explanations of SQL operators.

SELECT Statement

The SELECT statement is used to fetch data from a database. The data returned is stored in a result table, sometimes called the result-set.

Basic Syntax

SELECT column1, column2, ...
FROM table_name;

Example

Consider a database named Employees with a table EmployeeDetails that contains the following columns: EmployeeID, FirstName, LastName, Department, and Salary.

Get SHUBHAM INGOLE’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

To retrieve all columns for all employees, you can use the following query:

SELECT * 
FROM EmployeeDetails;

To retrieve only the first names and salaries of all employees:

SELECT FirstName, Salary 
FROM EmployeeDetails;

WHERE Clause

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.

Basic Syntax

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

Example

To fetch the details of employees who work in the ‘Sales’ department:

SELECT * 
FROM EmployeeDetails
WHERE Department = 'Sales';

To fetch the details of employees with a salary greater than 50000:

SELECT * 
FROM EmployeeDetails
WHERE Salary > 50000;

SQL Operators

SQL operators are used to specify conditions in SQL statements. They can be categorized into several types: arithmetic, comparison, logical, and others.

Comparison Operators

• =: Equal
• <> or !=: Not equal
• >: Greater than
• <: Less than
• >=: Greater than or equal
• <=: Less than or equal
• BETWEEN: Between a certain range
• LIKE: Search for a pattern
• IN: Specify multiple possible values for a column

Example

To find employees whose salary is between 40000 and 60000:

SELECT * 
FROM EmployeeDetails
WHERE Salary BETWEEN 40000 AND 60000;

To find employees whose first name starts with ‘J’:

SELECT * 
FROM EmployeeDetails
WHERE FirstName LIKE 'J%';

Logical Operators

• AND: Combines two or more conditions - all conditions must be true
• OR: Combines two or more conditions - at least one condition must be true
• NOT: Negates a condition

Example

To find employees who work in the ‘HR’ department and have a salary greater than 45000:

SELECT * 
FROM EmployeeDetails
WHERE Department = 'HR' AND Salary > 45000;

To find employees who work in the ‘HR’ department or have a salary greater than 45000:

SELECT * 
FROM EmployeeDetails
WHERE Department = 'HR' OR Salary > 45000;

Practical Examples

Example 1: Combining SELECT and WHERE with Multiple Conditions

To retrieve the first names, last names, and departments of employees who work in either the ‘IT’ or ‘Finance’ department and have a salary greater than 55000:

SELECT FirstName, LastName, Department 
FROM EmployeeDetails
WHERE (Department = 'IT' OR Department = 'Finance') AND Salary > 55000;

Example 2: Using IN and BETWEEN Operators

To find employees who belong to either the ‘Marketing’, ‘Sales’, or ‘HR’ department and whose salary is between 30000 and 70000:

SELECT * 
FROM EmployeeDetails
WHERE Department IN ('Marketing', 'Sales', 'HR') AND Salary BETWEEN 30000 AND 70000;

Example 3: Using NOT and LIKE Operators

To retrieve the details of employees whose last name does not start with ‘S’:

SELECT * 
FROM EmployeeDetails
WHERE LastName NOT LIKE 'S%';

Conclusion

The SELECT statement and WHERE clause are essential components of SQL, providing the means to query and filter data effectively. By understanding and utilizing SQL operators, users can perform complex queries to meet their data retrieval needs. This tutorial has provided an overview of these concepts with practical examples to help you get started with querying databases using SQL.

For further learning, practice these queries on your database, explore more advanced SQL functionalities, and refer to official documentation for deeper insights. Happy querying!