MySQL Crash Course

-- #1

 Windows / macOS Installation
  1. Download from: https://dev.mysql.com/downloads/installer/
  2. Run the installer and choose Developer Default.
  3. Set a root password when prompted.
  4. Install MySQL Workbench (optional but helpful GUI)
 

 Linux (Ubuntu) Installation
 Follow these steps to install MySQL and create a user

-- Step 1: Update Package Index

   sudo apt update
 
-- Step 2: Install MySQL Server

   sudo apt install mysql-server

-- Step 3: Secure the Installation

   sudo mysql_secure_installation

-- Choose your options (yes to most)

-- Step 4: Create a User 'rootuser'@'localhost

-- Log into MySQL:
 sudo MySQL

 CREATE USER 'rootuser'@'localhost' IDENTIFIED BY 'password';
 GRANT ALL PRIVILEGES ON *.* TO 'rootuser'@'localhost' WITH GRANT OPTION;
 FLUSH PRIVILEGES;
 EXIT;
 

-- Step 5: Test Login

 mysql -u rootuser -p

 Enter the password: password



Create Database:

CREATE DATABASE database_name;


Use Database:

USE database_name;


Drop Database:

DROP DATABASE database_name;

Other

SHOW DATABASES;

SHOW TABLES;


-- #2

-- Creating our First Table

-- Syntax:-
USE db_name;

 CREATE TABLE table_name ( 
   column_name1 datatype constraint,
   column_name2 datatype constraint,
   column_name2 datatype constraint
 )

-- Example:-

USE my_db;

 CREATE TABLE users (
    id    INT AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    age   INT
);


-- Insert Data

-- Syntax:-

INSERT INTO table_name (col1, col2, col3)
VALUES (val1, val2, val3);

-- Example:-

INSERT INTO users (name, email, age)
VALUES ('John', 'john@gmail.com', 25);

INSERT INTO users (name, email, age)
VALUES ('Set', 'seth@gmail.com', 30),
       ('roman', 'roman@gmail.com', 28);


-- Describe (Show Table Schema)

-- Syntax:-
DESCRIBE table_name;

 -- Or --

DESC table_name;


-- Example:-

DESCRIBE users;

 -- Or --

DESC users;


-- Select Data

-- Syntax:-

SELECT * FROM table_name

SELECT column1, column2 FROM table_name


-- Example:-

SELECT * FROM users;                    -- all columns

SELECT name, email FROM users;          -- specific columns


-- Update Data

-- Syntax:-

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

-- Example:-

UPDATE users SET age = 26 WHERE id = 1;


-- Delete Data

-- Syntax:-

DELETE table_name WHERE condition;

-- Example:-

DELETE users WHERE id = 1;


-- Truncate Table

-- Syntax:-

TRUNCATE TABLE table_name;


-- Example:-

TRUNCATE TABLE users;


-- Alter Table

-- Syntax:-

ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name MODIFY column_name new_datatype;
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
ALTER TABLE table_name DROP COLUMN column_name;


-- Example:-

ALTER TABLE users ADD phone VARCHAR(15);
ALTER TABLE users MODIFY age SMALLINT;
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users DROP COLUMN phone;


-- Rename Table

-- Syntax:-

RENAME TABLE old_name TO new_name;


-- Example:-

RENAME TABLE users TO customers;


-- Drop Table

-- Syntax:-

DROP TABLE table_name;


-- Example:-

DROP TABLE users;



-- #3

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    age INT,
    balance INT,
    city VARCHAR(100),
    status VARCHAR(100) DEFAULT 'active'
);

INSERT INTO users (name, email, age, balance, status, city) VALUES
('Alice Johnson', 'alice@example.com', 28, 1500, 'active', 'New York'),
('Bob Smith', 'bob@example.com', 34, 2200, 'inactive', 'Los Angeles'),
('Carol Davis', 'carol@example.com', 22, 800, 'active', 'Chicago'),
('David Wilson', 'david@example.com', 45, 3400, 'active', 'New York'),
('Eva Brown', 'eva@example.com', 31, 500, 'suspended', 'Los Angeles'),
('Frank Miller', 'frank@example.com', 29, 1750, 'active', 'Chicago'),
('Grace Lee', 'grace@example.com', 27, 2600, 'inactive', 'New York'),
('Henry Taylor', 'henry@example.com', 39, 4000, 'active', 'Los Angeles'),
('Ivy Anderson', 'ivy@example.com', 24, 1200, 'pending', 'Chicago'),
('Jack Thomas', 'jack@example.com', 33, 3100, 'active', 'New York');


