SQL Basics in DBMS

-- CREATE: Create a new table
CREATE TABLE employees (
    emp_id      INT          PRIMARY KEY AUTO_INCREMENT,
    first_name  VARCHAR(50)  NOT NULL,
    last_name   VARCHAR(50)  NOT NULL,
    email       VARCHAR(100) UNIQUE NOT NULL,
    dept_id     INT,
    salary      DECIMAL(10,2) DEFAULT 0.00,
    hire_date   DATE,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- CREATE INDEX
CREATE INDEX idx_email ON employees(email);
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

-- ALTER: Modify existing table structure
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2) NOT NULL;
ALTER TABLE employees RENAME COLUMN phone TO mobile;
ALTER TABLE employees DROP COLUMN mobile;
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary >= 0);

-- DROP: Delete table (irreversible!)
DROP TABLE IF EXISTS temp_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 (first_name, last_name, email, dept_id, salary, hire_date)
VALUES ('Alice', 'Johnson', 'alice@example.com', 1, 75000.00, '2023-01-15');

-- Insert multiple rows
INSERT INTO employees (first_name, last_name, email, salary)
VALUES
    ('Bob', 'Smith', 'bob@example.com', 65000),
    ('Charlie', 'Brown', 'charlie@example.com', 70000),
    ('Diana', 'Prince', 'diana@example.com', 85000);

-- UPDATE: Modify existing rows
UPDATE employees SET salary = salary * 1.10 WHERE dept_id = 1;
UPDATE employees SET dept_id = 2, salary = 80000 WHERE emp_id = 3;

-- DELETE: Remove rows
DELETE FROM employees WHERE emp_id = 5;
DELETE FROM employees WHERE hire_date < '2020-01-01';

-- SELECT: Query data
SELECT * FROM employees;
SELECT first_name, last_name, salary FROM employees;
SELECT * FROM employees WHERE salary > 70000 ORDER BY salary DESC;
SELECT * FROM employees WHERE dept_id IN (1, 2, 3);
SELECT * FROM employees WHERE first_name LIKE 'A%';
SELECT * FROM employees WHERE salary BETWEEN 60000 AND 80000;
SELECT * FROM employees WHERE dept_id IS NOT NULL;

-- Aggregate functions
SELECT COUNT(*) AS total_employees FROM employees;
SELECT AVG(salary) AS avg_salary FROM employees;
SELECT MAX(salary) AS max_salary, MIN(salary) AS min_salary FROM employees;
SELECT SUM(salary) AS total_payroll FROM employees;

-- GROUP BY and HAVING
SELECT dept_id, COUNT(*) AS emp_count, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 2;

-- JOIN
SELECT e.first_name, e.last_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- Subquery
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- DCL: Data Control Language
-- GRANT: Give privileges to a user
GRANT SELECT, INSERT, UPDATE ON employees TO 'john'@'localhost';
GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'%';

-- REVOKE: Remove privileges
REVOKE INSERT ON employees FROM 'john'@'localhost';
REVOKE ALL PRIVILEGES ON mydb.* FROM 'admin'@'%';

-- TCL: Transaction Control Language
-- COMMIT: Save all changes permanently
START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
COMMIT; -- Both updates saved

-- ROLLBACK: Undo all changes since last COMMIT
START TRANSACTION;
DELETE FROM employees WHERE dept_id = 5;
-- Oops! Wrong department
ROLLBACK; -- Undo the delete

-- SAVEPOINT: Create a checkpoint within a transaction
START TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (1, 100.00);
SAVEPOINT after_order;

INSERT INTO order_items (order_id, product_id, qty) VALUES (LAST_INSERT_ID(), 5, 2);
-- Something went wrong with items
ROLLBACK TO SAVEPOINT after_order; -- Undo only items, keep order

COMMIT; -- Save the order (without items)