AI SQL Examples

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

SELECT
  *
FROM
  address;

SELECT
  *
FROM
  address
LIMIT
  10;

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

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

SELECT
  SUM(payment.amount)
FROM
  payment;

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

SELECT
  AVG(rental_rate)
FROM
  film;

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

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;

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

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;

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;

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;

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

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

ALTER TABLE actor
ADD COLUMN active BOOLEAN NOT NULL DEFAULT FALSE;

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