MySQL Interview Questions with Answers

-- Hello World in MySQL
SELECT 'Hello, World!' AS greeting;

-- Variables in MySQL
-- User-defined variables
SET @mutableVar = 'Hello';
SET @immutableVar = 'World';

-- System variables
SELECT @@version;
SELECT @@autocommit;

-- Local variables in stored procedures
DELIMITER //
CREATE PROCEDURE demoVariables()
BEGIN
    DECLARE localVar VARCHAR(20) DEFAULT 'Local';
    SELECT localVar;
END//
DELIMITER ;

-- Display
SELECT @mutableVar;
SELECT @immutableVar;

-- Data Types in MySQL
-- Numeric types
CREATE TABLE data_types (
    int_col INT,
    tinyint_col TINYINT,
    smallint_col SMALLINT,
    mediumint_col MEDIUMINT,
    bigint_col BIGINT,
    decimal_col DECIMAL(10,2),
    float_col FLOAT,
    double_col DOUBLE,
    
    -- String types
    char_col CHAR(10),
    varchar_col VARCHAR(100),
    text_col TEXT,
    blob_col BLOB,
    
    -- Date and time
    date_col DATE,
    datetime_col DATETIME,
    timestamp_col TIMESTAMP,
    time_col TIME,
    year_col YEAR,
    
    -- Boolean (TINYINT)
    is_active BOOLEAN,
    
    -- JSON
    json_col JSON,
    
    -- Enum
    status ENUM('active', 'inactive', 'pending')
);

-- Type checking
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'data_types';

-- Functions in MySQL
-- Basic function
DELIMITER //
CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN a + b;
END//
DELIMITER ;

-- Function with default parameters
DELIMITER //
CREATE FUNCTION greet(name VARCHAR(100))
RETURNS VARCHAR(200)
DETERMINISTIC
BEGIN
    IF name IS NULL THEN
        SET name = 'Guest';
    END IF;
    RETURN CONCAT('Hello, ', name, '!');
END//
DELIMITER ;

-- Function with multiple statements
DELIMITER //
CREATE FUNCTION divide_numbers(a INT, b INT)
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    DECLARE result VARCHAR(100);
    IF b = 0 THEN
        SET result = 'Division by zero';
    ELSE
        SET result = CONCAT('Quotient: ', a DIV b, ', Remainder: ', a MOD b);
    END IF;
    RETURN result;
END//
DELIMITER ;

-- Usage
SELECT add_numbers(5, 3);
SELECT greet('Alice');
SELECT divide_numbers(10, 3);

-- Arrays in MySQL
-- MySQL doesn't have arrays, but we can use JSON or temporary tables

-- Using JSON arrays
CREATE TABLE json_demo (
    id INT PRIMARY KEY,
    numbers JSON
);

INSERT INTO json_demo VALUES (1, '[1, 2, 3, 4, 5]');
INSERT INTO json_demo VALUES (2, '[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]');

-- Access array elements
SELECT 
    id,
    JSON_EXTRACT(numbers, '$[2]') AS third_element,
    JSON_LENGTH(numbers) AS array_length
FROM json_demo;

-- Using temporary table as array
CREATE TEMPORARY TABLE temp_array (
    id INT AUTO_INCREMENT PRIMARY KEY,
    value INT
);

INSERT INTO temp_array (value) VALUES (1), (2), (3), (4), (5);

-- Iterate through array
SELECT value FROM temp_array ORDER BY id;

-- JSON array functions
SELECT 
    JSON_ARRAY(1, 2, 3, 4, 5) AS numbers,
    JSON_ARRAY_APPEND('[1,2,3]', '$', 4) AS appended,
    JSON_ARRAY_INSERT('[1,2,3]', '$[1]', 99) AS inserted,
    JSON_REMOVE('[1,2,3,4]', '$[2]') AS removed;

-- Collections in MySQL
-- Tables as collections
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    age INT,
    city VARCHAR(100)
);

-- Insert documents (rows)
INSERT INTO users (name, age, city) VALUES 
    ('Alice', 25, 'NYC'),
    ('Bob', 30, 'LA'),
    ('Charlie', 35, 'Chicago');

-- Query all
SELECT * FROM users;

-- Query with filter
SELECT * FROM users WHERE age > 25;

-- Update
UPDATE users SET age = 26 WHERE name = 'Alice';

-- Delete
DELETE FROM users WHERE name = 'Bob';

-- Count
SELECT COUNT(*) FROM users WHERE age > 25;

-- JSON collections
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    items JSON,
    total DECIMAL(10,2)
);

INSERT INTO orders (customer_id, items, total) VALUES 
    (1, '[{"product": "A", "qty": 2}, {"product": "B", "qty": 1}]', 30.00);

SELECT 
    id,
    JSON_EXTRACT(items, '$[0].product') AS first_product
FROM orders;

-- Data Classes (Tables as Objects)
-- Creating a table as a data class
CREATE TABLE person (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT CHECK (age >= 0),
    city VARCHAR(100) DEFAULT 'Unknown',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert data
INSERT INTO person (name, age, city) VALUES 
    ('Alice', 25, 'NYC'),
    ('Bob', 30, 'LA');

-- Query
SELECT * FROM person WHERE id = 1;

-- Update
UPDATE person SET age = 26 WHERE id = 1;

-- Delete
DELETE FROM person WHERE id = 2;

-- Copy
CREATE TABLE person_backup AS SELECT * FROM person;

-- Struct-like views
CREATE VIEW person_view AS 
SELECT 
    id,
    name,
    age,
    city,
    CONCAT(name, ' (', age, ')') AS display_name
FROM person;

-- Schema Validation in MySQL
-- MySQL 8.0+ supports JSON schema validation

-- Create table with JSON validation
CREATE TABLE validated_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    age INT CHECK (age >= 0 AND age <= 150),
    status ENUM('active', 'inactive', 'pending') DEFAULT 'pending',
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Unique constraint
    CONSTRAINT unique_email UNIQUE (email)
);

-- Check constraints
ALTER TABLE validated_users 
ADD CONSTRAINT check_age_positive 
CHECK (age >= 0);

-- JSON schema validation (MySQL 8.0+)
ALTER TABLE validated_users
ADD CONSTRAINT valid_metadata
CHECK (JSON_SCHEMA_VALID(
    '{
        "type": "object",
        "properties": {
            "department": {"type": "string"},
            "role": {"type": "string"}
        },
        "required": ["department"]
    }',
    metadata
));

-- Test validation
INSERT INTO validated_users (name, email, age, metadata) 
VALUES ('Alice', 'alice@example.com', 25, '{"department": "IT"}');

-- This will fail validation
INSERT INTO validated_users (name, email, age, metadata) 
VALUES ('Bob', 'bob@example.com', 30, '{"role": "admin"}');

-- Null Safety in MySQL
-- Handling NULL values

-- NULL vs NOT NULL
CREATE TABLE null_demo (
    id INT PRIMARY KEY,
    nullable_field VARCHAR(100) NULL,
    non_nullable_field VARCHAR(100) NOT NULL
);

-- Insert NULL
INSERT INTO null_demo (id, nullable_field, non_nullable_field) 
VALUES (1, NULL, 'value');

-- Query NULL
SELECT * FROM null_demo WHERE nullable_field IS NULL;
SELECT * FROM null_demo WHERE nullable_field IS NOT NULL;

-- COALESCE - first non-NULL
SELECT 
    COALESCE(nullable_field, 'default') AS with_default
FROM null_demo;

-- IFNULL - MySQL specific
SELECT IFNULL(nullable_field, 'default') AS with_default FROM null_demo;

-- NULLIF - returns NULL if equal
SELECT NULLIF(1, 1); -- Returns NULL
SELECT NULLIF(1, 2); -- Returns 1

-- ISNULL function
SELECT ISNULL(nullable_field) FROM null_demo;

-- Handling NULL in aggregates
SELECT 
    COUNT(*),          -- Counts all rows
    COUNT(column),     -- Counts non-NULL values
    AVG(column),       -- Ignores NULL values
    SUM(column)        -- Ignores NULL values
FROM null_demo;

-- Using DEFAULT
INSERT INTO null_demo (id, nullable_field) 
VALUES (2, DEFAULT(non_nullable_field));

-- Control Flow in MySQL
-- IF statement in stored procedures
DELIMITER //
CREATE PROCEDURE check_age(IN age INT)
BEGIN
    IF age < 18 THEN
        SELECT 'Minor' AS status;
    ELSE
        SELECT 'Adult' AS status;
    END IF;
END//
DELIMITER ;

-- IF function (in SELECT)
SELECT 
    name,
    age,
    IF(age < 18, 'Minor', 'Adult') AS status
FROM users;

-- CASE expression
SELECT 
    name,
    age,
    CASE 
        WHEN age < 18 THEN 'Minor'
        WHEN age < 65 THEN 'Adult'
        ELSE 'Senior'
    END AS category
FROM users;

-- CASE with simple values
SELECT 
    name,
    status,
    CASE status
        WHEN 'active' THEN 'Active'
        WHEN 'inactive' THEN 'Inactive'
        ELSE 'Unknown'
    END AS status_description
FROM users;

-- WHILE loop
DELIMITER //
CREATE PROCEDURE while_demo()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 5 DO
        SELECT i;
        SET i = i + 1;
    END WHILE;
END//
DELIMITER ;

-- REPEAT loop
DELIMITER //
CREATE PROCEDURE repeat_demo()
BEGIN
    DECLARE i INT DEFAULT 1;
    REPEAT
        SELECT i;
        SET i = i + 1;
    UNTIL i > 5
    END REPEAT;
END//
DELIMITER ;

-- LOOP with LEAVE
DELIMITER //
CREATE PROCEDURE loop_demo()
BEGIN
    DECLARE i INT DEFAULT 1;
    loop_label: LOOP
        SELECT i;
        SET i = i + 1;
        IF i > 5 THEN
            LEAVE loop_label;
        END IF;
    END LOOP;
END//
DELIMITER ;

-- Inheritance in MySQL
-- MySQL doesn't support inheritance, but patterns exist

-- Single Table Inheritance
CREATE TABLE animals (
    id INT PRIMARY KEY AUTO_INCREMENT,
    type VARCHAR(50),
    name VARCHAR(100),
    sound VARCHAR(100),
    breed VARCHAR(100),  -- For dogs
    color VARCHAR(50)    -- For cats
);

INSERT INTO animals (type, name, sound, breed) VALUES 
    ('Dog', 'Rex', 'Woof!', 'German Shepherd');

INSERT INTO animals (type, name, sound, color) VALUES 
    ('Cat', 'Whiskers', 'Meow!', 'Black');

-- Class Table Inheritance
CREATE TABLE persons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(255)
);

CREATE TABLE employees (
    person_id INT PRIMARY KEY,
    employee_id VARCHAR(50),
    department VARCHAR(100),
    FOREIGN KEY (person_id) REFERENCES persons(id)
);

CREATE TABLE customers (
    person_id INT PRIMARY KEY,
    customer_id VARCHAR(50),
    loyalty_points INT,
    FOREIGN KEY (person_id) REFERENCES persons(id)
);

-- Concrete Table Inheritance
CREATE TABLE employees_detail (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    employee_id VARCHAR(50),
    department VARCHAR(100)
);

CREATE TABLE customers_detail (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    customer_id VARCHAR(50),
    loyalty_points INT
);

-- Polymorphic associations
CREATE TABLE comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    content TEXT,
    commentable_type VARCHAR(50),
    commentable_id INT
);

