Example of databases for testing text to SQL

Text to SQL - i.e. generating precise SQL queries from natural language questions - is an exciting interface between natural language and structured data processing.

For this type of AI to work, it needs a realistic context: tables, columns, relationships and content.

This is exactly where sample databases come into play.

In this article, I present some examples of open source SQL databases that you can use immediately to carry out your own experiments or to test a text-to-SQL model.

Aviano DB - Car Rental System

Use Case:
This database maps a complete rental car system - including customers, vehicles, locations, insurance, invoices and additional options. It is ideal for training complex SQL joins, totals, groupings and date functions.

Database:
The Aviano DB contains realistic tables such as customer, vehicle_type, rental, fuel_option, insurance, equipment_type and many more. An ERD is included in the repo.

Text prompt example:

"Show me all customers with their name, vehicle category, pick-up location and pick-up date."

SELECT
    CONCAT_WS(',', c.first_name, c.last_name) AS 'Customer Name',
    vt.name AS 'Car Type',
    CONCAT_WS(',', l1.city, l1.state) AS 'Pickup Location',
    r.start_date as 'Pickup Date'
FROM
    rental AS r
        JOIN customer AS c ON r.customer_id = c.id
        JOIN vehicle_type AS vt ON r.vehicle_type_id = vt.id
        JOIN location AS l1 ON r.pickup_location_id = l1.id;

📎 To the GitHub repo

Chinook DB - Classic media business

Use Case:
Chinook is perfect for music and media related analysis. It contains artists, albums, tracks, genres, customers and sales. Ideal for beginners and business intelligence use cases.

Database:
Typical tables: Customer, Invoice, InvoiceLine, Track, Album, Artist.

Text prompt example:

"Which five artists have generated the most sales?"

SELECT
    a.Name AS Artist,
    SUM(il.Quantity) AS TotalSales
FROM
    InvoiceLine il
        JOIN Track t ON il.TrackId = t.TrackId
        JOIN Album al ON t.AlbumId = al.AlbumId
        JOIN Artist a ON al.ArtistId = a.ArtistId
GROUP BY a.Name
ORDER BY TotalSales DESC
LIMIT 5;

📎 To the GitHub repo

Northwind Traders - classic for B2B trading

Use Case:
Northwind simulates the sales department of a wholesale company with products, orders, suppliers and employees. The database is rich in relations and is particularly suitable for JOIN exercises and business logic analyses.

Database:
Core tables: Orders, OrderDetails, Products, Customers, Suppliers, Employees, Shippers.

Text prompt example:

"Which employees processed orders in May 1997 and what was the total turnover?"

SELECT
    e.FirstName || ' ' || e.LastName AS Employee,
    SUM(od.UnitPrice * od.Quantity) AS TotalSales
FROM
    Employees e
        JOIN Orders o ON e.EmployeeID = o.EmployeeID
        JOIN OrderDetails od ON o.OrderID = od.OrderID
WHERE
    o.OrderDate BETWEEN '1997-05-01' AND '1997-05-31'
GROUP BY Employee;

📎 To the GitHub repo

Pizza database

Use Case:
Minimalist database for pizza orders. Ideal for demonstrations, performance tests or setting up an initial chatbot for restaurants.

Database:
Tables like pizzas, orders, customers, toppings, order_details.

Text prompt example:

"Which pizzas were ordered most frequently last month?"

SELECT
    p.name,
    COUNT(od.pizza_id) AS TimesOrdered
FROM
    order_details od
        JOIN pizzas p ON od.pizza_id = p.id
        JOIN orders o ON od.order_id = o.id
WHERE
    o.order_date >= DATE('now', '-1 month')
GROUP BY p.name
ORDER BY TimesOrdered DESC;

📎 Go to GitHub-Repo

Employee database

profolsen/employees

Purpose:
Provision of a modified version of the PostgreSQL demo database "employees".

Contents:

• SQL and Java files for import and access.
• The structure and data correspond in part to the well-known MySQL database "employees".
• Focus on correct licensing and attribution of original sources.

Special features:

• License: Creative Commons Attribution-Share Alike 3.0 Unported License
• Originally based on templates from other repositories, with modifications.
• No focus on a web interface or API, purely data and structure provision.

Data analysis on the basis of an airport

fortunewalla/airportdb

Learning project for SQL, MySQL and data analysis based on an airport dataset.

Contents:

• SQL dumps (schema and data) of a modified airport data record ("ffdb").
• Instructions for installation with mysql and mysqlsh.
• Preparation for Analytics & HeatWave-ML from Oracle.

Special features:

• Data originally from the German flughafenendb.cctranslated into English.
• Contains large SQL files (over 90 MB).
• Integration with HeatWave/ML documented.
• License not explicitly stated, but references to original sources available.

Analysis of baby names in the USA at national and state level

ktaranov/BabbyNames

Visualization and analysis of baby names in the US at the national and state level.

Special features:

• Source: data.gov
• Contains basic information on data import.
• License: MIT License

Simulation of a web store with 1000 customers, 2000 orders and 1000 products

Provision of a PostgreSQL data set to simulate a web store.

JannikArndt/PostgreSQLSampleDatabase

Contents:

• Scheme with:
  • 1000 customers
  • 2000 orders
  • 1000 products with a total of 17,730 articles
• src/ contains scripts for DB generation.
• data/ contains dump for recovery via restore.sh.

Conclusion

There are some open source SQL databases that can be used for experimenting and testing text-to-SQL models.
If there are other freely available SQL databases that you would like us to list here, please let us know in your feedback.