🧱 Employee Table (for reference)

CREATE TABLE Employee (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC(10,2),
    hire_date DATE,
    manager_id INT
);

INSERT INTO Employee (first_name, last_name, department, salary, hire_date, manager_id) VALUES
('Abhishek', 'Raut', 'Engineering', 90000, '2021-06-10', NULL),
('Riya', 'Patel', 'HR', 60000, '2022-01-15', 1),
('Arjun', 'Mehta', 'Engineering', 75000, '2020-09-05', 1),
('Sneha', 'Sharma', 'Marketing', 50000, '2023-03-20', 2),
('Raj', 'Singh', 'Engineering', 95000, '2019-12-01', NULL),
('Neha', 'Khan', 'Finance', 80000, '2020-04-12', 3);

🧠 Filtering & Operators in PostgreSQL

🔹 1️⃣ BETWEEN — Range Filtering

-- Employees whose salary is between 60k and 90k
SELECT first_name, salary
FROM Employee
WHERE salary BETWEEN 60000 AND 90000;

-- Employees hired between 2020 and 2022
SELECT first_name, hire_date
FROM Employee
WHERE hire_date BETWEEN '2020-01-01' AND '2022-12-31';

🧩 Note: BETWEEN is inclusive — it includes both boundary values.

🔹 2️⃣ IN — Match Against Multiple Values

-- Employees in Engineering or HR department
SELECT first_name, department
FROM Employee
WHERE department IN ('Engineering', 'HR');

-- Employees NOT in Finance or Marketing
SELECT first_name, department
FROM Employee
WHERE department NOT IN ('Finance', 'Marketing');

🧠 Tip:

IN = shorthand for multiple ORs

👉 department IN ('Engineering', 'HR')

is same as

👉 department = 'Engineering' OR department = 'HR'

🔹 3️⃣ LIKE & ILIKE — Pattern Matching

-- Employees whose first name starts with 'A'
SELECT first_name
FROM Employee
WHERE first_name LIKE 'A%';

-- Employees whose last name ends with 'a'
SELECT first_name, last_name
FROM Employee
WHERE last_name LIKE '%a';

-- Employees whose name contains 'ha'
SELECT first_name
FROM Employee
WHERE first_name LIKE '%ha%';

-- Case-insensitive match (PostgreSQL only)
SELECT first_name
FROM Employee
WHERE first_name ILIKE 'a%';

🧠 LIKE Wildcards: