SQL Formatter & Beautifier

SELECT col1, col2, col3
FROM employees
WHERE is_active = 1
  AND salary > 50000
ORDER BY salary DESC
LIMIT 10;

INSERT INTO employees
  (name, dept_id, salary, hire_date)
VALUES
  ('John Doe', 3, 75000, NOW()),
  ('Jane Smith', 2, 68000, NOW());

UPDATE employees
SET salary = salary * 1.10,
    updated_at = NOW()
WHERE dept_id = 3
  AND is_active = 1;

DELETE FROM employees
WHERE is_active = 0
  AND hire_date < '2020-01-01'
  AND dept_id NOT IN (1, 2);

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d
  ON e.dept_id = d.id
WHERE d.location = 'Mumbai';

SELECT e.name, p.project_name
FROM employees e
LEFT JOIN projects p
  ON e.id = p.assigned_to
WHERE p.id IS NULL;

SELECT e.name AS employee,
       m.name AS manager
FROM employees e
LEFT JOIN employees m
  ON e.manager_id = m.id
ORDER BY m.name;

SELECT name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE dept_id = 3
)
ORDER BY salary DESC;

WITH dept_stats AS (
  SELECT dept_id,
    COUNT(*) AS headcount,
    AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept_id
)
SELECT d.dept_name, ds.headcount, ds.avg_sal
FROM dept_stats ds
JOIN departments d ON ds.dept_id = d.id
ORDER BY ds.avg_sal DESC;

SELECT name, dept_id, salary,
  RANK() OVER (
    PARTITION BY dept_id
    ORDER BY salary DESC
  ) AS dept_rank,
  DENSE_RANK() OVER (
    ORDER BY salary DESC
  ) AS global_rank
FROM employees;

SELECT name, salary, hire_date,
  LAG(salary) OVER (
    PARTITION BY dept_id
    ORDER BY hire_date
  ) AS prev_salary,
  LEAD(salary) OVER (
    PARTITION BY dept_id
    ORDER BY hire_date
  ) AS next_salary
FROM employees;

SELECT name, salary,
  SUM(salary) OVER (
    ORDER BY hire_date
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW
  ) AS running_total,
  AVG(salary) OVER (
    PARTITION BY dept_id
  ) AS dept_avg
FROM employees;

SELECT dept_id,
  COUNT(*) AS total,
  AVG(salary) AS avg_sal,
  MAX(salary) AS max_sal,
  MIN(salary) AS min_sal,
  SUM(salary) AS payroll
FROM employees
WHERE is_active = 1
GROUP BY dept_id
HAVING COUNT(*) > 3
ORDER BY payroll DESC;

SELECT name, salary,
  CASE
    WHEN salary >= 100000 THEN 'Senior'
    WHEN salary >= 70000 THEN 'Mid'
    WHEN salary >= 40000 THEN 'Junior'
    ELSE 'Entry'
  END AS salary_band,
  COALESCE(manager_id, 0) AS mgr
FROM employees;

CREATE TABLE employees (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(255) UNIQUE NOT NULL,
  dept_id    INT REFERENCES departments(id),
  salary     DECIMAL(10,2) DEFAULT 0,
  is_active  BOOLEAN DEFAULT TRUE,
  hire_date  DATE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

ALTER TABLE employees
  ADD COLUMN phone VARCHAR(20),
  ADD COLUMN address TEXT,
  ADD INDEX idx_dept (dept_id),
  ADD INDEX idx_email (email),
  MODIFY COLUMN name VARCHAR(200) NOT NULL;

CREATE INDEX idx_salary
  ON employees (dept_id, salary DESC);

CREATE UNIQUE INDEX idx_email
  ON employees (email)
  WHERE is_active = 1;

BEGIN TRANSACTION;

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

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

COMMIT;

-- Oracle 11g and below
SELECT * FROM employees
WHERE ROWNUM <= 10
ORDER BY salary DESC;

-- Oracle 12c+
SELECT * FROM employees
ORDER BY salary DESC
FETCH NEXT 10 ROWS ONLY;

SELECT id, name, manager_id,
  LEVEL AS depth,
  SYS_CONNECT_BY_PATH(name, '/') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id
ORDER SIBLINGS BY name;

MERGE INTO target_table t
USING source_table s
  ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.name = s.name,
             t.salary = s.salary
WHEN NOT MATCHED THEN
  INSERT (id, name, salary)
  VALUES (s.id, s.name, s.salary);

SELECT name,
  NVL(phone, 'N/A') AS phone,
  NVL2(manager_id, 'Has Manager', 'No Manager') AS mgr_status,
  DECODE(dept_id,
    1, 'Engineering',
    2, 'Design',
    3, 'DBA',
    'Other') AS dept_name
FROM employees;