🗄️ SQL Essentials

-- Example table structure
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE
);

-- View all databases
SHOW DATABASES;

-- Use a database
USE company_db;

-- View all tables
SHOW TABLES;

-- Describe table structure
DESCRIBE employees;

-- Select all columns
SELECT * FROM employees;

-- Select specific columns
SELECT name, department, salary FROM employees;

-- Select with alias
SELECT name AS employee_name, salary AS annual_salary 
FROM employees;

-- Select distinct values
SELECT DISTINCT department FROM employees;

-- Count distinct
SELECT COUNT(DISTINCT department) FROM employees;

-- Calculated columns
SELECT name, salary, salary * 12 AS annual_salary 
FROM employees;

-- String concatenation
SELECT CONCAT(first_name, ' ', last_name) AS full_name 
FROM employees;

-- Conditional column (CASE)
SELECT name, salary,
    CASE 
        WHEN salary > 100000 THEN 'High'
        WHEN salary > 50000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_category
FROM employees;

-- Basic conditions
SELECT * FROM employees WHERE department = 'Sales';
SELECT * FROM employees WHERE salary > 75000;
SELECT * FROM employees WHERE hire_date >= '2020-01-01';

-- Multiple conditions (AND, OR)
SELECT * FROM employees 
WHERE department = 'Sales' AND salary > 60000;

SELECT * FROM employees 
WHERE department = 'Sales' OR department = 'Marketing';

-- BETWEEN
SELECT * FROM employees 
WHERE salary BETWEEN 50000 AND 100000;

-- IN operator
SELECT * FROM employees 
WHERE department IN ('Sales', 'Marketing', 'IT');

-- NOT IN
SELECT * FROM employees 
WHERE department NOT IN ('HR', 'Admin');

-- LIKE pattern matching
SELECT * FROM employees WHERE name LIKE 'John%';  -- Starts with John
SELECT * FROM employees WHERE name LIKE '%son';   -- Ends with son
SELECT * FROM employees WHERE name LIKE '%ann%';  -- Contains ann

-- IS NULL / IS NOT NULL
SELECT * FROM employees WHERE manager_id IS NULL;
SELECT * FROM employees WHERE email IS NOT NULL;

-- Sort ascending (default)
SELECT * FROM employees ORDER BY salary;
SELECT * FROM employees ORDER BY salary ASC;

-- Sort descending
SELECT * FROM employees ORDER BY salary DESC;

-- Sort by multiple columns
SELECT * FROM employees 
ORDER BY department ASC, salary DESC;

-- Sort by column position
SELECT name, salary FROM employees 
ORDER BY 2 DESC;  -- Sort by 2nd column (salary)

-- MySQL, PostgreSQL, SQLite
SELECT * FROM employees LIMIT 10;

-- Top 10 highest salaries
SELECT * FROM employees 
ORDER BY salary DESC 
LIMIT 10;

-- Skip first 10, get next 10 (pagination)
SELECT * FROM employees 
ORDER BY id 
LIMIT 10 OFFSET 10;

-- SQL Server / MS Access
SELECT TOP 10 * FROM employees;

-- COUNT - count rows
SELECT COUNT(*) FROM employees;
SELECT COUNT(manager_id) FROM employees;  -- Excludes NULLs
SELECT COUNT(DISTINCT department) FROM employees;

-- SUM - total
SELECT SUM(salary) FROM employees;

-- AVG - average
SELECT AVG(salary) FROM employees;
SELECT ROUND(AVG(salary), 2) AS avg_salary FROM employees;

-- MIN and MAX
SELECT MIN(salary) AS lowest_salary FROM employees;
SELECT MAX(salary) AS highest_salary FROM employees;

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

-- Group by single column
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

-- Group with multiple aggregates
SELECT department,
    COUNT(*) AS num_employees,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary
FROM employees
GROUP BY department;

-- Group by multiple columns
SELECT department, YEAR(hire_date) AS hire_year, COUNT(*) 
FROM employees
GROUP BY department, YEAR(hire_date);

-- Order grouped results
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

-- Filter groups
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

-- Having with multiple conditions
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000 AND COUNT(*) > 5;

-- WHERE vs HAVING
-- WHERE filters rows before grouping
-- HAVING filters groups after grouping
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE salary > 50000  -- Filter individual rows first
GROUP BY department
HAVING AVG(salary) > 75000  -- Then filter groups
ORDER BY avg_salary DESC;

