### CREATE TEMPORARY TABLE

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/compress.txt

Example of creating a temporary table to hold configuration values.

```sql
CREATE TEMPORARY TABLE `PREFIX_configuration_tmp` ( `value` text )
```

--------------------------------

### SQL FOR Loop

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/highlight.html

A basic PL/SQL example demonstrating a FOR loop to iterate from 1 to 5 and print each number.

```SQL
BEGIN
  FOR i IN 1..5
  LOOP
    DBMS_OUTPUT.PUT_LINE(i);
  END LOOP;
END;
```

--------------------------------

### SQL Reserved Keywords as Identifiers

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/compress.txt

Shows an example of using SQL reserved keywords (like start, end, limit, case, when, then, else) as table or column identifiers by quoting them.

```SQL
SELECT t.id, t.start, t.end, t.end AS e2, t.limit, t.begin, t.case, t.when, t.then, t.else FROM t WHERE t.start = t.end
```

--------------------------------

### Basic SELECT Statements

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/compress.txt

Examples of basic SELECT statements, including selecting all columns, selecting specific columns with LIMIT, and selecting with conditions.

```sql
SELECT * FROM MyTable WHERE 1 = 2;
```

```sql
SELECT
```

```sql
SELECT Test FROM Test WHERE ( MyColumn = 1 )) AND ((( SomeOtherColumn = 2);
```

--------------------------------

### SQL Window Functions Examples

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/highlight.html

Provides examples of various SQL window functions including GROUP_CONCAT, AVG, MIN, MAX, LISTAGG, and their different framing clauses.

```sql
SELECT a,
    GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS) AS no_others,
    GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) AS current_row,
    GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) AS grp,
    GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) AS tie,
    GROUP_CONCAT(b, '.') FILTER (WHERE c != 'two') OVER (ORDER BY a) AS filtered,
    CONVERT(VARCHAR(20), AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg,
    AVG(starting_salary) OVER w2 AVG,
    MIN(starting_salary) OVER w2 MIN_STARTING_SALARY,
    MAX(starting_salary) OVER (w1 ORDER BY hire_date),
    LISTAGG(arg, ',') OVER (PARTITION BY part ORDER BY ord ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS LISTAGG_ROWS,
    LISTAGG(arg, ',') OVER (PARTITION BY part ORDER BY ord RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS LISTAGG_RANGE,
    MIN(Revenue) OVER (PARTITION BY DepartmentID ORDER BY RevenueYear ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS MinRevenueBeyond
FROM t1
```

--------------------------------

### Oracle PL/SQL Loop Example

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/compress.txt

A simple PL/SQL block in Oracle demonstrating a FOR loop to iterate and print numbers.

```SQL
BEGIN FOR i IN 1..5 LOOP DBMS_OUTPUT.PUT_LINE(i); END LOOP; END;
```

--------------------------------

### PL/SQL FOR LOOP Example

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format-highlight.html

A basic PL/SQL block demonstrating a FOR loop that iterates from 1 to 5 and prints each number.

```SQL
BEGIN
  FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
  END LOOP;
END;
```

--------------------------------

### PL/SQL Loop Example

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/clihighlight.txt

A simple PL/SQL block demonstrating a FOR loop that iterates from 1 to 5 and prints the loop counter using DBMS_OUTPUT.PUT_LINE.

```sql
BEGIN
  FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
  END LOOP;
END;
```

--------------------------------

### SQL Reserved Keywords as Identifiers

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format.txt

Illustrates how to use SQL reserved keywords (like start, end, limit, case, when, then, else) as column or table identifiers by quoting them.

```sql
SELECT
  t.id,
  t.start,
  t.end,
  t.end AS e2,
  t.limit,
  t.begin,
  t.case,
  t.when,
  t.then,
  t.else
FROM
  t
WHERE
  t.start = t.end
```

--------------------------------

### SQL UNION ALL Example

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/highlight.html

This snippet demonstrates combining results from two separate SELECT statements using UNION ALL.

```sql
SELECT * FROM sometable
UNION ALL
SELECT * FROM someothertable;
```

--------------------------------

### SQL Reserved Keywords as Identifiers

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/highlight.html

Demonstrates how to use SQL reserved keywords like 'start', 'end', 'limit', 'begin', 'when', 'then', 'else' as column or table identifiers by quoting them.

```SQL
SELECT
  t.id,
  t.start,
  t.end,
  t.end AS e2,
  t.limit,
  t.begin,
  t.case,
  t.when,
  t.then,
  t.else
FROM t
WHERE t.start = t.end
```

--------------------------------

### Select with Limit and Offset

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format.txt

Demonstrates the use of the LIMIT clause in SELECT statements. The first example limits the result set to 1 row. The second example skips the first row and retrieves the next 2 rows.

```sql
SELECT
  *
LIMIT
  1;

SELECT
  a,
  b,
  c,
  d
FROM
  e
LIMIT
  1, 2;
```

--------------------------------

### Table Locking and Unlocking

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/compress.txt

Demonstrates how to lock a table for writing and then unlock it.

```sql
LOCK TABLES `admin_assert` WRITE
```

```sql
UNLOCK TABLES
```