-- Properties (Columns) in MySQL
-- Table with various column properties
CREATE TABLE products (
    -- Primary key
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- NOT NULL constraint
    name VARCHAR(200) NOT NULL,
    
    -- UNIQUE constraint
    sku VARCHAR(50) UNIQUE NOT NULL,
    
    -- Default value
    price DECIMAL(10,2) DEFAULT 0.00,
    
    -- Check constraint
    quantity INT CHECK (quantity >= 0),
    
    -- Foreign key
    category_id INT,
    
    -- Timestamps with automatic updates
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Index
    INDEX idx_name (name),
    
    -- Composite index
    INDEX idx_category_price (category_id, price),
    
    -- Full-text index
    FULLTEXT INDEX ft_description (description),
    
    -- Foreign key constraint
    FOREIGN KEY (category_id) REFERENCES categories(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- Computed columns (generated columns)
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

-- Virtual generated column
CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    amount DECIMAL(10,2) NOT NULL,
    tax_rate DECIMAL(5,2) NOT NULL,
    tax_amount DECIMAL(10,2) GENERATED ALWAYS AS (amount * tax_rate / 100) VIRTUAL
);

-- Stored Procedures in MySQL
-- Basic stored procedure
DELIMITER //
CREATE PROCEDURE get_users(IN city_param VARCHAR(100))
BEGIN
    SELECT * FROM users WHERE city = city_param;
END//
DELIMITER ;

-- Stored procedure with multiple parameters
DELIMITER //
CREATE PROCEDURE get_users_by_age(
    IN min_age INT,
    IN max_age INT
)
BEGIN
    SELECT * FROM users WHERE age BETWEEN min_age AND max_age;
END//
DELIMITER ;

-- Stored procedure with OUT parameters
DELIMITER //
CREATE PROCEDURE get_user_count(
    IN city_param VARCHAR(100),
    OUT user_count INT
)
BEGIN
    SELECT COUNT(*) INTO user_count 
    FROM users 
    WHERE city = city_param;
END//
DELIMITER ;

-- Stored procedure with INOUT parameters
DELIMITER //
CREATE PROCEDURE increment_age(
    INOUT age_param INT,
    IN increment INT
)
BEGIN
    SET age_param = age_param + increment;
END//
DELIMITER ;

-- Stored procedure with transaction
DELIMITER //
CREATE PROCEDURE transfer_funds(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Transaction failed' AS message;
    END;
    
    START TRANSACTION;
    
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    
    COMMIT;
    SELECT 'Transaction successful' AS message;
END//
DELIMITER ;

-- Call procedures
CALL get_users('NYC');
CALL get_users_by_age(18, 30);
CALL get_user_count('NYC', @count);
SELECT @count;

-- Exception Handling in MySQL
-- MySQL 5.5+: DECLARE ... HANDLER

-- Basic exception handling
DELIMITER //
CREATE PROCEDURE safe_divide(
    IN numerator INT,
    IN denominator INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'Error occurred' AS result;
    END;
    
    SELECT numerator / denominator AS result;
END//
DELIMITER ;

-- Specific error handling
DELIMITER //
CREATE PROCEDURE safe_insert(
    IN name VARCHAR(100),
    IN email VARCHAR(255)
)
BEGIN
    DECLARE EXIT HANDLER FOR 1062  -- Duplicate entry error
    BEGIN
        SELECT 'Email already exists' AS error;
    END;
    
    INSERT INTO users (name, email) VALUES (name, email);
    SELECT 'User inserted successfully' AS result;
END//
DELIMITER ;

-- Custom error messages
DELIMITER //
CREATE PROCEDURE validate_age(
    IN age INT
)
BEGIN
    IF age < 0 OR age > 150 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Invalid age: Age must be between 0 and 150';
    END IF;
    
    SELECT 'Age is valid' AS result;
END//
DELIMITER ;

-- Using GET DIAGNOSTICS (MySQL 5.6+)
DELIMITER //
CREATE PROCEDURE get_error_info()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
            @sqlstate = RETURNED_SQLSTATE,
            @errno = MYSQL_ERRNO,
            @text = MESSAGE_TEXT;
        SELECT @sqlstate, @errno, @text;
    END;
    
    -- This will cause an error
    INSERT INTO nonexistent_table VALUES (1);
END//
DELIMITER ;

-- Functions in MySQL (UDF)
-- User-defined functions

-- Simple function
DELIMITER //
CREATE FUNCTION square(x INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN x * x;
END//
DELIMITER ;

-- Function with multiple parameters
DELIMITER //
CREATE FUNCTION multiply(a INT, b INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN a * b;
END//
DELIMITER ;

-- Function with default value
DELIMITER //
CREATE FUNCTION greet_user(name VARCHAR(100))
RETURNS VARCHAR(200)
DETERMINISTIC
BEGIN
    IF name IS NULL THEN
        RETURN 'Hello, Guest!';
    END IF;
    RETURN CONCAT('Hello, ', name, '!');
END//
DELIMITER ;

-- Function with validation
DELIMITER //
CREATE FUNCTION calculate_bonus(
    salary DECIMAL(10,2),
    performance_rating INT
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE bonus DECIMAL(10,2);
    
    CASE performance_rating
        WHEN 5 THEN SET bonus = salary * 0.20;
        WHEN 4 THEN SET bonus = salary * 0.15;
        WHEN 3 THEN SET bonus = salary * 0.10;
        WHEN 2 THEN SET bonus = salary * 0.05;
        ELSE SET bonus = 0;
    END CASE;
    
    RETURN bonus;
END//
DELIMITER ;

-- Usage
SELECT 
    square(5),
    multiply(3, 4),
    greet_user('Alice'),
    calculate_bonus(50000, 4);

-- Window Functions in MySQL (8.0+)
-- Window functions for advanced analytics

-- ROW_NUMBER
SELECT 
    name,
    age,
    city,
    ROW_NUMBER() OVER (PARTITION BY city ORDER BY age) AS row_num
FROM users;

-- RANK and DENSE_RANK
SELECT 
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank_position,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_position
FROM employees;

-- LAG and LEAD
SELECT 
    date,
    amount,
    LAG(amount, 1) OVER (ORDER BY date) AS previous_amount,
    LEAD(amount, 1) OVER (ORDER BY date) AS next_amount
FROM sales;

-- NTILE
SELECT 
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

-- SUM with window
SELECT 
    name,
    salary,
    SUM(salary) OVER (ORDER BY salary) AS cumulative_salary
FROM employees;

-- Moving average
SELECT 
    date,
    amount,
    AVG(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM sales;

-- FIRST_VALUE and LAST_VALUE
SELECT 
    name,
    department,
    salary,
    FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_paid,
    LAST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_paid
FROM employees;

-- Common Table Expressions (CTE) in MySQL
-- CTE for complex queries

-- Simple CTE
WITH user_stats AS (
    SELECT 
        city,
        COUNT(*) AS user_count,
        AVG(age) AS avg_age
    FROM users
    GROUP BY city
)
SELECT * FROM user_stats WHERE user_count > 5;

-- Recursive CTE (MySQL 8.0+)
WITH RECURSIVE numbers (n) AS (
    SELECT 1  -- Anchor
    UNION ALL
    SELECT n + 1  -- Recursive
    FROM numbers
    WHERE n < 10
)
SELECT * FROM numbers;

-- Recursive CTE for hierarchical data
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);

INSERT INTO employees VALUES 
    (1, 'CEO', NULL),
    (2, 'VP', 1),
    (3, 'Manager', 2),
    (4, 'Developer', 3),
    (5, 'Developer', 3);

WITH RECURSIVE org_chart AS (
    SELECT 
        id,
        name,
        manager_id,
        0 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        oc.level + 1
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT 
    CONCAT(REPEAT('  ', level), name) AS org_chart
FROM org_chart
ORDER BY level, id;

-- Multiple CTEs
WITH 
stats AS (
    SELECT 
        city,
        COUNT(*) AS count,
        AVG(age) AS avg_age
    FROM users
    GROUP BY city
),
ranked AS (
    SELECT 
        city,
        count,
        avg_age,
        RANK() OVER (ORDER BY count DESC) AS rank_position
    FROM stats
)
SELECT * FROM ranked WHERE rank_position <= 5;

-- Views in MySQL
-- Views are virtual tables

-- Create simple view
CREATE VIEW active_users AS 
SELECT id, name, email, age
FROM users
WHERE status = 'active';

-- Create view with JOIN
CREATE VIEW user_orders AS 
SELECT 
    u.id AS user_id,
    u.name AS user_name,
    o.id AS order_id,
    o.total AS order_total,
    o.created_at AS order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- Create view with aggregation
CREATE VIEW city_stats AS 
SELECT 
    city,
    COUNT(*) AS user_count,
    AVG(age) AS avg_age,
    MIN(age) AS min_age,
    MAX(age) AS max_age
FROM users
GROUP BY city;

-- Create view with algorithm
CREATE ALGORITHM = MERGE VIEW user_names AS 
SELECT id, CONCAT(first_name, ' ', last_name) AS full_name
FROM users;

-- Create view with check option
CREATE VIEW ny_users AS 
SELECT * FROM users WHERE city = 'NYC'
WITH CHECK OPTION;

-- Update data through view
UPDATE active_users SET age = 26 WHERE id = 1;

-- Drop view
DROP VIEW IF EXISTS active_users;

-- Show create view
SHOW CREATE VIEW user_orders;

-- Information about views
SELECT * FROM INFORMATION_SCHEMA.VIEWS 
WHERE TABLE_SCHEMA = 'database_name';

-- Indexes in MySQL
-- Index types and usage

-- CREATE INDEX
CREATE INDEX idx_users_name ON users(name);

-- CREATE UNIQUE INDEX
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- CREATE COMPOSITE INDEX
CREATE INDEX idx_users_city_age ON users(city, age);

-- CREATE FULLTEXT INDEX
CREATE FULLTEXT INDEX idx_posts_content ON posts(content);

-- CREATE SPATIAL INDEX
CREATE SPATIAL INDEX idx_locations_coords ON locations(coordinates);

-- CREATE INDEX WITH DESC
CREATE INDEX idx_users_name_desc ON users(name DESC);

-- Index on prefix
CREATE INDEX idx_users_name_prefix ON users(name(10));

-- DROP INDEX
DROP INDEX idx_users_name ON users;

-- Show indexes
SHOW INDEX FROM users;

-- Analyze table for index usage
ANALYZE TABLE users;

-- Optimize table
OPTIMIZE TABLE users;

-- Index usage with EXPLAIN
EXPLAIN SELECT * FROM users WHERE name = 'Alice';

-- Invisible index (MySQL 8.0+)
CREATE INDEX idx_invisible ON users(name) INVISIBLE;

-- Visible/invisible toggle
ALTER TABLE users ALTER INDEX idx_invisible VISIBLE;
ALTER TABLE users ALTER INDEX idx_invisible INVISIBLE;

-- Triggers in MySQL
-- Triggers for automatic actions

-- BEFORE INSERT trigger
DELIMITER //
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    SET NEW.updated_at = NOW();
END//
DELIMITER ;

-- BEFORE UPDATE trigger
DELIMITER //
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END//
DELIMITER ;

-- AFTER INSERT trigger for logging
DELIMITER //
CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, action, record_id, user_name, timestamp)
    VALUES ('users', 'INSERT', NEW.id, NEW.name, NOW());
END//
DELIMITER ;

-- BEFORE DELETE trigger
DELIMITER //
CREATE TRIGGER before_user_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO deleted_users (id, name, email, deleted_at)
    VALUES (OLD.id, OLD.name, OLD.email, NOW());
END//
DELIMITER ;

-- Trigger with validation
DELIMITER //
CREATE TRIGGER validate_user_age
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.age < 0 OR NEW.age > 150 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Invalid age';
    END IF;
END//
DELIMITER ;

-- Show triggers
SHOW TRIGGERS;
SHOW TRIGGERS LIKE 'users%';

-- Drop trigger
DROP TRIGGER IF EXISTS after_user_insert;

-- Stored Procedures vs Functions
-- Comparison and examples

-- Stored Procedure (can modify data)
DELIMITER //
CREATE PROCEDURE update_user_age(
    IN user_id INT,
    IN new_age INT
)
BEGIN
    UPDATE users SET age = new_age WHERE id = user_id;
    SELECT ROW_COUNT() AS rows_affected;
END//
DELIMITER ;

-- Function (must be deterministic, cannot modify data)
DELIMITER //
CREATE FUNCTION get_user_age(user_id INT)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE user_age INT;
    SELECT age INTO user_age FROM users WHERE id = user_id;
    RETURN user_age;
END//
DELIMITER ;

-- Procedure with multiple outputs
DELIMITER //
CREATE PROCEDURE get_user_stats(
    IN user_id INT,
    OUT user_name VARCHAR(100),
    OUT user_age INT,
    OUT user_city VARCHAR(100)
)
BEGIN
    SELECT name, age, city 
    INTO user_name, user_age, user_city
    FROM users 
    WHERE id = user_id;
END//
DELIMITER ;

-- Calling procedures
CALL update_user_age(1, 26);
CALL get_user_stats(1, @name, @age, @city);
SELECT @name, @age, @city;

-- Using functions
SELECT get_user_age(1) AS age;

-- Transactions in MySQL
-- ACID properties and transaction control

-- Basic transaction
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

-- Transaction with rollback
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Check balance
SELECT balance FROM accounts WHERE id = 1;

IF balance < 0 THEN
    ROLLBACK;
    SELECT 'Insufficient balance' AS result;
ELSE
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    COMMIT;
    SELECT 'Transfer successful' AS result;
END IF;

-- SAVEPOINT
START TRANSACTION;

INSERT INTO orders (user_id, total) VALUES (1, 100);
SAVEPOINT order_inserted;

INSERT INTO order_items (order_id, product_id, quantity) 
VALUES (LAST_INSERT_ID(), 1, 2);

-- If order item fails, rollback to savepoint
ROLLBACK TO order_inserted;

COMMIT;

-- Transaction isolation levels
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Locking reads
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
UPDATE users SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Shared lock
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

-- User-Defined Variables in MySQL
-- Session-level variables

-- Set variables
SET @var1 = 10;
SET @var2 := 20;  -- Alternative syntax
SELECT @var3 := 30;  -- Set from SELECT

-- Use variables in queries
SET @min_age = 18;
SET @max_age = 30;

SELECT * FROM users WHERE age BETWEEN @min_age AND @max_age;

-- Variables from SELECT
SELECT COUNT(*) INTO @user_count FROM users;
SELECT @user_count;

-- Multiple variables
SELECT name, age INTO @name, @age FROM users WHERE id = 1;
SELECT @name, @age;

-- Variables in LIMIT
SET @offset = 0;
SET @limit = 10;
PREPARE stmt FROM 'SELECT * FROM users LIMIT ?, ?';
EXECUTE stmt USING @offset, @limit;

-- Session vs global variables
SET SESSION sort_buffer_size = 1024;
SET GLOBAL max_connections = 1000;

-- System variables
SELECT @@global.max_connections;
SELECT @@session.autocommit;

-- Show all variables
SHOW VARIABLES;
SHOW SESSION VARIABLES;
SHOW GLOBAL VARIABLES;

-- Prepared Statements in MySQL
-- SQL injection prevention and performance

-- Prepare statement
PREPARE stmt1 FROM 'SELECT * FROM users WHERE id = ?';

SET @user_id = 1;
EXECUTE stmt1 USING @user_id;

DEALLOCATE PREPARE stmt1;

-- Multiple parameters
PREPARE stmt2 FROM 'SELECT * FROM users WHERE age BETWEEN ? AND ?';

SET @min_age = 18;
SET @max_age = 30;
EXECUTE stmt2 USING @min_age, @max_age;

DEALLOCATE PREPARE stmt2;

-- Dynamic table name (not directly possible, use CONCAT)
SET @table_name = 'users';
SET @query = CONCAT('SELECT * FROM ', @table_name, ' WHERE id = ?');
PREPARE stmt3 FROM @query;
SET @user_id = 1;
EXECUTE stmt3 USING @user_id;
DEALLOCATE PREPARE stmt3;

-- Dynamic ORDER BY
SET @order_by = 'name';
SET @direction = 'DESC';
SET @query = CONCAT('SELECT * FROM users ORDER BY ', @order_by, ' ', @direction);
PREPARE stmt4 FROM @query;
EXECUTE stmt4;
DEALLOCATE PREPARE stmt4;

-- Using with stored procedures
DELIMITER //
CREATE PROCEDURE dynamic_query(
    IN table_name VARCHAR(100),
    IN column_name VARCHAR(100)
)
BEGIN
    SET @query = CONCAT('SELECT ', column_name, ' FROM ', table_name);
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END//
DELIMITER ;

CALL dynamic_query('users', 'name');

-- Partitioning in MySQL
-- Table partitioning for performance

-- Range partitioning
CREATE TABLE orders_partitioned (
    id INT,
    order_date DATE,
    customer_id INT,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- List partitioning
CREATE TABLE users_by_region (
    id INT,
    name VARCHAR(100),
    region VARCHAR(50)
)
PARTITION BY LIST (region) (
    PARTITION p_north VALUES IN ('North', 'Northeast'),
    PARTITION p_south VALUES IN ('South', 'Southeast'),
    PARTITION p_west VALUES IN ('West', 'Southwest'),
    PARTITION p_east VALUES IN ('East', 'Midwest')
);

-- Hash partitioning
CREATE TABLE logs (
    id INT,
    log_data TEXT,
    created_at DATETIME
)
PARTITION BY HASH (id)
PARTITIONS 4;

-- Key partitioning (similar to hash but uses MySQL's hash function)
CREATE TABLE sessions (
    id INT,
    session_id VARCHAR(255),
    data TEXT
)
PARTITION BY KEY (id)
PARTITIONS 4;

-- Subpartitioning
CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount DECIMAL(10,2),
    region VARCHAR(50)
)
PARTITION BY RANGE (YEAR(sale_date))
SUBPARTITION BY HASH (id)
SUBPARTITIONS 2 (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022)
);

-- Query partition information
SELECT * FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'orders_partitioned';

-- Partition pruning
EXPLAIN SELECT * FROM orders_partitioned 
WHERE order_date BETWEEN '2021-01-01' AND '2021-12-31';

-- Add partition
ALTER TABLE orders_partitioned 
ADD PARTITION (PARTITION p2024 VALUES LESS THAN (2025));

-- Drop partition
ALTER TABLE orders_partitioned DROP PARTITION p_future;

-- Reorganize partitions
ALTER TABLE orders_partitioned 
REORGANIZE PARTITION p_future INTO (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Replication in MySQL
-- Master-slave replication concepts

-- On Master server
-- Enable binary log
-- my.cnf:
-- server-id = 1
-- log-bin = mysql-bin
-- binlog-do-db = mydatabase

-- Create replication user
CREATE USER 'replication'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

-- Get master status
SHOW MASTER STATUS;

-- On Slave server
-- my.cnf:
-- server-id = 2
-- relay-log = mysql-relay-bin

-- Configure slave
CHANGE MASTER TO
    MASTER_HOST = 'master_host',
    MASTER_USER = 'replication',
    MASTER_PASSWORD = 'password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 123;

-- Start slave
START SLAVE;

-- Check slave status
SHOW SLAVE STATUSG

-- Stop slave
STOP SLAVE;

-- Reset slave
RESET SLAVE ALL;

-- Multi-source replication (MySQL 5.7+)
CHANGE MASTER TO
    MASTER_HOST = 'master1_host',
    MASTER_USER = 'replication',
    MASTER_PASSWORD = 'password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 123
FOR CHANNEL 'channel1';

CHANGE MASTER TO
    MASTER_HOST = 'master2_host',
    MASTER_USER = 'replication',
    MASTER_PASSWORD = 'password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 456
FOR CHANNEL 'channel2';

START SLAVE FOR CHANNEL 'channel1';
START SLAVE FOR CHANNEL 'channel2';

-- Semi-synchronous replication
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- Performance Tuning in MySQL
-- Query optimization and tuning

-- Query cache (MySQL 5.7 and earlier)
SHOW VARIABLES LIKE 'query_cache%';
SET GLOBAL query_cache_size = 1000000;

-- SQL_NO_CACHE (for testing)
SELECT SQL_NO_CACHE * FROM users WHERE age > 18;

-- SQL_CACHE (force cache)
SELECT SQL_CACHE * FROM users WHERE age > 18;

-- Analyze slow query log
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;

-- Use EXPLAIN for query analysis
EXPLAIN SELECT * FROM users WHERE age > 18;

-- Use EXPLAIN FORMAT=JSON
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 18;

-- Query profiling
SET profiling = 1;
SELECT * FROM users WHERE age > 18;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

-- OPTIMIZE TABLE
OPTIMIZE TABLE users;

-- ANALYZE TABLE
ANALYZE TABLE users;

-- CHECK TABLE
CHECK TABLE users;

-- REPAIR TABLE
REPAIR TABLE users;

-- Innodb status
SHOW ENGINE INNODB STATUSG

-- Processlist
SHOW FULL PROCESSLIST;

-- Kill query
KILL QUERY 123;

-- Kill connection
KILL 123;

-- Backup and Restore in MySQL
-- Backup methods

-- mysqldump
-- mysqldump -u username -p database_name > backup.sql

-- mysqldump specific tables
-- mysqldump -u username -p database_name users orders > backup.sql

-- mysqldump with options
-- mysqldump -u username -p --add-drop-table --create-options database_name > backup.sql

-- mysqldump for all databases
-- mysqldump -u username -p --all-databases > all_backup.sql

-- mysqldump with compression
-- mysqldump -u username -p database_name | gzip > backup.sql.gz

-- mysqldump with --single-transaction (for InnoDB)
-- mysqldump -u username -p --single-transaction database_name > backup.sql

-- mysqldump with --master-data (for replication)
-- mysqldump -u username -p --master-data=2 database_name > backup.sql

-- Restore
-- mysql -u username -p database_name < backup.sql

-- Restore with compression
-- gunzip < backup.sql.gz | mysql -u username -p database_name

-- Restore without database creation
-- mysql -u username -p < backup.sql

-- MySQL Enterprise Backup
-- mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/backup backup

-- Percona XtraBackup
-- xtrabackup --backup --target-dir=/backup

-- Binary log backup
-- mysqlbinlog mysql-bin.000001 > binlog.sql

-- Export to CSV
SELECT * FROM users INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '
';

-- Import from CSV
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '
'
IGNORE 1 LINES;

-- User Management in MySQL
-- Creating and managing users

-- Create user
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'app_user'@'%' IDENTIFIED BY 'password';

-- Drop user
DROP USER 'app_user'@'localhost';

-- Rename user
RENAME USER 'old_user'@'localhost' TO 'new_user'@'localhost';

-- Grant privileges
GRANT ALL PRIVILEGES ON database_name.* TO 'app_user'@'localhost';
GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'app_user'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'localhost' WITH GRANT OPTION;

-- Grant specific privileges
GRANT SELECT ON database_name.users TO 'app_user'@'localhost';
GRANT EXECUTE ON PROCEDURE database_name.procedure_name TO 'app_user'@'localhost';

-- Revoke privileges
REVOKE ALL PRIVILEGES ON database_name.* FROM 'app_user'@'localhost';
REVOKE GRANT OPTION ON *.* FROM 'admin_user'@'localhost';

-- Show grants
SHOW GRANTS FOR 'app_user'@'localhost';
SHOW GRANTS;  -- For current user

-- Create user with password expiration
CREATE USER 'temp_user'@'localhost' IDENTIFIED BY 'password'
PASSWORD EXPIRE INTERVAL 30 DAY;

-- Create user with account lock
CREATE USER 'locked_user'@'localhost' IDENTIFIED BY 'password'
ACCOUNT LOCK;

-- Unlock account
ALTER USER 'locked_user'@'localhost' ACCOUNT UNLOCK;

-- Change password
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'new_password';
SET PASSWORD FOR 'app_user'@'localhost' = PASSWORD('new_password');

-- Roles (MySQL 8.0+)
CREATE ROLE 'app_role', 'admin_role';
GRANT SELECT ON database_name.* TO 'app_role';
GRANT ALL PRIVILEGES ON *.* TO 'admin_role';
GRANT 'app_role' TO 'app_user'@'localhost';
SET DEFAULT ROLE 'app_role' TO 'app_user'@'localhost';

-- Security Best Practices in MySQL
-- Security configurations

-- Remove anonymous users
DELETE FROM mysql.user WHERE User = '';
FLUSH PRIVILEGES;

-- Remove test database
DROP DATABASE IF EXISTS test;

-- Disable remote root login
RENAME USER 'root'@'%' TO 'root'@'localhost';

-- Use SSL/TLS
-- my.cnf:
-- ssl-ca=/etc/mysql/ssl/ca.pem
-- ssl-cert=/etc/mysql/ssl/server-cert.pem
-- ssl-key=/etc/mysql/ssl/server-key.pem

-- Require SSL for user
ALTER USER 'app_user'@'%' REQUIRE SSL;

-- Set password policy
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 8;

-- Enable audit log
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET GLOBAL audit_log_policy = ALL;

-- Limit connections per user
ALTER USER 'app_user'@'%' WITH MAX_CONNECTIONS_PER_HOUR 100;

-- Limit queries per hour
ALTER USER 'app_user'@'%' WITH MAX_QUERIES_PER_HOUR 1000;

-- Limit updates per hour
ALTER USER 'app_user'@'%' WITH MAX_UPDATES_PER_HOUR 100;

-- Disable LOAD DATA LOCAL INFILE
SET GLOBAL local_infile = 0;

-- Enable SQL_MODE
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE';

-- Show security variables
SHOW VARIABLES LIKE '%ssl%';
SHOW VARIABLES LIKE '%validate%';
SHOW VARIABLES LIKE '%secure%';

-- Check user connections
SELECT user, host, connection_id FROM information_schema.processlist;

-- Normalization in MySQL
-- Database normalization principles

-- First Normal Form (1NF) - Atomic values
-- Bad: Products table with multiple categories
CREATE TABLE products_bad (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    categories VARCHAR(255)  -- Comma separated values
);

-- Good: 1NF compliant
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE product_categories (
    product_id INT,
    category VARCHAR(50),
    PRIMARY KEY (product_id, category)
);

-- Second Normal Form (2NF) - Remove partial dependencies
-- Bad: Orders with product details
CREATE TABLE orders_bad (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),
    quantity INT,
    unit_price DECIMAL(10,2),
    PRIMARY KEY (order_id, product_id)
);

-- Good: 2NF compliant
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10,2),
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- Third Normal Form (3NF) - Remove transitive dependencies
-- Bad: Orders with customer details
CREATE TABLE orders_bad (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100),
    customer_city VARCHAR(100),
    order_date DATE
);

-- Good: 3NF compliant
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Boyce-Codd Normal Form (BCNF)
-- Handle overlapping candidate keys

-- Fourth Normal Form (4NF)
-- Handle multi-valued dependencies

-- Fifth Normal Form (5NF)
-- Handle join dependencies

-- Denormalization in MySQL
-- Denormalization for performance

-- Denormalized table with redundant data
CREATE TABLE orders_denormalized (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100),
    customer_email VARCHAR(255),
    product_id INT,
    product_name VARCHAR(100),
    quantity INT,
    unit_price DECIMAL(10,2),
    total DECIMAL(10,2),
    order_date DATE
);

-- Update denormalized data with triggers
DELIMITER //
CREATE TRIGGER update_order_total
BEFORE INSERT ON orders_denormalized
FOR EACH ROW
BEGIN
    SET NEW.total = NEW.quantity * NEW.unit_price;
END//
DELIMITER ;

-- Materialized views (using tables and triggers)
CREATE TABLE sales_summary (
    product_id INT PRIMARY KEY,
    total_sales DECIMAL(10,2),
    units_sold INT,
    last_updated TIMESTAMP
);

-- Refresh materialized view
UPDATE sales_summary ss
JOIN (
    SELECT 
        product_id,
        SUM(quantity * unit_price) AS total_sales,
        SUM(quantity) AS units_sold
    FROM order_items
    GROUP BY product_id
) o ON ss.product_id = o.product_id
SET 
    ss.total_sales = o.total_sales,
    ss.units_sold = o.units_sold,
    ss.last_updated = NOW();

-- Denormalized query performance
-- Without denormalization (JOIN)
SELECT 
    o.order_id,
    c.name AS customer_name,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

-- With denormalization (single table)
SELECT * FROM orders_denormalized;

-- JSON in MySQL (MySQL 5.7+)
-- JSON data type and functions

-- Create table with JSON column
CREATE TABLE json_data (
    id INT PRIMARY KEY AUTO_INCREMENT,
    data JSON
);

-- Insert JSON
INSERT INTO json_data (data) VALUES 
    ('{"name": "Alice", "age": 25, "city": "NYC"}'),
    ('{"name": "Bob", "age": 30, "city": "LA", "hobbies": ["reading", "gaming"]}');

-- Extract value
SELECT 
    id,
    JSON_EXTRACT(data, '$.name') AS name,
    JSON_EXTRACT(data, '$.age') AS age
FROM json_data;

-- Extract with -> operator
SELECT 
    id,
    data->'$.name' AS name,
    data->'$.age' AS age
FROM json_data;

-- Extract as text with ->> operator
SELECT 
    id,
    data->>'$.name' AS name,
    data->>'$.age' AS age
FROM json_data;

-- JSON functions
SELECT 
    JSON_OBJECT('name', name, 'age', age) AS user_json
FROM users;

SELECT 
    JSON_ARRAY(name, age, city) AS user_array
FROM users;

-- Update JSON
UPDATE json_data 
SET data = JSON_SET(data, '$.age', 26, '$.city', 'NYC') 
WHERE id = 1;

-- Add to JSON
UPDATE json_data 
SET data = JSON_INSERT(data, '$.status', 'active') 
WHERE id = 1;

-- Remove from JSON
UPDATE json_data 
SET data = JSON_REMOVE(data, '$.status') 
WHERE id = 1;

-- Search in JSON
SELECT * FROM json_data 
WHERE JSON_EXTRACT(data, '$.name') = 'Alice';

-- JSON path
SELECT 
    JSON_SEARCH(data, 'one', 'Alice') AS path
FROM json_data;

-- JSON table (MySQL 8.0+)
SELECT * 
FROM JSON_TABLE(
    '[{"name": "Alice"}, {"name": "Bob"}]',
    '$[*]' COLUMNS (
        name VARCHAR(100) PATH '$.name'
    )
) AS jt;

-- Full-Text Search in MySQL
-- Full-text indexing and searching

-- Create table with FULLTEXT index
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT INDEX ft_title_content (title, content)
);

-- Insert data
INSERT INTO articles (title, content) VALUES
    ('MySQL Full-Text Search', 'MySQL supports full-text searching and indexing'),
    ('Advanced SQL Queries', 'Learn about complex SQL queries and optimization'),
    ('Database Performance Tuning', 'Tips for improving database performance');

-- NATURAL LANGUAGE MODE
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL' IN NATURAL LANGUAGE MODE);

-- BOOLEAN MODE
-- + Required, - Excluded, * Wildcard, > Increase rank, < Decrease rank
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('+MySQL +search' IN BOOLEAN MODE);

SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL -Oracle' IN BOOLEAN MODE);

SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL*' IN BOOLEAN MODE);

SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('>MySQL +search' IN BOOLEAN MODE);

-- WITH QUERY EXPANSION
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL' WITH QUERY EXPANSION);

-- Query with relevance score
SELECT 
    *,
    MATCH(title, content) AGAINST('MySQL') AS relevance
FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL')
ORDER BY relevance DESC;

-- Full-text search on multiple tables (using UNION)
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL')
UNION
SELECT * FROM comments 
WHERE MATCH(comment) AGAINST('MySQL');

-- Stopwords
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

-- Minimum word length
SHOW VARIABLES LIKE 'ft_min_word_len';
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';

-- Spatial Data in MySQL (MySQL 5.7+)
-- Spatial data types and functions

-- Create table with spatial column
CREATE TABLE locations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    coordinates POINT NOT NULL,
    SPATIAL INDEX idx_coords (coordinates)
);

-- Insert spatial data
INSERT INTO locations (name, coordinates) VALUES
    ('Central Park', ST_PointFromText('POINT(-73.9654 40.7829)')),
    ('Times Square', ST_PointFromText('POINT(-73.9855 40.7580)')),
    ('Empire State', ST_PointFromText('POINT(-73.9857 40.7484)'));

-- Create polygon
CREATE TABLE areas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    boundary POLYGON NOT NULL,
    SPATIAL INDEX idx_boundary (boundary)
);

