Skip to content

Functions for Manipulating Data in PostgreSQL

Here you can access the tables used in the course. To access the table, you will need to specify the dvdrentals schema in your queries (e.g., dvdrentals.film for the film table and dvdrentals.country for the country table).

Note: When using sample integrations such as those that contain course data, you have read-only access. You can run queries, but cannot make any changes such as adding, deleting, or modifying the data (e.g., creating tables, views, etc.).

Take Notes

Add notes about the concepts you've learned and SQL cells with queries you want to keep.

Add your notes here

Spinner
DataFrameas
movie_info
variable
-- Add your own queries here
SELECT *
FROM dvdrentals.film
LIMIT 10

Explore Datasets

Use the different tables to explore the data and practice your skills!

  • Select the title, release_year, and rating of films in the film table.
    • Add a description_shortened column which contains the first 50 characters of the description column, ending with "...".
    • Filter the film table for rows where the special_features column contains "Commentaries".
  • Select the customer_id, amount, and payment_date from the payment table.
    • Extract date information from the payment_date column, creating new columns for the day, month, quarter, and year of transaction.
    • Use the rental table to include a column containing the number of days rented (i.e., time between the rental_date and the return_date).
  • Update the title column so that titles with multiple words are reduced to the first word and the first letter of the second word followed by a period.
    • For example:
      • "BEACH HEARTBREAKERS" becomes "BEACH H."
      • "BEAST HUNCHBACK" becomes "BEAST H."
    • Reformat your shortened title to title case (e.g., "BEACH H." becomes "Beach H.").
Spinner
DataFrameas
df
variable
SELECT
 	-- Select the rental and return dates
	rental_date,
	return_date,
 	-- Calculate the expected_return_date
	rental_date + INTERVAL '3 days' AS expected_return_date
FROM rental;
Spinner
DataFrameas
df1
variable
SELECT 
  title, 
  special_features 
FROM film 
-- Filter where special_features contains 'Deleted Scenes'
WHERE special_features @> ARRAY['Deleted Scenes'];
Spinner
DataFrameas
df2
variable
SELECT
	CURRENT_TIMESTAMP(0)::timestamp AS right_now,
    interval '5 days' + CURRENT_TIMESTAMP(0) AS five_days_from_now;
Spinner
DataFrameas
df3
variable
SELECT 
  c.first_name || ' ' || c.last_name AS customer_name,
  f.title,
  r.rental_date,
  -- Extract the day of week date part from the rental_date
  EXTRACT(dow FROM r.rental_date) AS dayofweek,
  AGE(r.return_date, r.rental_date) AS rental_days,
  -- Use DATE_TRUNC to get days from the AGE function
  CASE WHEN DATE_TRUNC('day', AGE(r.return_date, r.rental_date)) > 
  -- Calculate number of d
    f.rental_duration * INTERVAL '1' day 
  THEN TRUE 
  ELSE FALSE END AS past_due 
FROM 
  film AS f 
  INNER JOIN inventory AS i 
  	ON f.film_id = i.film_id 
  INNER JOIN rental AS r 
  	ON i.inventory_id = r.inventory_id 
  INNER JOIN customer AS c 
  	ON c.customer_id = r.customer_id 
WHERE 
  -- Use an INTERVAL for the upper bound of the rental_date 
  r.rental_date BETWEEN CAST('2005-05-01' AS DATE) 
  AND CAST('2005-05-01' AS DATE) + INTERVAL '90 day';
Spinner
DataFrameas
df4
variable
SELECT 
  CONCAT(UPPER(c.name), ': ', f.title) AS film_category, 
  -- Truncate the description remove trailing whitespace
  TRIM(LEFT(description,50)) AS film_desc
FROM 
  film AS f 
  INNER JOIN film_category AS fc 
  	ON f.film_id = fc.film_id 
  INNER JOIN category AS c 
  	ON fc.category_id = c.category_id;