--------------------------------

### Example SQL Query for Compression

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/README.md

An example SQL query containing various types of comments (single-line, multi-line, inline) to demonstrate the `compress` method's functionality.

```sql
-- This is a comment
    SELECT
    /* This is another comment
    On more than one line */
    Id #This is one final comment
    as temp, DateCreated as Created FROM MyTable;

```

--------------------------------

### INSERT INTO Hook Table

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/compress.txt

Inserts a new hook with its name, title, and description into the hook table.

```sql
INSERT INTO `PREFIX_hook` (`name`, `title`, `description`, `position`) VALUES ('processCarrier', 'Carrier Process', NULL, 0)
```

--------------------------------

### INSERT INTO Configuration with Subquery

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/compress.txt

Illustrates inserting a value into the configuration table using a subquery to fetch a CMS ID.

```sql
INSERT INTO `PREFIX_configuration` (`name`, `value`, `date_add`, `date_upd`) VALUES ('PS_CONDITIONS_CMS_ID', IFNULL((SELECT `id_cms` FROM `PREFIX_cms` WHERE `id_cms` = 3), 0), NOW(), NOW())
```

--------------------------------

### SQL CREATE TABLE with Character Set and Engine

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/highlight.html

An example of a CREATE TABLE statement specifying the character set as utf8mb4 and the storage engine as InnoDB.

```SQL
CREATE TABLE t (c VARCHAR(20)) DEFAULT CHARACTER SET utf8mb4 ENGINE = InnoDB
```

--------------------------------

### SQL Offset and Limit Clauses

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format.txt

Provides examples of different SQL dialects for limiting and offsetting query results. It includes PostgreSQL's LIMIT/OFFSET, MSSQL's OFFSET/FETCH, and Oracle's OFFSET/FETCH FIRST syntax.

```sql
SELECT
  (
    SELECT
      *
    FROM
      T
    LIMIT
      5
    OFFSET
      10
  ) PostgreSQL_offset_10_limit_5,
  (
    SELECT
      *
    FROM
      T
    LIMIT
      5
    OFFSET
      10
    ROWS
    FETCH
      NEXT 5
    ROWS
      ONLY
  ) MSSQL_offset_10_limit_5,
  (
    SELECT
      *
    FROM
      T
    FETCH
      FIRST ROW ONLY
  ) oracle_limit_1,
  (
    SELECT
      *
    FROM
      T
    OFFSET
      10
    ROWS
  ) oracle_offset_10,
  (
    SELECT
      *
    FROM
      T
    ORDER BY
      I
    OFFSET
      10
    ROWS
    FETCH
      NEXT 5
    ROWS
      ONLY
  ) oracle_offset_10_limit_5,
FROM
  dual;
```

--------------------------------

### SQL with Common Table Expressions (CTEs)

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/compress.txt

Examples of using Common Table Expressions (CTEs) for recursive queries and joining multiple CTEs.

```sql
WITH cte AS (SELECT a, b FROM table), RECURSIVE fibonacci (n, fib_n, next_fib_n) AS ( SELECT 1, 0, 1 UNION ALL SELECT n + 1, next_fib_n, fib_n + next_fib_n FROM fibonacci WHERE n < 10 ) SELECT * FROM fibonacci;
```

```sql
WITH cte1 AS (SELECT a, b FROM table1), cte2 AS (SELECT c, d FROM table2) SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c;
```

--------------------------------

### SQL Parameter Placeholder

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/highlight.html

Demonstrates the use of a named parameter placeholder, often used in prepared statements.

```sql
SELECT :pdoParam;
```

--------------------------------

### SQL UPDATE for Configuration Setting

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/highlight.html

This snippet demonstrates updating a specific configuration value in the `PREFIX_configuration` table.

```sql
UPDATE `PREFIX_configuration` SET value = '6' WHERE name = 'PS_SEARCH_WEIGHT_PNAME'
```

--------------------------------

### SQL Type Casting

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format-highlight.html

Demonstrates a simple type casting operation in SQL.

```sql
SELECT
  1 :: text;
```

--------------------------------

### SQL SELECT with LIMIT Clause Variations

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/clihighlight.txt

Provides examples of using the LIMIT clause in SELECT statements, including a simple limit and a limit with an offset.

```sql
SELECT
  *
LIMIT
  1;

SELECT
  a,
  b,
  c,
  d
FROM
  e
LIMIT
  1, 2;

SELECT
  1,
  2,
  3
WHERE
  a IN (1, 2, 3, 4, 5)
  and b = 5;
```

--------------------------------

### Basic Select Statement

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format-highlight.html

A minimal SELECT statement querying 'Test' from 'Test'.

```sql
SELECT
  Test
FROM
  Test
WHERE
```

--------------------------------

### SQL SELECT with OFFSET and LIMIT

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format-highlight.html

Demonstrates a SELECT statement using both LIMIT and OFFSET to paginate results.

```sql
SELECT
  a,
  b,
  c,
  d
FROM
  e
LIMIT
  1, 2;
```

--------------------------------

### Create Temporary Table for Configuration

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/clihighlight.txt

Creates a temporary table named 'PREFIX_configuration_tmp' with a single column 'value' of type text.