INSERT INTO areas (name, boundary) VALUES
    ('Manhattan', ST_PolygonFromText('POLYGON((-74.05 40.70, -73.90 40.70, -73.90 40.80, -74.05 40.80, -74.05 40.70))'));

-- Spatial queries
-- ST_Distance
SELECT 
    name,
    ST_Distance(coordinates, ST_PointFromText('POINT(-73.97 40.76)')) AS distance
FROM locations
ORDER BY distance;

-- ST_Within
SELECT 
    l.name AS location,
    a.name AS area
FROM locations l, areas a
WHERE ST_Within(l.coordinates, a.boundary);

-- ST_Contains
SELECT 
    a.name AS area,
    COUNT(*) AS location_count
FROM areas a, locations l
WHERE ST_Contains(a.boundary, l.coordinates)
GROUP BY a.id;

-- ST_Buffer
SELECT 
    name,
    ST_AsText(ST_Buffer(coordinates, 0.01)) AS buffer
FROM locations;

-- ST_Intersects
SELECT 
    l1.name AS point1,
    l2.name AS point2,
    ST_Distance(l1.coordinates, l2.coordinates) AS distance
FROM locations l1, locations l2
WHERE l1.id < l2.id
AND ST_Distance(l1.coordinates, l2.coordinates) < 0.01;

