SQL for Everybody

Harness the power of AI to write SQL (Structured Query Language) effortlessly. Get the precise SQL queries and data you need, exactly when you need them.

Effortless use and learn SQL

SQL is the standard language for interacting with databases, enabling you to retrieve, add, update, and delete data. Whether you're using MySQL, PostgreSQL, MSSQL, or other relational databases, our AI-powered tool makes SQL accessible to everyone.

Below, you can see how easy it is to use AI to generate various SQL statements and clauses. The SQL examples are based on the video rental store database, Sakila for MySQL. (SQL queries for other databases are very similar.)

If you examine the Sakila database schema, you'll notice it consists of separate tables. Some of these tables are interconnected, such as customer and rental, using a foreign key. Rows in the rental table that belong to a specific customer are referenced using the customer_id field. This concept is fundamental to relational databases, allowing for effective organization and connection of diverse data.

SQL SELECT statement

The SELECT statement is used to select data from a database (as opposed the INSERT or DELETE statement).

What to write to get SQL query

Get customers

SQL query

            
              
            
            SELECT
  *
FROM
  customer;

The column1, column2, ... are the field names of the table you want to select data from. The table_name represents the name of the table you want to select data from:

            
              
            
            SELECT column1, column2, ... FROM table_name;

Alternatively, you can use * (asterisk) to choose all columns in the table instead of writing them out:

            
              
            
            SELECT * FROM FROM table_name;

Running the generated SQL query would return the following data:

customer_id	store_id	first_name	last_name	email	address_id	active	create_date	last_update
1	1	MARY	SMITH	MARY.SMITH@sakilacustomer.org	5	1	2006-02-14T21:04:36.000Z	2006-02-15T03:57:20.000Z
2	1	PATRICIA	JOHNSON	PATRICIA.JOHNSON@sakilacustomer.org	6	1	2006-02-14T21:04:36.000Z	2006-02-15T03:57:20.000Z
3	1	LINDA	WILLIAMS	LINDA.WILLIAMS@sakilacustomer.org	7	1	2006-02-14T21:04:36.000Z	2006-02-15T03:57:20.000Z
4	2	BARBARA	JONES	BARBARA.JONES@sakilacustomer.org	8	1	2006-02-14T21:04:36.000Z	2006-02-15T03:57:20.000Z
5	1	ELIZABETH	BROWN	ELIZABETH.BROWN@sakilacustomer.org	9	1	2006-02-14T21:04:36.000Z	2006-02-15T03:57:20.000Z

SQL WHERE clause

The WHERE clause is used to filter records. It can be used in SELECT, UPDATE, WHERE and other statements to filter records.

What to write to get SQL query

Get customers named Smith

SQL query

            
              
            
            SELECT
  customer_id, -- Retrieve specified columns instead of all using *
  first_name,
  last_name
FROM
  customer
WHERE
  last_name = 'Smith';

The syntax is the following:

            
              
            
            SELECT column1, column2, ... FROM table_name WHERE condition;

The following operators are available (which can be combined using "AND" or "OR"):

Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<>	Not equal. Note: In some versions of SQL this operator may be written as !=
BETWEEN	Between a certain range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

Here are a few examples using the different operators. These are easily generated using AI by simply instructing it with your filtering criteria/logic.

Given instruction: Get customers named Smith or Jones:

            
              
            
            SELECT
  customer_id,
  first_name,
  last_name
FROM
  customer
WHERE
  last_name = 'Smith' OR last_name = 'Jones';

Given instruction: Get customers named Mary Smith:

            
              
            
            SELECT
  customer_id,
  first_name,
  last_name
FROM
  customer
WHERE
  first_name = 'Mary' AND last_name = 'Smith';

Given instruction: Get customers created the past month and with an email from @gmail.com:

            
              
            
            SELECT
  customer_id,
  first_name,
  last_name,
  email
FROM
  customer
WHERE
  create_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
  AND email LIKE '%@gmail.com';

customer_id	first_name	last_name
1	MARY	SMITH

SQL ORDER BY clause

The ORDER BY clause is used to sort the result-set in ascending or descending order. It is used with the SELECT statement.

What to write to get SQL query

Get customers ordered by last name

SQL query

            
              
            
            SELECT
  customer_id,
  first_name,
  last_name
FROM
  customer
ORDER BY
  last_name ASC;

The syntax is the following:

            
              
            
            SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

Here are a few examples using the different keywords. These are easily generated using AI by simply instructing it with your sorting criteria.