```sql
CREATE TEMPORARY TABLE `PREFIX_configuration_tmp` (`value` text)
```

--------------------------------

### PL/SQL FOR LOOP

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format.txt

A PL/SQL block demonstrating a FOR loop to iterate from 1 to 5 and print each number using DBMS_OUTPUT.PUT_LINE.

```sql
BEGIN
  FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
  END LOOP;
END;
```

--------------------------------

### SET NAMES Statement

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/clihighlight.txt

A simple SQL statement to set the character set for the connection.

```sql
SET NAMES 'utf8';
```

--------------------------------

### SET Variable with Subquery

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/compress.txt

Demonstrates setting a user-defined variable using a subquery to retrieve a configuration value.

```sql
SET @defaultOOS = (SELECT value FROM `PREFIX_configuration` WHERE name = 'PS_ORDER_OUT_OF_STOCK')
```

--------------------------------

### SET NAMES Command

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format.txt

This command sets the character set for the database connection to 'utf8'.

```sql
SET
  NAMES 'utf8';
```

--------------------------------

### SQL Window Functions

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/highlight.html

Demonstrates the use of window functions with partitioning and ordering. Defines two windows, w1 and w2, with specific partitioning and ordering criteria.

```SQL
WINDOW w1 AS (PARTITION BY department, division), w2 AS (w1 ORDER BY hire_date);
```

--------------------------------

### SQL STRAIGHT_JOIN Syntax

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/compress.txt

Illustrates the use of STRAIGHT_JOIN in SQL, which forces the optimizer to join tables in the specified order.

```SQL
SELECT a FROM test STRAIGHT_JOIN test2 ON test.id = test2.id
```

--------------------------------

### Create Temporary Table

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/highlight.html

Defines a temporary table to hold configuration values, typically used for intermediate processing.

```sql
CREATE TEMPORARY TABLE `PREFIX_configuration_tmp` (
	`value` text
)
```

--------------------------------

### INSERT INTO Configuration Table

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/compress.txt

Shows how to insert multiple rows into a configuration table with various data types and functions like NOW().

```sql
INSERT INTO `PREFIX_configuration` (`name`, `value`, `date_add`, `date_upd`) VALUES ('PS_SPECIFIC_PRICE_PRIORITIES', 'id_shop;id_currency;id_country;id_group', NOW(), NOW()), ('PS_TAX_DISPLAY', 0, NOW(), NOW()), ('PS_SMARTY_FORCE_COMPILE', 1, NOW(), NOW()), ('PS_DISTANCE_UNIT', 'km', NOW(), NOW()), ('PS_STORES_DISPLAY_CMS', 0, NOW(), NOW()), ('PS_STORES_DISPLAY_FOOTER', 0, NOW(), NOW()), ('PS_STORES_SIMPLIFIED', 0, NOW(), NOW()), ('PS_STATSDATA_CUSTOMER_PAGESVIEWS', 1, NOW(), NOW()), ('PS_STATSDATA_PAGESVIEWS', 1, NOW(), NOW()), ('PS_STATSDATA_PLUGINS', 1, NOW(), NOW())
```

--------------------------------

### SQL STRAIGHT_JOIN

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/highlight.html

Shows the usage of STRAIGHT_JOIN in SQL, which forces the optimizer to join tables in the specified order.

```SQL
SELECT a FROM test STRAIGHT_JOIN test2 ON test.id = test2.id
```

--------------------------------

### SQL STRAIGHT_JOIN

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format.txt

Shows the use of STRAIGHT_JOIN in SQL, which forces the optimizer to join tables in the order specified.

```sql
SELECT
  a
FROM
  test
  STRAIGHT_JOIN test2 ON test.id = test2.id
```

--------------------------------

### SQL SELECT with Special Variable Syntax

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/clihighlight.txt

Demonstrates selecting variables using '@' prefix and quoted identifiers.

```sql
SELECT
  @ 
  and b;
```

--------------------------------

### Insert Configuration Data

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/highlight.html

Shows how to insert multiple rows of configuration data into a table, including current timestamps.

```sql
INSERT INTO `PREFIX_configuration` (`name`, `value`, `date_add`, `date_upd`) VALUES
('PS_SPECIFIC_PRICE_PRIORITIES', 'id_shop;id_currency;id_country;id_group', NOW(), NOW()),
('PS_TAX_DISPLAY', 0, NOW(), NOW()),
('PS_SMARTY_FORCE_COMPILE', 1, NOW(), NOW()),
('PS_DISTANCE_UNIT', 'km', NOW(), NOW()),
('PS_STORES_DISPLAY_CMS', 0, NOW(), NOW()),
('PS_STORES_DISPLAY_FOOTER', 0, NOW(), NOW()),
('PS_STORES_SIMPLIFIED', 0, NOW(), NOW()),
('PS_STATSDATA_CUSTOMER_PAGESVIEWS', 1, NOW(), NOW()),
('PS_STATSDATA_PAGESVIEWS', 1, NOW(), NOW()),
('PS_STATSDATA_PLUGINS', 1, NOW(), NOW())
```

--------------------------------

### Insert Configuration Settings

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format-highlight.html

