The SQL WHERE clause is used to filter records based on a specified condition. It is commonly used with the SELECT, UPDATE, and DELETE statements.
SQL WHERE Syntax:
To solve a query of SQL, It is more important to understand its syntax of any statement.
SQL code
SELECT column1, column2, ...
FROM table_name
WHERE department = condition;
Create a simple database schema with SQL
Here we have created a sample database for the SQL WHERE clause so that you can understand that easily and can also check on SQL server and even check & validate the same so easily. After click over run button you can check with the SQL server compiler.
SQL WHERE Code
-- Create a table for storing information about employees
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10, 2),
HireDate DATE
);
-- Insert some sample data into the Employees table
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary, HireDate)
VALUES
(1, 'John', 'Doe', 'Sales', 50000.00, '2020-01-15'),
(2, 'Jane', 'Smith', 'Marketing', 60000.00, '2019-07-10'),
(3, 'Michael', 'Johnson', 'Sales', 55000.00, '2021-02-20'),
(4, 'Emily', 'Brown', 'HR', 48000.00, '2020-11-05'),
(5, 'David', 'Martinez', 'IT', 65000.00, '2018-05-12');
-- Query to select employees from the Sales department
SELECT * FROM Employees WHERE Department = 'Sales';
-- Query to select employees hired after January 1, 2020
SELECT * FROM Employees WHERE HireDate > '2020-01-01';
-- Query to select employees with a salary greater than 55000
SELECT * FROM Employees WHERE Salary > 55000.00;
Output-
Query 1 - Select employees from the Sales department
SELECT * FROM Employees WHERE Department = 'Sales';
| EmployeeID | FirstName | LastName | Department | Salary | HireDate |
|---|---|---|---|---|---|
| 1 | John | Doe | Sales | 50000.00 | 2020-01-15 |
| 3 | Michael | Johnson | Sales | 55000.00 | 2021-02-20 |
Query 2 - Select employees hired after January 1, 2020
SELECT * FROM Employees WHERE HireDate > '2020-01-01';
| EmployeeID | FirstName | LastName | Department | Salary | HireDate |
|---|---|---|---|---|---|
| 1 | John | Doe | Sales | 50000.00 | 2020-01-15 |
| 3 | Michael | Johnson | Sales | 55000.00 | 2021-02-20 |
| 4 | Emily | Brown | HR | 48000.00 | 2020-11-05 |
Query 3 - Select employees with a salary greater than 55000
SELECT * FROM Employees WHERE Salary > 55000.00;
| EmployeeID | FirstName | LastName | Department | Salary | HireDate |
|---|---|---|---|---|---|
| 2 | Jane | Smith | Marketing | 60000.00 | 2019-07-10 |
| 5 | David | Martinez | IT | 65000.00 | 2018-05-12 |
Create Data Base
-- Create the database
CREATE DATABASE ArticleDB;
USE ArticleDB;
-- Create tables
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50)
);
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
price DECIMAL(10, 2)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE
);
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100),
grade VARCHAR(2)
);
-- Insert sample data into tables (Optional)
INSERT INTO employees (employee_id, first_name, last_name, department) VALUES
(1, 'John', 'Doe', 'HR'),
(2, 'Jane', 'Smith', 'IT'),
(3, 'Michael', 'Johnson', 'Marketing');
INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Product A', 25.99),
(2, 'Product B', 50.50),
(3, 'Product C', 75.00);
INSERT INTO orders (order_id, order_date) VALUES
(1, '2024-01-05'),
(2, '2024-01-15'),
(3, '2024-01-25');
INSERT INTO customers (customer_id, name) VALUES
(1, 'John Doe'),
(2, 'Jane Smith'),
(3, 'David Martinez');
INSERT INTO students (student_id, name, grade) VALUES
(1, 'Alice', 'A'),
(2, 'Bob', 'B'),
(3, 'Charlie', NULL);
Use the SQL WHERE clause to filter data based on specific conditions in various tables of the ArticleDB database.
Filtering employees by department:
This query retrieves all employees who belong to the ‘HR’ department.
SQL WHERE Query
SELECT *
FROM employees
WHERE department = 'HR';
Filtering products by price:
This query selects all products with a price greater than $50.
SQL WHERE Query
SELECT *
FROM products
WHERE price > 50;
Filtering orders by order date:
This query retrieves orders placed on or after January 15, 2024.
SQL WHERE Query
SELECT *
FROM orders
WHERE order_date >= '2024-01-15';
Filtering customers by name:
This query selects customers whose names start with ‘Jane’
SQL WHERE Query
SELECT *
FROM customers
WHERE name LIKE 'Jane%';
Filtering students with NULL grade:
This query retrieves students with no assigned grade.
SQL WHERE Query
SELECT *
FROM students
WHERE grade IS NULL;
Q. Can I use multiple conditions in a WHERE clause?
A. Yes, you can combine conditions using logical operators such as AND, OR, and NOT.
Q. Can I use functions in the WHERE clause?
A. Yes, you can use functions like UPPER(), LOWER(), LENGTH(), etc., to manipulate data before applying conditions.
Q. What if I want to filter based on a range of values?
A. You can use comparison operators like >, <, >=, <=, BETWEEN, and IN to specify ranges or lists of values.
Q. How do I handle NULL values in WHERE clauses?
A. Use IS NULL or IS NOT NULL to filter rows based on whether a column contains NULL values.
Q. Can I nest WHERE clauses?
A. Yes, you can use subqueries to nest WHERE clauses for more complex filtering.
Do you like this article?
0 Likes
