#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →

Build This Project · AI Portfolio

Natural-Language-to-SQL Analytics Assistant for Your AI Portfolio

Build an assistant that turns plain-English questions into safe, read-only SQL, with tested code, an evaluation script and a small Streamlit app.

Tracks
NL to SQL · Live Interactive
Project Focus
—
What you'll build
Skills Demonstrated
—
Key competencies
Guardrail
—
What keeps it safe
Design DB → LLM → Query Gen → App → Portfolio
Click a stage to see what it does and the skill it shows.

Home / Tutorials / Project Guides / Natural-Language-to-SQL Analytics Assistant

Build This Project · AI Portfolio

Natural-Language-to-SQL Analytics Assistant for Your AI Portfolio

A natural-language-to-SQL assistant lets someone type "total revenue by city" and get back a table, without knowing SQL. A large language model (LLM) writes the query; your code checks it, runs it safely and shows the result. It is a good portfolio project because it combines three things employers look for in applied AI roles: working with an LLM, working with real data, and building guardrails around a model that will sometimes be wrong.

This guide builds the whole thing in plain Python and SQLite: a sample database, the prompt, a safety layer, a retry loop, an evaluation script and a small Streamlit app. The scripts below were run and the outputs shown are their real outputs. The LLM call sits behind one function, so you can use whichever provider you have access to.

How the pieces fit

  1. Read the database schema (table and column definitions).
  2. Send the schema and the user's question to the LLM, asking for one SELECT query.
  3. Clean the reply (models often wrap SQL in markdown fences).
  4. Validate it: one statement, read-only, nothing dangerous.
  5. Run it on a read-only connection with a row limit.
  6. If it fails, send the error back to the model once and try again.
  7. Measure accuracy against a test set of questions with known answers.

Step 1: a small sales database

Three tables (customers, products, orders) are enough to need joins and aggregates. Save this as setup_db.py and run it once.

import sqlite3

con = sqlite3.connect("sales.db")
con.executescript("""
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    city        TEXT NOT NULL,
    signup_date TEXT NOT NULL          -- ISO date, e.g. 2026-01-15
);
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    category   TEXT NOT NULL,
    price      REAL NOT NULL           -- in rupees
);
CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    product_id  INTEGER NOT NULL REFERENCES products(product_id),
    quantity    INTEGER NOT NULL,
    order_date  TEXT NOT NULL
);
INSERT INTO customers VALUES
 (1,'Aarav','Delhi','2026-01-05'), (2,'Diya','Mumbai','2026-01-20'),
 (3,'Kabir','Delhi','2026-02-11'), (4,'Meera','Pune','2026-03-02');
INSERT INTO products VALUES
 (1,'Wireless Mouse','Electronics',799), (2,'Notebook Pack','Stationery',250),
 (3,'USB-C Charger','Electronics',1499);
INSERT INTO orders VALUES
 (1,1,1,2,'2026-03-01'), (2,2,3,1,'2026-03-03'), (3,1,2,5,'2026-03-10'),
 (4,3,3,2,'2026-04-02'), (5,4,2,3,'2026-04-05'), (6,2,1,1,'2026-04-18');
""")
con.commit()
con.close()
print("sales.db created")

If joins between these tables are unfamiliar, review SQL joins explained with real examples first. You need to be able to check the model's SQL yourself.

Step 2: the core module

Save this as nl2sql.py. It does not depend on any LLM library: answer() takes an ask_llm function that receives a prompt string and returns the model's text.

import re
import sqlite3

DB = "sales.db"
FORBIDDEN = re.compile(r"\b(insert|update|delete|drop|alter|create|replace|attach|pragma)\b", re.I)

def get_schema(db=DB):
    con = sqlite3.connect(db)
    rows = con.execute("SELECT sql FROM sqlite_master WHERE type = 'table'").fetchall()
    con.close()
    return "\n\n".join(r[0] for r in rows)

def build_prompt(question, schema, error=None):
    prompt = (
        "You write SQLite queries for a sales database.\n"
        "Use only the tables and columns in this schema:\n\n"
        f"{schema}\n\n"
        "Rules: return ONE read-only SELECT statement and nothing else. "
        "Prices are in rupees. Revenue = price * quantity.\n\n"
        f"Question: {question}\nSQL:"
    )
    if error:
        prompt += f"\n\nYour previous query failed with: {error}\nReturn a corrected query."
    return prompt

def extract_sql(text):
    """Strip markdown fences and trailing semicolons from a model reply."""
    m = re.search(r"```(?:sql)?\s*(.*?)```", text, re.S | re.I)
    sql = m.group(1) if m else text
    return sql.strip().rstrip(";").strip()