-- Basic Select

-- Syntax:

 SELECT column1, column2, ...  FROM table_name;

-- Example:

SELECT * FROM users;              
SELECT name, email FROM users;    -- select all columns-- select specific columns

SELECT with WHERE (Filtering Data)

-- Syntax:

 SELECT column1, column2 FROM table_name WHERE condition;

-- Example:

 SELECT * FROM users WHERE age > 25;
 SELECT * FROM users WHERE name = 'Waseem';

-------- Where Clause In Detail



-- BETWEEN (Range Check)

-- Syntax:

 SELECT * FROM table_name WHERE column BETWEEN value1 AND value2;

 -- Example:

 SELECT * FROM users WHERE age BETWEEN 20 AND 30;


-- IN (Multiple Values)

-- Syntax:

SELECT * FROM table_name 

-- WHERE column IN (value1, value2, value3);

-- Example:
SELECT * FROM users WHERE age IN (20, 30);


-- LIKE (Pattern Matching)

-- Syntax:

SELECT * FROM table_name WHERE column LIKE 'pattern';

-- Example:

SELECT * FROM users WHERE name LIKE 'W%'; 
    
-- starting with W

 SELECT * FROM users WHERE email LIKE '%@gmail.com';  -- emails 

-- ending with gmail

 SELECT * FROM users WHERE name LIKE '_a%';   'a'-- names -- 2nd letter is

 
-- IS NULL / IS NOT NULL

 -- Syntax:

 SELECT * FROM table_name WHERE column IS NULL;
 SELECT * FROM table_name WHERE column IS NOT NULL;

-- Example:

SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;


-- ORDER BY (Sorting)

-- Syntax:

SELECT * FROM table_name ORDER BY column ASC;  -- ascending
SELECT * FROM table_name ORDER BY column DESC; -- descending

-- Example:

SELECT * FROM users ORDER BY name ASC;
SELECT * FROM users ORDER BY age DESC;

-- LIMIT (Restrict Rows)

-- Syntax:

SELECT * FROM table_name LIMIT number;
SELECT * FROM table_name LIMIT offset, count;

-- Example:

SELECT * FROM users LIMIT 5;        
SELECT * FROM users LIMIT 2, 3;     -- first 5 rows-- skip 2, then take 3 rows


-- DISTINCT (Unique Values)

-- Syntax:

SELECT DISTINCT column FROM table_name;

-- Example:

SELECT DISTINCT age FROM users;


-- GROUP By & Having

 -- GROUP BY: Used to group rows that have the same values in specified columns into summary rows (like totals or counts).


 -- HAVING: Used to filter groups after aggregation (similar to WHERE, but for groups).

-- Syntax:

SELECT column_name, AGGREGATE_FUNCTION(column_name) FROM table_name  WHERE condition GROUP BY column_name HAVING condition_on_aggregate;

-- Example:

SELECT city, COUNT(*) AS total_users FROM users GROUP BY city; -- count users city wise

SELECT city, COUNT(*) AS total_users FROM users GROUP BY city HAVING COUNT(*) > 1;


-- #4

-- We will use this table

 CREATE TABLE users (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(50),
 country VARCHAR(50),
 age INT,
 salary DECIMAL(10,2),
 created_at DATE
 );

 INSERT INTO users (name, country, age, salary, created_at) VALUES
 ('Alice', 'India', 25, 50000, '2023-01-15'),
 ('Bob', 'India', 30, 60000, '2023-02-10'),
 ('Charlie', 'USA', 28, 70000, '2023-02-20'),
 ('David', 'USA', 35, 80000, '2023-03-05'),
 ('Eve', 'UK', 40, 55000, '2023-03-15'),
 ('Frank', 'India', 45, 65000, '2023-04-01'),
 ('Grace', 'UK', 29, 72000, '2023-04-10');

COUNT()

 Example: SELECT COUNT(*) FROM users; 
   
Example:

Group By:- GROUP BY is a SQL clause used to group rows that 
have the same values in one or more columns into summary rows.

 SELECT country, COUNT(*) AS total_users FROM users GROUP BY country;

SUM()

 Example: SELECT SUM(salary) FROM users;

 Example: SELECT country, SUM(salary) AS total_salary FROM users GROUP BY country;