Inserts multiple key-value pairs into the 'PREFIX_configuration' table, setting various application parameters such as specific price priorities, tax display, smarty compilation, distance unit, and store display options.

```sql
INSERT INTO `PREFIX_configuration` (
  `name`, `value`, `date_add`, `date_upd`
)
VALUES
  (
    'PS_SPECIFIC_PRICE_PRIORITIES',
    'id_shop;id_currency;id_country;id_group',
    NOW(),
    NOW()
  ),
  (
    'PS_TAX_DISPLAY',
    0,
    NOW(),
    NOW()
  ),
  (
    'PS_SMARTY_FORCE_COMPILE',
    1,
    NOW(),
    NOW()
  ),
  (
    'PS_DISTANCE_UNIT',
    'km',
    NOW(),
    NOW()
  ),
  (
    'PS_STORES_DISPLAY_CMS',
    0,
    NOW(),
    NOW()
  ),
  (
    'PS_STORES_DISPLAY_FOOTER',
    0,
    NOW(),
    NOW()
  ),
  (
    'PS_STORES_SIMPLIFIED',
    0,
    NOW(),
    NOW()
  ),
  (
    'PS_STATSDATA_CUSTOMER_PAGESVIEWS',
    1,
    NOW(),
    NOW()
  ),
  (
    'PS_STATSDATA_PAGESVIEWS',
    1,
    NOW(),
    NOW()
  ),
  (
    'PS_STATSDATA_PLUGINS',
    1,
    NOW(),
    NOW()
  )
```

--------------------------------

### UNION ALL Statement

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/clihighlight.txt

Illustrates a formatted SQL query using UNION ALL to combine results from two different tables.

```sql
SELECT
  *
FROM
  sometable
UNION ALL
SELECT
  *
FROM
  someothertable;
```

--------------------------------

### SQL Type Casting

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/compress.txt

Example of casting a value to a specific data type in SQL.

```sql
SELECT 1::text;
```

--------------------------------

### SQL Control Flow and Subqueries

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/compress.txt

Demonstrates SQL control flow statements like BEGIN/END and nested subqueries, along with function calls.

```sql
MY_NON_TOP_LEVEL_KEYWORD_FX_1(); MY_NON_TOP_LEVEL_KEYWORD_FX_2(); SELECT x FROM (SELECT 1 as x); MY_NON_TOP_LEVEL_KEYWORD_FX_3(); BEGIN MY_NON_TOP_LEVEL_KEYWORD_FX_4(); MY_NON_TOP_LEVEL_KEYWORD_FX_5(); END; BEGIN SELECT x FROM (SELECT 1 as x); MY_NON_TOP_LEVEL_KEYWORD_FX_6(); END;
```

--------------------------------

### Insert Stock Movement Reason Translations

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/highlight.html

Populates a table with translations for stock movement reasons in multiple languages.

```sql
INSERT INTO `PREFIX_stock_mvt_reason_lang` (`id_stock_mvt_reason`, `id_lang`, `name`) VALUES
(1, 1, 'Order'),
(1, 2, 'Commande'),
(2, 1, 'Missing Stock Movement'),
(2, 2, 'Mouvement de stock manquant'),
(3, 1, 'Restocking'),
(3, 2, 'Réassort')
```

--------------------------------

### SQL WHERE Clause with Self-Comparison

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/clihighlight.txt

An example of a SELECT statement with a WHERE clause that compares a column to itself, aliasing one instance for clarity.

```sql
SELECT
  t.id,
  t.start,
  t.end,
  t.end AS e2,
  t.limit,
  t.begin,
  t.case,
  t.when,
  t.then,
  t.else
FROM
  t
WHERE
  t.start = t.end
```

--------------------------------

### SELECT with LIMIT and Conditions

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/compress.txt

Demonstrates various SELECT syntaxes including LIMIT clauses, OFFSET, IN conditions, and arithmetic operations within WHERE clauses.

```sql
SELECT * LIMIT 1; SELECT a,b,c,d FROM e LIMIT 1, 2; SELECT 1,2,3 WHERE a in (1,2,3,4,5) and b=5;
```

```sql
SELECT count - 50 WHERE a-50 = b WHERE 1 and - 50 WHERE -50 = a WHERE a = -50 WHERE 1 - 50 WHERE 1 and -50;
```

```sql
SELECT @ and b;
```

```sql
SELECT @"weird variable name";
```

```sql
SELECT :pdoParam;
```

```sql
SELECT "no closing quote
```

```sql
SELECT [sqlserver] FROM [escap[e]]d style];
```

```sql
SELECT a FROM b LEFT OUTER JOIN c on (d=f);
```

--------------------------------

### SQL SELECT with PDO Parameter Syntax

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/clihighlight.txt

Illustrates the use of PDO parameter syntax (':pdoParam') in a SELECT statement.

```sql
SELECT
  :pdoParam;
```

--------------------------------

### SQL Pagination with LIMIT and OFFSET

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/clihighlight.txt

Demonstrates how to implement pagination in SQL using LIMIT and OFFSET clauses, common in databases like PostgreSQL.