def validate(sql):
    if ";" in sql:
        raise ValueError("only one statement is allowed")
    if not re.match(r"^\s*(select|with)\b", sql, re.I):
        raise ValueError("only SELECT queries are allowed")
    if FORBIDDEN.search(sql):
        raise ValueError("query contains a forbidden keyword")
    return sql

def run_query(sql, db=DB, max_rows=200):
    con = sqlite3.connect(f"file:{db}?mode=ro", uri=True)   # read-only connection
    try:
        cur = con.execute(f"SELECT * FROM ({sql}) LIMIT {max_rows}")
        cols = [d[0] for d in cur.description]
        return cols, cur.fetchall()
    finally:
        con.close()

def answer(question, ask_llm, retries=1):
    schema = get_schema()
    error = None
    for _ in range(retries + 1):
        sql = extract_sql(ask_llm(build_prompt(question, schema, error)))
        try:
            return sql, run_query(validate(sql))
        except (ValueError, sqlite3.Error) as e:
            error = str(e)
    raise RuntimeError(f"could not answer: {error}")

What each part does and why:

  • get_schema() reads the CREATE TABLE statements from sqlite_master. Giving the model the exact schema, including comments like "in rupees", is the biggest single accuracy improvement. For large databases, send only the tables relevant to the question; this is where retrieval, as in retrieval-augmented generation, comes in.
  • build_prompt() states the rules and defines business terms ("Revenue = price * quantity"). Without that definition a model may sum price alone. On a retry it includes the database error so the model can fix its mistake.
  • validate() rejects multiple statements, anything that is not SELECT or WITH, and write keywords. It is deliberately strict: it would also reject a semicolon inside a text literal or a call to SQLite's replace() function.
  • run_query() opens the database with mode=ro, so even a query that slipped past validation cannot change data. This is the real safety net; keyword checks alone are never enough. Wrapping the query in an outer SELECT ... LIMIT stops a careless query from returning millions of rows.
  • answer() gives the model one chance to correct itself using the error message, then gives up with a clear error instead of showing wrong output.

Step 3: test the pipeline offline

Before spending API credits, test the plumbing with a fake LLM that returns fixed replies. The second question's first reply is deliberately wrong (it forgets the join), and the third question asks to delete data.

from nl2sql import answer

# Stand-in for a real LLM call, so the pipeline can be tested offline.
CANNED = {
    "Total revenue by city": [
        "```sql\nSELECT c.city, SUM(p.price * o.quantity) AS revenue\n"
        "FROM orders o JOIN customers c ON c.customer_id = o.customer_id\n"
        "JOIN products p ON p.product_id = o.product_id\n"
        "GROUP BY c.city ORDER BY revenue DESC;\n```"],
    "Which product sold the most units?": [
        "SELECT name, SUM(quantity) AS units FROM orders GROUP BY name",   # wrong: no join
        "SELECT p.name, SUM(o.quantity) AS units FROM orders o "
        "JOIN products p ON p.product_id = o.product_id "
        "GROUP BY p.name ORDER BY units DESC LIMIT 1"],
    "Delete all orders": ["DELETE FROM orders", "DELETE FROM orders"],
}

def make_fake_llm(question):
    replies = iter(CANNED[question])
    return lambda prompt: next(replies)

for q in CANNED:
    try:
        sql, (cols, rows) = answer(q, make_fake_llm(q))
        print(q, "->", cols, rows)
    except RuntimeError as e:
        print(q, "->", e)

Output:

Total revenue by city -> ['city', 'revenue'] [('Delhi', 5846.0), ('Mumbai', 2298.0), ('Pune', 750.0)]
Which product sold the most units? -> ['name', 'units'] [('Notebook Pack', 8)]
Delete all orders -> could not answer: only SELECT queries are allowed

Check the first result by hand: Delhi has three orders, 2 × 799 + 5 × 250 + 2 × 1,499 = 5,846 rupees. For the second question, the first query failed with "no such column: name", the error went back into the prompt, and the corrected query joined products. The delete request was blocked by validate() on both attempts.

Step 4: connect a real model

Create a file llm.py with one function, ask_llm(prompt), that sends the prompt to your provider's chat API and returns the text of the reply. Every major provider's Python SDK can do this in a few lines. Copy the current call and model name from the provider's documentation, because both change over time. Keep the API key in an environment variable, never in the code or the GitHub repository, and use a temperature of 0 or close to it so the same question gives the same SQL.

Step 5: measure accuracy

A demo that works on three questions proves little. Interviewers will ask how accurate it is, and the honest answer comes from a test set: questions paired with a hand-written "gold" query. The script compares result values, not SQL text, because two different queries can be equally correct.