-- ST_Area
SELECT 
    name,
    ST_Area(boundary) AS area_sq_degrees
FROM areas;

-- ST_Centroid
SELECT 
    name,
    ST_AsText(ST_Centroid(boundary)) AS centroid
FROM areas;

-- Generated Columns in MySQL
-- Virtual and stored generated columns

-- Stored generated column
CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

-- Virtual generated column
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    full_name VARCHAR(200) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL
);

-- Generated column with complex expression
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    subtotal DECIMAL(10,2),
    tax_rate DECIMAL(5,2) DEFAULT 10.00,
    tax_amount DECIMAL(10,2) GENERATED ALWAYS AS (subtotal * tax_rate / 100) STORED,
    total DECIMAL(10,2) GENERATED ALWAYS AS (subtotal + (subtotal * tax_rate / 100)) STORED
);

-- Generated column with conditional logic
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    price DECIMAL(10,2),
    discount_percent DECIMAL(5,2) DEFAULT 0,
    discounted_price DECIMAL(10,2) GENERATED ALWAYS AS (
        CASE 
            WHEN discount_percent > 0 THEN price * (1 - discount_percent / 100)
            ELSE price
        END
    ) STORED
);

-- Index on generated column
CREATE INDEX idx_discounted_price ON products(discounted_price);