```sql
SELECT
  (
    SELECT
      *
    FROM
      T
    LIMIT 5
    OFFSET 10
  ) PostgreSQL_offset_10_limit_5,
  (
    SELECT
      *
    FROM
      T
    LIMIT 5
    OFFSET 10
  )
```

--------------------------------

### SQL CREATE TABLE with Character Set and Engine

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format.txt

Demonstrates the SQL syntax for creating a table with specified column types, character set (utf8mb4), and storage engine (InnoDB).

```sql
CREATE TABLE t (
  c VARCHAR(20)
) DEFAULT CHARACTER SET utf8mb4 ENGINE = InnoDB
```

--------------------------------

### Select with Unclosed Quote

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format.txt

An example of a SELECT statement with an unclosed string literal, which would result in a syntax error.

```sql
SELECT
  "no closing quote
```

--------------------------------

### Database-Specific Offset and Limit Syntax

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/compress.txt

Demonstrates how to fetch data with specific offsets and limits across different SQL dialects, including PostgreSQL, MSSQL, and Oracle.

```SQL
SELECT ( SELECT * FROM T LIMIT 5 OFFSET 10 ) PostgreSQL_offset_10_limit_5, ( SELECT * FROM T LIMIT 5 OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY ) MSSQL_offset_10_limit_5, ( SELECT * FROM T FETCH FIRST ROW ONLY ) oracle_limit_1, ( SELECT * FROM T OFFSET 10 ROWS ) oracle_offset_10, ( SELECT * FROM T ORDER BY I OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY ) oracle_offset_10_limit_5, FROM dual;
```

--------------------------------

### SQL Formatting with Non-Top-Level Keywords

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/clihighlight.txt

Demonstrates SQL formatting where keywords like 'MY_NON_TOP_LEVEL_KEYWORD_FX' are not at the top level, showing how indentation and semicolons affect parsing.

```sql
-- semicolon must decrease special indentation level
MY_NON_TOP_LEVEL_KEYWORD_FX_1();

MY_NON_TOP_LEVEL_KEYWORD_FX_2();

SELECT
  x
FROM
  (SELECT
    1 as x
  );

MY_NON_TOP_LEVEL_KEYWORD_FX_3();

BEGIN
  MY_NON_TOP_LEVEL_KEYWORD_FX_4();

  MY_NON_TOP_LEVEL_KEYWORD_FX_5();
END;

BEGIN
  SELECT
    x
  FROM
    (SELECT
      1 as x
    );

  MY_NON_TOP_LEVEL_KEYWORD_FX_6();
END;
```

--------------------------------

### Install Dependencies

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/CONTRIBUTING.md

Installs the project dependencies using Composer. This command should be run in the project's root directory.

```shell
composer install
```

--------------------------------

### Insert Configuration Settings

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format.txt

Inserts multiple configuration key-value pairs into the PREFIX_configuration table, including settings for specific prices, tax display, Smarty compilation, distance units, and store display options.

```sql
INSERT INTO `PREFIX_configuration` ( 
  `name`, `value`, `date_add`, `date_upd` 
)
VALUES
  (
    'PS_SPECIFIC_PRICE_PRIORITIES',
    'id_shop;id_currency;id_country;id_group',
    NOW(),
    NOW()
  ),
  (
    'PS_TAX_DISPLAY',
    0,
    NOW(),
    NOW()
  ),
  (
    'PS_SMARTY_FORCE_COMPILE',
    1,
    NOW(),
    NOW()
  ),
  (
    'PS_DISTANCE_UNIT',
    'km',
    NOW(),
    NOW()
  ),
  (
    'PS_STORES_DISPLAY_CMS',
    0,
    NOW(),
    NOW()
  ),
  (
    'PS_STORES_DISPLAY_FOOTER',
    0,
    NOW(),
    NOW()
  ),
  (
    'PS_STORES_SIMPLIFIED',
    0,
    NOW(),
    NOW()
  ),
  (
    'PS_STATSDATA_CUSTOMER_PAGESVIEWS',
    1,
    NOW(),
    NOW()
  ),
  (
    'PS_STATSDATA_PAGESVIEWS',
    1,
    NOW(),
    NOW()
  ),
  (
    'PS_STATSDATA_PLUGINS',
    1,
    NOW(),
    NOW()
  )
```

--------------------------------

### INSERT INTO Stock Movement Reason Language Table

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/compress.txt

Populates the stock movement reason language table with multilingual names for reasons.

```sql
INSERT INTO `PREFIX_stock_mvt_reason_lang` (`id_stock_mvt_reason`, `id_lang`, `name`) VALUES (1, 1, 'Order'), (1, 2, 'Commande'), (2, 1, 'Missing Stock Movement'), (2, 2, 'Mouvement de stock manquant'), (3, 1, 'Restocking'), (3, 2, 'Réassort')
```

--------------------------------

### SQL Nested CASE Statements

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/highlight.html

Provides examples of complex nested CASE statements in SQL, including conditional logic and subqueries.

