----------------------------------
-- MySQL Reference and Guide
-- ReferenceCollection.com
-- Licensed under CC BY-SA
----------------------------------
-- Table of Contents
--------------------
-- 1. Introduction
-- 2. Basic Syntax
-- 3. Data Types
-- 4. Database Operations
-- 5. Table Operations
-- 6. Constraints
-- 7. Data Manipulation
-- 8. Querying Data
-- 9. Joins
-- 10. Aggregate Functions
-- 11. Subqueries
-- 12. Indexes
-- 13. Stored Procedures
-- 14. Transactions
-- 15. Views
-- 16. Triggers
-- 17. User Management
-- 18. Backup and Restore
-- 19. Locking
-- 20. Replication
-- 21. Partitioning
------------------
-- 1. Introduction
------------------
-- Overview
-----------
-- MySQL is an open-source relational database management system (RDBMS).
-- It uses SQL (Structured Query Language) for database operations.
-- MySQL is widely used for web applications and provides high performance,
-- scalability, and ease of use.
-- History
----------
-- MySQL was originally developed in 1994, It was acquired by Sun Microsystems
-- in 2008, which was then acquired by Oracle Corporation in 2010.
-- MySQL continues to be developed and maintained by Oracle.
-- Architecture
---------------
-- MySQL operates in a client-server architecture. The server manages databases
-- by handling actions such as creating, reading, updating, and deleting data.
-- While the client allows users to interact with the server using SQL commands.
-- Components
-------------
-- Databases: Each database is a collection of related tables.
-- Tables: Structures within a database that hold data in rows and columns.
-- Rows: Individual records in a table.
-- Columns: Fields within a table that define the type of data stored.
-- SQL Commands
---------------
-- SQL commands are divided into several categories:
-- Data Definition Language (DDL): CREATE, ALTER, DROP, TRUNCATE
-- Data Manipulation Language (DML): SELECT, INSERT, UPDATE, DELETE
-- Data Control Language (DCL): GRANT, REVOKE
-- Transaction Control Language (TCL): COMMIT, ROLLBACK, SAVEPOINT
------------------
-- 2. Basic Syntax
------------------
-- Comments
-----------
-- There are two types of comments: single-line and multi-line.
-- Single-line comment
# Another single-line comment
/* Multi-line comment */
-- Semicolons
-------------
-- Each SQL statement should end with a semicolon.
SELECT 'Hello, MySQL';
-- Case Sensitivity
-------------------
-- SQL keywords, column names are case-insensitive
-- Database and table names are case-sensitive
CREATE DATABASE ExampleDB;
CREATE DATABASE exampledb; -- This will create a different database
----------------
-- 3. Data Types
----------------
-- MySQL supports various data types.
-- Numeric Types
INT -- Whole numbers
DECIMAL(M,D) -- Exact numeric decimal values
FLOAT -- Approximate numeric values
-- String Types
CHAR(M) -- Fixed-length strings
VARCHAR(M) -- Variable-length strings
TEXT -- Large amount of text data
-- Date and Time Types
DATE -- Date values (YYYY-MM-DD)
TIME -- Time values (HH:MM:SS)
DATETIME -- Date and time combination
-- Other Types
BOOLEAN -- True or false values
ENUM -- String object with a value chosen from a list
JSON -- Stores and enables efficient access to JSON
-------------------------
-- 4. Database Operations
-------------------------
-- In MySQL, data is organized into databases, which contain tables.
-- Listing databases
SHOW DATABASES;
-- Creating a database
CREATE DATABASE IF NOT EXISTS my_database;
-- Dropping a database
DROP DATABASE IF EXISTS my_database;
-- Selecting a database
USE my_database;
----------------------
-- 5. Table Operations
----------------------
-- Tables are composed of columns (fields) and rows (records).
-- Syntax: <column> <datatype> <constraints>
-- Creating a table
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(50),
price DECIMAL(8, 2) NOT NULL,
stock_quantity INT,
created_at DATE
);
-- Altering a table (add, modify, drop)
ALTER TABLE products ADD COLUMN discount DECIMAL(5, 2);
ALTER TABLE products MODIFY COLUMN discount DECIMAL(6, 2);
ALTER TABLE products DROP COLUMN discount;
-- Dropping a table
DROP TABLE products;
-----------------
-- 6. Constraints
-----------------
-- MySQL constraints are used to specify rules for the data in a table.
-- They ensure the integrity of data in the database.
--
-- Common MySQL constraints:
-- * AUTO_INCREMENT: Generates a unique sequential value for a column.
-- * PRIMARY KEY: Uniquely identifies each row in a table.
-- * FOREIGN KEY: Establishes a link between two tables.
-- * UNIQUE: Ensures all values in a column are distinct.
-- * NOT NULL: Ensures a column cannot have NULL values.
-- * CHECK: Ensures that all column values satisfy a specific condition.
-- * DEFAULT: Provides a default value, if no value is specified.
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT, -- PRIMARY KEY constraint
product_id INT UNIQUE, -- UNIQUE constraint
customer_name VARCHAR(100) NOT NULL, -- NOT NULL constraint
order_date DATE NOT NULL, -- NOT NULL constraint
quantity INT CHECK (quantity >= 1), -- CHECK constraint
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- DEFAULT constraint
FOREIGN KEY (product_id) REFERENCES products(id) -- FOREIGN KEY constraint
);
-- Adding a CHECK constraint
ALTER TABLE orders ADD CONSTRAINT chk_valid_order CHECK (order_date <= CURRENT_DATE);
-----------------------
-- 7. Data Manipulation
-----------------------
-- Inserting/Updating data in your tables.
-- You can insert either a single row or multiple rows
INSERT INTO products (product_name, category, price, stock_quantity, created_at)
VALUES
('Laptop', 'Electronics', 999.99, 50, '2023-01-15'),
('Smartphone', 'Electronics', 799.99, 150, '2023-02-01'),
('Desk Chair', 'Furniture', 199.99, 75, '2023-03-10');
-- Updating the price of a specific product based on its ID.
UPDATE products SET price = 879.99 WHERE id = 2;
-- Deleting a specific product from the table based on its ID.
DELETE FROM products WHERE id = 3;
-------------------
-- 8. Querying Data
-------------------
-- Retrieving data from your tables.
-- Retrieve all columns for each product in table
SELECT * FROM products;
-- Retrieve only specific columns for each product in table
SELECT product_name, price, stock_quantity FROM products;
-- Retrieve all products with a price greater than 500
SELECT * FROM products WHERE price > 500;
-- Results can be ordered: ascending (ASC) or descending (DESC)
SELECT * FROM products ORDER BY created_at DESC;
-- Retrieve the first 10 products with their names and prices
SELECT product_name, price FROM products LIMIT 10;
-----------
-- 9. Joins
-----------
-- Joins are crucial for querying data spread across multiple tables.
-- They are used to combine rows from two or more tables based
-- on a related column between them.
--
-- Main types of MySQL joins:
-- * INNER JOIN: Returns rows with matching values in both tables.
-- * LEFT JOIN : Returns all rows from the left table, and matching
-- rows from the right table. unmatched right rows are NULL.
-- * RIGHT JOIN: Returns all rows from the right table, and matching
-- rows from the left table. unmatched left rows are NULL.
-- * CROSS JOIN: Returns the Cartesian product of two tables, showing all
-- possible row combinations.
-- * SELF JOIN : Joins a table with itself to compare same table rows.
-- INNER JOIN: Retrieve the order details and product name for all orders
SELECT orders.order_id, products.product_name, orders.customer_name, orders.quantity, orders.order_date
FROM orders
INNER JOIN products ON orders.product_id = products.id;
-- LEFT JOIN: Retrieve all products with their order (if available, else NULL)
SELECT products.product_name, orders.order_id, orders.customer_name, orders.quantity, orders.order_date
FROM products
LEFT JOIN orders ON products.id = orders.product_id;
-- RIGHT JOIN: Retrieve all orders with their product (if available, else NULL)
SELECT products.product_name, orders.order_id, orders.customer_name, orders.quantity, orders.order_date
FROM products
RIGHT JOIN orders ON products.id = orders.product_id;
--------------------------
-- 10. Aggregate Functions
--------------------------
-- Used to perform calculations on a set of values and return a single value.
-- Often used with the GROUP BY clause to group rows that have the same values.
--
-- Some common MySQL aggregate functions:
-- * COUNT(): Returns the number of rows in a set.
-- * SUM(): Returns the sum of a numeric column.
-- * AVG(): Returns the average value of a numeric column.
-- * MIN(): Returns the minimum value of a column.
-- * MAX(): Returns the maximum value of a column.
-- Get the total number of orders
SELECT COUNT(*) AS total_orders FROM orders;
-- Get the total amount of all orders
SELECT SUM(amount) AS total_amount FROM orders;
-- Get the average order amount
SELECT AVG(amount) AS average_amount FROM orders;
-- Get the number of orders per customer
SELECT customer_id, COUNT(*) AS orders_per_customer
FROM orders
GROUP BY customer_id;
-- Get the total amount spent by each customer
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;
-----------------
-- 11. Subqueries
-----------------
-- A Subquery is a query nested inside another query. They are useful for
-- breaking down complex queries and can be used in SELECT, WHERE, HAVING,
-- FROM, and EXISTS clauses.
--
-- Types of Subqueries:
-- * Scalar: Returns a single value.
-- * Row: Returns a single row with multiple columns.
-- * Table: Returns multiple rows and columns.
-- * Correlated: References columns from the outer query.
-- Scalar: Retrieves the name of the most expensive product.
SELECT product_name
FROM products
WHERE price = (
SELECT MAX(price)
FROM products
);
-- Row: Retrieve products with the highest price in each category.
SELECT id, product_name, price
FROM products
WHERE (category, price) = (
SELECT category, MAX(price)
FROM products
GROUP BY category
);
-- Table: Retrieves products that have been ordered more than 10 times.
SELECT product_name
FROM products
WHERE id IN (
SELECT product_id
FROM Orders
GROUP BY product_id
HAVING SUM(quantity) > 10
);
-- Correlated: Products with orders in the Last 30 Days
SELECT p.product_name
FROM products p
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.product_id = p.id
AND o.order_date > CURDATE() - INTERVAL 30 DAY
);
--------------
-- 12. Indexes
--------------
-- Improve query performance by speeding up data retrieval. But may slow
-- down data modification operations such as INSERT, UPDATE, and DELETE.
--
-- Some common MySQL indexes:
-- * INDEX: Speeds up retrieval but doesn't enforce uniqueness.
-- * PRIMARY KEY: Uniquely identifies each row and creates a unique index.
-- * UNIQUE: Ensures distinct values and creates a unique index.
-- * FULLTEXT: For full-text searches.
-- * SPATIAL: For spatial data types.
-- * COMPOSITE: Useful for queries that involve multiple columns.
-- Create an index on the price column.
CREATE INDEX idx_price ON Products (price);
-- Create a full-text index on the description column.
CREATE FULLTEXT INDEX idx_description ON Products (description);
-- Create a composite index on category and price columns.
CREATE INDEX idx_category_price ON Products (category, price);
-- Drop the full-text index on the description column.
DROP INDEX idx_description ON Products;
------------------------
-- 13. Stored Procedures
------------------------
-- A set of SQL statements that can be stored in the database and executed
-- repeatedly. They improve performance by reducing client-server communication.
--
-- Key points:
-- * DELIMITER: Changes the delimiter to allow for multi-line statements.
-- * CREATE PROCEDURE: Used to create a new procedure.
-- * BEGIN and END: Enclose the SQL statements of the procedure.
-- A Procedure that retrieves all orders placed by a specific customer.
DELIMITER $
CREATE PROCEDURE GetCustomerOrders(
-- Procedures can accept IN, OUT and INOUT parameters.
IN p_customer_name VARCHAR(100),
OUT p_total_orders INT
)
BEGIN
-- Select the order details
SELECT order_id, product_id, order_date, quantity FROM Orders
WHERE customer_name = p_customer_name;
-- Count the total number of orders and store it in the OUT parameter.
SELECT COUNT(*) INTO p_total_orders FROM Orders
WHERE customer_name = p_customer_name;
END $
DELIMITER ;
-- Calling the Procedure.
SET @total_orders = 0;
CALL GetCustomerOrders('Customer Name', @total_orders);
SELECT @total_orders AS TotalOrders;
-- A Procedure that inserts a new order into the orders table.
DELIMITER //
CREATE PROCEDURE PlaceOrder(
IN p_product_id INT,
IN p_customer_name VARCHAR(100),
IN p_quantity INT
)
BEGIN
DECLARE stock INT;
-- Check the stock availability.
SELECT stock_quantity INTO stock
FROM products
WHERE id = p_product_id;
IF stock >= p_quantity THEN
-- Insert the order if stock is sufficient.
INSERT INTO Orders (product_id, customer_name, order_date, quantity)
VALUES (p_product_id, p_customer_name, CURDATE(), p_quantity);
-- Update the stock quantity.
UPDATE products
SET stock_quantity = stock_quantity - p_quantity
WHERE id = p_product_id;
ELSE
-- Raise an error if there is insufficient stock.
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock for this product';
END IF;
END //
DELIMITER ;
-- Calling the Procedure.
CALL PlaceOrder(1, 'Customer Name', 2);
-- Dropping Stored Procedures.
DROP PROCEDURE IF EXISTS PlaceOrder;
-------------------
-- 14. Transactions
-------------------
-- Transaction is a sequence of one or more SQL operations that are executed
-- as a single unit. If any statement fails, the entire transaction can be
-- rolled back to maintain data integrity.
--
-- Transactions have the following properties (ACID):
-- * Atomicity: Entire transaction succeeds or fails as a whole.
-- * Consistency: Ensures valid state transitions in the database.
-- * Isolation: Transactions run independently of each other.
-- * Durability: Committed transactions persist, even after failures.
--
-- Basic Transaction Commands:
-- * START TRANSACTION / BEGIN: Begins a new transaction.
-- * COMMIT : Saves all changes made in the transaction.
-- * ROLLBACK : Reverts all changes made in the transaction.
-- * SAVEPOINT: Sets a point to which you can roll back.
-- Start a transaction.
START TRANSACTION;
-- Insert a new product.
INSERT INTO products (product_name, category, price, stock_quantity, created_at)
VALUES ('Laptop', 'Electronics', 999.99, 50, '2023-01-15');
-- If something goes wrong, you would roll back the transaction.
ROLLBACK;
-- Commit the transaction, applying all changes.
COMMIT;
-- Another transaction.
START TRANSACTION;
-- Set a savepoint.
SAVEPOINT savepoint_1;
INSERT INTO products (product_name, category, price, stock_quantity, created_at)
VALUES
('Smartphone', 'Electronics', 799.99, 150, '2023-02-01'),
('Desk Chair', 'Furniture', 199.99, 75, '2023-03-10');
-- If needed, roll back to the savepoint.
ROLLBACK TO SAVEPOINT savepoint_1;
-- if everything is correct, commit the transaction.
COMMIT;
------------
-- 15. Views
------------
-- Views are virtual tables based on the result of an SQL statement.
-- They don't store data and provide a way to simplify complex queries,
-- Improve data security and Ensure a consistent data interface.
-- Create a view to display products that have been ordered.
CREATE VIEW OrderedProducts AS
SELECT
p.id,
p.product_name,
p.category,
COUNT(o.order_id) AS times_ordered
FROM
Products p
JOIN
Orders o ON p.id = o.product_id
GROUP BY
p.id, p.product_name, p.category;
-- Create a view to display orders along with product details.
CREATE VIEW OrderDetails AS
SELECT
o.order_id,
o.customer_name,
o.order_date,
o.quantity,
p.product_name,
p.category,
p.price
FROM
Orders o
JOIN
Products p ON o.product_id = p.id;
-- Selecting the views.
SELECT * FROM OrderedProducts;
SELECT * FROM OrderDetails;
-- Dropping a view.
DROP VIEW OrderDetails;
---------------
-- 16. Triggers
---------------
-- Triggers are database objects that are automatically executed when
-- certain events occurs.
--
-- Types of triggers in MySQL:
-- * BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE,
-- BEFORE DELETE, AFTER DELETE
-- This trigger checks if there is enough stock before placing an order.
DELIMITER //
CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
DECLARE stock INT;
-- Get the stock quantity of the product being ordered.
SELECT stock_quantity INTO stock FROM Products WHERE id = NEW.product_id;
-- Check if there's enough stock.
IF stock < NEW.quantity THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Insufficient stock for this product';
END IF;
END//
DELIMITER ;
-- This trigger adjusts the stock after an order's quantity is updated.
DELIMITER //
CREATE TRIGGER after_order_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
-- Adjust the stock quantity based on the new order quantity.
UPDATE Products
SET stock_quantity = stock_quantity - (NEW.quantity - OLD.quantity)
WHERE id = NEW.product_id;
END//
DELIMITER ;
-- Dropping a trigger.
DROP TRIGGER after_order_update;
----------------------
-- 17. User Management
----------------------
-- User management involves creating, modifying, and deleting user
-- accounts, as well as granting and revoking privileges.
-- Create a new user with a specified password.
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
-- Grant SELECT, INSERT, and UPDATE privileges on the database to the user.
GRANT SELECT, INSERT, UPDATE ON my_database.* TO 'newuser'@'localhost';
-- Grant all privileges on the database to the user, with the ability to grant.
GRANT ALL PRIVILEGES ON my_database.* TO 'newuser'@'localhost' WITH GRANT OPTION;
-- Revoke INSERT privilege on the database from the user.
REVOKE INSERT ON my_database.* FROM 'newuser'@'localhost';
-- Revoke all privileges and the ability to grant privileges from the user.
REVOKE ALL PRIVILEGES, GRANT OPTION ON my_database.* FROM 'newuser'@'localhost';
-- Create a role and grant SELECT privileges on the database to that role.
CREATE ROLE 'view_role';
GRANT SELECT ON my_database.* TO 'view_role';
-- Assign the role to an existing user.
GRANT 'view_role' TO 'readonly_user'@'localhost';
-- Show the privileges granted to the user.
SHOW GRANTS FOR 'newuser'@'localhost';
-- Change the user's password.
ALTER USER 'newuser'@'localhost' IDENTIFIED BY 'newpassword';
-- Delete the user from the database.
DROP USER 'newuser'@'localhost';
-------------------------
-- 18. Backup and Restore
-------------------------
-- Regular backups are crucial for data safety and recovery.
-- Backup the entire database to a SQL file.
mysqldump -u username -p my_database > backup_file.sql;
-- Backup specific tables to a SQL file.
mysqldump -u username -p my_database table1 table2 > backup_file.sql;
-- Restore a database from a SQL file.
mysql -u username -p my_database < backup_file.sql;
-- Point-in-Time Recovery:
-- 1. Enable binary logging (in MySQL config my.cnf).
log-bin=mysql-bin
-- 2. List binary logs:
SHOW BINARY LOGS;
-- 3. Apply binary logs up to a specific timestamp:
mysqlbinlog /path/to/mysql-bin.000001 | mysql -u username -p my_database
--------------
-- 19. Locking
--------------
-- A Mechanisms used to manage concurrent access to database resources.
-- Preventing conflicts when multiple transactions interact with the same data.
--
-- Main types of locks in MySQL:
-- 1. Table-level locks: Lock an entire table.
-- 2. Row-level locks: Lock specific rows within a table.
-- 3. Advisory locks: Custom, application-defined locks for complex scenarios.
--
-- Lock Types:
-- * Shared (Read) Lock: Allows multiple transactions to read the data,
-- but prevents any transaction from modifying it.
-- * Exclusive (Write) Lock: Allows a transaction to modify data
-- but prevents others from reading or modifying it.
-- Table-level locking: READ (or WRITE)
LOCK TABLES Products READ;
-- Perform read operations on the Products table
-- Release the lock
UNLOCK TABLES;
-- Row-level Locking: FOR UPDATE
START TRANSACTION;
-- This will lock the row where user_id = 1
SELECT * FROM Users WHERE user_id = 1 FOR UPDATE;
-- Perform operations on the locked row
UPDATE Users SET age = 30 WHERE user_id = 1;
-- Commit the transaction, releasing the lock
COMMIT;
-- Row-level Locking: SHARE MODE
START TRANSACTION;
-- Allows reading but prevents other transactions from writing to these rows.
SELECT * FROM Products WHERE category = 'Electronics' LOCK IN SHARE MODE;
-- Releases the shared lock
COMMIT;
------------------
-- 20. Replication
------------------
-- Is a process that allows you to automatically copy data from one
-- database server (the master) to one or more database servers (the slaves).
--
-- Key concepts:
-- * Master: Source database server with original data.
-- * Slave: Server receiving data copies from the master.
-- * Binary Log: File recording all changes on the master.
-- * Relay Log: File on the slave with data from the master’s binary log.
--
-- Benefits :
-- High availability by allowing a slave to be promoted to master in case of
-- failure, enables load balancing by distributing read queries across slaves,
-- facilitates backups on a slave, and allows intensive data analysis
-- MASTER:
-- Step 1: Configure the Master Server (my.cnf or my.ini).
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = my_database -- specify database to replicate
-- Create a Replication User on the Master.
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;
-- Lock the tables to prevent changes during the setup.
FLUSH TABLES WITH READ LOCK; -- Lock all tables
-- Get Master Status.
SHOW MASTER STATUS;
UNLOCK TABLES;
-- SLAVE:
-- Configure the Slave Server (my.cnf or my.ini)
[mysqld]
server-id = 2
relay-log = mysql-relay-bin
-- Set Up the Slave.
CHANGE MASTER TO
MASTER_HOST='master_ip_address',
MASTER_USER='repl_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001', -- File value from 'Step 3'
MASTER_LOG_POS=123; -- Position value from 'Step 3'
-- Start the Slave.
START SLAVE;
-- Verify Replication Status.
SHOW SLAVE STATUS;
-- Stop replication.
STOP SLAVE;
-- Reset replication.
RESET SLAVE;
-------------------
-- 21. Partitioning
-------------------
-- Is a database feature that allows you to divide a large table into smaller,
-- more manageable pieces, yet still treat them as a single table.
--
-- Some common types of MySQL partitioning:
-- * RANGE: Divides the table based on a range of values.
-- * LIST: Divides the table based on a list of values.
-- * HASH: Distributes rows across a predefined number of partitions.
-- * KEY: Similar to HASH but uses MySQL’s internal function.
-- * COMPOSITE: Uses a combination of multiple partitioning methods.
-- RANGE Partitioning
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(50),
price DECIMAL(8, 2) NOT NULL,
stock_quantity INT,
created_at DATE
)
PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p0 VALUES LESS THAN (2010),
PARTITION p1 VALUES LESS THAN (2015),
PARTITION p2 VALUES LESS THAN (2020),
PARTITION p3 VALUES LESS THAN (MAXVALUE) -- Handles all dates from 2020 onwards
);
-- Query data from a specific partition
SELECT * FROM products PARTITION (p1);
-- Add a new partition
ALTER TABLE products ADD PARTITION (PARTITION p5 VALUES LESS THAN (2025));
-- Remove a partition
ALTER TABLE products DROP PARTITION p0;
-- LIST Partitioning
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(50),
price DECIMAL(8, 2) NOT NULL,
stock_quantity INT,
created_at DATE
)
PARTITION BY LIST (category) (
PARTITION p_electronics VALUES IN ('Electronics'),
PARTITION p_accessories VALUES IN ('Accessories'),
PARTITION p_furniture VALUES IN ('Furniture'),
PARTITION p_other VALUES IN ('Other') -- Handles all other categories
);