How to Generate SQL Query (and NoSQL)

Introduction
Video
How to Use
Usage with MongoDB (NoSQL)
Instruction Examples
- Business Insights
- Pseudo-SQL Phrasing
Dos
Don'ts
Tips
Shortcuts

Introduction

SQLAI.ai provides a powerful and highly accurate SQL generator. Simply provide instructions in your preferred language for the SQL query or data you require, and it will generate the query instantly. Adding a database schema (i.e., data source) enhances accuracy.

Video

How to Use

Using the generator is straightforward; follow these steps:

Go to the SQL generator.
Instruct it as if you were speaking to an assistant, e.g., "Get customers who live in New York and didn't make a purchase the previous month. Order alphabetically."
Use the generated SQL query.

When you have generated a query, you can click the helper tools above the query for additional functionality:

Helper tools including 'run query,' 'refine query,' and 'format query'

Run Database Query

If you have connected to a database, you can run queries directly by clicking the run button () and display the results in a table:

Or in an AI-generated chart:

Refine Query

Click the refine button () to make adjustments to the generated query:

Format Query

Click the format button () to format the SQL query:

Additionally, you can set custom formatting rules.

Save Query

Click the save button () to save the query:

Saved queries can be private or public.

Database Schema Autocomplete

When you have added your database schema either by connecting to it or by running the query to retrieve the schema, you can use the autosuggest by typing / (slash). This is an quick way to lookup and find table or column names and helps to generate precise queries. You can tweak the autocomplete settings too.

Data Source Rules

Data source rules are concise and general rules that should always be added to AI when generating queries, e.g. Wrap tables and columns in quotes. Data source rules help to make AI even more precise and match your expectations. You can set the data source rules on each data source.

Usage with MongoDB (NoSQL)

When you have chosen MongoDB as the target database engine on the settings page, it will generate the arguments used in the db.runCommand function. For example, if you want to find a specific user from the users collection, you could write:

            
              
            
            Get user with email john@doe.org

And you will get:

            
              
            
            {
  find: "users",
  filter: { email: "john@doe.org" }
}

To use the above in your code, use it with the db.runCommand:

            
              
            
            const user = db.runCommand({
  find: 'users',
  filter: { email: 'john@doe.org' },
});

Generating the argument for db.runCommand() makes it easy to use the generated queries in your own code and modify them as needed.

Tip: If you connect to your MongoDB database, you can run the queries directly via SQLAI.ai and get the data you need.

Instruction Examples

Generally, usage is fairly intuitive, but below are a few specific examples:

Business Insights

Here are examples of business intelligence questions to ask the generator using the Rental database schema:

What are the top-selling film categories?
Which cities have the highest and lowest number of customers?
What is the average rental rate per film category?
What is the average payment amount per customer?
Which films have the highest and lowest rental rates?
What is the total sales per store?
Which stores have the highest and lowest total sales?

Pseudo-SQL Phrasing

Pseudo-SQL uses "SQL-lingo" in everyday sentences. Here are some examples:

Simple Select Query

            
              
            
            Retrieve the names and email addresses of all customers from the `customers` table.

Conditional Query

            
              
            
            Get the order IDs and order dates from the `orders` table where the order status is 'shipped'.

Aggregate Function

            
              
            
            Calculate the total sales amount from the `sales` table for the year 2023.

Join Tables

            
              
            
            List the product names and their corresponding supplier names by joining the `products` and `suppliers` tables.

Group By Clause

            
              
            
            Show the number of employees in each department from the `employees` table, grouped by department.

Order By Clause

            
              
            
            Fetch the top 5 highest-paid employees from the `employees` table, ordered by salary in descending order.

Insert Statement

            
              
            
            Add a new record to the `customers` table with the following details: name 'John Doe', email 'john.doe@example.com', and phone '123-456-7890'.

Update Statement

            
              
            
            Update the email address of the customer with name 'John Doe' to 'john.doe@example.com' in the `customers` table.

Delete Statement

            
              
            
            Remove all records from the `sessions` table where the session date is before '2023-01-01'.

Complex Query with Subquery

            
              
            
            Select the names of customers who have placed more than 5 orders, using the `customers` and `orders` tables.

Dos

Set the target database engine.
Provide instructions in your preferred language (it automatically translates table/column naming into English if the database schema is in English).
Add database schema to increase accuracy.
Using specific table or column naming isn't strictly necessary; it will find the movies table even if the instructions only mention films.

Don'ts

Don't write your own AI prompt, e.g., "You are a PostgreSQL bot..."
Avoid too many run-on sentences (preferably split them up with periods).
Avoid too many parentheses (the exact meaning can be hard to deduce).

Tips

Re-run generation if the query is complex and you are unhappy with the result.
Add database schema to increase accuracy.

Shortcuts

On each generator you can hover the helper tool icon above the generated query to see the shortcut. And also use:

Run the generator: Press CMD + Enter (or Press CTRL + Enter on Windows)