```SQL
SELECT
  case when name = 1 then
    10
  when name = 2 then
    20
  when name = 3 then
    case when age > 10 then
      30
    else
      31
    end
  else
    40
  end AS case1,
  (SELECT
    case name
      when 1 then
        10
      when 2 then
        20
      when 3 then
        case age
          when 10 then
            30
          else
            31
        end
      else
        40
  end) case2,
  name
FROM
  user
```

--------------------------------

### SQL Unclosed String Literal

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/highlight.html

An example of a SQL query with an unclosed string literal, which would typically result in a syntax error.

```sql
SELECT "no closing quote
```

--------------------------------

### SQL Pagination - Various Dialects

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format-highlight.html

Demonstrates different syntaxes for LIMIT and OFFSET clauses across various SQL dialects (PostgreSQL, MSSQL, Oracle).

```sql
SELECT
  (
    SELECT
      *
    FROM
      T
    LIMIT
      5
    OFFSET
      10
  ) PostgreSQL_offset_10_limit_5,
  (
    SELECT
      *
    FROM
      T
    LIMIT
      5
    OFFSET
      10
    ROWS
    FETCH
      NEXT 5
    ROWS
      ONLY
  ) MSSQL_offset_10_limit_5,
  (
    SELECT
      *
    FROM
      T
    FETCH
      FIRST ROW ONLY
  ) oracle_limit_1,
  (
    SELECT
      *
    FROM
      T
    OFFSET
      10
    ROWS
  ) oracle_offset_10,
  (
    SELECT
      *
    FROM
      T
    ORDER BY
      I
    OFFSET
      10
    ROWS
    FETCH
      NEXT 5
    ROWS
      ONLY
  ) oracle_offset_10_limit_5,
FROM
  dual;
```

--------------------------------

### SQL LEFT OUTER JOIN Syntax

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/highlight.html

An example of a LEFT OUTER JOIN clause with the join condition specified on a new line.

```sql
SELECT a FROM b LEFT
OUTER
JOIN c on (d=f);
```

--------------------------------

### SQL Window Functions

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/compress.txt

Demonstrates advanced SQL window functions including GROUP_CONCAT, AVG, MIN, MAX with various framing clauses and PARTITION BY.

```sql
SELECT a, GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS) AS no_others, GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) AS current_row, GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) AS grp, GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) AS tie, GROUP_CONCAT(b, '.') FILTER (WHERE c != 'two') OVER (ORDER BY a) AS filtered, CONVERT(VARCHAR(20), AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg, AVG(starting_salary) OVER w2 AVG, MIN(starting_salary) OVER w2 MIN_STARTING_SALARY, MAX(starting_salary) OVER (w1 ORDER BY hire_date), LISTAGG(arg, ',') OVER (PARTITION BY part ORDER BY ord ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS LISTAGG_ROWS, LISTAGG(arg, ',') OVER (PARTITION BY part ORDER BY ord RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS LISTAGG_RANGE, MIN(Revenue) OVER (PARTITION BY DepartmentID ORDER BY RevenueYear ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS MinRevenueBeyond FROM t1 WINDOW w1 AS (PARTITION BY department, division), w2 AS (w1 ORDER BY hire_date);
```

--------------------------------

### SQL Common Table Expression (CTE) - Recursive Fibonacci

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/clihighlight.txt

An example of using a recursive CTE to generate Fibonacci numbers up to a certain limit.

```sql
WITH
  cte AS (
    SELECT
      a,
      b
    FROM
      `table`
  ), 
  RECURSIVE fibonacci (n, fib_n, next_fib_n) AS (
    SELECT
      1, 0, 1
    UNION ALL
    SELECT
      n + 1,
      next_fib_n,
      fib_n + next_fib_n
    FROM
      fibonacci
    WHERE
      n < 10
  )
SELECT
  * 
FROM
  fibonacci;
```

--------------------------------

### SQL Control Flow and Indentation

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format.txt

Shows SQL code with custom keywords and control flow structures like BEGIN/END blocks. It highlights how indentation is handled around non-standard keywords and within procedural blocks.

```sql
-- semicolon must decrease special indentation level
MY_NON_TOP_LEVEL_KEYWORD_FX_1();

MY_NON_TOP_LEVEL_KEYWORD_FX_2();

SELECT
  x
FROM
  (
    SELECT
      1 as x
  );

MY_NON_TOP_LEVEL_KEYWORD_FX_3();

BEGIN
  MY_NON_TOP_LEVEL_KEYWORD_FX_4();

  MY_NON_TOP_LEVEL_KEYWORD_FX_5();
END;

BEGIN
  SELECT
    x
  FROM
    (
      SELECT
        1 as x
    );

  MY_NON_TOP_LEVEL_KEYWORD_FX_6();
END;
```

--------------------------------

### SQL Window Functions

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/clihighlight.txt

Demonstrates the usage of various SQL window functions including GROUP_CONCAT, AVG, MIN, MAX, and LISTAGG with different partitioning, ordering, and framing clauses. It also shows how to use the FILTER clause with aggregate functions.

