SQL Filtering & Sorting Data

WHERE Clause

The WHERE clause filters records based on specified conditions.

Basic Syntax:

sql

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

Comparison Operators

Logical Operators

• AND: All conditions must be true

• OR: At least one condition must be true

• NOT: Negates a condition

Sample Data for Examples

Customers Table:

sql

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    city VARCHAR(50),
    country VARCHAR(50),
    join_date DATE
);

INSERT INTO Customers VALUES
(1, 'John Smith', 25, 'New York', 'USA', '2020-01-15'),
(2, 'Maria Garcia', 32, 'Madrid', 'Spain', '2019-03-20'),
(3, 'Bob Johnson', 45, 'London', 'UK', '2018-07-10'),
(4, 'Alice Brown', 28, 'Paris', 'France', '2021-05-30'),
(5, 'Mike Davis', 35, 'Berlin', 'Germany', '2020-11-22'),
(6, 'Sarah Wilson', 22, 'Sydney', 'Australia', '2022-02-14'),
(7, 'Tom Miller', 41, 'Toronto', 'Canada', '2017-09-05');

Products Table:

sql

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2),
    stock_quantity INT,
    rating DECIMAL(3,2)
);

INSERT INTO Products VALUES
(1, 'Laptop', 'Electronics', 999.99, 15, 4.5),
(2, 'Smartphone', 'Electronics', 699.99, 25, 4.2),
(3, 'Desk Chair', 'Furniture', 149.99, 30, 4.0),
(4, 'Coffee Maker', 'Kitchen', 79.99, 40, 4.3),
(5, 'Headphones', 'Electronics', 199.99, 20, 4.7),
(6, 'Bookshelf', 'Furniture', 89.99, 35, 4.1),
(7, 'Blender', 'Kitchen', 49.99, 50, 3.8);

WHERE Clause Examples

1. Filter by exact value:

sql

SELECT * FROM Customers 
WHERE country = 'USA';

2. Filter with comparison operators:

sql

-- Customers older than 30
SELECT name, age, city 
FROM Customers 
WHERE age > 30;

-- Products cheaper than $100
SELECT product_name, price 
FROM Products 
WHERE price < 100;

3. Multiple conditions with AND:

sql

-- Customers from USA older than 25
SELECT * FROM Customers 
WHERE country = 'USA' AND age > 25;

-- Electronics products between $100 and $500
SELECT product_name, price 
FROM Products 
WHERE category = 'Electronics' 
AND price BETWEEN 100 AND 500;

4. Multiple conditions with OR:

sql

-- Customers from USA or UK
SELECT name, country 
FROM Customers 
WHERE country = 'USA' OR country = 'UK';

-- Products in Electronics or Kitchen category
SELECT product_name, category 
FROM Products 
WHERE category = 'Electronics' OR category = 'Kitchen';

5. Combining AND and OR (use parentheses):

sql

-- Customers from USA older than 25 OR from UK any age
SELECT name, country, age 
FROM Customers 
WHERE (country = 'USA' AND age > 25) 
   OR country = 'UK';

6. NOT operator:

sql

-- Customers NOT from USA
SELECT name, country 
FROM Customers 
WHERE NOT country = 'USA';

-- Products NOT in Electronics category
SELECT product_name, category 
FROM Products 
WHERE NOT category = 'Electronics';

LIKE Operator for Pattern Matching

• % - Matches any sequence of characters

• _ - Matches any single character

sql

-- Names starting with 'A'
SELECT name FROM Customers WHERE name LIKE 'A%';

-- Names ending with 'n'
SELECT name FROM Customers WHERE name LIKE '%n';

-- Names containing 'oh'
SELECT name FROM Customers WHERE name LIKE '%oh%';

-- Names with exactly 3 letters starting with 'B'
SELECT name FROM Customers WHERE name LIKE 'B__';

ORDER BY Clause

Sorts the result set in ascending or descending order.

Syntax:

sql

SELECT columns
FROM table
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

Examples:

sql

-- Sort customers by age (ascending - default)
SELECT name, age FROM Customers ORDER BY age;

-- Sort products by price (descending)
SELECT product_name, price FROM Products ORDER BY price DESC;

-- Sort by multiple columns
SELECT name, age, country FROM Customers 
ORDER BY country ASC, age DESC;

DISTINCT Keyword

Removes duplicate values from the result set.

sql

-- Get unique countries
SELECT DISTINCT country FROM Customers;

-- Get unique combinations of country and city
SELECT DISTINCT country, city FROM Customers;

LIMIT and OFFSET

• LIMIT: Restricts the number of rows returned

• OFFSET: Skips a specified number of rows

sql

-- Get top 3 most expensive products
SELECT product_name, price 
FROM Products 
ORDER BY price DESC 
LIMIT 3;

-- Get 3 products starting from the 2nd most expensive
SELECT product_name, price 
FROM Products 
ORDER BY price DESC 
LIMIT 3 OFFSET 1;

-- Alternative syntax (some databases)
SELECT product_name, price 
FROM Products 
ORDER BY price DESC 
OFFSET 1 ROWS FETCH NEXT 3 ROWS ONLY;

Practical Exercises

Exercise 1: Filter Customers by Age

sql

-- Customers aged 25-35
SELECT name, age, city 
FROM Customers 
WHERE age BETWEEN 25 AND 35 
ORDER BY age;

-- Youngest 3 customers
SELECT name, age 
FROM Customers 
ORDER BY age ASC 
LIMIT 3;

-- Customers older than 40 from Europe
SELECT name, age, country 
FROM Customers 
WHERE age > 40 
AND country IN ('UK', 'Germany', 'France', 'Spain');

Exercise 2: Sort Products by Price

sql

-- Top 5 most expensive products
SELECT product_name, price, category 
FROM Products 
ORDER BY price DESC 
LIMIT 5;

-- Cheapest product in each category
SELECT category, MIN(price) as min_price 
FROM Products 
GROUP BY category 
ORDER BY min_price;

-- Products sorted by rating then price
SELECT product_name, rating, price 
FROM Products 
ORDER BY rating DESC, price ASC;

Exercise 3: Complex Filtering

sql

-- Electronics products with high rating and good stock
SELECT product_name, price, rating, stock_quantity 
FROM Products 
WHERE category = 'Electronics' 
AND rating >= 4.0 
AND stock_quantity > 10 
ORDER BY rating DESC, price ASC;

-- Customers who joined in the last 3 years and are under 30
SELECT name, age, join_date 
FROM Customers 
WHERE join_date >= DATE_SUB(CURDATE(), INTERVAL 3 YEAR) 
AND age < 30 
ORDER BY join_date DESC;

Key Points to Remember

1. WHERE comes before ORDER BY in SQL syntax

2. Use parentheses when combining AND/OR for complex logic

3. LIKE is case-sensitive in some databases (use ILIKE in PostgreSQL for case-insensitive)

4. LIMIT/OFFSET syntax varies between databases

5. Always test your filters with sample data to ensure they work as expected

Practice these concepts with different datasets to become proficient in data filtering and sorting!