Script.sql

select rental_id, return_date
from rental
where return_date is null;

select title, rating
from film
where ( rating != 'G' and rating != 'PG' ) or rating is null;

select first_name , last_name 
from customer
where email is not null;
order by last_name desc;

select country_id, city
from city
order by country_id asc, city asc;

select first_name || ' ' || last_name as full_name, length(first_name || ' ' || last_name) as len
from actor
order by len desc; 

select payment_id, payment_date
from payment
order by payment_date desc
limit 3;

select title, length, rating
from film
where rating != 'R' or rating is null
order by length, title;

select payment_id, amount, payment_date, extract('month' from payment_date) as month
where extract('month' from payment_date) = 1
order by payment_date asc

select distinct rating
from film
where rating is not null;

select distinct date_part('hour' ,rental_date) as hour
from rental
order by hour;

select distinct rating, 
  case rating
    when 'G' then 'General'
    when 'PG' then 'PGR'
    when 'PG-13' then 'PSC'
    when 'R' then 'R'
    when 'NC-17' then 'AO'
  end
from film
where rating is not null
order by rating;

select rental_id, rental_date, return_date,
  case
    when return_date is null then 'Not Returned'
    else 'Returned'
  end as return_status
from rental
order by return_status

select country
from country
order by 
  case
    when country = 'Australia' then 'AAAA'
    when country = 'United Kingdom' then 'AAAA'
    when country = 'United States' then 'AAAAA'
    else country
  end;
  
select first_name, last_name, email
from customer
order by random()
limit 5;

select rental_id, rental_date
from rental
where date_part('month', rental_date) = 6 and date_part('year', rental_date) = 2005;

select rental_id, rental_date
from rental
where rental_date between date '2005-06-01' and '2005-07-01';

select rental_id, rental_date
from rental
where rental_date > date '2005-06-01' and rental_date < date '2005-07-01';

select title, rental_rate, length, rental_rate / length as per_minute
from film
where length != 0
order by per_minute desc
limit 5;

select first_name, regexp_matches(first_name, '.*A.*A.*')
from customer;

select first_name
from customer
where (first_name ~ '.*A.*A.*');

select distinct on (customer_id) customer_id, rental_date
from rental
order by customer_id, rental_date desc;

select first_name, last_name, email
from customer
where email != (first_name || '.' || last_name || '@sakilacustomer.org')

select count(distinct rating)
from film;

select sum(length)
from film;

select 
 min(amount),
 max(amount),
 avg(amount)
from payment;

select 
  count(*) as customers,
  count(email) as "customer with email",
  100.0 * count(email) / count(*) as "% with email"
from customer;

select count(distinct customer_id)
from customer;

select avg(return_date - rental_date)
from rental;

select sum(amount)
from payment;

select rating, sum(*) 
from film
group by rating;

select rating, avg(length)
from film
group by rating;

select customer_id, staff_id, count(*) as interactions
from payment
group by customer_id, staff_id
having count(*) > 20 /*operates on groups*/
order by interactions desc;


select rating, title
from film
order by rating;

select actor_id, count(*) as num_films
from film_actor
group by actor_id
order by num_films desc;

select customer_id 
from rental
group by customer_id
having count(*) > 40;

select 
  date_part('year', payment_date) as year,
  date_part('month', payment_date) as month,
  staff_id,
  count(*) as num_payments,
  sum(amount) as payment_total,
  avg(amount) as avg_payment
from payment
group by date_part('year', payment_date), date_part('month', payment_date), staff_id;


select 
  case 
    when length < 60 then 'short'
    when length between 60 and 120 then 'medium'
    when length > 120 then 'long'
    else 'short'
  end,
  count(*)
from film
group by 1; /* pastes first (case statement) from select into the group by, just a shortcut */

select 
  sum(case when rating in ('R', 'NC-17') then 1 else 0 end) as adult,
  count(*) as total_films
 from film;
 
 /* postgres way */
 select 
   count(*) filter(where rating in ('R', 'NC-17')) as adult,
   count(*)
from film;

select
  case /* could also use filter(where) */
    when return_date - rental_date  < interval '3 days' then 'lt 3 days'
    when return_date - rental_date  > interval '3 days' then 'gt 3 days'
    else 'never returned'
  end,
  count(*)
from rental
group by 1;

select customer_id, avg(return_date - rental_date)
from rental
group by customer_id
order by avg(return_date - rental_date) desc;

select customer_id
from payment
group by customer_id
having bool_and(amount > 2);

select rating, repeat('*', (count(*) / 10)::int) as "count/10"
from film
where rating is not null
group by rating;