Examples

Of course the best way is to try it out for yourself. Here are some examples of questions that you can ask. We are using the dvdrental database (opens in a new tab) for this.

Example 1

What we asked:

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

What we got:

1. The requested statement is asking for the first name and last name of actors who have either Nick, Ed, or Jennifer as their first name.

2. The relevant tables and columns are:

• actor: first_name, last_name, actor_id
• actor_info: actor_id, first_name

3. SQL statement for PostgreSQL:

SELECT DISTINCT
  actor.first_name,
  actor.last_name
FROM
  actor
  JOIN actor_info ON actor.actor_id = actor_info.actor_id
WHERE
  actor.first_name IN ('Nick', 'Ed', 'Jennifer');

Example 2

What we asked:

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

What we got:

1. The requested statement is asking for customers who rented a movie in the 12 months before the last 12 months, but did not rent a movie in the last 12 months.

2. Relevant tables and columns are:

• customer: customer_id
• rental: customer_id, rental_date, return_date

3. SQL statement for PostgreSQL:

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 < (CURRENT_DATE - INTERVAL '12 months')
  AND (
    r.return_date IS NULL
    OR r.return_date > (CURRENT_DATE - INTERVAL '12 months')
  )
  AND c.customer_id NOT IN (
    SELECT
      customer_id
    FROM
      rental
    WHERE
      rental_date >= (CURRENT_DATE - INTERVAL '12 months')
  )
GROUP BY
  c.customer_id;

Example 3

What we asked:

extract first name from customer emails by using dot

What we got:

1. The requested statement is asking to extract the first name from customer emails by using the dot as a delimiter.

2. The relevant table for this statement is the "customer" table, specifically the "email" column.

3. The following SQL statement can be used to extract the first name from the "email" column in the "customer" table:

SELECT
  SUBSTRING(
    email
    FROM
      1 FOR POSITION('.' IN email) -1
  ) AS first_name
FROM
  customer;

This statement uses the SUBSTRING function to extract the characters from the beginning of the email string up to the position of the dot delimiter. The POSITION function is used to find the position of the dot in the email string. The AS keyword is used to alias the resulting column as "first_name". The statement selects the "first_name" column from the "customer" table.