- Sample database: dvdrental
- Generating SQL queries with AI
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.
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
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
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
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
payment table. The data insights provided can help you improve your decision making process and optimize your business.
Additional SQL query examples
- get actor with id 13, 26, 52
- which is the most common city for customers
- which is the most rented film title and how many times it has been rented
- list all films categories
- get the 10 customers with the most payments
- average film rentals per month
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.