📝 How To: Generate Accurate SQL Queries

The fastest way to generate accurate SQL queries is to first setup your database in AI Query. Once the database is setup, we are ready to start generating SQL queries. Let's take a look at how to generate SQL queries.

Sample Database

💡

For example purposes we will use the PostgreSQL sample database.

We will use a PostgreSQL sample database (opens in a new tab) for example purposes. This PostgreSQL sample database is widely used in most tutorials and examples, and serves as a real-world database to learn and build SQL queries through AI Query.

The database schema is as follows:

Generating SQL Queries

The above database schema shows us the tables, fields and the relationships that we can consider for SQL queries through AI Query.

What can I Ask?

AI Query is very capable of interpreting your query prompt to return what you want. AI Query will automatically figure out the correct tables and fields based on your query prompt to determine the returned SQL query.

That's great but can I see some real examples?

Let's take a look at some Query Prompt's and generated SQL Queries to see if we can answer your question! 😁

First, on the AI Query Dashboard, navigate to Query Playground and select the Database you have setup and set the Query Type as Create Query. Now you are ready to query!

Example 1

Query Prompt

get all actors starting with A

SQL Query

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

Example 2

Query Prompt

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

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%';

Example 3

Query Prompt

get the 5 best rental_rated films

SQL Query

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

Example 4

Query Prompt

which is the most common city for customers

SQL Query

SELECT c.city, COUNT(*) AS num_customers
FROM customer c
JOIN address a ON c.address_id = a.address_id
GROUP BY c.city
ORDER BY num_customers DESC
LIMIT 1;

Example 5

Query Prompt

which is the most rented film title and how many times it has been rented

SQL Query

SELECT title, COUNT(*) AS num_rentals
FROM film
INNER JOIN rental ON film.film_id = rental.film_id
GROUP BY title
ORDER BY num_rentals DESC
LIMIT 1;

Example 6

Query Prompt

list all films categories

SQL Query

SELECT c.name
FROM category c
INNER JOIN film_category fc ON c.category_id = fc.category_id;

Example 7

Query Prompt

get the 10 customers with the most payments

SQL Query

SELECT c.customer_id, c.first_name, c.last_name, COUNT(p.payment_id) AS num_payments
FROM customer c
INNER JOIN payment p ON c.customer_id = p.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY num_payments DESC
LIMIT 10;

Example 8

Query Prompt

average film rentals per month

SQL Query

SELECT EXTRACT(MONTH FROM rental_date) AS month, COUNT(*) / COUNT(DISTINCT EXTRACT(YEAR FROM rental_date)) AS avg_rentals_per_month
FROM rental
GROUP BY month;

Final Thoughts

As you can see it is fairly easy to get AI Query to generate SQL queries for you. You bring the database, AI Query does the rest! 😉

Core Concepts Manage Database Schema