from nl2sql import answer, run_query

# Each test pairs a question with a hand-written "gold" query.
TESTS = [
    ("Total revenue by city",
     "SELECT c.city, SUM(p.price * o.quantity) FROM orders o "
     "JOIN customers c USING (customer_id) JOIN products p USING (product_id) GROUP BY c.city"),
    ("How many customers signed up in January 2026?",
     "SELECT COUNT(*) FROM customers WHERE signup_date BETWEEN '2026-01-01' AND '2026-01-31'"),
]

def same_result(a, b):
    # compare values only, ignoring row order and column names
    return sorted(a[1]) == sorted(b[1])

def evaluate(ask_llm):
    passed = 0
    for question, gold in TESTS:
        try:
            _, got = answer(question, ask_llm)
            ok = same_result(got, run_query(gold))
        except RuntimeError:
            ok = False
        passed += ok
        print("PASS" if ok else "FAIL", question)
    print(f"{passed}/{len(TESTS)} passed")

if __name__ == "__main__":
    fake = {
        "Total revenue by city":
            "SELECT c.city, SUM(p.price*o.quantity) AS revenue FROM orders o "
            "JOIN customers c ON c.customer_id=o.customer_id "
            "JOIN products p ON p.product_id=o.product_id GROUP BY c.city",
        "How many customers signed up in January 2026?":
            "SELECT COUNT(*) FROM customers WHERE signup_date LIKE '2026-02%'",  # wrong month
    }
    evaluate(lambda prompt: fake[prompt.split("Question: ")[1].split("\n")[0]])

Output with the fake model, which gets the month wrong on the second question:

PASS Total revenue by city
FAIL How many customers signed up in January 2026?
1/2 passed

For your portfolio, write 30 to 50 test questions covering filters, dates, joins, grouping and "top N" questions, run them against the real model, and report the pass rate together with the failure types you saw (wrong date logic, missing joins, invented columns). That table of failures impresses interviewers more than any single accuracy figure.

Step 6: a simple interface

Streamlit turns the module into a web app in a few lines. Install it with pip install streamlit and start it with streamlit run app.py.

import streamlit as st
from nl2sql import answer
from llm import ask_llm          # your wrapper around the LLM provider you use

st.title("Ask the sales database")
question = st.text_input("Question", placeholder="Total revenue by city")

if question:
    try:
        sql, (cols, rows) = answer(question, ask_llm)
        st.code(sql, language="sql")
        st.dataframe([dict(zip(cols, r)) for r in rows])
    except RuntimeError as e:
        st.error(str(e))

Always show the generated SQL next to the result, as this app does. Users and reviewers can then see exactly what was run.

Limitations to state honestly

  • Ambiguity. "Best customers" could mean revenue, order count or recency. A production tool asks a clarifying question or documents its definitions.
  • Large schemas. Real warehouses have hundreds of tables; you need schema selection, column descriptions and example queries in the prompt.
  • Silent wrong answers. The worst failure is valid SQL that answers a different question. Only evaluation and showing the SQL protect against it.
  • Data access. In a company, the database user must have only the permissions the person asking is allowed, so the assistant cannot reveal data they should not see.
  • Privacy. If you add a step that summarises results with the model, the data goes to the provider; check that this is allowed.

Presenting it in a portfolio

Put the code on GitHub with a README covering the architecture, the safety measures (read-only connection, validation, row limit), the evaluation results with failure examples, and what you would add next (query caching, clarifying questions, PostgreSQL support). In an interview, be ready to explain why keyword filtering alone is unsafe and how you measured accuracy. For more ideas at a similar level, see 25 AI projects for students; for a simpler first SQLite project, try a to-do list app with Python and SQLite.

Frequently asked questions

Which LLM is best for natural-language-to-SQL?

Current general-purpose models from the major providers write simple SQL well. The differences show up on complex joins and ambiguous questions, so compare models on your own test set rather than on general claims.

Is it safe to let an LLM run SQL on a database?

Only with layers of protection: a read-only connection or a database user with minimal permissions, validation of the query, a row limit, and showing the SQL to the user. Never connect it to a database with write access.

Can I build this without paying for an API?

Yes. You can develop and test everything with a fake model as shown above, and open-source models running locally let you try the real step without per-call charges.

Should I use SQLite or PostgreSQL?

SQLite is ideal for learning because it needs no server. Moving to PostgreSQL later mainly means changing the connection code and telling the model which SQL dialect to write.

Build This Project

More AI project guides

Career resources

Build your career

Latest articles

Fresh this week