Text-to-SQL Guide for Core and Advanced SQL Queries

Get all customers

Generated SQL:

SELECT
  *
FROM
  customer;

SELECT column1, column2
FROM table_name;

Select all columns:

SELECT *
FROM table_name;

Get customers named Smith

Generated SQL:

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

SELECT column1, column2
FROM table_name
WHERE condition;

Common operators:

Example with OR:

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

Example with AND:

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

Count the number of customers in each country

Generated SQL:

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

Example:

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

Get the first 5 customers

Generated SQL:

SELECT
  customer_id,
  first_name,
  last_name
FROM
  customer
LIMIT 5;

SELECT column1, column2
FROM table_name
LIMIT number;

With offset:

SELECT column1, column2
FROM table_name
LIMIT offset, number;

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

Generated SQL:

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

Example:

UPDATE customer
SET store_id = 2
WHERE store_id = 1;

Get customers who did not purchase in the last 12 months but did purchase in the previous 12 months

Generated SQL:

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;

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

Generated SQL:

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;

Find customers who purchased last year and sort by number of purchases

Generated SQL:

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;

Create a view called Customers NYC with customer, address, and rental data

Generated SQL:

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