SQL Code Formatter
Format, polish, and document SQL code following Oracle Database 19 best practices with consistent style and readability.
Purpose
This skill provides comprehensive SQL code formatting rules and guidelines for Oracle Database 19. It enforces consistent code style, improves readability, and applies industry-standard formatting conventions to SQL queries, DDL statements, and DML operations.
When to Use This Skill
Use this skill when:
- Formatting or beautifying SQL code
- Working with .sql files that need polishing
- Improving SQL query readability
- Standardizing SQL code style across projects
- Documenting complex SQL queries
- Reviewing or refactoring existing SQL code
- Converting unformatted SQL to well-structured queries
Core Formatting Principles
These principles summarize the most common cases. The complete 13-rule specification lives in references/sql-formatting-rules.md; load it for full coverage and edge cases.
1. Case Conventions
- SQL Keywords: UPPERCASE (SELECT, FROM, WHERE, JOIN, etc.)
- Identifiers: lowercase (column names, table names, aliases)
- Consistency: Maintain consistent casing throughout queries
Example:
SELECT employee_id,
first_name,
last_name
FROM employees
WHERE department_id = 10;
2. Indentation and Alignment
- Use 4 spaces for indentation (no tabs)
- Align subsequent columns/conditions vertically with the first item
- Indent sub-queries one level deeper than parent query
- Align clause keywords (SELECT, FROM, WHERE) at consistent positions
3. Line Breaks and Structure
- First item on same line: Start first column/condition on the same line as the clause keyword
- New line for each item: Each subsequent column, condition, or table goes on a new line
- New line for clauses: Start each major clause (SELECT, FROM, WHERE, JOIN, GROUP BY, ORDER BY, HAVING) on a new line
- Vertical alignment: Align continuation items vertically
Example:
SELECT employee_id,
first_name,
last_name
FROM employees
WHERE department_id = 50
AND salary > 5000
AND commission_pct IS NOT NULL;
4. Operators and Spacing
- Single space on either side of operators (=, <, >, <=, >=, !=, ||, +, -, *, /)
- Single space after commas
- Single space around AS keyword for aliases
5. Common Table Expressions (CTEs)
- Begin with WITH keyword followed by CTE name
- Place AS ( on the same line as CTE name
- Close with ) on new line, aligned with WITH
- Separate multiple CTEs with comma and line break
Example:
WITH high_earners AS (
SELECT employee_id,
first_name,
last_name,
department_id
FROM employees
WHERE salary > 5000
),
department_summary AS (
SELECT department_id,
COUNT(*) AS employee_count
FROM high_earners
GROUP BY department_id
)
SELECT *
FROM department_summary;
6. JOIN Clauses
- Explicitly specify JOIN type (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN)
- Place JOIN and first ON condition on same line
- Indent JOIN to align with FROM clause
- Additional ON conditions go on new lines with AND keyword
Example:
SELECT e.employee_id,
e.first_name,
d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
AND e.salary > 5000
AND d.location_id = 1700;
7. CASE Expressions
- Start CASE with first WHEN on same line
- Each subsequent WHEN, all THEN, and ELSE on separate lines
- Align WHEN, THEN, and ELSE vertically
- Place END aligned with CASE
- Column alias on same line as END
Example:
SELECT CASE WHEN salary < 5000
THEN 'Low'
WHEN salary BETWEEN 5000 AND 10000
THEN 'Medium'
ELSE 'High'
END AS salary_category
FROM employees;
8. INSERT Statements
- List columns in parentheses, one per line (except first)
- Align columns vertically
- VALUES clause follows same pattern
Example:
INSERT INTO employees (
employee_id,
first_name,
last_name,
department_id
) VALUES (
208,
'Jane',
'Smith',
20
);
9. UPDATE Statements
- Place table name on same line or new line after UPDATE
- SET clause on new line
- Each column assignment on new line (except first)
- WHERE clause on new line
Example:
UPDATE employees
SET first_name = 'John',
last_name = 'Doe',
salary = 9000
WHERE employee_id = 207;
10. Comments
- Use
-- for single-line comments
- Use
/* comment */ for multi-line comments
- Add comments to explain complex logic only when explicitly requested
- Place comments above the code they describe
11. Subqueries and Derived Tables
- Keep the opening
( on the same line as the clause keyword (for example, FROM ()
- Indent the subquery body one level deeper so it sits to the right of the
(, and align its clause keywords on their own river
- Place the closing
) on a new line, aligned with the opening ( (unlike CTEs, where ) aligns with WITH)
- A short subquery may stay on one line, such as
IN (SELECT department_id FROM departments)
Example:
SELECT COUNT(*)
FROM (
SELECT employee_id
FROM employees
WHERE salary > 5000
ORDER BY employee_id
);
Bundled Resources
References (references/)
references/sql-formatting-rules.md - Complete formatting specification with all 13 rules and detailed examples
Load this reference when working with complex SQL formatting scenarios or when users need detailed rule explanations.
Examples (examples/)
examples/unformatted.sql - Before-formatting samples
examples/formatted.sql - The same statements after the formatting rules are applied
examples/complex-query.sql - Comprehensive query exercising all 13 rules
Compare the unformatted and formatted files to demonstrate the rules in practice.
How to Use This Skill
Basic SQL Formatting
When user provides unformatted SQL code:
- Identify SQL statement type (SELECT, INSERT, UPDATE, DELETE, CREATE, etc.)
- Apply core formatting principles from this skill
- Ensure keywords are UPPERCASE and identifiers are lowercase
- Apply proper indentation (4 spaces)
- Align columns, conditions, and clauses vertically
- Return formatted SQL code
Advanced Formatting
For complex queries with CTEs, joins, subqueries, and CASE expressions:
- Read
references/sql-formatting-rules.md for detailed specifications
- Apply all 13 formatting rules in sequence
- Pay special attention to vertical alignment
- Ensure consistent indentation at all nesting levels
- Validate that all examples in the reference are followed
Working with .sql Files
When user requests formatting of .sql files:
- Read the SQL file content
- Apply formatting rules to each statement
- Preserve existing comments unless reformatting is requested
- Write back formatted SQL to the file or display for review
- Ensure file encoding is preserved (UTF-8 recommended)
Key Information
- Database: Oracle Database 19
- Indentation: 4 spaces (no tabs)
- Keywords: UPPERCASE
- Identifiers: lowercase
- Line Length: No strict limit, but prefer readability
- File Extension: .sql
Best Practices
- Apply formatting consistently across all SQL files in a project
- Format SQL before committing to version control
- Use vertical alignment to improve readability
- Keep related conditions grouped with parentheses
- Add comments sparingly, only for complex logic
- Test formatted SQL to ensure functionality is preserved
- Preserve the logical structure and query optimization
Troubleshooting
Issue: Query becomes too long horizontally
- Break long expressions across multiple lines
- Use CTEs to simplify complex subqueries
- Split long CASE expressions into multiple lines
Issue: Unclear which columns belong to which clause
- Ensure consistent vertical alignment
- Use proper indentation (4 spaces per level)
- Verify first column/condition is on same line as clause keyword
Issue: Complex joins are hard to read
- Place each JOIN on its own line
- Align all JOINs with FROM clause
- Put additional ON conditions on separate lines with AND
Issue: Formatted SQL doesn't execute
- Verify formatting didn't introduce syntax errors
- Check that all parentheses are balanced
- Ensure string literals are properly quoted
- Test the query after formatting
Examples
See the examples/ directory for sample SQL files showing:
examples/unformatted.sql - Before formatting
examples/formatted.sql - After applying formatting rules
examples/complex-query.sql - Complex query with CTEs and joins
Additional Notes
- This skill focuses on formatting and style, not query optimization
- The formatting rules preserve Oracle SQL syntax and semantics
- For very large SQL files (>1000 lines), consider formatting sections separately
- Formatted SQL is easier to review, debug, and maintain
- Consistent formatting improves team collaboration and code reviews
1---2name: sql-formatter3description: This skill should be used when the user asks to format SQL code, polish SQL queries, improve SQL readability, or work with .sql files. Use when queries mention SQL formatting, code beautification, Oracle SQL, or database query polishing.4metadata:5 source: https://skillmd.com/skills/s2005/sql-formatter6---78# SQL Code Formatter910Format, polish, and document SQL code following Oracle Database 19 best practices with consistent style and readability.1112## Purpose1314This skill provides comprehensive SQL code formatting rules and guidelines for Oracle Database 19. It enforces consistent code style, improves readability, and applies industry-standard formatting conventions to SQL queries, DDL statements, and DML operations.1516## When to Use This Skill1718Use this skill when:1920- Formatting or beautifying SQL code21- Working with .sql files that need polishing22- Improving SQL query readability23- Standardizing SQL code style across projects24- Documenting complex SQL queries25- Reviewing or refactoring existing SQL code26- Converting unformatted SQL to well-structured queries2728## Core Formatting Principles2930These principles summarize the most common cases. The complete 13-rule specification lives in `references/sql-formatting-rules.md`; load it for full coverage and edge cases.3132### 1. Case Conventions3334- **SQL Keywords**: UPPERCASE (SELECT, FROM, WHERE, JOIN, etc.)35- **Identifiers**: lowercase (column names, table names, aliases)36- **Consistency**: Maintain consistent casing throughout queries3738Example:3940```sql41SELECT employee_id,42 first_name,43 last_name44 FROM employees45 WHERE department_id = 10;46```4748### 2. Indentation and Alignment4950- Use **4 spaces** for indentation (no tabs)51- Align subsequent columns/conditions vertically with the first item52- Indent sub-queries one level deeper than parent query53- Align clause keywords (SELECT, FROM, WHERE) at consistent positions5455### 3. Line Breaks and Structure5657- **First item on same line**: Start first column/condition on the same line as the clause keyword58- **New line for each item**: Each subsequent column, condition, or table goes on a new line59- **New line for clauses**: Start each major clause (SELECT, FROM, WHERE, JOIN, GROUP BY, ORDER BY, HAVING) on a new line60- **Vertical alignment**: Align continuation items vertically6162Example:6364```sql65SELECT employee_id,66 first_name,67 last_name68 FROM employees69 WHERE department_id = 5070 AND salary > 500071 AND commission_pct IS NOT NULL;72```7374### 4. Operators and Spacing7576- Single space on either side of operators (=, <, >, <=, >=, !=, ||, +, -, *, /)77- Single space after commas78- Single space around AS keyword for aliases7980### 5. Common Table Expressions (CTEs)8182- Begin with WITH keyword followed by CTE name83- Place AS ( on the same line as CTE name84- Close with ) on new line, aligned with WITH85- Separate multiple CTEs with comma and line break8687Example:8889```sql90WITH high_earners AS (91 SELECT employee_id,92 first_name,93 last_name,94 department_id95 FROM employees96 WHERE salary > 500097),98department_summary AS (99 SELECT department_id,100 COUNT(*) AS employee_count101 FROM high_earners102 GROUP BY department_id103)104SELECT *105 FROM department_summary;106```107108### 6. JOIN Clauses109110- Explicitly specify JOIN type (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN)111- Place JOIN and first ON condition on same line112- Indent JOIN to align with FROM clause113- Additional ON conditions go on new lines with AND keyword114115Example:116117```sql118SELECT e.employee_id,119 e.first_name,120 d.department_name121 FROM employees e122 INNER JOIN departments d ON e.department_id = d.department_id123 AND e.salary > 5000124 AND d.location_id = 1700;125```126127### 7. CASE Expressions128129- Start CASE with first WHEN on same line130- Each subsequent WHEN, all THEN, and ELSE on separate lines131- Align WHEN, THEN, and ELSE vertically132- Place END aligned with CASE133- Column alias on same line as END134135Example:136137```sql138SELECT CASE WHEN salary < 5000139 THEN 'Low'140 WHEN salary BETWEEN 5000 AND 10000141 THEN 'Medium'142 ELSE 'High'143 END AS salary_category144 FROM employees;145```146147### 8. INSERT Statements148149- List columns in parentheses, one per line (except first)150- Align columns vertically151- VALUES clause follows same pattern152153Example:154155```sql156INSERT INTO employees (157 employee_id,158 first_name,159 last_name,160 department_id161) VALUES (162 208,163 'Jane',164 'Smith',165 20166);167```168169### 9. UPDATE Statements170171- Place table name on same line or new line after UPDATE172- SET clause on new line173- Each column assignment on new line (except first)174- WHERE clause on new line175176Example:177178```sql179UPDATE employees180 SET first_name = 'John',181 last_name = 'Doe',182 salary = 9000183 WHERE employee_id = 207;184```185186### 10. Comments187188- Use `--` for single-line comments189- Use `/* comment */` for multi-line comments190- Add comments to explain complex logic only when explicitly requested191- Place comments above the code they describe192193### 11. Subqueries and Derived Tables194195- Keep the opening `(` on the same line as the clause keyword (for example, `FROM (`)196- Indent the subquery body one level deeper so it sits to the right of the `(`, and align its clause keywords on their own river197- Place the closing `)` on a new line, aligned with the opening `(` (unlike CTEs, where `)` aligns with `WITH`)198- A short subquery may stay on one line, such as `IN (SELECT department_id FROM departments)`199200Example:201202```sql203SELECT COUNT(*)204 FROM (205 SELECT employee_id206 FROM employees207 WHERE salary > 5000208 ORDER BY employee_id209 );210```211212## Bundled Resources213214### References (`references/`)215216- `references/sql-formatting-rules.md` - Complete formatting specification with all 13 rules and detailed examples217218Load this reference when working with complex SQL formatting scenarios or when users need detailed rule explanations.219220### Examples (`examples/`)221222- `examples/unformatted.sql` - Before-formatting samples223- `examples/formatted.sql` - The same statements after the formatting rules are applied224- `examples/complex-query.sql` - Comprehensive query exercising all 13 rules225226Compare the unformatted and formatted files to demonstrate the rules in practice.227228## How to Use This Skill229230### Basic SQL Formatting231232When user provides unformatted SQL code:2332341. Identify SQL statement type (SELECT, INSERT, UPDATE, DELETE, CREATE, etc.)2352. Apply core formatting principles from this skill2363. Ensure keywords are UPPERCASE and identifiers are lowercase2374. Apply proper indentation (4 spaces)2385. Align columns, conditions, and clauses vertically2396. Return formatted SQL code240241### Advanced Formatting242243For complex queries with CTEs, joins, subqueries, and CASE expressions:2442451. Read `references/sql-formatting-rules.md` for detailed specifications2462. Apply all 13 formatting rules in sequence2473. Pay special attention to vertical alignment2484. Ensure consistent indentation at all nesting levels2495. Validate that all examples in the reference are followed250251### Working with .sql Files252253When user requests formatting of .sql files:2542551. Read the SQL file content2562. Apply formatting rules to each statement2573. Preserve existing comments unless reformatting is requested2584. Write back formatted SQL to the file or display for review2595. Ensure file encoding is preserved (UTF-8 recommended)260261## Key Information262263- **Database:** Oracle Database 19264- **Indentation:** 4 spaces (no tabs)265- **Keywords:** UPPERCASE266- **Identifiers:** lowercase267- **Line Length:** No strict limit, but prefer readability268- **File Extension:** .sql269270## Best Practices271272- Apply formatting consistently across all SQL files in a project273- Format SQL before committing to version control274- Use vertical alignment to improve readability275- Keep related conditions grouped with parentheses276- Add comments sparingly, only for complex logic277- Test formatted SQL to ensure functionality is preserved278- Preserve the logical structure and query optimization279280## Troubleshooting281282### Issue: Query becomes too long horizontally283284- Break long expressions across multiple lines285- Use CTEs to simplify complex subqueries286- Split long CASE expressions into multiple lines287288### Issue: Unclear which columns belong to which clause289290- Ensure consistent vertical alignment291- Use proper indentation (4 spaces per level)292- Verify first column/condition is on same line as clause keyword293294### Issue: Complex joins are hard to read295296- Place each JOIN on its own line297- Align all JOINs with FROM clause298- Put additional ON conditions on separate lines with AND299300### Issue: Formatted SQL doesn't execute301302- Verify formatting didn't introduce syntax errors303- Check that all parentheses are balanced304- Ensure string literals are properly quoted305- Test the query after formatting306307## Examples308309See the `examples/` directory for sample SQL files showing:310311- `examples/unformatted.sql` - Before formatting312- `examples/formatted.sql` - After applying formatting rules313- `examples/complex-query.sql` - Complex query with CTEs and joins314315## Additional Notes316317- This skill focuses on formatting and style, not query optimization318- The formatting rules preserve Oracle SQL syntax and semantics319- For very large SQL files (>1000 lines), consider formatting sections separately320- Formatted SQL is easier to review, debug, and maintain321- Consistent formatting improves team collaboration and code reviews