Given instruction: Get customers ordered by last name in descending order:

            
              
            
            SELECT
  customer_id,
  first_name,
  last_name
FROM
  customer
ORDER BY
  last_name DESC;

Given instruction: Get customers ordered by last name then first name:

            
              
            
            SELECT
  customer_id,
  first_name,
  last_name
FROM
  customer
ORDER BY
  last_name ASC,
  first_name ASC;

Given instruction: Get customers ordered by creation date from newest to oldest:

            
              
            
            SELECT
  customer_id,
  first_name,
  last_name,
  create_date
FROM
  customer
ORDER BY
  create_date DESC;

customer_id	first_name	last_name
2	JOHN	DOE
3	JANE	JOHNSON
1	MARY	SMITH

SQL FROM keyword

The FROM keyword is used to specify the table from which to retrieve records. It is used in conjunction with multiple SQL statements, e.g. SELECT or DELETE.

What to write to get SQL query

Get all customers

SQL query

            
              
            
            SELECT
  *
FROM
  customer;

The syntax is the following:

            
              
            
            SELECT column1, column2, ... FROM table_name;

The FROM keyword is required in every SELECT statement. It follows the SELECT keyword and specifies the table where the data resides.

Running the generated SQL query would return the following data:

customer_id	first_name	last_name	email	active	create_date	last_update	address_id
1	MARY	SMITH	MARY.SMITH@sakilacustomer.org	1	2006-02-14 22:04:36	2006-02-15 04:57:20	5
2	PATRICIA	JOHNSON	PATRICIA.JOHNSON@sakilacustomer.org	1	2006-02-14 22:04:36	2006-02-15 04:57:20	6
3	LINDA	WILLIAMS	LINDA.WILLIAMS@sakilacustomer.org	1	2006-02-14 22:04:36	2006-02-15 04:57:20	7
4	BARBARA	JONES	BARBARA.JONES@sakilacustomer.org	1	2006-02-14 22:04:36	2006-02-15 04:57:20	8
5	ELIZABETH	BROWN	ELIZABETH.BROWN@sakilacustomer.org	1	2006-02-14 22:04:36	2006-02-15 04:57:20	9
...

SQL GROUP BY clause

The GROUP BY statement groups rows that have the same values in one or more columns into a summary row, like "find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

What to write to get SQL query

Count the number of customers in each country

SQL query

            
              
            
            SELECT
  COUNT(customer_id),
  country
FROM
  customer
GROUP BY
  country;

We assume the customer table has a country column.

The syntax is the following:

            
              
            
            SELECT
  column_name(s)
FROM
  table_name
WHERE
  condition
GROUP BY
  column_name(s);

Using AI, you can easily generate SQL queries with GROUP BY clauses by providing instructions specifying the grouping and aggregation required.

Given instruction: Find the total sales amount for each product:

            
              
            
            SELECT
  product_id,
  SUM(amount) AS total_sales
FROM
  sales
GROUP BY
  product_id;

Given instruction: Get the average customer age for each city:

            
              
            
            SELECT
  city,
  AVG(age) AS average_age
FROM
  customer
GROUP BY
  city;

Given instruction: Count the number of orders for each customer created in the last month:

            
              
            
            SELECT
  customer_id,
  COUNT(order_id) AS total_orders
FROM
  orders
WHERE
  create_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
GROUP BY
  customer_id;

COUNT(customer_id)	country
5	USA
3	Canada
2	Mexico
1	UK

SQL JOIN clause

The JOIN clause is used to combine rows from two or more tables, based on a related column between them.

What to write to get SQL query

Get each customer with their address

SQL query

            
              
            
            SELECT
  customer.customer_id,
  customer.first_name,
  customer.last_name,
  address.address,
  address.district
FROM
  customer
JOIN address ON customer.address_id = address.address_id;

The basic syntax is the following:

            
              
            
            SELECT
  column1,
  column2,
  ...
FROM
  table1
JOIN table2 ON table1.column_name = table2.column_name;

Different types of JOINs:

JOIN Type	Description
INNER JOIN	Returns records that have matching values in both tables
LEFT (OUTER) JOIN	Returns all records from the left table	and the matched records from the right table
RIGHT (OUTER) JOIN	Returns all records from the right table	and the matched records from the left table
FULL (OUTER) JOIN	Returns all records when there is a match in either left or right table

Here are a few examples demonstrating different JOIN types. Using AI can help to quickly generate the code for your specific database schema and desired logic/filtering.

