SQLAI.ai

How to Generate SQL Query (and NoSQL)

Table of Contents

Introduction

Generate SQL query screenshot
Generate SQL query screenshot

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'
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.

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' }, });
Generation for MongoDB
Generation for MongoDB

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)