Generate Accurate SQL with AI: Case Study

Introduction

The easiest way to generate accurate SQL is to import your database schema into AI Bot or connect to your database. This allows AI Bot to better understand and interpret your input. AI Bot will use relevant part of your database schema when building the SQL query, e.g. automatically figure out correct foreign keys, field and table names, sorting fields, and so on.

If you haven't yet added or connected your database, you can see how to do that here. If you have connected to your database, you can run the SQL queries directly giving you data insights.

Sample database: dvdrental

To show how to use AI Bot we are using the dvdrental database schema for Postgres. This is a sample database that is used in many tutorials and examples. It is a good example of a real-world database that you can use to learn SQL and AI Bot. It has the following structure:

Generating SQL queries with AI

By looking at the database diagram above we can see what tables and fields the database has and that we can query with AI Bot. On a side note, you can additionally type / (slash) in the input to open a searchable auto-suggest containing your database schema in case you forgot a table or field name.

What can I ask AI Bot?

Generally AI Bot is very capable of interpreting your input and usually returns what you want. However in case where that isn't the case, try to reformulate and/or add a bit more information about what you want (this is usually only necessary when building very "tricky" SQL queries).

Try to use the same names that you use in your database schema for tables and fields (e.g. actor, film, customer).

It is not necessary anymore to use the same names as in your database schema. AI Bot will automatically figure out the correct table and field names (e.g. "actor" or "performer" or "artist" all will both work and AI Bot will understand what you refer to).

If we look at the actor table in the database diagram we see fields we can query like this:

            

              
            

            get all actors starting with A

And AI Bot generates the SQL query:

            

              
            

            SELECT
  first_name,
  last_name
FROM
  actor
WHERE
  first_name LIKE 'A%'
ORDER BY
  first_name;

And if you have connected AI Bot to your database you can execute the SQL query instantly and get the data you need. This is of course useful, but let's say we want to get additional information about which films the actor starred in:

            

              
            

            get all actors starting with A and films with title, release year, length and rating

And AI Bot generates the SQL query:

            

              
            

            SELECT
  a.first_name,
  a.last_name,
  f.title,
  f.release_year,
  f.length,
  f.rating
FROM
  actor a
  INNER JOIN film_actor fa ON a.actor_id = fa.actor_id
  INNER JOIN film f ON fa.film_id = f.film_id
WHERE
  a.first_name LIKE 'A%';

AI Bot understands that the dvdrental data sample uses a bridge table film_actor to construct a many-to-many relation and builds the SQL query using correct tables, foreign keys, and returns the desired fields.

Knowing your data, tables and fields, is important to see what you can ask AI Bot and also to spot naming ambiguity. Let's say we want to get the 5 best rated films. Since the film table has multiple fields containing "rate", being specific helps AI Bot understand that we want to use the rental_rate field and not rating which rates if the movie is suitable for different age groups:

            

              
            

            get the 5 best rental_rated films

And AI Bot generates the SQL query:

            

              
            

            SELECT
  title,
  rental_rate
FROM
  film
ORDER BY
  rental_rate DESC
LIMIT
  5;

You can do the same thing with other tables that you need data insights from like customer or payment table. The data insights provided can help you improve your decision making process and optimize your business.

Additional SQL query examples

Conclusion

As you see it is easy to get AI Bot to build SQL queries for you and if you have connected your database you can instantly get the data insights you need. A few best practice tips when asking AI Bot:

  • try to know the tables and fields in your database
  • try to use table and field names directly in your input (or variations)
  • try to re-request AI Bot to generate SQL query, it often have other suggestions
  • try using AI Bot a few times and you quickly get the hang of it

You bring the data, AI Bot does the rest. You can check out the AI Bot demo.