What is SQL? Explain DDL, DML, DCL, and TCL with examples.

-- CREATE — create a new table
CREATE TABLE employees (
    id         INT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary     DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ALTER — modify existing table
ALTER TABLE employees ADD COLUMN email VARCHAR(100);
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12, 2);
ALTER TABLE employees DROP COLUMN email;

-- DROP — permanently delete table + data
DROP TABLE employees;

-- TRUNCATE — delete all rows but keep structure (faster than DELETE)
TRUNCATE TABLE employees;

-- RENAME
RENAME TABLE employees TO staff;

-- INSERT — add new rows
INSERT INTO employees (id, name, department, salary)
VALUES (1, 'Alice', 'QA', 95000.00);

-- INSERT multiple rows
INSERT INTO employees (id, name, department, salary) VALUES
(2, 'Bob',     'Dev',  120000.00),
(3, 'Charlie', 'QA',    85000.00),
(4, 'Diana',   'Dev',  135000.00);

-- UPDATE — modify existing rows
UPDATE employees SET salary = 100000 WHERE id = 1;
UPDATE employees SET salary = salary * 1.10 WHERE department = 'QA';

-- DELETE — remove rows
DELETE FROM employees WHERE id = 3;
DELETE FROM employees WHERE department = 'QA' AND salary < 80000;

-- SELECT — read data
SELECT name, salary FROM employees WHERE department = 'QA';

-- GRANT — give permission
GRANT SELECT, INSERT ON employees TO 'qa_user'@'localhost';
GRANT ALL PRIVILEGES ON testdb.* TO 'admin'@'%';

-- REVOKE — remove permission
REVOKE INSERT ON employees FROM 'qa_user'@'localhost';

BEGIN;  -- or START TRANSACTION

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

COMMIT;    -- save both changes permanently
-- or
ROLLBACK;  -- undo both changes if something went wrong

-- SAVEPOINT — partial rollback
BEGIN;
INSERT INTO orders VALUES (101, 'Alice', 500);
SAVEPOINT order_placed;

INSERT INTO payments VALUES (201, 101, 500);
-- Something went wrong — rollback only payment, keep order
ROLLBACK TO SAVEPOINT order_placed;

COMMIT;