CREATE TRIGGER update_employee_salary
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    -- Perform actions when an employee's salary is updated
    -- For example, you can log the change in a separate table
    INSERT INTO salary_changes (employee_id, old_salary, new_salary, change_date)
    VALUES (NEW.employee_id, OLD.salary, NEW.salary, NOW());
END;

SELECT *
FROM (
    SELECT employee_id, department, salary
    FROM employee_salary
) AS src
PIVOT (
    SUM(salary)
    FOR department IN ('Sales', 'Marketing', 'Finance', 'IT')
) AS pivot_table;

DECLARE @employee_id INT;
DECLARE @salary INT;

DECLARE employee_cursor CURSOR FOR
SELECT employee_id, salary
FROM employees;

OPEN employee_cursor;

FETCH NEXT FROM employee_cursor INTO @employee_id, @salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Process the current row
    -- For example, update the salary based on some condition
    IF @salary < 50000
    BEGIN
        UPDATE employees
        SET salary = salary * 1.1
        WHERE employee_id = @employee_id;
    END;

    FETCH NEXT FROM employee_cursor INTO @employee_id, @salary;
END;

CLOSE employee_cursor;
DEALLOCATE employee_cursor;

-- Creating a Trigger Example

-- Create a table
CREATE TABLE employees (
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary INT
);

-- Create a table to store salary changes
CREATE TABLE salary_changes (
    employee_id INT,
    old_salary INT,
    new_salary INT,
    change_date DATE
);

-- Create a trigger that fires after an update on the employees table
CREATE TRIGGER update_employee_salary
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    -- Perform actions when an employee's salary is updated
    -- For example, you can log the change in the salary_changes table
    INSERT INTO salary_changes (employee_id, old_salary, new_salary, change_date)
    VALUES (NEW.employee_id, OLD.salary, NEW.salary, CURRENT_DATE());
END;

-- Insert sample data into the employees table
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (1, 'John', 'Doe', 50000),
       (2, 'Jane', 'Smith', 60000),
       (3, 'Michael', 'Johnson', 70000);

-- Update an employee's salary
UPDATE employees
SET salary = 55000
WHERE employee_id = 1;

-- Check the salary_changes table to see the recorded change
SELECT *
FROM salary_changes;

-- Creating a Pivot Example

-- Create a table
CREATE TABLE sales (
    product_id INT,
    region VARCHAR(50),
    year INT,
    sales_amount DECIMAL(10, 2)
);

-- Insert sample data into the sales table
INSERT INTO sales (product_id, region, year, sales_amount)
VALUES (1, 'North', 2019, 5000),
       (1, 'South', 2019, 6000),
       (1, 'North', 2020, 7000),
       (1, 'South', 2020, 8000),
       (2, 'North', 2019, 9000),
       (2, 'South', 2019, 10000),
       (2, 'North', 2020, 11000),
       (2, 'South', 2020, 12000);

-- Perform the pivot operation
SELECT *
FROM (
    SELECT product_id, region, sales_amount
    FROM sales
) AS src
PIVOT (
    SUM(sales_amount)
    FOR year IN (2019, 2020)
) AS pivot_table;

-- Using Aggregate Functions Example

-- Create a table
CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    order_total DECIMAL(10, 2)
);

-- Insert sample data into the orders table
INSERT INTO orders (order_id, customer_id, order_date, order_total)
VALUES (1, 1001, '2022-01-01', 100),
       (2, 1002, '2022-01-02', 200),
       (3, 1001, '2022-01-03', 300),
       (4, 1003, '2022-01-04', 400),
       (5, 1002, '2022-01-05', 500);

-- Count the number of rows
SELECT COUNT(*) AS total_orders
FROM orders;

-- Calculate the sum of the order_total column
SELECT SUM(order_total) AS total_sales
FROM orders;

-- Calculate the average of the order_total column
SELECT AVG(order_total) AS average_sales
FROM orders;

-- Find the minimum order_total value
SELECT MIN(order_total) AS min_sales
FROM orders;

-- Find the maximum order_total value
SELECT MAX(order_total) AS max_sales
FROM orders;

-- Using Cursors Example

-- Create a table
CREATE TABLE employees (
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary INT
);

-- Insert sample data into the employees table
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (1, 'John', 'Doe', 50000),
       (2, 'Jane', 'Smith', 60000),
       (3, 'Michael', 'Johnson', 70000);