```sql
SELECT
  GROUP_CONCAT(b, '.') OVER (
    ORDER BY
      c
    GROUPS
      BETWEEN UNBOUNDED PRECEDING
      AND CURRENT ROW
      EXCLUDE NO OTHERS) AS no_others,
  GROUP_CONCAT(b, '.') OVER (
    ORDER BY
      c
    GROUPS
      BETWEEN UNBOUNDED PRECEDING
      AND CURRENT ROW
      EXCLUDE CURRENT ROW) AS current_row,
  GROUP_CONCAT(b, '.') OVER (
    ORDER BY
      c
    GROUPS
      BETWEEN UNBOUNDED PRECEDING
      AND CURRENT ROW
      EXCLUDE GROUP) AS grp,
  GROUP_CONCAT(b, '.') OVER (
    ORDER BY
      c
    GROUPS
      BETWEEN UNBOUNDED PRECEDING
      AND CURRENT ROW
      EXCLUDE TIES) AS tie,
  GROUP_CONCAT(b, '.') FILTER (
    WHERE
      c != 'two') OVER (
    ORDER BY
      a) AS filtered,
  CONVERT(
    VARCHAR(20),
    AVG(SalesYTD) OVER (
      PARTITION BY
        TerritoryID
      ORDER BY
        DATEPART(yy, ModifiedDate))
    , 1) AS MovingAvg,
  AVG(starting_salary) OVER w2 AVG,
  MIN(starting_salary) OVER w2 MIN_STARTING_SALARY,
  MAX(starting_salary) OVER (
    w1
    ORDER BY
      hire_date
  ),
  LISTAGG(arg, ',') OVER (
    PARTITION BY
      part
    ORDER BY
      ord
    ROWS
      BETWEEN 1 PRECEDING
      AND 1 FOLLOWING) AS LISTAGG_ROWS,
  LISTAGG(arg, ',') OVER (
    PARTITION BY
      part
    ORDER BY
      ord
    RANGE
      BETWEEN 1 PRECEDING
      AND 1 FOLLOWING) AS LISTAGG_RANGE,
  MIN(Revenue) OVER (
    PARTITION BY
      DepartmentID
    ORDER BY
      RevenueYear
    ROWS
      BETWEEN CURRENT ROW
      AND UNBOUNDED FOLLOWING
  ) AS MinRevenueBeyond
FROM
  t1
WINDOW
  w1 AS (
    PARTITION BY
      department,
      division
  ),
  w2 AS (
    w1
    ORDER BY
      hire_date
  );
```

--------------------------------

### ALTER TABLE - MODIFY and CHANGE Column

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/compress.txt

Shows examples of modifying a column's definition and changing a column's name and definition.

```sql
ALTER TABLE `test_modify` MODIFY `id` INT(11) UNSIGNED NOT NULL;
```

```sql
ALTER TABLE `test_change` CHANGE `id` `_id` BIGINT(20) UNSIGNED NULL;
```

--------------------------------

### Select with IN Clause and Arithmetic

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format.txt

Shows a SELECT statement with conditions using the IN operator and arithmetic expressions. It includes examples of subtracting constants from columns and variables.

```sql
SELECT
  1,
  2,
  3
WHERE
  a in (1, 2, 3, 4, 5)
  and b = 5;

SELECT
  count - 50
WHERE
  a - 50 = b
WHERE
  1
  and -50
WHERE
  -50 = a
WHERE
  a = -50
WHERE
  1
  /*test*/
  -50
WHERE
  1
  and -50;
```

--------------------------------

### SQL Straight Join Syntax

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/clihighlight.txt

Shows the usage of the STRAIGHT_JOIN keyword in SQL, which hints to the optimizer to join the tables in the order they are listed.

```sql
SELECT
  a
FROM
  test
  STRAIGHT_JOIN test2 ON test.id = test2.id
```

--------------------------------

### Incomplete Select Statement

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format.txt

An example of an incomplete SELECT statement, likely intended for testing or demonstrating syntax errors. It includes a comment and a trailing semicolon.

```sql
SELECT
  -- This is a test
```

--------------------------------

### Populate Specific Price from Product Reductions

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format-highlight.html

Inserts data into 'PREFIX_specific_price' based on reduction information present in the 'PREFIX_product' table. It converts price reductions to 'amount' and percentage reductions to 'percentage', handling cases where reduction dates are the same.

```sql
INSERT INTO `PREFIX_specific_price` (
  `id_product`, `id_shop`, `id_currency`,
  `id_country`, `id_group`, `priority`,
  `price`, `from_quantity`, `reduction`,
  `reduction_type`, `from`, `to`
) (
  SELECT
    p.`id_product`,
    1,
    0,
    0,
    0,
    0,
    0.00,
    1,
    IF(
      p.`reduction_price` > 0, p.`reduction_price`,
      p.`reduction_percent` / 100
    ),
    IF(
      p.`reduction_price` > 0, 'amount',
      'percentage'
    ),
    IF (
      p.`reduction_from` = p.`reduction_to`,
      '0000-00-00 00:00:00', p.`reduction_from`
    ),
    IF (
      p.`reduction_from` = p.`reduction_to`,
      '0000-00-00 00:00:00', p.`reduction_to`
    )
  FROM
    `PREFIX_product` p
  WHERE
    p.`reduction_price`
    OR p.`reduction_percent`
)
```

--------------------------------

### Populate Specific Price from Discount Quantity

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format-highlight.html