-- Generated column with JSON data
CREATE TABLE json_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_data JSON,
    user_name VARCHAR(100) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(user_data, '$.name'))) STORED,
    user_age INT GENERATED ALWAYS AS (JSON_EXTRACT(user_data, '$.age')) VIRTUAL
);

-- Insert into generated column
INSERT INTO sales (quantity, unit_price) VALUES (5, 10.00);
INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe');
INSERT INTO json_users (user_data) VALUES ('{"name": "Alice", "age": 25}');

-- Select generated columns
SELECT * FROM sales;
SELECT full_name FROM employees;
SELECT user_name, user_age FROM json_users;

-- Views with Check Option in MySQL
-- Updatable views with check option

-- Basic view with check option
CREATE VIEW active_users AS 
SELECT id, name, email, status
FROM users
WHERE status = 'active'
WITH CHECK OPTION;

-- Insert through view
INSERT INTO active_users (name, email, status) 
VALUES ('Alice', 'alice@example.com', 'active');  -- Works

-- This will fail (status not active)
INSERT INTO active_users (name, email, status) 
VALUES ('Bob', 'bob@example.com', 'inactive');  -- Fails

-- View with cascade check option
CREATE VIEW nyc_users AS 
SELECT id, name, city, status
FROM active_users
WHERE city = 'NYC'
WITH CASCADED CHECK OPTION;

-- View with local check option
CREATE VIEW la_users AS 
SELECT id, name, city, status
FROM active_users
WHERE city = 'LA'
WITH LOCAL CHECK OPTION;

-- View with no check option
CREATE VIEW all_users AS 
SELECT id, name, email, status, city
FROM users
WHERE status = 'active';

-- Insert into no-check view
INSERT INTO all_users (name, email, status, city) 
VALUES ('Bob', 'bob@example.com', 'inactive', 'LA');

-- Update view with check option
UPDATE active_users SET status = 'inactive' WHERE id = 1;  -- Fails
UPDATE active_users SET status = 'active' WHERE id = 1;  -- Works

-- View check option restrictions
-- Views with JOIN, GROUP BY, DISTINCT, UNION, subqueries cannot be updatable

-- Check if view is updatable
SELECT 
    TABLE_NAME,
    IS_UPDATABLE
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'database_name';

-- Temporary Tables in MySQL
-- Temporary tables for session-specific data

-- Create temporary table
CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    age INT
);

-- Insert data
INSERT INTO temp_users (name, age) VALUES 
    ('Alice', 25),
    ('Bob', 30);

-- Query temporary table
SELECT * FROM temp_users;

-- Temporary table with SELECT
CREATE TEMPORARY TABLE temp_active_users
SELECT * FROM users WHERE status = 'active';

-- Temporary table with indexes
CREATE TEMPORARY TABLE temp_orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    total DECIMAL(10,2),
    INDEX idx_user (user_id)
);

-- Temporary table with ENGINE option
CREATE TEMPORARY TABLE temp_memory (
    id INT PRIMARY KEY,
    data VARCHAR(100)
) ENGINE = MEMORY;

-- Temporary table with ON COMMIT DELETE ROWS
CREATE TEMPORARY TABLE temp_sessions (
    session_id VARCHAR(255),
    data TEXT
) ON COMMIT DELETE ROWS;

-- Drop temporary table
DROP TEMPORARY TABLE temp_users;
DROP TEMPORARY TABLE IF EXISTS temp_users;

-- Check if table is temporary
SELECT 
    TABLE_NAME,
    TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'database_name'
AND TABLE_TYPE = 'TEMPORARY';

-- Using temporary table for complex query
CREATE TEMPORARY TABLE temp_high_spenders
SELECT 
    user_id,
    SUM(total) AS total_spent
FROM orders
GROUP BY user_id
HAVING total_spent > 1000;

SELECT 
    u.name,
    u.email,
    t.total_spent
FROM users u
JOIN temp_high_spenders t ON u.id = t.user_id
ORDER BY t.total_spent DESC;

-- Temporary table for pagination
CREATE TEMPORARY TABLE temp_page
SELECT * FROM users
ORDER BY name
LIMIT 10 OFFSET 20;

-- Clean up
DROP TEMPORARY TABLE temp_high_spenders;
DROP TEMPORARY TABLE temp_page;

-- Character Sets and Collations in MySQL
-- Character set and collation management

-- Show character sets
SHOW CHARACTER SET;
SHOW CHARSET;

-- Show collations
SHOW COLLATION;
SHOW COLLATION LIKE 'utf8%';

-- Set character set for database
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Set character set for table
CREATE TABLE mytable (
    id INT PRIMARY KEY,
    name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Set character set for column
ALTER TABLE users MODIFY name VARCHAR(100) CHARACTER SET utf8mb4;

-- Convert table character set
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Convert column character set
ALTER TABLE users MODIFY name VARCHAR(100) CHARACTER SET latin1;

-- Set session character set
SET NAMES 'utf8mb4';
SET CHARACTER SET utf8mb4;

-- Set global character set
SET GLOBAL character_set_server = 'utf8mb4';
SET GLOBAL collation_server = 'utf8mb4_unicode_ci';

-- Check character sets
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

-- Compare strings with different collations
SELECT 'abc' = 'ABC' COLLATE utf8mb4_unicode_ci;  -- 1
SELECT 'abc' = 'ABC' COLLATE utf8mb4_bin;         -- 0

-- Use specific collation in query
SELECT * FROM users 
WHERE name = 'Alice' COLLATE utf8mb4_unicode_ci;

-- Unicode vs non-unicode
-- utf8mb4 supports all Unicode characters (including emoji)
CREATE TABLE messages (
    id INT PRIMARY KEY,
    content VARCHAR(255) CHARACTER SET utf8mb4
);

INSERT INTO messages (content) VALUES ('Hello 😊');

-- Information Schema in MySQL
-- Querying metadata

-- List all databases
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

-- List all tables in database
SELECT 
    TABLE_NAME,
    TABLE_TYPE,
    ENGINE,
    ROW_FORMAT,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'database_name';

-- List all columns in table
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    CHARACTER_MAXIMUM_LENGTH,
    NUMERIC_PRECISION,
    COLUMN_KEY,
    EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'database_name'
AND TABLE_NAME = 'users';

-- List indexes
SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'database_name'
AND TABLE_NAME = 'users';

-- List foreign keys
SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'database_name'
AND REFERENCED_TABLE_NAME IS NOT NULL;

-- List views
SELECT 
    TABLE_NAME,
    VIEW_DEFINITION,
    CHECK_OPTION,
    IS_UPDATABLE
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'database_name';

-- List privileges
SELECT * FROM INFORMATION_SCHEMA.USER_PRIVILEGES
WHERE GRANTEE LIKE '%app_user%';

-- Table sizes
SELECT 
    TABLE_NAME,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size_mb
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'database_name'
ORDER BY size_mb DESC;

-- Query optimizer statistics
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

-- InnoDB metrics
SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS;

-- Show process list
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

-- Find missing IDs using recursive CTE (MySQL 8.0+)
WITH RECURSIVE numbers(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < (SELECT MAX(id) FROM mytable)
)
SELECT n AS missing_id
FROM numbers
WHERE n NOT IN (SELECT id FROM mytable);

-- Using self-join
SELECT t1.id + 1 AS missing_id
FROM mytable t1
LEFT JOIN mytable t2 ON t2.id = t1.id + 1
WHERE t2.id IS NULL
AND t1.id < (SELECT MAX(id) FROM mytable);

-- Find consecutive days of activity (MySQL 8.0+)
WITH numbered AS (
    SELECT 
        date,
        ROW_NUMBER() OVER (ORDER BY date) AS rn
    FROM activities
),
grouped AS (
    SELECT 
        date,
        rn - DATEDIFF(date, (SELECT MIN(date) FROM activities)) AS grp
    FROM numbered
)
SELECT 
    MIN(date) AS start_date,
    MAX(date) AS end_date,
    COUNT(*) AS days
FROM grouped
GROUP BY grp
HAVING COUNT(*) >= 3
ORDER BY start_date;

-- Find overlapping intervals
SELECT 
    a.id AS interval1,
    b.id AS interval2,
    a.start_date AS start1,
    a.end_date AS end1,
    b.start_date AS start2,
    b.end_date AS end2
FROM intervals a
JOIN intervals b ON a.id < b.id
WHERE a.start_date < b.end_date 
  AND a.end_date > b.start_date;

-- Recursive hierarchy (MySQL 8.0+)
WITH RECURSIVE org_tree AS (
    -- Anchor: top-level employees
    SELECT 
        id,
        name,
        manager_id,
        0 AS level,
        CAST(name AS CHAR(200)) AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive: children
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        ot.level + 1,
        CONCAT(ot.path, ' -> ', e.name)
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT 
    CONCAT(REPEAT('  ', level), name) AS org_chart,
    level,
    path
FROM org_tree
ORDER BY path;

-- Department with highest average salary
SELECT 
    department_id,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC
LIMIT 1;

-- Using subquery
SELECT 
    department_id,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
    SELECT MAX(avg_salary)
    FROM (SELECT AVG(salary) AS avg_salary FROM employees GROUP BY department_id) t
);

-- Percentage contribution (MySQL 8.0+)
SELECT 
    category,
    amount,
    (amount / SUM(amount) OVER ()) * 100 AS percentage
FROM sales;

-- Using subquery
SELECT 
    category,
    amount,
    (amount / (SELECT SUM(amount) FROM sales)) * 100 AS percentage
FROM sales;

-- Percentage with grouping
SELECT 
    category,
    amount,
    (amount / SUM(amount) OVER (PARTITION BY category)) * 100 AS percentage_in_category
FROM sales;

-- Running total per category (MySQL 8.0+)
SELECT 
    category,
    date,
    amount,
    SUM(amount) OVER (PARTITION BY category ORDER BY date) AS running_total
FROM sales
ORDER BY category, date;

-- Running total using self-join
SELECT 
    a.category,
    a.date,
    a.amount,
    SUM(b.amount) AS running_total
FROM sales a
JOIN sales b ON a.category = b.category AND b.date <= a.date
GROUP BY a.category, a.date, a.amount
ORDER BY a.category, a.date;

-- Find islands of data (MySQL 8.0+)
WITH numbered AS (
    SELECT 
        date,
        ROW_NUMBER() OVER (ORDER BY date) AS rn
    FROM activities
),
grouped AS (
    SELECT 
        date,
        rn - ROW_NUMBER() OVER (ORDER BY date) AS grp
    FROM numbered
)
SELECT 
    MIN(date) AS start_date,
    MAX(date) AS end_date,
    COUNT(*) AS days
FROM grouped
GROUP BY grp
HAVING COUNT(*) > 1
ORDER BY start_date;

-- Gap analysis using LAG (MySQL 8.0+)
WITH numbered AS (
    SELECT 
        id,
        LAG(id) OVER (ORDER BY id) AS prev_id
    FROM mytable
)
SELECT 
    prev_id + 1 AS gap_start,
    id - 1 AS gap_end
FROM numbered
WHERE id > prev_id + 1;

-- Gap analysis using self-join
SELECT 
    t1.id + 1 AS gap_start,
    MIN(t2.id) - 1 AS gap_end
FROM mytable t1
JOIN mytable t2 ON t2.id > t1.id
WHERE NOT EXISTS (
    SELECT 1 FROM mytable t3 
    WHERE t3.id > t1.id AND t3.id < t2.id
)
GROUP BY t1.id;

-- Mode (most frequent value)
SELECT column_name, COUNT(*) AS frequency
FROM mytable
GROUP BY column_name
ORDER BY frequency DESC
LIMIT 1;

-- Using subquery
SELECT column_name
FROM mytable
GROUP BY column_name
HAVING COUNT(*) = (
    SELECT MAX(count)
    FROM (SELECT COUNT(*) AS count FROM mytable GROUP BY column_name) t
);

-- Median using PERCENTILE_CONT (MySQL 8.0+)
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) 
OVER () AS median
FROM employees
LIMIT 1;