-- Declare variables to store employee data
DECLARE @employee_id INT;
DECLARE @first_name VARCHAR(50);
DECLARE @last_name VARCHAR(50);
DECLARE @salary INT;

-- Declare the cursor
DECLARE employee_cursor CURSOR FOR
SELECT employee_id, first_name, last_name, salary
FROM employees;

-- Open the cursor
OPEN employee_cursor;

-- Fetch the first row
FETCH NEXT FROM employee_cursor INTO @employee_id, @first_name, @last_name, @salary;

-- Loop through the cursor and process each row
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Process the current row
    -- For example, you can print or manipulate the data
    PRINT 'Employee ID: ' + CAST(@employee_id AS VARCHAR) + ', Name: ' + @first_name + ' ' + @last_name + ', Salary: ' + CAST(@salary AS VARCHAR);

    -- Fetch the next row
    FETCH NEXT FROM employee_cursor INTO @employee_id, @first_name, @last_name, @salary;
END;

-- Close the cursor
CLOSE employee_cursor;

-- Deallocate the cursor
DEALLOCATE employee_cursor;

-- Creating a Trigger, Pivot, and Cursor Example

-- Create a table for employee data
CREATE TABLE employees (
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary INT
);

-- Create a table for salary changes
CREATE TABLE salary_changes (
    employee_id INT,
    old_salary INT,
    new_salary INT,
    change_date DATE
);

-- Insert sample data into the employees table
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (1, 'John', 'Doe', 50000),
       (2, 'Jane', 'Smith', 60000),
       (3, 'Michael', 'Johnson', 70000);

-- 1. Triggers: Track salary changes

-- Create a trigger that fires after an update on the employees table
CREATE TRIGGER update_employee_salary
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    -- Perform actions when an employee's salary is updated
    -- For example, log the change in the salary_changes table
    INSERT INTO salary_changes (employee_id, old_salary, new_salary, change_date)
    VALUES (NEW.employee_id, OLD.salary, NEW.salary, CURRENT_DATE());
END;

-- Update an employee's salary
UPDATE employees
SET salary = 55000
WHERE employee_id = 1;

-- Check the salary_changes table to see the recorded change
SELECT *
FROM salary_changes;

-- 2. Pivot: Aggregate and transform data

-- Create a table for sales data
CREATE TABLE sales (
    product_id INT,
    region VARCHAR(50),
    year INT,
    sales_amount DECIMAL(10, 2)
);

-- Insert sample data into the sales table
INSERT INTO sales (product_id, region, year, sales_amount)
VALUES (1, 'North', 2019, 5000),
       (1, 'South', 2019, 6000),
       (1, 'North', 2020, 7000),
       (1, 'South', 2020, 8000),
       (2, 'North', 2019, 9000),
       (2, 'South', 2019, 10000),
       (2, 'North', 2020, 11000),
       (2, 'South', 2020, 12000);

-- Perform the pivot operation
SELECT *
FROM (
    SELECT product_id, region, sales_amount
    FROM sales
) AS src
PIVOT (
    SUM(sales_amount)
    FOR year IN (2019, 2020)
) AS pivot_table;

-- 3. Cursors: Process data row by row

-- Declare variables to store employee data
DECLARE @employee_id INT;
DECLARE @first_name VARCHAR(50);
DECLARE @last_name VARCHAR(50);
DECLARE @salary INT;

-- Declare the cursor
DECLARE employee_cursor CURSOR FOR
SELECT employee_id, first_name, last_name, salary
FROM employees;

-- Open the cursor
OPEN employee_cursor;

-- Fetch the first row
FETCH NEXT FROM employee_cursor INTO @employee_id, @first_name, @last_name, @salary;

-- Loop through the cursor and process each row
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Process the current row
    -- For example, print the employee data
    PRINT 'Employee ID: ' + CAST(@employee_id AS VARCHAR) + ', Name: ' + @first_name + ' ' + @last_name + ', Salary: ' + CAST(@salary AS VARCHAR);

    -- Fetch the next row
    FETCH NEXT FROM employee_cursor INTO @employee_id, @first_name, @last_name, @salary;
END;

-- Close and deallocate the cursor
CLOSE employee_cursor;
DEALLOCATE employee_cursor;