-- Employees table
-- id | name      | dept_id
-- 1  | Alice     | 1
-- 2  | Bob       | 2
-- 3  | Charlie   | NULL

-- Departments table
-- id | dept_name
-- 1  | Sales
-- 2  | IT
-- 3  | HR

SELECT employees.name, departments.dept_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.id;

-- Result: Alice-Sales, Bob-IT (Charlie excluded, no dept match)

-- Using table aliases
SELECT e.name, d.dept_name, e.salary
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- Result: Alice-Sales, Bob-IT, Charlie-NULL (all employees included)

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

-- Result: Alice-Sales, Bob-IT, NULL-HR (all departments included)

SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

-- Result: All employees and all departments (with NULLs where no match)

SELECT e.name, d.dept_name, p.project_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
INNER JOIN projects p ON e.id = p.employee_id;

-- Find employees and their managers
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Employees with above-average salary
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Employees in Sales department
SELECT name
FROM employees
WHERE dept_id = (SELECT id FROM departments WHERE dept_name = 'Sales');

-- Using IN with subquery
SELECT name
FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE dept_name IN ('Sales', 'IT'));

SELECT name, salary,
    (SELECT AVG(salary) FROM employees) AS avg_salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

-- Derived table
SELECT dept_name, avg_salary
FROM (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) AS dept_averages
JOIN departments ON dept_averages.dept_id = departments.id
WHERE avg_salary > 70000;

-- Departments that have employees
SELECT dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.id
);

-- Departments with NO employees
SELECT dept_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.id
);

-- Insert single row
INSERT INTO employees (name, department, salary, hire_date)
VALUES ('John Doe', 'Sales', 65000, '2023-01-15');

-- Insert multiple rows
INSERT INTO employees (name, department, salary)
VALUES 
    ('Alice Smith', 'IT', 75000),
    ('Bob Jones', 'Marketing', 60000),
    ('Charlie Brown', 'HR', 55000);

-- Insert without specifying columns (must match ALL columns in order)
INSERT INTO employees 
VALUES (101, 'Jane Wilson', 'Finance', 70000, '2023-02-01');

-- Insert from another table
INSERT INTO employees_backup
SELECT * FROM employees WHERE department = 'Sales';

-- Insert with default values
INSERT INTO employees (name, department) 
VALUES ('Test User', 'IT');  -- Other columns will use default values or NULL

-- Update single column
UPDATE employees 
SET salary = 70000 
WHERE id = 5;

-- Update multiple columns
UPDATE employees 
SET salary = 80000, department = 'IT' 
WHERE name = 'John Doe';

-- Update with calculation
UPDATE employees 
SET salary = salary * 1.10 
WHERE department = 'Sales';

-- Update based on condition
UPDATE employees 
SET status = 'Senior'
WHERE YEAR(hire_date) < 2015;

-- Update with JOIN (MySQL)
UPDATE employees e
JOIN departments d ON e.dept_id = d.id
SET e.salary = e.salary * 1.05
WHERE d.dept_name = 'IT';

-- ⚠️ WARNING: Update without WHERE updates ALL rows!
-- UPDATE employees SET salary = 50000;  -- Don't do this!

-- Delete specific rows
DELETE FROM employees WHERE id = 10;

-- Delete with condition
DELETE FROM employees WHERE hire_date < '2010-01-01';

-- Delete with subquery
DELETE FROM employees 
WHERE dept_id IN (SELECT id FROM departments WHERE dept_name = 'Temp');

-- ⚠️ WARNING: Delete without WHERE deletes ALL rows!
-- DELETE FROM employees;  -- Don't do this!

-- TRUNCATE - faster way to delete all rows (can't be rolled back)
TRUNCATE TABLE employees;

-- Basic table creation
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department VARCHAR(50),
    salary DECIMAL(10, 2) DEFAULT 50000,
    hire_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- With foreign key
CREATE TABLE projects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    employee_id INT,
    start_date DATE,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

-- Create table from another table
CREATE TABLE employees_backup AS 
SELECT * FROM employees;

-- Add column
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

-- Drop column
ALTER TABLE employees DROP COLUMN phone;

-- Modify column
ALTER TABLE employees MODIFY COLUMN name VARCHAR(150);

-- Rename column
ALTER TABLE employees RENAME COLUMN name TO full_name;

-- Add constraint
ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email);

