Default LLM prompt can contain multiple SQL queries in SQL fence.

[kracekumar]

Settings

• LLM Model: Deepseek-reasoner

Here is an example that contains two different SQL queries in a single SQL Fence.

Example

test.db> \llm+ "Suggest 2 ways to find duplicate rows in the people table"
To find duplicate rows in the `people` table, here are two approaches:

1. **Group and Count**: Group by `first_name` and `last_name`, then filter groups with counts exceeding 1.
   - **Why `people`?** This is the only table needed since duplicates are defined by matching both name fields.

2. **Self-Join**: Join the table to itself on matching names but different `rowid` (SQLite's implicit primary key).
   - **Why `people`?** The self-join compares rows within the same table to identify duplicates.

-- Method 1: Group by and count duplicates
SELECT first_name, last_name, COUNT(*) AS duplicate_count
FROM people
GROUP BY first_name, last_name
HAVING COUNT(*) > 1;

-- Method 2: Self-join to find duplicate pairs
SELECT p1.first_name, p1.last_name
FROM people p1
JOIN people p2
  ON p1.first_name = p2.first_name
  AND p1.last_name = p2.last_name
  AND p1.rowid < p2.rowid;

Then both the SQL code is copied to the CLI

/Users/kracekumar/code/mcp-chatbot/test.db> -- Method 1: Group by and count duplicates
                                            SELECT first_name, last_name, COUNT(*) AS duplicate_count
                                            FROM people
                                            GROUP BY first_name, last_name
                                            HAVING COUNT(*) > 1;

                                            -- Method 2: Self-join to find duplicate pairs
                                            SELECT p1.first_name, p1.last_name
                                            FROM people p1
                                            JOIN people p2
                                              ON p1.first_name = p2.first_name
                                              AND p1.last_name = p2.last_name
                                              AND p1.rowid < p2.rowid;

Fix

Adding an extra line at the end of the prompt helps: When the user requests for multiple sql queries, separate them with in a separate code fence.

Here is the output for the same model

test.db> \llm+ "Suggest 2 ways to find duplicate rows in the people table"
To find duplicate rows in the `people` table, here are two methods:

1. **Group and Count**: Group by `first_name` and `last_name` to count occurrences, filtering groups with counts >1. This identifies which name pairs are duplicated.
2. **Row Numbering**: Use a window function to number rows within each name group. Rows with numbers >1 are duplicates.

**Why the `people` table?** The question specifically targets duplicates in this table. Other tables (`contacts`, `groups`, etc.) are unrelated to this task.

-- Method 1: Group and Count
SELECT first_name, last_name, COUNT(*) AS duplicate_count
FROM people
GROUP BY first_name, last_name
HAVING COUNT(*) > 1;

-- Method 2: Row Numbering
WITH cte AS (
    SELECT first_name, last_name,
           ROW_NUMBER() OVER (PARTITION BY first_name, last_name ORDER BY rowid) AS rn
    FROM people
)
SELECT first_name, last_name
FROM cte
WHERE rn > 1;

/Users/kracekumar/code/mcp-chatbot/test.db> -- Method 1: Group and Count
                                            SELECT first_name, last_name, COUNT(*) AS duplicate_count
                                            FROM people
                                            GROUP BY first_name, last_name
                                            HAVING COUNT(*) > 1;

Even though user asked for 2 options, only first one selected and pre-filled. I think it's better experience than adding two sql queries.