-- Median using NTILE (MySQL 8.0+)
SELECT AVG(salary) AS median
FROM (
    SELECT 
        salary,
        NTILE(2) OVER (ORDER BY salary) AS tile
    FROM employees
) t
WHERE tile = 1 OR tile = 2
GROUP BY tile;

-- Median using user variables (older MySQL)
SELECT AVG(salary) AS median
FROM (
    SELECT salary
    FROM employees
    ORDER BY salary
    LIMIT 2 - (SELECT COUNT(*) FROM employees) % 2
    OFFSET (SELECT (COUNT(*) - 1) / 2 FROM employees)
) t;

-- Nth highest salary using LIMIT
SELECT DISTINCT salary 
FROM employees 
ORDER BY salary DESC 
LIMIT N-1, 1;

-- Nth highest using DENSE_RANK (MySQL 8.0+)
WITH ranked AS (
    SELECT 
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT DISTINCT salary
FROM ranked
WHERE rnk = N;

-- 3rd highest salary example
SELECT DISTINCT salary 
FROM employees 
ORDER BY salary DESC 
LIMIT 2, 1;

-- Cumulative distribution (MySQL 8.0+)
SELECT 
    value,
    CUME_DIST() OVER (ORDER BY value) AS cume_dist,
    PERCENT_RANK() OVER (ORDER BY value) AS percent_rank
FROM mytable
ORDER BY value;

-- Manual calculation
SELECT 
    value,
    (SELECT COUNT(*) FROM mytable WHERE value <= t.value) / COUNT(*) AS cume_dist
FROM mytable t
GROUP BY value
ORDER BY value;

-- First and last value in group (MySQL 8.0+)
SELECT 
    category,
    date,
    value,
    FIRST_VALUE(value) OVER (PARTITION BY category ORDER BY date) AS first_value,
    LAST_VALUE(value) OVER (
        PARTITION BY category 
        ORDER BY date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_value
FROM mytable
ORDER BY category, date;

-- Using subquery
SELECT 
    category,
    date,
    value,
    (SELECT value FROM mytable t2 
     WHERE t2.category = t1.category 
     ORDER BY t2.date ASC LIMIT 1) AS first_value,
    (SELECT value FROM mytable t2 
     WHERE t2.category = t1.category 
     ORDER BY t2.date DESC LIMIT 1) AS last_value
FROM mytable t1;

-- LAG and LEAD (MySQL 8.0+)
SELECT 
    date,
    amount,
    LAG(amount, 1) OVER (ORDER BY date) AS previous_amount,
    LAG(amount, 2) OVER (ORDER BY date) AS previous_2_amount,
    LEAD(amount, 1) OVER (ORDER BY date) AS next_amount,
    LEAD(amount, 2) OVER (ORDER BY date) AS next_2_amount
FROM sales
ORDER BY date;

-- LAG and LEAD with partition
SELECT 
    category,
    date,
    amount,
    LAG(amount, 1) OVER (PARTITION BY category ORDER BY date) AS prev_in_category,
    LEAD(amount, 1) OVER (PARTITION BY category ORDER BY date) AS next_in_category
FROM sales
ORDER BY category, date;

-- Difference between current and previous (MySQL 8.0+)
SELECT 
    date,
    amount,
    amount - LAG(amount, 1) OVER (ORDER BY date) AS difference,
    amount - LAG(amount, 1) OVER (ORDER BY date) AS diff_from_prev
FROM sales
ORDER BY date;

-- Using self-join
SELECT 
    a.date,
    a.amount,
    a.amount - b.amount AS difference
FROM sales a
JOIN sales b ON b.date = (
    SELECT MAX(date) 
    FROM sales 
    WHERE date < a.date
)
ORDER BY a.date;

-- Percentage change (MySQL 8.0+)
SELECT 
    date,
    amount,
    ((amount - LAG(amount, 1) OVER (ORDER BY date)) / 
     LAG(amount, 1) OVER (ORDER BY date)) * 100 AS pct_change
FROM sales
ORDER BY date;

-- Percentage change from previous year
SELECT 
    date,
    amount,
    ((amount - LAG(amount, 12) OVER (ORDER BY date)) / 
     LAG(amount, 12) OVER (ORDER BY date)) * 100 AS yoy_pct_change
FROM monthly_sales
ORDER BY date;

-- Year-over-year comparison
SELECT 
    YEAR(date) AS year,
    MONTH(date) AS month,
    amount,
    LAG(amount, 12) OVER (ORDER BY date) AS same_month_last_year,
    amount - LAG(amount, 12) OVER (ORDER BY date) AS yoy_change,
    ((amount - LAG(amount, 12) OVER (ORDER BY date)) / 
     LAG(amount, 12) OVER (ORDER BY date)) * 100 AS yoy_pct_change
FROM monthly_sales
ORDER BY date;

-- Rolling sum (MySQL 8.0+)
SELECT 
    date,
    amount,
    SUM(amount) OVER (
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7day_sum,
    AVG(amount) OVER (
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7day_avg
FROM daily_sales
ORDER BY date;

-- Rolling sum with partition
SELECT 
    category,
    date,
    amount,
    SUM(amount) OVER (
        PARTITION BY category
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7day_sum_category
FROM daily_sales
ORDER BY category, date;

-- Fill missing dates (MySQL 8.0+)
WITH RECURSIVE dates(dt) AS (
    SELECT MIN(date) FROM sales
    UNION ALL
    SELECT dt + INTERVAL 1 DAY
    FROM dates
    WHERE dt < (SELECT MAX(date) FROM sales)
)
SELECT 
    d.dt AS date,
    COALESCE(s.amount, 0) AS amount
FROM dates d
LEFT JOIN sales s ON d.dt = s.date
ORDER BY d.dt;

-- Fill missing dates with previous value
WITH RECURSIVE dates(dt) AS (
    SELECT MIN(date) FROM sales
    UNION ALL
    SELECT dt + INTERVAL 1 DAY
    FROM dates
    WHERE dt < (SELECT MAX(date) FROM sales)
),
filled AS (
    SELECT 
        d.dt AS date,
        s.amount,
        @prev_amount := COALESCE(s.amount, @prev_amount) AS filled_amount
    FROM dates d
    LEFT JOIN sales s ON d.dt = s.date
    CROSS JOIN (SELECT @prev_amount := 0) init
)
SELECT date, filled_amount AS amount
FROM filled;

-- Difference between dates
SELECT 
    start_date,
    end_date,
    DATEDIFF(end_date, start_date) AS days_diff,
    TIMESTAMPDIFF(DAY, start_date, end_date) AS days_diff_2,
    TIMESTAMPDIFF(MONTH, start_date, end_date) AS months_diff,
    TIMESTAMPDIFF(YEAR, start_date, end_date) AS years_diff
FROM projects;

-- Difference from current date
SELECT 
    start_date,
    DATEDIFF(CURDATE(), start_date) AS days_since_start,
    TIMESTAMPDIFF(DAY, start_date, CURDATE()) AS days_since_start_2
FROM projects;

-- Extract date parts
SELECT 
    date_col,
    YEAR(date_col) AS year,
    MONTH(date_col) AS month,
    DAY(date_col) AS day,
    HOUR(date_col) AS hour,
    MINUTE(date_col) AS minute,
    SECOND(date_col) AS second,
    WEEK(date_col) AS week,
    QUARTER(date_col) AS quarter,
    DAYOFWEEK(date_col) AS day_of_week,
    DAYOFYEAR(date_col) AS day_of_year,
    WEEKDAY(date_col) AS weekday_index
FROM events;

-- Using DATE_FORMAT
SELECT 
    date_col,
    DATE_FORMAT(date_col, '%Y') AS year,
    DATE_FORMAT(date_col, '%m') AS month,
    DATE_FORMAT(date_col, '%d') AS day,
    DATE_FORMAT(date_col, '%W') AS day_name,
    DATE_FORMAT(date_col, '%M') AS month_name
FROM events;

-- Format date
SELECT 
    date_col,
    DATE_FORMAT(date_col, '%Y-%m-%d') AS yyyy_mm_dd,
    DATE_FORMAT(date_col, '%m/%d/%Y') AS mm_dd_yyyy,
    DATE_FORMAT(date_col, '%M %e, %Y') AS full_date,
    DATE_FORMAT(date_col, '%W, %M %e, %Y') AS full_date_with_day,
    DATE_FORMAT(date_col, '%r') AS time_12h,
    DATE_FORMAT(date_col, '%H:%i:%s') AS time_24h,
    DATE_FORMAT(date_col, '%Y-%m-%d %H:%i:%s') AS datetime_format
FROM events;

-- Date arithmetic
SELECT 
    date_col,
    DATE_ADD(date_col, INTERVAL 1 DAY) AS tomorrow,
    DATE_ADD(date_col, INTERVAL 1 WEEK) AS next_week,
    DATE_ADD(date_col, INTERVAL 1 MONTH) AS next_month,
    DATE_ADD(date_col, INTERVAL 1 YEAR) AS next_year,
    DATE_SUB(date_col, INTERVAL 1 DAY) AS yesterday,
    DATE_SUB(date_col, INTERVAL 1 MONTH) AS last_month,
    date_col + INTERVAL 1 DAY AS tomorrow_alt,
    date_col - INTERVAL 1 DAY AS yesterday_alt
FROM events;

-- Date arithmetic with NOW
SELECT 
    NOW() AS now,
    NOW() + INTERVAL 1 HOUR AS in_1_hour,
    NOW() - INTERVAL 1 DAY AS yesterday,
    DATE_ADD(NOW(), INTERVAL 1 HOUR) AS in_1_hour_2;

-- Current date and time
SELECT 
    NOW() AS current_datetime,
    CURDATE() AS current_date,
    CURTIME() AS current_time,
    UTC_TIMESTAMP() AS utc_timestamp,
    UNIX_TIMESTAMP() AS unix_timestamp,
    CURRENT_TIMESTAMP() AS current_timestamp,
    SYSDATE() AS sysdate;

-- Date only
SELECT 
    DATE(NOW()) AS date_only,
    TIME(NOW()) AS time_only,
    YEAR(NOW()) AS current_year,
    MONTH(NOW()) AS current_month,
    DAY(NOW()) AS current_day;

-- String concatenation
SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name,
    CONCAT_WS(' ', first_name, last_name) AS full_name_ws,
    CONCAT(first_name, ' ', last_name, ' (', email, ')') AS detailed_info
FROM users;

-- Concatenation with NULL handling
SELECT 
    CONCAT(first_name, ' ', COALESCE(middle_name, ''), ' ', last_name) AS full_name,
    CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name_ws
FROM users;

-- Group concatenation
SELECT 
    department_id,
    GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS employees
FROM employees
GROUP BY department_id;

-- String splitting using SUBSTRING_INDEX
SELECT 
    'a,b,c,d,e' AS original,
    SUBSTRING_INDEX('a,b,c,d,e', ',', 1) AS first,
    SUBSTRING_INDEX('a,b,c,d,e', ',', 2) AS first_two,
    SUBSTRING_INDEX('a,b,c,d,e', ',', -1) AS last,
    SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c,d,e', ',', 3), ',', -1) AS third;

-- Splitting into rows using JSON (MySQL 8.0+)
SELECT 
    JSON_EXTRACT(
        JSON_ARRAY('a', 'b', 'c', 'd', 'e'),
        CONCAT('$[', n, ']')
    ) AS element
FROM (
    SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
) numbers;

-- String pattern matching using LIKE
SELECT * FROM users 
WHERE email LIKE '%@example.com';

SELECT * FROM users 
WHERE name LIKE 'A%';

SELECT * FROM users 
WHERE name LIKE '%son%';

-- Using REGEXP
SELECT * FROM users 
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+.[a-zA-Z]{2,}$';

SELECT * FROM users 
WHERE name REGEXP '^[A-Z]';

SELECT * FROM users 
WHERE phone REGEXP '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';

-- Using RLIKE (synonym for REGEXP)
SELECT * FROM users 
WHERE email RLIKE '^[a-z]+@[a-z]+\.[a-z]+$';

-- Substring extraction
SELECT 
    'Hello World' AS original,
    SUBSTR('Hello World', 1, 5) AS first_word,
    SUBSTRING('Hello World', 7, 5) AS second_word,
    SUBSTR('Hello World', 7) AS from_position_7,
    LEFT('Hello World', 5) AS left_5,
    RIGHT('Hello World', 5) AS right_5,
    SUBSTRING_INDEX('Hello World', ' ', 1) AS first_word_2,
    SUBSTRING_INDEX('Hello World', ' ', -1) AS last_word
FROM dual;

-- Extract from column
SELECT 
    email,
    SUBSTRING_INDEX(email, '@', 1) AS username,
    SUBSTRING_INDEX(email, '@', -1) AS domain
FROM users;

-- Case conversion
SELECT 
    UPPER('hello world') AS upper_case,
    LOWER('HELLO WORLD') AS lower_case,
    'hello world' AS original,
    UPPER(first_name) AS upper_name,
    LOWER(email) AS lower_email
FROM users;

-- INITCAP (MySQL 8.0+)
SELECT 
    INITCAP('hello world') AS title_case;

-- Custom INITCAP for older MySQL
SELECT 
    CONCAT(
        UPPER(SUBSTR('hello world', 1, 1)),
        LOWER(SUBSTR('hello world', 2))
    ) AS custom_initcap;

-- String cleaning
SELECT 
    '  hello  ' AS original,
    TRIM('  hello  ') AS trimmed,
    LTRIM('  hello') AS left_trimmed,
    RTRIM('hello  ') AS right_trimmed,
    TRIM(LEADING ' ' FROM '  hello') AS lead_trimmed,
    TRIM(TRAILING ' ' FROM 'hello  ') AS trail_trimmed,
    TRIM(BOTH ' ' FROM '  hello  ') AS both_trimmed;

-- Trimming specific characters
SELECT 
    TRIM(LEADING 'x' FROM 'xxhelloxx') AS trim_leading_x,
    TRIM(TRAILING 'x' FROM 'xxhelloxx') AS trim_trailing_x,
    TRIM(BOTH 'x' FROM 'xxhelloxx') AS trim_both_x;

-- Replace in string
SELECT 
    'Hello World' AS original,
    REPLACE('Hello World', 'World', 'MySQL') AS replaced,
    REPLACE('Hello World', 'o', '0') AS replace_o;

-- REGEXP_REPLACE (MySQL 8.0+)
SELECT 
    'Hello 123 World 456' AS original,
    REGEXP_REPLACE('Hello 123 World 456', '[0-9]+', '') AS remove_numbers,
    REGEXP_REPLACE('Hello 123 World 456', '[^0-9]+', ' ') AS extract_numbers,
    REGEXP_REPLACE('Hello World', '([A-Z])', '_\1') AS add_underscore;

-- Remove multiple spaces
SELECT 
    REGEXP_REPLACE('Hello   World', '\s+', ' ') AS normalize_spaces;

-- String length
SELECT 
    'Hello' AS str,
    LENGTH('Hello') AS bytes_length,
    CHAR_LENGTH('Hello') AS char_length,
    CHARACTER_LENGTH('Hello') AS char_length_2;

-- Unicode strings
SELECT 
    '你好' AS unicode_str,
    LENGTH('你好') AS bytes_length_unicode,
    CHAR_LENGTH('你好') AS char_length_unicode;

-- Length of column
SELECT 
    name,
    LENGTH(name) AS byte_length,
    CHAR_LENGTH(name) AS char_length
FROM users;

-- Find position of substring
SELECT 
    'Hello World' AS str,
    LOCATE('World', 'Hello World') AS locate_position,
    POSITION('World' IN 'Hello World') AS position_function,
    INSTR('Hello World', 'World') AS instr_function,
    LOCATE('o', 'Hello World', 5) AS locate_from_position,
    LOCATE('World', 'Hello World') AS locate_position;

-- Position in column
SELECT 
    email,
    LOCATE('@', email) AS at_position,
    LOCATE('.', email) AS dot_position
FROM users;

-- JSON aggregation (MySQL 5.7+)
SELECT 
    department_id,
    JSON_ARRAYAGG(name) AS employee_names,
    JSON_OBJECTAGG(id, name) AS employee_map
FROM employees
GROUP BY department_id;

-- JSON_ARRAY with grouping
SELECT 
    city,
    JSON_ARRAYAGG(name) AS users_in_city
FROM users
GROUP BY city;

-- JSON_OBJECT with grouping
SELECT 
    department_id,
    JSON_OBJECT(
        'department', department_id,
        'employees', JSON_ARRAYAGG(name),
        'count', COUNT(*)
    ) AS department_info
FROM employees
GROUP BY department_id;

-- JSON path queries
SELECT 
    JSON_EXTRACT(data, '$.name') AS name,
    JSON_EXTRACT(data, '$.age') AS age,
    data->'$.name' AS name_operator,
    data->>'$.name' AS name_operator_text,
    data->'$.address.city' AS city,
    JSON_EXTRACT(data, '$.hobbies[0]') AS first_hobby
FROM users_json;

-- Nested JSON path
SELECT 
    data->>'$.name' AS name,
    data->'$.address' AS address,
    data->'$.address.city' AS city,
    JSON_EXTRACT(data, '$.address.zip') AS zip
FROM users_json;

-- JSON array operations
SELECT 
    '["a","b","c"]' AS original,
    JSON_ARRAY_APPEND('["a","b","c"]', '$', 'd') AS append,
    JSON_ARRAY_INSERT('["a","b","c"]', '$[1]', 'x') AS insert,
    JSON_REMOVE('["a","b","c","d"]', '$[2]') AS remove,
    JSON_INSERT('["a","b","c"]', '$[3]', 'd') AS insert_2,
    JSON_REPLACE('["a","b","c"]', '$[1]', 'x') AS replace;

-- JSON array functions
SELECT 
    JSON_LENGTH('["a","b","c"]') AS array_length,
    JSON_CONTAINS('["a","b","c"]', '"b"') AS contains_b,
    JSON_SEARCH('["a","b","c"]', 'one', 'b') AS search_b;

-- JSON_TABLE (MySQL 8.0+)
SELECT *
FROM JSON_TABLE(
    '[{"name": "Alice", "age": 25}, {"name": "Bob", "age": 30}]',
    '$[*]' COLUMNS (
        name VARCHAR(100) PATH '$.name',
        age INT PATH '$.age'
    )
) AS jt;

-- JSON_TABLE with nested data
SELECT *
FROM JSON_TABLE(
    '{"users": [{"name": "Alice", "hobbies": ["reading", "gaming"]}]}',
    '$.users[*]' COLUMNS (
        name VARCHAR(100) PATH '$.name',
        hobbies JSON PATH '$.hobbies'
    )
) AS jt;

-- JSON validation (MySQL 5.7+)
SELECT 
    JSON_VALID('{"name": "Alice"}') AS valid_json,
    JSON_VALID('invalid') AS invalid_json;

-- JSON schema validation (MySQL 8.0+)
SELECT 
    JSON_SCHEMA_VALID(
        '{"type": "object", "properties": {"name": {"type": "string"}}}',
        '{"name": "Alice"}'
    ) AS valid_schema;

-- Using JSON_VALID in constraint
ALTER TABLE users
ADD CONSTRAINT check_json_valid
CHECK (JSON_VALID(data));

-- GROUP_CONCAT
SELECT 
    department_id,
    GROUP_CONCAT(name) AS employees,
    GROUP_CONCAT(name ORDER BY name) AS employees_sorted,
    GROUP_CONCAT(name ORDER BY name SEPARATOR '; ') AS employees_separated,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

-- GROUP_CONCAT with DISTINCT
SELECT 
    department_id,
    GROUP_CONCAT(DISTINCT title ORDER BY title) AS unique_titles
FROM employees
GROUP BY department_id;

-- GROUP_CONCAT with CONCAT
SELECT 
    department_id,
    GROUP_CONCAT(CONCAT(name, ' (', title, ')') SEPARATOR ', ') AS employees_info
FROM employees
GROUP BY department_id;

-- Random number generation
SELECT 
    RAND() AS random_float,
    RAND(123) AS random_seeded,
    FLOOR(RAND() * 100) AS random_int_0_99,
    FLOOR(RAND() * 100) + 1 AS random_int_1_100,
    ROUND(RAND() * 100) AS rounded_random;

-- Random order
SELECT * FROM users ORDER BY RAND() LIMIT 5;

-- Random sample (MySQL 8.0+)
SELECT * FROM users ORDER BY RAND() LIMIT 5;

-- Random sample using TABLESAMPLE (MySQL 8.0+)
SELECT * FROM users TABLESAMPLE SYSTEM(10);

-- Row number using ROW_NUMBER (MySQL 8.0+)
SELECT 
    *,
    ROW_NUMBER() OVER (ORDER BY id) AS row_num
FROM users;

-- Row number with partition
SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept
FROM employees;

-- Row number using user variables (older MySQL)
SELECT 
    *,
    @row_num := @row_num + 1 AS row_num
FROM users, (SELECT @row_num := 0) r
ORDER BY id;

-- Row number with reset
SELECT 
    *,
    @row_num := IF(@prev_dept = department_id, @row_num + 1, 1) AS row_num,
    @prev_dept := department_id
FROM employees
CROSS JOIN (SELECT @row_num := 0, @prev_dept := NULL) r
ORDER BY department_id, salary DESC;

-- Conditional aggregation
SELECT 
    department_id,
    COUNT(*) AS total_employees,
    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count,
    SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_count,
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_count,
    AVG(CASE WHEN status = 'active' THEN salary ELSE NULL END) AS avg_active_salary
FROM employees
GROUP BY department_id;

-- Conditional aggregation with multiple conditions
SELECT 
    department_id,
    SUM(CASE WHEN gender = 'M' AND status = 'active' THEN 1 ELSE 0 END) AS active_males,
    SUM(CASE WHEN gender = 'F' AND status = 'active' THEN 1 ELSE 0 END) AS active_females
FROM employees
GROUP BY department_id;

-- Filtered aggregation (MySQL 8.0+)
SELECT 
    department_id,
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE status = 'active') AS active_count,
    COUNT(*) FILTER (WHERE status = 'inactive') AS inactive_count,
    AVG(salary) FILTER (WHERE status = 'active') AS avg_active_salary
FROM employees
GROUP BY department_id;

-- Multiple filters
SELECT 
    department_id,
    COUNT(*) FILTER (WHERE gender = 'M') AS male_count,
    COUNT(*) FILTER (WHERE gender = 'F') AS female_count,
    AVG(salary) FILTER (WHERE gender = 'M') AS avg_male_salary,
    AVG(salary) FILTER (WHERE gender = 'F') AS avg_female_salary
FROM employees
GROUP BY department_id;

-- GREATEST and LEAST
SELECT 
    col1,
    col2,
    col3,
    GREATEST(col1, col2, col3) AS max_value,
    LEAST(col1, col2, col3) AS min_value
FROM mytable;

-- GREATEST with NULL handling
SELECT 
    col1,
    col2,
    col3,
    GREATEST(COALESCE(col1, 0), COALESCE(col2, 0), COALESCE(col3, 0)) AS max_with_default
FROM mytable;

-- Find max of values from different tables
SELECT 
    u.id,
    GREATEST(u.salary, COALESCE(b.bonus, 0)) AS total_compensation
FROM users u
LEFT JOIN bonuses b ON u.id = b.user_id;

-- Bitwise operations
-- Example table: permissions with bit flags
-- 1 = read, 2 = write, 4 = execute, 8 = delete

-- Check if flag is set
SELECT 
    permissions,
    (permissions & 1) AS can_read,
    (permissions & 2) AS can_write,
    (permissions & 4) AS can_execute,
    (permissions & 8) AS can_delete
FROM user_permissions;

-- Set flag
UPDATE user_permissions 
SET permissions = permissions | 4 
WHERE user_id = 1;

-- Clear flag
UPDATE user_permissions 
SET permissions = permissions & ~4 
WHERE user_id = 1;

-- Toggle flag
UPDATE user_permissions 
SET permissions = permissions ^ 4 
WHERE user_id = 1;

-- Check multiple flags
SELECT * FROM user_permissions 
WHERE (permissions & 3) = 3;  -- Has read AND write

-- Type conversion
-- CAST
SELECT 
    CAST('123' AS SIGNED) AS int_val,
    CAST('123.45' AS DECIMAL(10,2)) AS dec_val,
    CAST('2024-01-01' AS DATE) AS date_val,
    CAST(123 AS CHAR) AS char_val,
    CAST(123.45 AS UNSIGNED) AS unsigned_val;

-- CONVERT
SELECT 
    CONVERT('123', SIGNED) AS int_val,
    CONVERT('123.45', DECIMAL(10,2)) AS dec_val,
    CONVERT('2024-01-01', DATE) AS date_val,
    CONVERT(123, CHAR) AS char_val;

-- Converting binary
SELECT 
    CONVERT('Hello' USING utf8mb4) AS utf8_string,
    CAST('Hello' AS BINARY) AS binary_data;

-- Conditional UPDATE
-- Update with CASE
UPDATE employees 
SET salary = CASE 
    WHEN performance_rating = 5 THEN salary * 1.20
    WHEN performance_rating = 4 THEN salary * 1.15
    WHEN performance_rating = 3 THEN salary * 1.10
    WHEN performance_rating = 2 THEN salary * 1.05
    ELSE salary
END
WHERE status = 'active';

-- Update with IF
UPDATE employees 
SET status = IF(active_years > 5, 'senior', 'junior')
WHERE status = 'active';

-- Update with multiple conditions
UPDATE products 
SET price = CASE 
    WHEN category = 'electronics' THEN price * 0.90
    WHEN category = 'clothing' THEN price * 0.85
    WHEN category = 'books' THEN price * 0.80
    ELSE price
END;

-- INSERT ON DUPLICATE KEY UPDATE
INSERT INTO users (id, name, age, email) 
VALUES (1, 'Alice', 25, 'alice@example.com')
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    age = VALUES(age),
    email = VALUES(email),
    updated_at = NOW();

-- REPLACE (delete + insert)
REPLACE INTO users (id, name, age, email) 
VALUES (1, 'Alice', 25, 'alice@example.com');

-- INSERT with multiple rows
INSERT INTO users (id, name, age) 
VALUES 
    (1, 'Alice', 25),
    (2, 'Bob', 30),
    (3, 'Charlie', 35)
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    age = VALUES(age);

-- Bulk insert
INSERT INTO users (name, age, email) VALUES 
    ('Alice', 25, 'alice@example.com'),
    ('Bob', 30, 'bob@example.com'),
    ('Charlie', 35, 'charlie@example.com'),
    ('David', 28, 'david@example.com'),
    ('Eve', 32, 'eve@example.com');

-- INSERT FROM SELECT
INSERT INTO archive_users (id, name, age, email, archived_at)
SELECT id, name, age, email, NOW()
FROM users
WHERE created_at < '2023-01-01';

-- INSERT IGNORE
INSERT IGNORE INTO users (id, name, email) VALUES 
    (1, 'Alice', 'alice@example.com'),
    (2, 'Bob', 'bob@example.com');

-- Copy table structure
-- Copy structure only
CREATE TABLE new_users LIKE users;

-- Copy structure and data
CREATE TABLE users_backup AS SELECT * FROM users;

-- Copy structure with specific columns
CREATE TABLE users_small AS 
SELECT id, name, email FROM users WHERE 1 = 0;

-- Copy data to existing table
INSERT INTO users_backup SELECT * FROM users;

-- Copy with condition
CREATE TABLE active_users AS 
SELECT * FROM users WHERE status = 'active';

-- ALTER TABLE operations
-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN birth_date DATE AFTER email;
ALTER TABLE users ADD COLUMN last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- Modify column
ALTER TABLE users MODIFY phone VARCHAR(15);
ALTER TABLE users MODIFY age INT NOT NULL DEFAULT 0;

-- Change column (rename + modify)
ALTER TABLE users CHANGE phone mobile VARCHAR(15);

-- Drop column
ALTER TABLE users DROP COLUMN phone;

-- Rename table
ALTER TABLE users RENAME TO app_users;
RENAME TABLE users TO app_users;

-- Add index
ALTER TABLE users ADD INDEX idx_name (name);
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- Drop index
ALTER TABLE users DROP INDEX idx_name;

-- Foreign key constraints
-- Create table with foreign key
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);

-- Add foreign key to existing table
ALTER TABLE orders 
ADD CONSTRAINT fk_orders_users 
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- Add foreign key with ON DELETE SET NULL
ALTER TABLE orders 
ADD CONSTRAINT fk_orders_products 
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL;

-- Drop foreign key
ALTER TABLE orders DROP FOREIGN KEY fk_orders_users;

-- Disable foreign key checks
SET FOREIGN_KEY_CHECKS = 0;
-- Operations
SET FOREIGN_KEY_CHECKS = 1;

-- CHECK constraint
-- Create table with CHECK
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT CHECK (age >= 0 AND age <= 150),
    email VARCHAR(255) CHECK (email LIKE '%@%'),
    status ENUM('active', 'inactive') CHECK (status IN ('active', 'inactive'))
);

-- Add CHECK to existing table
ALTER TABLE users ADD CONSTRAINT check_age CHECK (age >= 0 AND age <= 150);

-- Add CHECK with multiple conditions
ALTER TABLE users 
ADD CONSTRAINT check_user_data 
CHECK (
    age >= 0 AND 
    age <= 150 AND 
    email LIKE '%@%' AND
    status IN ('active', 'inactive')
);

-- Drop CHECK constraint
ALTER TABLE users DROP CHECK check_age;

-- Default values
-- Create table with defaults
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    age INT DEFAULT 0,
    city VARCHAR(100) DEFAULT 'Unknown'
);

-- Alter default value
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- Drop default
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- INSERT with defaults
INSERT INTO users (name) VALUES ('Alice');  -- Uses defaults for other columns

-- Explicitly use default
INSERT INTO users (name, status) VALUES ('Bob', DEFAULT);

-- Drop table
-- Drop table
DROP TABLE users;

-- Drop with IF EXISTS
DROP TABLE IF EXISTS users;

-- Drop multiple tables
DROP TABLE users, orders, products;

-- Truncate table (removes all rows)
TRUNCATE TABLE users;

-- Difference: DROP removes table structure, TRUNCATE keeps structure
-- DROP TABLE users; -- Removes table
-- TRUNCATE TABLE users; -- Removes all data but keeps table

-- Drop with CASCADE
DROP TABLE users CASCADE;

-- Drop with RESTRICT
DROP TABLE users RESTRICT;

-- Temporary tables
-- Create temporary table
CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

-- Temporary table from SELECT
CREATE TEMPORARY TABLE temp_active_users
SELECT * FROM users WHERE status = 'active';

-- Temporary table with indexes
CREATE TEMPORARY TABLE temp_orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    total DECIMAL(10,2),
    INDEX idx_user (user_id)
) ENGINE = MEMORY;

-- Use temporary table
INSERT INTO temp_users VALUES (1, 'Alice', 25);
SELECT * FROM temp_users;

-- Drop temporary table
DROP TEMPORARY TABLE temp_users;
DROP TEMPORARY TABLE IF EXISTS temp_users;

-- Temporary tables are session-specific and dropped automatically
-- on session end

-- Use temporary table for complex query
CREATE TEMPORARY TABLE temp_high_spenders
SELECT user_id, SUM(total) AS total_spent
FROM orders
GROUP BY user_id
HAVING total_spent > 1000;

SELECT u.name, u.email, t.total_spent
FROM users u
JOIN temp_high_spenders t ON u.id = t.user_id;

-- Show table information
-- DESCRIBE
DESCRIBE users;
DESC users;

-- SHOW CREATE TABLE
SHOW CREATE TABLE users;

-- SHOW TABLE STATUS
SHOW TABLE STATUS LIKE 'users';
SHOW TABLE STATUS FROM database_name;

-- SHOW COLUMNS
SHOW COLUMNS FROM users;
SHOW FULL COLUMNS FROM users;

-- SHOW INDEX
SHOW INDEX FROM users;

-- INFORMATION_SCHEMA
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users';
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users';
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 'users';

-- Database information
-- Current database
SELECT DATABASE() AS current_database;

