AI SQL Examples

We run automated tests to ensure that the generated SQL queries and statements are best-in-class and don't regress.

Database sample used for test suite: Postgres and MySQL.

Results from Thu, 23 Mar 2023 12:31:35 GMT (total tokens: 8675)

actor's first name and last name that have Nick, Ed and Jennifer as their first names

SELECT first_name, last_name FROM actor WHERE first_name IN ('Nick', 'Ed', 'Jennifer');

Usage: 452 tokens (prompt: 422, completion: 30). Database engine: mysql.

all the details of the address

SELECT * FROM address;

Usage: 416 tokens (prompt: 410, completion: 6). Database engine: mysql.

all the details of the address (take 10)

SELECT * FROM address LIMIT 10;

Usage: 422 tokens (prompt: 413, completion: 9). Database engine: mysql.

how many movies are rented out

SELECT COUNT(*) FROM rental WHERE return_date IS NULL;

Usage: 431 tokens (prompt: 410, completion: 21). Database engine: mysql.

get all movies that are in inventory and are not rented out

SELECT film.title FROM film INNER JOIN inventory ON film.film_id = inventory.film_id WHERE inventory.rental_id IS NULL;

Usage: 460 tokens (prompt: 416, completion: 44). Database engine: mysql.

how much money did we make in total

SELECT SUM(payment.amount) FROM payment;

Usage: 426 tokens (prompt: 412, completion: 14). Database engine: mysql.

what is the highest and lowest movie rental price

SELECT MIN(rental_rate), MAX(rental_rate) FROM film;

Usage: 434 tokens (prompt: 413, completion: 21). Database engine: mysql.

what is the average movie rental price

SELECT AVG(rental_rate) FROM film;

Usage: 427 tokens (prompt: 411, completion: 16). Database engine: mysql.

movies rented per month

SELECT MONTHNAME(rental_date) AS 'Month', COUNT(*) AS 'Movies Rented' FROM rental GROUP BY MONTHNAME(rental_date) ORDER BY MONTHNAME(rental_date);

Usage: 465 tokens (prompt: 409, completion: 56). Database engine: mysql.

show me all movies and categorize each by length: Short, Medium, Long and Very Long

SELECT film.title, CASE WHEN film.length <= 60 THEN 'Short' WHEN film.length > 60 AND film.length <= 90 THEN 'Medium' WHEN film.length > 90 AND film.length <= 120 THEN 'Long' WHEN film.length > 120 THEN 'Very Long' END AS movie_length FROM film;

Usage: 512 tokens (prompt: 423, completion: 89). Database engine: mysql.

extract first name from customer emails by using dot

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(customer.email, '.', 1), '@', -1) AS first_name FROM customer;

Usage: 455 tokens (prompt: 414, completion: 41). Database engine: mysql.

get all staff members with name, address, phone, zip code, city, country and store id

SELECT staff.first_name, staff.last_name, address.address, address.phone, address.postal_code, city.city, country.country, store.store_id FROM staff INNER JOIN address ON staff.address_id = address.address_id INNER JOIN city ON address.city_id = city.city_id INNER JOIN country ON city.country_id = country.country_id INNER JOIN store ON staff.store_id = store.store_id;

Usage: 544 tokens (prompt: 424, completion: 120). Database engine: mysql.

get stores with total sales revenue, staff manager's name, and location city

SELECT s.store_id, SUM(p.amount) AS total_sales_revenue, CONCAT(st.first_name, ' ', st.last_name) AS manager_name, c.city FROM store s JOIN staff st ON s.manager_staff_id = st.staff_id JOIN payment p ON s.store_id = p.staff_id JOIN address a ON s.address_id = a.address_id JOIN city c ON a.city_id = c.city_id GROUP BY s.store_id;

Usage: 563 tokens (prompt: 419, completion: 144). Database engine: mysql.

find customers who didn't rent a movie in the last 12 months but rented a movie in the 12 months before that

SELECT c.customer_id, c.first_name, c.last_name FROM customer c INNER JOIN rental r ON c.customer_id = r.customer_id WHERE r.rental_date < (NOW() - INTERVAL 12 MONTH) AND r.rental_date > (NOW() - INTERVAL 24 MONTH) GROUP BY c.customer_id;

Usage: 529 tokens (prompt: 427, completion: 102). Database engine: mysql.

create table movies with id, title, description, release year, language_id, rental_duration, rental rate, length, replacement cost, rating, special features, last update

CREATE TABLE movies ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, description TEXT NOT NULL, release_year INT NOT NULL, language_id INT NOT NULL, rental_duration INT NOT NULL, rental_rate DECIMAL(10, 2) NOT NULL, length INT NOT NULL, replacement_cost DECIMAL(10, 2) NOT NULL, rating VARCHAR(255) NOT NULL, special_features VARCHAR(255) NOT NULL, last_update TIMESTAMP NOT NULL );

Usage: 587 tokens (prompt: 440, completion: 147). Database engine: mysql.

update all actors starting with a vowel to also start their last name with that vowel

UPDATE actor SET last_name = CONCAT( SUBSTRING(last_name, 1, 1), SUBSTRING(first_name, 1, 1) ) WHERE first_name LIKE 'A%' OR first_name LIKE 'E%' OR first_name LIKE 'I%' OR first_name LIKE 'O%' OR first_name LIKE 'U%';

Usage: 506 tokens (prompt: 420, completion: 86). Database engine: mysql.

add a column called "active" to the actor table


Usage: 437 tokens (prompt: 415, completion: 22). Database engine: mysql.

create new film with title "The Matrix", description "A computer hacker learns from mysterious rebels about the true nature of his reality and his role in the war against its controllers.", release year 1999, language_id 1, rental_duration 6, rental rate 0.99, length 136, replacement cost 19.99, rating "R", special features "Trailers,Commentaries", last update "2006-02-15 09:34:33"

INSERT INTO film ( title, description, release_year, language_id, rental_duration, rental_rate, length, replacement_cost, rating, special_features, last_update ) VALUES ( 'The Matrix', 'A computer hacker learns from mysterious rebels about the true nature of his reality and his role in the war against its controllers.', 1999, 1, 6, 0.99, 136, 19.99, 'R', 'Trailers,Commentaries', '2006-02-15 09:34:33' );

Usage: 609 tokens (prompt: 494, completion: 115). Database engine: mysql.

Noticed a missing test case? Please send us an email.