The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Filtering Data with WHERE

Next ►
►

In this section, we will explore how to filter data in SQL using the WHERE clause. The WHERE clause is used to specify conditions that must be met for the rows to be included in the result set. This is a fundamental concept in SQL that allows you to retrieve only the data that meets specific criteria.

Key Concepts

  1. Basic Syntax of WHERE Clause
  2. Comparison Operators
  3. Logical Operators
  4. Combining Multiple Conditions
  5. Practical Examples
  6. Exercises

  1. Basic Syntax of WHERE Clause

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

Syntax

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

Example

SELECT * FROM employees
WHERE age > 30;

In this example, the query selects all columns from the employees table where the age is greater than 30.

  1. Comparison Operators

Comparison operators are used in the WHERE clause to compare values. Here are some common comparison operators:

Operator Description
= Equal to
<> Not equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to

Example

SELECT * FROM employees
WHERE salary >= 50000;

This query selects all employees with a salary of 50,000 or more.

  1. Logical Operators

Logical operators are used to combine multiple conditions in the WHERE clause. The most common logical operators are:

Operator Description
AND All conditions must be true
OR At least one condition must be true
NOT Reverses the condition

Example

SELECT * FROM employees
WHERE age > 30 AND department = 'Sales';

This query selects all employees who are older than 30 and work in the Sales department.

  1. Combining Multiple Conditions

You can combine multiple conditions using logical operators to create more complex queries.

Example

SELECT * FROM employees
WHERE (age > 30 AND department = 'Sales') OR (age < 25 AND department = 'Marketing');

This query selects employees who are either older than 30 and work in Sales or younger than 25 and work in Marketing.

  1. Practical Examples

Example 1: Simple Condition

SELECT * FROM products
WHERE price < 100;

This query selects all products with a price less than 100.

Example 2: Multiple Conditions with AND

SELECT * FROM orders
WHERE order_date >= '2023-01-01' AND status = 'Shipped';

This query selects all orders that were placed on or after January 1, 2023, and have a status of 'Shipped'.

Example 3: Multiple Conditions with OR

SELECT * FROM customers
WHERE city = 'New York' OR city = 'Los Angeles';

This query selects all customers who are located in either New York or Los Angeles.

  1. Exercises

Exercise 1

Question: Write a query to select all employees who are older than 40.

Solution:

SELECT * FROM employees
WHERE age > 40;

Exercise 2

Question: Write a query to select all products that are either in the 'Electronics' category or have a price greater than 500.

Solution:

SELECT * FROM products
WHERE category = 'Electronics' OR price > 500;

Exercise 3

Question: Write a query to select all orders that were placed in 2022 and have a status of 'Pending'.

Solution:

SELECT * FROM orders
WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31' AND status = 'Pending';

Common Mistakes and Tips

  • Using Incorrect Operators: Ensure you use the correct comparison and logical operators for your conditions.
  • Parentheses for Clarity: Use parentheses to group conditions and make your queries easier to read and understand.
  • Case Sensitivity: Be aware of case sensitivity in string comparisons, which can vary depending on the SQL database system.

Conclusion

In this section, we covered how to filter data using the WHERE clause in SQL. We explored the basic syntax, comparison operators, logical operators, and how to combine multiple conditions. Practical examples and exercises were provided to reinforce the concepts. Understanding how to use the WHERE clause effectively is crucial for retrieving specific data from your database. In the next section, we will learn how to sort data using the ORDER BY clause.

◄ Previous
◄
Next ►
►

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL Queries

Module 3: Working with Multiple Tables

Module 4: Advanced Data Filtering

Module 5: Data Manipulation

Module 6: Advanced SQL Functions

Module 7: Subqueries and Nested Queries

Module 8: Indexes and Performance Optimization

Module 9: Transactions and Concurrency

Module 10: Advanced Topics

Module 11: SQL in Practice

Module 12: Final Project

The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved