Text-to-SQL examples

Text-to-SQL Examples

Natural-language prompts and the SQL they produce — from simple SELECTs to grouping, updates, views, and functions.

Examples use PostgreSQL syntax. The same prompts and patterns apply across all 28 supported databases — MySQL, SQL Server, Oracle, BigQuery, Snowflake, and more.

Basic SQL generations

Generate basic SQL queries, e.g. SELECT queries with clauses and JOINs.

1. Get customers

SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  c.email,
  c.active,
  c.create_date,
  a.address,
  a.address2,
  a.district,
  a.postal_code,
  a.phone,
  ci.city AS city,
  co.country AS country
FROM customer c
LEFT JOIN address a ON c.address_id = a.address_id
LEFT JOIN city ci ON a.city_id = ci.city_id
LEFT JOIN country co ON ci.country_id = co.country_id
ORDER BY c.customer_id;

2. Get customers created past month

SELECT
  customer_id,
  first_name,
  last_name,
  email,
  create_date
FROM
  customer
WHERE
  create_date >= CURRENT_TIMESTAMP - INTERVAL '1 month'
  AND create_date IS NOT NULL
ORDER BY
  create_date DESC;

3. Get customers created past month and limit it 20

SELECT
  *
FROM
  customer
WHERE
  create_date >= now () - interval '1 month'
ORDER BY
  create_date DESC
LIMIT
  20;

4. Get customers created past month and group by email domain

SELECT
  COALESCE(
    NULLIF(lower(split_part (email, '@', 2)), ''),
    '(no_domain)'
  ) AS domain,
  COUNT(*) AS customer_count,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct_of_period
FROM
  customer
WHERE
  create_date >= now () - interval '30 days'
GROUP BY
  COALESCE(
    NULLIF(lower(split_part (email, '@', 2)), ''),
    '(no_domain)'
  )
ORDER BY
  customer_count DESC;

5. Get customers created past month who haven't made a purchase yet

SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  c.email,
  c.create_date
FROM customer c
WHERE c.create_date >= now() - interval '1 month'
  AND NOT EXISTS (
    SELECT 1
    FROM payment p
    WHERE p.customer_id = c.customer_id
  )
ORDER BY c.create_date DESC;

6. Get customers who made more than 3 purchases last year and who lives in New York

SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  c.email,
  COUNT(p.payment_id) AS purchases_last_year
FROM
  customer c
  JOIN address a ON c.address_id = a.address_id
  JOIN city ci ON a.city_id = ci.city_id
  JOIN payment p ON p.customer_id = c.customer_id
WHERE
  ci.city ILIKE 'New York'
  AND p.payment_date >= (
    date_trunc ('year', current_date) - interval '1 year'
  )
  AND p.payment_date < date_trunc ('year', current_date)
GROUP BY
  c.customer_id,
  c.first_name,
  c.last_name,
  c.email
HAVING
  COUNT(p.payment_id) > 3
ORDER BY
  purchases_last_year DESC;

7. Get customer with email john@doe.org

SELECT
  customer_id,
  first_name,
  last_name,
  email,
  active,
  address_id,
  create_date,
  last_update,
  store_id
FROM customer
WHERE email = 'john@doe.org';

Advanced SQL generations

Generate more advanced SQL queries, e.g. queries with complex logic.

1. Get customers within 500m of the Eiffel Tower using lat/lng (using PostGIS extensions)

SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  a.address,
  a.postal_code,
  a.latitude,
  a.longitude,
  ST_Distance(
    ST_SetSRID(ST_MakePoint(a.longitude, a.latitude), 4326)::geography,
    ST_SetSRID(ST_MakePoint(2.2945, 48.8584), 4326)::geography
  ) AS distance_m
FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.latitude IS NOT NULL
  AND a.longitude IS NOT NULL
  AND ST_DWithin(
    ST_SetSRID(ST_MakePoint(a.longitude, a.latitude), 4326)::geography,
    ST_SetSRID(ST_MakePoint(2.2945, 48.8584), 4326)::geography,
    500
  )
ORDER BY distance_m;

2. Get customers who didn't make a purchase in the last 12 months but a purchase in the 12 months before that

SELECT c.customer_id,
       c.first_name,
       c.last_name,
       c.email
FROM customer c
WHERE EXISTS (
    SELECT 1
    FROM payment p
    WHERE p.customer_id = c.customer_id
      AND p.payment_date >= now() - interval '24 months'
      AND p.payment_date <  now() - interval '12 months'
)
AND NOT EXISTS (
    SELECT 1
    FROM payment p2
    WHERE p2.customer_id = c.customer_id
      AND p2.payment_date >= now() - interval '12 months'
);

3. Update all customers starting with a vowel to also start their last name with that vowel

UPDATE customer
SET
  last_name = left (trim(first_name), 1) || COALESCE(
    substring(
      trim(last_name)
      FROM
        2
    ),
    ''
  ),
  last_update = now ()
WHERE
  lower(left (trim(first_name), 1)) IN ('a', 'e', 'i', 'o', 'u')
  AND COALESCE(lower(left (trim(last_name), 1)), '') <> lower(left (trim(first_name), 1));

4. Find customers who made a purchase last year, categorize each by number of purchase and sort by purchases

WITH customer_counts AS (
  SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    c.email,
    COUNT(p.payment_id) AS purchases
  FROM payment p
  JOIN customer c USING (customer_id)
  WHERE p.payment_date >= date_trunc('year', current_date) - interval '1 year'
    AND p.payment_date <  date_trunc('year', current_date)
  GROUP BY c.customer_id, c.first_name, c.last_name, c.email
)
SELECT
  customer_id,
  first_name,
  last_name,
  email,
  purchases,
  CASE
    WHEN purchases = 1 THEN '1'
    WHEN purchases BETWEEN 2 AND 5 THEN '2-5'
    WHEN purchases BETWEEN 6 AND 10 THEN '6-10'
    ELSE '11+'
  END AS purchase_category
FROM customer_counts
ORDER BY purchases DESC;

5. Create customers table with id, first_name, last_name, email, image_url, created_at, updated_at

CREATE TABLE IF NOT EXISTS customers (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  first_name varchar(100) NOT NULL,
  last_name varchar(100) NOT NULL,
  email varchar(320) NOT NULL UNIQUE,
  image_url text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

-- Trigger function to update updated_at on UPDATE
CREATE OR REPLACE FUNCTION public.set_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger (drop existing to ensure idempotence)
DROP TRIGGER IF EXISTS set_updated_at ON customers;
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON customers
FOR EACH ROW
EXECUTE FUNCTION public.set_updated_at();

6. Create customers table with id (uuid_generate_v4), first_name (nullable), last_name (nullable), email, image_url, created_at, updated_at

-- Ensure uuid_generate_v4() is available
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create customers table with requested columns
CREATE TABLE IF NOT EXISTS customers (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  first_name varchar,
  last_name varchar,
  email varchar NOT NULL,
  image_url text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

-- Enforce unique emails
CREATE UNIQUE INDEX IF NOT EXISTS customers_email_idx ON customers(email);

-- Trigger function to update updated_at on UPDATE
CREATE OR REPLACE FUNCTION customers_set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger (drop existing to ensure idempotence)
DROP TRIGGER IF EXISTS customers_set_updated_at_trg ON customers;
CREATE TRIGGER customers_set_updated_at_trg
BEFORE UPDATE ON customers
FOR EACH ROW
EXECUTE FUNCTION customers_set_updated_at();

7. Create view "Customers NYC" for all customers from New York City

CREATE OR REPLACE VIEW "Customers NYC" AS
SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  c.email,
  c.active,
  c.store_id,
  c.create_date,
  c.last_update,
  a.address     AS street,
  a.address2    AS street2,
  a.district,
  a.postal_code,
  a.phone,
  ci.city       AS city
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city ci ON a.city_id = ci.city_id
WHERE ci.city ILIKE 'new york%';

8. Create view "Expensive Products" that get all products with higher than average price

CREATE OR REPLACE VIEW "Expensive Products" AS
SELECT
  film_id,
  title,
  rental_rate
FROM film
WHERE rental_rate > (
  SELECT AVG(rental_rate) FROM film
);

9. Create stored procedure for getting customers with city as parameter

CREATE OR REPLACE FUNCTION get_customers_by_city_partial(p_city TEXT)
RETURNS TABLE (
  customer_id smallint,
  first_name varchar,
  last_name varchar,
  email varchar,
  address varchar,
  city varchar,
  postal_code varchar,
  phone varchar
)
LANGUAGE sql
AS $$
  SELECT c.customer_id,
         c.first_name,
         c.last_name,
         c.email,
         a.address,
         ci.city,
         a.postal_code,
         a.phone
  FROM customer c
  JOIN address a ON c.address_id = a.address_id
  JOIN city ci ON a.city_id = ci.city_id
  WHERE ci.city ILIKE '%' || p_city || '%';
$$;

10. Create function for daily created customers

CREATE FUNCTION daily_created_customers(start_date date, end_date date)
RETURNS TABLE(created_day date, customer_count bigint)
LANGUAGE sql
AS $$
  SELECT gs.d::date AS created_day,
         COALESCE(cnt.customer_count, 0) AS customer_count
  FROM generate_series(start_date::date, end_date::date, interval '1 day') AS gs(d)
  LEFT JOIN (
    SELECT create_date::date AS day, COUNT(*)::bigint AS customer_count
    FROM customer
    WHERE create_date >= start_date::date
      AND create_date < (end_date::date + interval '1 day')
    GROUP BY create_date::date
  ) cnt ON cnt.day = gs.d::date
  ORDER BY created_day;
$$;

Try these on your own queries

Generate, optimize, validate, explain, and format SQL with the SQLAI.ai tools — across every supported database.

Text-to-SQL Generator SQL Optimizer SQL Validator SQL Explainer SQL Formatter All supported databases

Text-to-SQL Examples

Basic SQL generations

Advanced SQL generations

Try these on your own queries

Ship SQL faster with AI