Given instruction: Get payments with customer information:

            
              
            
            SELECT
  payment.payment_id,
  payment.amount,
  customer.first_name,
  customer.last_name
FROM
  payment
INNER JOIN customer ON payment.customer_id = customer.customer_id;

Given instruction: Get all customers and their rental information if available:

            
              
            
            SELECT
  customer.customer_id,
  customer.first_name,
  customer.last_name,
  rental.rental_date
FROM
  customer
LEFT JOIN rental ON customer.customer_id = rental.customer_id;

Given instruction: Get film title, category, and language for each film:

            
              
            
            SELECT
  film.title,
  category.name AS category_name,
  language.name AS language_name
FROM
  film
  INNER JOIN film_category ON film.film_id = film_category.film_id
  INNER JOIN category ON film_category.category_id = category.category_id
  INNER JOIN language ON film.language_id = language.language_id;

Running the generated SQL query would return the following data:

customer_id	first_name	last_name	address	district
1	MARY	SMITH	1913 Hanoi Way	California
2	PATRICIA	JOHNSON	1121 Park Avenue	New York City
3	LINDA	WILLIAMS	692 Joliet Street	Attika
4	BARBARA	JONES	1566 Ingel Manor	Mandalay
5	ELIZABETH	BROWN	53 Ifu Parkway	Nantou
...

SQL LIMIT clause

The LIMIT clause is used to constrain the number of rows returned by the SELECT statement.

The LIMIT clause is typically used with large datasets where you want to avoid returning a huge number of rows or for pagination.

What to write to get SQL query

Get the first 5 customers

SQL query

            
              
            
            SELECT
  customer_id,
  first_name,
  last_name
FROM
  customer
LIMIT 5;

The syntax is the following:

            
              
            
            SELECT column1, column2, ... FROM table_name LIMIT number;

Optionally, you can add an offset to skip a certain number of rows, using this syntax: LIMIT [offset], [number]

Given instruction: Get the first 10 customers:

            
              
            
            SELECT
  customer_id,
  first_name,
  last_name
FROM
  customer
LIMIT 10;

Given instruction: Get 5 customers starting from the 6th one:

            
              
            
            SELECT
  customer_id,
  first_name,
  last_name
FROM
  customer
LIMIT 5, 5;

customer_id	first_name	last_name
1	MARY	SMITH
2	PATRICIA	JOHNSON
3	LINDA	WILLIAMS
4	BARBARA	JONES
5	ELIZABETH	BROWN

SQL INSERT Statement

The INSERT INTO statement is used to insert new records (rows) into a table.

What to write to get SQL query

Insert a new customer named John Doe with email john.doe@example.com

SQL query

            
              
            
            INSERT INTO
  customer (
    first_name, last_name, email, address_id, store_id, active, create_date
  )
VALUES
  (
    'JOHN', 'DOE', 'john.doe@example.com', 1, 1, 1, NOW ()
  );

There are two ways to write the INSERT INTO statement:

            
              
            
            -- Method 1: Specify both column names and values
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

            
              
            
            -- Method 2: If adding values for all columns, you can omit column names
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Important considerations:

String values must be enclosed in single quotes.
Numeric values should not be enclosed in quotes.
NULL values are used to represent missing or unknown data.
Date/time values should be in the format specified by your database (e.g., 'YYYY-MM-DD').
NOW() is a function that returns the current date and time.
Each value should correspond to the correct data type of its respective column.

customer_id	first_name	last_name	email
...	JOHN	DOE	john.doe@example.com

SQL DELETE Statement

The DELETE statement is used to delete existing records in a table.

What to write to get SQL query

Delete customer named "JANE" "DOE"

SQL query

            
              
            
            DELETE FROM customer
WHERE first_name = 'JANE' AND last_name = 'DOE';

NOTE:The WHERE clause is crucial; omitting it will delete all records in the table. The above will delete all records with the first_name "JANE" and last_name "DOE".

The syntax is the following:

            
              
            
            DELETE FROM table_name WHERE condition;

The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!

Here are a few examples. These are easily generated using AI by simply instructing it with your deletion criteria/logic.

Given instruction: Delete all customers with email from @hotmail.com:

            
              
            
            DELETE FROM customer
WHERE email LIKE '%@hotmail.com';

Before the DELETE statement, the table might look like this:

customer_id	first_name	last_name	email
1	MARY	SMITH	MARY.SMITH@sakilacustomer.org
2	JANE	DOE
3	JOHN	DOE	JOHN.DOE@hotmail.com