AVG()

 Example: SELECT AVG(salary) FROM users;

MIN()

  Example: SELECT MIN(salary) FROM users;

MAX()
 
Example: SELECT MAX(salary) FROM users;

CONCAT()

 Example: SELECT id, CONCAT(name, ' from ', country) AS user_info FROM users;

GROUP_CONCAT()

 Example: SELECT country, GROUP_CONCAT(name) AS users_list FROM users GROUP BY country;

IF()

 Example: SELECT name, salary, IF(salary > 70000, 'High', 'Normal') AS salary_level FROM users;

YEAR(), MONTH(), DAY()

 Example: SELECT name, YEAR(created_at) AS year, MONTH(created_at) AS month, DAY(created_at) AS day FROM users;

ADDDATE() / SUBDATE()

 Example: SELECT name, ADDDATE(created_at, INTERVAL 10 DAY) AS plus_10_days, SUBDATE(created_at, INTERVAL 10 DAY) AS minus_10_days FROM users;

DATE_FORMAT()

 Example: SELECT name, DATE_FORMAT(created_at, '%d-%M-%Y') AS formatted_date FROM users;

LENGTH() / CHAR_LENGTH()

 Example: SELECT name, LENGTH(name) AS byte_length, CHAR_LENGTH(name) AS char_length FROM users;

UPPER() / LOWER()

 Example: SELECT name, UPPER(name) AS upper_name, LOWER(name) AS lower_name FROM users;


-- #5

CREATE TABLE users (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(100),
 email VARCHAR(100)
 );

 INSERT INTO users (name, email) VALUES
 ('Alice', 'alice@example.com'),
 ('Bob', 'bob@example.com'),
 ('Charlie', 'charlie@example.com'),
 ('David', 'david@example.com');

 CREATE TABLE orders (
 id INT AUTO_INCREMENT PRIMARY KEY,
 user_id INT,
 product_name VARCHAR(100),
 order_date DATE,
 FOREIGN KEY (user_id) REFERENCES users(id)
 );

INSERT INTO orders (user_id, order_date) VALUES
(1, 'Domain & Hosting', '2025-10-01'),   
(2, 'UI Kit', '2025-10-02'),             
(1, 'Payment Gateway', '2025-10-03'),    
(4, 'Dashboard Tool', '2025-10-04');     


Inner Join
 
SELECT users.name, orders.product_name, orders.order_date FROM users INNER JOIN orders ON users.id = orders.user_id;


Left Join
 
SELECT users.name, orders.product_name, orders.order_date FROM users LEFT JOIN orders ON users.id = orders.user_id;


Right Join
 
SELECT orders.product_name, orders.order_date, users.name FROM orders RIGHT JOIN users ON orders.user_id = users.id;


Full Join
 
SELECT users.name, orders.product_name, orders.order_date FROM users LEFT JOIN orders ON users.id = orders.user_id

UNION ALL

SELECT users.name, orders.product_name, orders.order_date FROM users RIGHT JOIN orders ON users.id = orders.user_id;


Cross Join

SELECT users.name, orders.product_name, orders.order_date FROM users CROSS JOIN orders;


Self Join

ALTER TABLE users ADD manager_id INT;

UPDATE users SET manager_id = NULL WHERE id = 1; 
 
UPDATE users SET manager_id = 1 WHERE id IN (2, 3, 4); 

SELECT e.name AS Employee, m.name AS Manager FROM users e LEFT JOIN users m ON e.manager_id = m.id


-- #6

CREATE TABLE users (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(100),
 email VARCHAR(100)
 );

 INSERT INTO users (name, email) VALUES
 ('Alice', 'alice@example.com'),
 ('Bob', 'bob@example.com'),
 ('Charlie', 'charlie@example.com'),
 ('David', 'david@example.com');

 CREATE TABLE orders (
 id INT AUTO_INCREMENT PRIMARY KEY,
 user_id INT,
 product_name VARCHAR(100),
 order_date DATE,
 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
 );

INSERT INTO orders (user_id,  product_name, order_date) VALUES
(1, 'Domain & Hosting', '2025-10-01'),   
(2, 'UI Kit', '2025-10-02'),             
(1, 'Payment Gateway', '2025-10-03'),    
(4, 'Dashboard Tool', '2025-10-04');     