Inserts data into the 'PREFIX_specific_price' table by selecting and transforming data from the 'PREFIX_discount_quantity' and 'PREFIX_product' tables. It handles discount types and sets default values for currency, shop, and country.

```sql
INSERT INTO `PREFIX_specific_price` (
  `id_product`, `id_shop`, `id_currency`,
  `id_country`, `id_group`, `priority`,
  `price`, `from_quantity`, `reduction`,
  `reduction_type`, `from`, `to`
) (
  SELECT
    dq.`id_product`,
    1,
    1,
    0,
    1,
    0,
    0.00,
    dq.`quantity`,
    IF(
      dq.`id_discount_type` = 2, dq.`value`,
      dq.`value` / 100
    ),
    IF ( 
      dq.`id_discount_type` = 2, 'amount',
      'percentage'
    ),
    '0000-00-00 00:00:00',
    '0000-00-00 00:00:00'
  FROM
    `PREFIX_discount_quantity` dq
    INNER JOIN `PREFIX_product` p ON (p.`id_product` = dq.`id_product`)
)
```

--------------------------------

### MSSQL Identity Insert and Error Handling

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/compress.txt

An example of handling potential errors during an INSERT operation in MSSQL, specifically managing IDENTITY_INSERT for tables with auto-incrementing primary keys.

```SQL
begin try insert into [t] ([name], [int], [float], [null]) values (N'Ewa', 1, 1.0, null); end try begin catch if ERROR_NUMBER() = 544 begin set IDENTITY_INSERT [t] on; begin try insert into [t] ([name], [int], [float], [null]) values (N'Ewa', 1, 1.0, null); set IDENTITY_INSERT [t] off; end try begin catch set IDENTITY_INSERT [t] off; throw; end catch end else begin throw; end end catch
```

--------------------------------

### CREATE TABLE for PREFIX_address

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/highlight.html

This snippet defines the SQL structure for the `PREFIX_address` table, including various columns for address details and indexes.

```sql
CREATE TABLE `PREFIX_address` (
  `id_address` int(10) unsigned NOT NULL auto_increment,
  `id_country` int(10) unsigned NOT NULL,
  `id_state` int(10) unsigned default NULL,
  `id_customer` int(10) unsigned NOT NULL default '0',
  `id_manufacturer` int(10) unsigned NOT NULL default '0',
  `id_supplier` int(10) unsigned NOT NULL default '0',
  `id_warehouse` int(10) unsigned NOT NULL default '0',
  `alias` varchar(32) NOT NULL,
  `company` varchar(64) default NULL,
  `lastname` varchar(32) NOT NULL,
  `firstname` varchar(32) NOT NULL,
  `address1` varchar(128) NOT NULL,
  `address2` varchar(128) default NULL,
  `postcode` varchar(12) default NULL,
  `city` varchar(64) NOT NULL,
  `other` text,
  `phone` varchar(16) default NULL,
  `phone_mobile` varchar(16) default NULL,
  `vat_number` varchar(32) default NULL,
  `dni` varchar(16) DEFAULT NULL,
  `date_add` datetime NOT NULL,
  `date_upd` datetime NOT NULL,
  `active` tinyint(1) unsigned NOT NULL default '1',
  `deleted` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY (`id_address`),
  KEY `address_customer` (`id_customer`),
  KEY `id_country` (`id_country`),
  KEY `id_state` (`id_state`),
  KEY `id_manufacturer` (`id_manufacturer`),
  KEY `id_supplier` (`id_supplier`),
  KEY `id_warehouse` (`id_warehouse`)
) ENGINE=ENGINE_TYPE DEFAULT CHARSET=utf8
```

--------------------------------

### Select with Common Table Expressions (CTE)

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format.txt

An example using Common Table Expressions (CTEs) to define temporary, named result sets. It includes a recursive CTE to generate Fibonacci numbers up to the 10th term.

```sql
WITH
  cte AS (
    SELECT
      a,
      b
    FROM
      table
  ),
  RECURSIVE fibonacci (n, fib_n, next_fib_n) AS (
    SELECT
      1,
      0,
      1
    UNION ALL
    SELECT
      n + 1,
      next_fib_n,
      fib_n + next_fib_n
    FROM
      fibonacci
    WHERE
      n < 10
  )
SELECT
  *
FROM
  cte;
```

--------------------------------

### SQL Common Table Expressions (CTEs)

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format.txt

Demonstrates the use of Common Table Expressions (CTEs) to define temporary named result sets for use within a single SQL statement. This example uses two CTEs, cte1 and cte2, joined together.

```sql
WITH
  cte1 AS (
    SELECT
      a,
      b
    FROM
      table1
  ),
  cte2 AS (
    SELECT
      c,
      d
    FROM
      table2
  )
SELECT
  b,
  d
FROM
  cte1
  JOIN cte2
WHERE
  cte1.a = cte2.c;
```

--------------------------------

### Drop Discount Quantity Table

Source: https://github.com/doctrine/sql-formatter/blob/1.5.x/tests/format-highlight.html

Removes the 'PREFIX_discount_quantity' table from the database.

```sql
DROP TABLE `PREFIX_discount_quantity`
```