After running the DELETE statement to delete customers with no email, it would look like this:

customer_id	first_name	last_name	email
1	MARY	SMITH	MARY.SMITH@sakilacustomer.org
3	JOHN	DOE	JOHN.DOE@hotmail.com

SQL UPDATE statement

The UPDATE statement is used to modify the existing records in a table. It is usually combined with a WHERE clause to specify which record(s) to update.

What to write to get SQL query

Update email to mary.smith@sakilacustomer.org for customer with id 34

SQL query

            
              
            
            UPDATE customer
SET
  email = 'mary.smith@sakilacustomer.org'
WHERE
  customer_id = '31';

Note: Always use a WHERE clause when updating records, unless you want to update ALL records!

The syntax is the following:

            
              
            
            UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

The SET keyword specifies the column(s) and value(s) to update. The WHERE clause specifies which record(s) to update using operators (see WHERE documentation).

Here are a few examples. These are easily generated using AI.

Given instruction: Update the address for customer MARY SMITH to 1913 Hanoi Way:

            
              
            
            UPDATE customer
SET
  address_id = (
    SELECT
      address_id
    FROM
      address
    WHERE
      address = '1913 Hanoi Way'
  )
WHERE
  first_name = 'MARY'
  AND last_name = 'SMITH';

Given instruction: Update all customers from store 1 to store 2:

            
              
            
            UPDATE customer
SET
  store_id = 2
WHERE
  store_id = 1;

customer_id	first_name	last_name	email	address_id	store_id
1	MARY	SMITH	mary.smith@sakilacustomer.org	5	1

Advanced SQL queries

Here are some examples of advanced SQL queries generated by AI.

What to write to get SQL query

Get customers who didn't make a purchase in the last 12 months but a purchase in the 12 months before that

SQL query

            
              
            
            SELECT
  c.customer_id,
  c.first_name,
  c.last_name
FROM
  customer AS c
  JOIN payment AS p ON c.customer_id = p.customer_id
WHERE
  p.payment_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 24 MONTH) AND DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
  AND c.customer_id NOT IN(
    SELECT
      p2.customer_id
    FROM
      payment AS p2
    WHERE
      p2.payment_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 12 MONTH) AND CURDATE()
  )
GROUP BY
  c.customer_id,
  c.first_name,
  c.last_name;

What to write to get SQL query

Get customers within 500m of the Eiffel Tower or Times Square NY using lat/lng

SQL query

            
              
            
            SELECT
  c.first_name,
  c.last_name,
  c.email
FROM
  customer AS c
  JOIN address AS a ON c.address_id = a.address_id
WHERE
  ST_Distance_Sphere(a.location, POINT(2.2945, 48.8584)) <= 500
  OR ST_Distance_Sphere(a.location, POINT(-73.9855, 40.7580)) <= 500;

Note: We have added a geometry field to the address table; location, to test geo-spatial SQL query generation.

What to write to get SQL query

Find customers who made a purchase last year, categorize each by number of purchase and sort by purchases

SQL query

            
              
            
            SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  COUNT(p.payment_id) AS purchase_count
FROM
  customer AS c
  JOIN payment AS p ON c.customer_id = p.customer_id
WHERE
  YEAR(p.payment_date) = YEAR(CURDATE()) - 1
GROUP BY
  c.customer_id,
  c.first_name,
  c.last_name
ORDER BY
  purchase_count DESC;

What to write to get SQL query

Create view "Customers NYC" for all customers from New York City and add relevant rental and address information

SQL query

            
              
            
            CREATE VIEW `Customers NYC` AS
SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  c.email,
  a.address,
  a.address2,
  a.district,
  a.postal_code,
  a.phone,
  ci.city,
  co.country,
  r.rental_date,
  r.return_date
FROM
  customer AS c
  JOIN address AS a ON c.address_id = a.address_id
  JOIN city AS ci ON a.city_id = ci.city_id
  JOIN country AS co ON ci.country_id = co.country_id
  LEFT JOIN rental AS r ON c.customer_id = r.customer_id
WHERE
  ci.city = 'New York';

What to write to get SQL query

Get actors who has the most rentals grouped by store

SQL query

            
              
            
            SELECT
  s.store_id,
  a.first_name,
  a.last_name,
  COUNT(r.rental_id) AS rental_count