Cascade With Delete

 DELETE FROM users WHERE id = 1;

Cascade With Update

 UPDATE users SET id = 10 WHERE id = 2;


Subquery - Find all users who placed at least one order:

SELECT name FROM users WHERE id IN (SELECT user_id FROM orders);


Subquery - Find users who have not placed any order:

SELECT name FROM users WHERE id NOT IN (SELECT user_id FROM orders);


Single Row Subquery - Find the user who placed the most recent order:

SELECT name FROM users WHERE id = (SELECT user_id FROM orders WHERE order_date = (SELECT MAX(order_date) FROM orders));


Multi Row Subquery - Find the user who placed the most recent order:

SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE order_date > '2025-10-02')
 
 

-- #7


 SET SESSION cte_max_recursion_depth = 100000;

 CREATE TABLE users (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(100),
 email VARCHAR(100)
 );

 INSERT INTO users (name, email)
 WITH RECURSIVE numbers AS (
 SELECT 1 AS n
 UNION ALL
 SELECT n + 1 FROM numbers WHERE n < 100000
 )

 SELECT 
 CONCAT('User', n),
 CONCAT('user', n, '@example.com')
 FROM numbers;


How To Use Indexes?

-- Create Index
-- Standard syntax

 CREATE INDEX idx_email ON users(email);

-- Alternative syntax using ALTER TABLE

 ALTER TABLE users ADD INDEX idx_email (email);

-- Show all indexes on table

 SHOW INDEXES FROM users;

-- Drop-- Single index from table

 ALTER TABLE users DROP INDEX idx_email;

-- Multi Indexes Drop

 ALTER TABLE users DROP INDEX idx_email, DROP INDEX idx_status_created, DROP INDEX idx_name;


-- #8


CREATE TABLE users (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(50),
 country VARCHAR(50),
 age INT,
 salary DECIMAL(10,2),
 created_at DATE
 );
 INSERT INTO users (name, country, age, salary, created_at) VALUES
 ('Alice', 'India', 25, 50000, '2023-01-15'),
 ('Bob', 'India', 30, 60000, '2023-02-10'),
 ('Charlie', 'USA', 28, 70000, '2023-02-20'),
 ('David', 'USA', 35, 80000, '2023-03-05'),
 ('Eve', 'UK', 40, 55000, '2023-03-15'),
 ('Frank', 'India', 45, 65000, '2023-04-01'),
 ('Grace', 'UK', 29, 72000, '2023-04-10');


Create View

 Syntax:CREATE VIEW view_name AS SELECT column1, column2, … FROM table_name WHERE condition;

 Example:- CREATE VIEW indian_users AS SELECT id, name, age, salary FROM users WHERE country = 'India';

 Test View: SELECT * FROM indian_users;


Show View

 Show All Views:- SHOW FULL TABLES WHERE Table_type = 'VIEW';

 Test View: SHOW CREATE VIEW indian_users;


Update View

 Syntax: CREATE OR REPLACE VIEW view_name AS SELECT column1, column2, … FROM  table_name WHERE condition;

 Example:- CREATE OR REPLACE VIEW indian_users AS SELECT id, name, age, salary FROM users WHERE country = 'India' AND salary > 55000;
          
                                       OR

          ALTER VIEW indian_users AS SELECT id, name, age, salary FROM users WHERE country = 'India' AND salary > 55000;


Drop View

 Syntax: DROP VIEW view_name; OR DROP VIEW IF EXISTS view_name;

 Example: DROP VIEW indian_users



View with Aggregation

 Example: CREATE VIEW country_salary_stats AS SELECT country, COUNT(*) AS total_users, AVG(salary) AS avg_salary FROM users GROUP BY country;

 Test View:- SELECT * FROM country_salary_stats;


Nested Views

 Example: CREATE VIEW high_salary AS SELECT * FROM users WHERE salary > 60000;

          CREATE VIEW high_salary_usa AS SELECT name, salary FROM high_salary WHERE country = 'USA';


 Test View:- SELECT * FROM high_salary_usa


-- #9 


