ADS_3_PARTIONING

Mysql
use db;
CREATE TABLE employees (
    emp_id INT,
    emp_name VARCHAR(20),
    age INT,
    city CHAR(25),
    salary DECIMAL(18,2)
) PARTITION BY RANGE (age) (
    PARTITION emp_p1 VALUES LESS THAN (20),
    PARTITION emp_p2 VALUES LESS THAN (30),
    PARTITION emp_p3 VALUES LESS THAN (40),
    PARTITION emp_p4 VALUES LESS THAN (50),
    PARTITION emp_p5 VALUES LESS THAN (MAXVALUE)  -- Catch-all partition
);

drop table employees

INSERT INTO employees VALUES
(1, 'John', 19, 'New York', 1200.00),
(2, 'David', 49, 'London', 3200.00),
(3, 'Alice', 25, 'Paris', 4500.00),
(4, 'Emma', 35, 'Berlin', 2100.00),
(5, 'Bob', 15, 'San Francisco', 1500.00),  -- Will go to emp_p1
(6, 'Charlie', 29, 'Boston', 2800.00);     -- Will go to emp_p2

-- Select all records from the employees table
SELECT * FROM employees;

SELECT partition_name, table_rows
FROM information_schema.partitions
WHERE table_name = 'employees';
-- Select all records from partition emp_p1
SELECT * FROM employees PARTITION (emp_p1);

--VERTICAL PARTIONING
CREATE TABLE INVENTORY (
    id INT,
    product_name VARCHAR(50),
    product_quantity INT,
    product_price INT
) PARTITION BY RANGE COLUMNS(product_quantity, product_price) (
    PARTITION P_low_stock VALUES LESS THAN (10, 100),
    PARTITION P_medium_stock VALUES LESS THAN (50, 500),
    PARTITION P_high_stock VALUES LESS THAN (200, 1200)
);
INSERT INTO INVENTORY VALUES
(1, 'Headphones', 5, 50),
(2, 'Mouse', 15, 200),
(3, 'Monitor', 30, 300),
(4, 'Keyboard', 60, 600),
(5, 'CPU', 100, 1000);
SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME='INVENTORY';
SELECT * FROM INVENTORY PARTITION (P_high_stock);