-- Add foreign key
ALTER TABLE projects 
ADD CONSTRAINT fk_employee 
FOREIGN KEY (employee_id) REFERENCES employees(id);

-- Drop table (permanent!)
DROP TABLE employees;

-- Drop if exists (no error if doesn't exist)
DROP TABLE IF EXISTS employees;

-- Create index
CREATE INDEX idx_department ON employees(department);

-- Create unique index
CREATE UNIQUE INDEX idx_email ON employees(email);

-- Composite index (multiple columns)
CREATE INDEX idx_dept_salary ON employees(department, salary);

-- View indexes
SHOW INDEX FROM employees;

-- Drop index
DROP INDEX idx_department ON employees;

-- UNION (removes duplicates)
SELECT name FROM employees WHERE department = 'Sales'
UNION
SELECT name FROM employees WHERE department = 'IT';

-- UNION ALL (keeps duplicates, faster)
SELECT name FROM employees WHERE salary > 70000
UNION ALL
SELECT name FROM managers WHERE salary > 70000;

-- ROW_NUMBER - assign unique row number
SELECT name, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

-- RANK - rank with gaps for ties
SELECT name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- DENSE_RANK - rank without gaps
SELECT name, salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

-- Running total
SELECT name, salary,
    SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees;

-- Moving average
SELECT name, salary,
    AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;

-- Basic CTE
WITH high_earners AS (
    SELECT name, department, salary
    FROM employees
    WHERE salary > 80000
)
SELECT department, COUNT(*) AS num_high_earners
FROM high_earners
GROUP BY department;

-- Multiple CTEs
WITH 
sales_dept AS (
    SELECT * FROM employees WHERE department = 'Sales'
),
high_performers AS (
    SELECT * FROM sales_dept WHERE salary > 70000
)
SELECT * FROM high_performers;

-- Recursive CTE (for hierarchical data)
WITH RECURSIVE employee_hierarchy AS (
    -- Anchor: start with top-level employees
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive: get subordinates
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

-- Create view
CREATE VIEW high_earners AS
SELECT name, department, salary
FROM employees
WHERE salary > 80000;

-- Use view like a table
SELECT * FROM high_earners;
SELECT department, AVG(salary) FROM high_earners GROUP BY department;

-- Drop view
DROP VIEW high_earners;

-- Begin transaction
START TRANSACTION;  -- or BEGIN;

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

-- Commit if successful
COMMIT;

-- Or rollback if error
ROLLBACK;

-- ACID properties: Atomicity, Consistency, Isolation, Durability

-- Common string functions
SELECT UPPER(name) FROM employees;           -- Uppercase
SELECT LOWER(name) FROM employees;           -- Lowercase
SELECT LENGTH(name) FROM employees;          -- String length
SELECT CONCAT(first_name, ' ', last_name) FROM employees;  -- Concatenate
SELECT SUBSTRING(name, 1, 3) FROM employees; -- Extract substring
SELECT TRIM(name) FROM employees;            -- Remove whitespace
SELECT REPLACE(name, 'John', 'Jane') FROM employees;  -- Replace text

-- Current date/time
SELECT NOW();                    -- Current datetime
SELECT CURDATE();                -- Current date
SELECT CURTIME();                -- Current time

-- Extract date parts
SELECT YEAR(hire_date) FROM employees;
SELECT MONTH(hire_date) FROM employees;
SELECT DAY(hire_date) FROM employees;
SELECT DAYNAME(hire_date) FROM employees;

-- Date arithmetic
SELECT DATE_ADD(hire_date, INTERVAL 1 YEAR) FROM employees;
SELECT DATE_SUB(hire_date, INTERVAL 6 MONTH) FROM employees;
SELECT DATEDIFF(NOW(), hire_date) AS days_employed FROM employees;

-- Format date
SELECT DATE_FORMAT(hire_date, '%Y-%m-%d') FROM employees;
SELECT DATE_FORMAT(hire_date, '%M %d, %Y') FROM employees;

-- COALESCE - return first non-NULL value
SELECT name, COALESCE(phone, email, 'No contact') AS contact
FROM employees;

-- IFNULL / ISNULL (MySQL)
SELECT name, IFNULL(manager_id, 0) AS manager
FROM employees;

-- NULLIF - return NULL if values are equal
SELECT name, NULLIF(department, 'Unknown') AS dept
FROM employees;