CREATE TABLE users (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(50),
 country VARCHAR(50),
 age INT,
 salary DECIMAL(10,2),
 created_at DATE
 );

 INSERT INTO users (name, country, age, salary, created_at) VALUES
 ('Alice', 'India', 25, 50000, '2023-01-15'),
 ('Bob', 'India', 30, 60000, '2023-02-10'),
 ('Charlie', 'USA', 28, 70000, '2023-02-20'),
 ('David', 'USA', 35, 80000, '2023-03-05'),
 ('Eve', 'UK', 40, 55000, '2023-03-15'),
 ('Frank', 'India', 45, 65000, '2023-04-01'),
 ('Grace', 'UK', 29, 72000, '2023-04-10');



Simple Stored Procedure:-

 DELIMITER $$
 CREATE PROCEDURE GetUsersByCountry(IN input_country VARCHAR(50))
 BEGIN
 SELECT * FROM users WHERE country = input_country;
 END $$
 DELIMITER ;

 CALL GetUsersByCountry('India');


Insert Data With Stored Procedure: -

DELIMITER $$
CREATE PROCEDURE InsertUserByCountry(
    IN input_name VARCHAR(50),
    IN input_country VARCHAR(50),
    IN input_age INT,
    IN input_salary DECIMAL(10,2)
)
BEGIN
    INSERT INTO users (name, country, age, salary, created_at)
    VALUES (input_name, input_country, input_age, input_salary, CURDATE());
END $$
DELIMITER ;


CALL InsertUserByCountry('Rahul Sharma', 'India', 30, 55000.00);


IF Condition Stored Procedure:-

 DELIMITER $$
 CREATE PROCEDURE CheckSalaryLevel(IN emp_name VARCHAR(50))
 BEGIN
 DECLARE sal DECIMAL(10,2);
 SELECT salary INTO sal FROM users WHERE name = emp_name;
 IF sal >= 70000 THEN
 SELECT CONCAT(emp_name, ' has a High Salary') AS message;
 ELSE
 SELECT CONCAT(emp_name, ' has a Normal Salary') AS message;
 END IF;
 END $$
 DELIMITER ;

 CALL CheckSalaryLevel('India');



Out Parameter Stored Procedure:-

 DELIMITER $$
 CREATE PROCEDURE CountUsersByCountry(
 IN input_country VARCHAR(50),
 OUT total_users INT
 )
 BEGIN
 SELECT COUNT(*) INTO total_users
 FROM users
 WHERE country = input_country;
 END $$
 DELIMITER ;

 CALL CountUsersByCountry('India', @count);

 SELECT @count AS total_users;



Multi Out Parameter Stored Procedure:-

 DELIMITER $$
 CREATE PROCEDURE CountUsersByCountry(
 IN input_country VARCHAR(50),
 OUT total_users INT,
 OUT avg_salary DECIMAL(10, 2)
)
 BEGIN
 SELECT COUNT(*), AVG(salary) 
INTO total_users, avg_salary FROM users
 WHERE country = input_country;
 END $$
 DELIMITER ;

 CALL CountUsersByCountry('India', @count, @avg);

 SELECT @count AS total_users, @avg AS avg_salary;


Show Stored Procedure:-

 SHOW CREATE PROCEDURE CountUsersByCountry;


Drop Stored Procedure:-

 DROP PROCEDURE IF EXISTS CountUsersByCountry


-- #10

CREATE TABLE users (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(50),
 country VARCHAR(50),
 age INT,
 salary DECIMAL(10,2),
 created_at DATE
 );
 INSERT INTO users (name, country, age, salary, created_at) VALUES
 ('Alice', 'India', 25, 50000, '2023-01-15'),
 ('Bob', 'India', 30, 60000, '2023-02-10'),
 ('Charlie', 'USA', 28, 70000, '2023-02-20'),
 ('David', 'USA', 35, 80000, '2023-03-05'),
 ('Eve', 'UK', 40, 55000, '2023-03-15'),
 ('Frank', 'India', 45, 65000, '2023-04-01'),
 ('Grace', 'UK', 29, 72000, '2023-04-10');



Atomicity — All or Nothing

 START TRANSACTION;

 UPDATE users SET salary = salary + 5000 WHERE country = 'India';
 UPDATE users SET age = age + 1 WHERE id = 1;  -- If either update fails, BOTH rollback

 COMMIT;

 select * from users;



Consistency — Valid State to Valid State

 START TRANSACTION;

 -- Add a constraint: salary must be positive
 ALTER TABLE users ADD CONSTRAINT chk_salary CHECK(salary > 0);

 UPDATE users SET salary = -1000 WHERE id = 1;  -- This violates the constraint

 COMMIT; -- This will fail and rollback

 SELECT * FROM users;



