CREATE TABLE employees (
   employee_id INT,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   department VARCHAR(50),
   salary DECIMAL(10, 2)
);

CREATE TABLE employees_partition (
   employee_id INT,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   department VARCHAR(50),
   salary DECIMAL(10, 2)
)
PARTITION BY 
   (CASE 
       WHEN department = 'IT' THEN 'IT' 
       WHEN department = 'HR' THEN 'HR' 
       WHEN department = 'Sales' THEN 'Sales' 
       ELSE 'Other' 
   END);

INSERT INTO employees (employee_id, first_name, last_name, department, salary)
VALUES 
   (1, 'John', 'Doe', 'IT', 5000),
   (2, 'Jane', 'Smith', 'HR', 4000),
   (3, 'Mike', 'Johnson', 'IT', 4500),
   (4, 'Lisa', 'Davis', 'Sales', 5500),
   (5, 'David', 'Brown', 'Finance', 6000);

INSERT INTO employees_partition
SELECT * FROM employees;

SELECT * FROM employees_partition
WHERE
   (CASE 
       WHEN department = 'IT' THEN 'IT' 
       WHEN department = 'HR' THEN 'HR' 
       WHEN department = 'Sales' THEN 'Sales' 
       ELSE 'Other' 
   END) = 'Sales';

ALTER TABLE employees_partition
ADD PARTITION (PARTITION p2 VALUES IN ('Finance'));