-- Server version
SELECT VERSION() AS version;
SELECT @@version AS version_2;

-- Connection ID
SELECT CONNECTION_ID() AS connection_id;

-- Server variables
SHOW VARIABLES;
SHOW VARIABLES LIKE 'version%';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'character%';

-- Server status
SHOW STATUS;
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Questions';

-- Processlist
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- Engines
SHOW ENGINES;

-- Character sets
SHOW CHARACTER SET;

-- Collations
SHOW COLLATION;

-- Query optimization tips

-- 1. Use EXPLAIN to analyze queries
EXPLAIN SELECT * FROM users WHERE name = 'Alice';
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = 'Alice';

-- 2. Create indexes on frequently used columns
CREATE INDEX idx_users_name ON users(name);
CREATE INDEX idx_users_email_age ON users(email, age);

-- 3. Use LIMIT for pagination
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

-- 4. Avoid SELECT *
SELECT id, name, email FROM users WHERE active = 1;

-- 5. Use EXISTS instead of IN when possible
SELECT * FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 6. Use JOIN instead of subqueries
SELECT u.*, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

-- 7. Use UNION ALL instead of UNION when duplicates are acceptable
SELECT id, name FROM users
UNION ALL
SELECT id, name FROM archived_users;

-- 8. Use appropriate data types
-- VARCHAR for variable length, CHAR for fixed length
-- INT vs BIGINT based on range needed

-- 9. Use batch operations
INSERT INTO users (name, email) VALUES 
    ('Alice', 'alice@example.com'),
    ('Bob', 'bob@example.com');

-- 10. Use prepared statements for repeated queries
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
SET @id = 1;
EXECUTE stmt USING @id;