Isolation — Concurrent Independence

-- Transaction 1 (User A)

 START TRANSACTION;

 SELECT name, salary FROM users WHERE id = 1;  -- Alice's salary: 50000-- User B modifies Alice's salary here...
 SELECT name, salary FROM users WHERE id = 1;  -- Still sees 50000 (isolation 
level dependent)

 COMMIT; -- Transaction 2 (User B) - Running simultaneously



-- Transaction 2 (User B) - Running simultaneously

 START TRANSACTION;

 UPDATE users SET salary = 55000 WHERE id = 1;
 SELECT name, salary FROM users WHERE id = 1;

 COMMIT;



Durability — Permanent Storage

 START TRANSACTION;

 UPDATE users SET country = 'Canada' WHERE id = 3;  -- Charlie moves to Canada

 DELETE FROM users WHERE id = 7;  -- Grace leaves the company

 INSERT INTO users (name, country, age, salary, created_at) VALUES ('Henry', 'India', 32, 62000, '2023-05-01');

 COMMIT;  -- Data is now permanently written to disk


-- #11

CREATE TABLE users (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(50),
 country VARCHAR(50),
 age INT,
 salary DECIMAL(10,2),
 created_at DATE
 );

 INSERT INTO users (name, country, age, salary, created_at) VALUES
 ('Alice', 'India', 25, 50000, '2023-01-15'),
 ('Bob', 'India', 30, 60000, '2023-02-10'),
 ('Charlie', 'USA', 28, 70000, '2023-02-20'),
 ('David', 'USA', 35, 80000, '2023-03-05'),
 ('Eve', 'UK', 40, 55000, '2023-03-15'),
 ('Frank', 'India', 45, 65000, '2023-04-01'),
 ('Grace', 'UK', 29, 72000, '2023-04-10')


Auto-Uppercase Country (BEFORE INSERT)

 DELIMITER $$
 CREATE TRIGGER uppercase_country
 BEFORE INSERT ON users
 FOR EACH ROW
 BEGIN
 SET NEW.country = UPPER(NEW.country);
 END$$
 DELIMITER ;
 
INSERT INTO users (name, country, age, salary, created_at) VALUES ('John', 'india', 25, 50000, '2023-06-01');


Show & Drop Trigger

 SHOW TRIGGERS;

 DROP TRIGGER uppercase_country;



Minimum Age Check (BEFORE INSERT)

 DELIMITER $$
 CREATE TRIGGER uppercase_country
 BEFORE INSERT ON users
 FOR EACH ROW
 BEGIN
 IF NEW.age < 18 THEN
 SIGNAL SQLSTATE '45000'
 SET MESSAGE_TEXT = 'Age must be 18 or above';
 END IF;
 END$$
 DELIMITER ;
 
INSERT INTO users (name, country, age, salary, created_at) VALUES ('Tom', 'USA', 16, 30000, '2023-06-01')


Log Salary Changes (AFTER UPDATE)

-- First create log table
 CREATE TABLE salary_log (
 log_id INT AUTO_INCREMENT PRIMARY KEY,
 user_name VARCHAR(50),
 old_salary DECIMAL(10,2),
 new_salary DECIMAL(10,2),
 change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );
 DELIMITER $$
 CREATE TRIGGER log_salary_change
 AFTER UPDATE ON users
 FOR EACH ROW
 BEGIN
 IF OLD.salary != NEW.salary THEN
 INSERT INTO salary_log (user_name, old_salary, new_salary) VALUES (NEW.name, OLD.salary, NEW.salary);
 END IF;
 END$$
 DELIMITER ;
 
UPDATE users SET salary = 75000 WHERE name = 'Charlie';

-- Check log
SELECT * FROM salary_log;



Archive Deleted Users (AFTER DELETE)

-- First create log table
 CREATE TABLE deleted_users (
 id INT,
 name VARCHAR(50),
 country VARCHAR(50),
 deleted_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );
 DELIMITER $$
 CREATE TRIGGER backup_deleted_user
 AFTER DELETE ON users
 FOR EACH ROW
 BEGIN
 INSERT INTO deleted_users (id, name, country) VALUES (OLD.id, OLD.name, OLD.country);
 END$$
 DELIMITER ;


 DELETE FROM users WHERE name = 'Alice'; -- Check log
 SELECT * FROM deleted_users