FROM
  rental AS r
  JOIN inventory AS i ON r.inventory_id = i.inventory_id
  JOIN store AS s ON i.store_id = s.store_id
  JOIN film_actor AS fa ON i.film_id = fa.film_id
  JOIN actor AS a ON fa.actor_id = a.actor_id
GROUP BY
  s.store_id,
  a.actor_id
ORDER BY
  s.store_id,
  rental_count DESC;

Why use SQLAI.ai?

We've packed our platform with powerful features to make your SQL workflow smoother and more efficient. Here's a glimpse of what we offer:

Create, Improve, and Understand Your SQL

From generating queries from scratch to fine-tuning existing ones, we've got you covered.

Write SQL (and NoSQL) queries instantly
Optimize SQL queries
Fix errors and validate your SQL
Explain complex SQL in plain English
Plus many more tools - explore them all!

Seamless Database Integration

Connect your database effortlessly and start working with your data in seconds.

Add or connect your database with a single click
Set up simple data source rules to enhance query accuracy
Quickly reference your database schema with autocomplete
Easily share databases with your team
Integrate with public API

Handy Tools for SQL Power Users

Boost your productivity with helpful features conveniently located right where you need them.

Run SQL (and NoSQL) queries directly on your connected database
A full-featured SQL editor (VS Code)
Easily compare improved SQL with "view diff"
And more tools to streamline your workflow!

Supported Databases

See all databases on the settings page or add it here.

BigQuery

MongoDB

Connect

Get started with SQL

Start boosting your productivity and skills by generating SQL and retrieve valuable data insights, without waiting for the data team.

Get started for free

FAQ

SQLAI.ai is an easy-to-use SQL multi-tool designed for both SQL beginners and professionals. Our core generators assist users in producing precise SQL or NoSQL queries, saving hours of work and resources. Additionally, SQLAI.ai is equipped with simple yet powerful helper tools, such as 'Run Query,' 'View Diff,' and 'Formatting.'

SQLAI.ai caters to a diverse range of users, from SQL beginners to seasoned professionals, each with different needs. While some require SQL generation, others seek data insights or SQL query optimization. Our users include developers, data analysts, business users, SQL learners, experienced SQL practitioners, small business owners, startups, and many more.

Yes, you can checkout the SQL examples page.

Here are some reasons to choose SQLAI.ai and why we stand out:

Unprecedented accuracy through highly optimized prompts, powered by the world's leading AI model and data source rules
Effortless handling of very large database schemas without exhausting the context window
Instant result streaming (no need to wait 5-15 seconds for an answer)
Fully encrypted and containerized database connectivity
A variety of powerful helper tools
Multi-lingual support (write in your preferred language)
Ease of use and affordability (pays off in no time)

Yes, we support generating NoSQL (and other query formats) for MongoDB, GraphQL, DynamoDB, Elasticsearch, Redis and others. See all on the setting page.

Yes, we have a public API. You can read more about it here.

Yes, SQLAI.ai has been successfully tested with databases containing over 600 tables to generate queries. You can add database schema here.

Yes, you can find our documentation here.

Yes, our data source connectivity acts only as a proxy between you and your data source. Connection credentials are stored fully encrypted on separate servers and can be deleted at any time. You can read more in our documentation.

SQL for Everybody

Harness the power of AI to write SQL (Structured Query Language) effortlessly. Get the precise SQL queries and data you need, exactly when you need them.

Effortless use and learn SQL

SQL SELECT statement

SQL WHERE clause

SQL ORDER BY clause

SQL FROM keyword

SQL GROUP BY clause

SQL JOIN clause

SQL LIMIT clause

SQL INSERT Statement

SQL DELETE Statement

SQL UPDATE statement

Advanced SQL queries

Why use SQLAI.ai?

Create, Improve, and Understand Your SQL

Seamless Database Integration

Handy Tools for SQL Power Users

Supported Databases

MySQL

PostgreSQL

SQL Server (MS)

Oracle PL/SQL

BigQuery

SQL (native)

MariaDB

SQLite

Snowflake

DB2

Sybase

Redshift

Trino SQL (AWS Athena)

Salesforce SOQL/SOSL

Presto

MongoDB

DynamoDB

PartiQL

GraphQL

Vertica

Cassandra

OrientDB

SAP Hana

Elasticsearch

Redis Query Engine

Splunk

ClickHouse

Microsoft Access

Neo4j

Apache Hive

Get started with SQL

FAQ

Trino SQL
(AWS Athena)

Salesforce
SOQL/SOSL