From Natural Language
to SQL Queries

var systemMessage = $"""
    You are an assistant that answers questions using the 
    information stored in a {provider.Name} database and 
    the {provider.Language} language.
    Your answers can only reference one or more of the 
    following tables: '{string.Join(',', tables)}'.
    You can create only {provider.Language} SELECT queries.
    """;

var request = $"""
    You must answer the following question, '{question}', 
    using a {provider.Language} query for a {provider.Name} 
    database.
    From the comma separated list of tables available in 
    the database, select those tables that might be useful 
    in the generated {provider.Language} query.
    The selected tables should be returned in a comma 
    separated list.
    """;

SELECT 
    isc.TABLE_SCHEMA AS [SCHEMA], 
    isc.TABLE_NAME AS [TABLE], 
    isc.COLUMN_NAME AS [COLUMN],
    UPPER(isc.DATA_TYPE) + 
    ISNULL('(' + CAST(isc.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) 
        + ')', '') + ' ' + 
    CASE WHEN isc.IS_NULLABLE = 'YES' 
        THEN 'NULL' ELSE 'NOT NULL' END + ' ' + 
    ISNULL(pKey.CONSTRAINT_TYPE, '') AS DESCRIPTION
FROM INFORMATION_SCHEMA.COLUMNS isc
LEFT JOIN PRIMARY_KEY_INFO pKey 
    ON pKey.ColumnName = isc.COLUMN_NAME

var request = $"""
    A {provider.Name} database contains the following 
    tables and columns:
    {createTableScripts}
    Generate a {provider.Language} query for a 
    {provider.Name} database to answer the question: 
    '{question}'
    The query must only reference table names and column 
    names that appear in this request.
    Your response should only contain the {provider.Language} 
    query, no other information is required.
    You can create only SELECT queries. Never create INSERT, 
    UPDATE nor DELETE commands.
    """;

query = await provider.NormalizeQueryAsync(
    query, 
    cancellationToken
);

var reader = await provider.ExecuteQueryAsync(
    query, 
    cancellationToken
);

return new DatabaseGptQueryResult(query, reader);

Show me the top 10 customers by total revenue this year, 
including their contact information and order count

[dbo].[Customers], [dbo].[Orders], [dbo].[Order Details]

CREATE TABLE [dbo].[Customers] (
    [CustomerID] NCHAR(5) NOT NULL PRIMARY KEY,
    [CompanyName] NVARCHAR(40) NOT NULL,
    [ContactName] NVARCHAR(30) NULL,
    [ContactTitle] NVARCHAR(30) NULL,
    [Address] NVARCHAR(60) NULL,
    [City] NVARCHAR(15) NULL,
    [Region] NVARCHAR(15) NULL,
    [PostalCode] NVARCHAR(10) NULL,
    [Country] NVARCHAR(15) NULL,
    [Phone] NVARCHAR(24) NULL,
    [Fax] NVARCHAR(24) NULL
);

CREATE TABLE [dbo].[Orders] (
    [OrderID] INT NOT NULL PRIMARY KEY,
    [CustomerID] NCHAR(5) NULL,
    [EmployeeID] INT NULL,
    [OrderDate] DATETIME NULL,
    [RequiredDate] DATETIME NULL,
    [ShippedDate] DATETIME NULL,
    [ShipVia] INT NULL,
    [Freight] MONEY NULL
);

CREATE TABLE [dbo].[Order Details] (
    [OrderID] INT NOT NULL PRIMARY KEY,
    [ProductID] INT NOT NULL PRIMARY KEY,
    [UnitPrice] MONEY NOT NULL,
    [Quantity] SMALLINT NOT NULL,
    [Discount] REAL NOT NULL
);

### Key Relationships:
Orders (CustomerID) joined with Customers (CustomerID)
Order Details (OrderID) joined with Orders (OrderID)

SELECT TOP 10
    c.CustomerID,
    c.CompanyName,
    c.ContactName,
    c.Phone,
    c.City,
    c.Country,
    COUNT(DISTINCT o.OrderID) AS OrderCount,
    SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) AS TotalRevenue
FROM [dbo].[Customers] c
INNER JOIN [dbo].[Orders] o ON c.CustomerID = o.CustomerID
INNER JOIN [dbo].[Order Details] od ON o.OrderID = od.OrderID
WHERE YEAR(o.OrderDate) = YEAR(GETDATE())
GROUP BY 
    c.CustomerID,
    c.CompanyName,
    c.ContactName,
    c.Phone,
    c.City,
    c.Country
ORDER BY TotalRevenue DESC;