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

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

Explore Datasets 1

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".
Spinner
DataFrameas
df
variable
SELECT title, release_year, rating,  LEFT(description, 50)||'...' AS description_shortened
FROM dvdrentals.film
WHERE special_features IS NOT NULL
;

Explore Datasets 2

  • Select the customer_id, amount, and payment_date from the payment table.
Spinner
DataFrameas
df1
variable
SELECT customer_id, amount, payment_date
FROM dvdrentals.payment
;

Explore Datasets 3

Extract date information from the payment_date column, creating new columns for the day, month, quarter, and year of transaction.

Spinner
DataFrameas
df2
variable
SELECT 
CAST(SUBSTRING(payment_date :: text, 9, 2) AS integer) AS day,
CAST(SUBSTRING(payment_date :: text, 6, 2) AS integer) AS month,
EXTRACT(quarter FROM TO_DATE(payment_date  :: text, 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"')) AS quarter,
CAST(SUBSTRING(payment_date :: text, 1, 4) AS integer) AS year
FROM
dvdrentals.payment
;

Explore Datasets 4

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

Spinner
DataFrameas
df3
variable
SELECT 
rental_date,
return_date,
    return_date - rental_date AS days_rented
FROM dvdrentals.rental
;

Explore Datasets 5

  • 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
df4
variable
SELECT INITCAP(CONCAT_WS(' ', SUBSTRING(title FROM 1 FOR POSITION(' ' IN title) - 1), SUBSTRING(title FROM POSITION(' ' IN title) + 1 FOR 1) || '.')) AS shortened_title
FROM dvdrentals.film;
;
Spinner
DataFrameas
df5
variable
SELECT DISTINCT time.year, time.month, COUNT(*) AS payed_rents
FROM
(
SELECT 
	CAST(SUBSTRING(payment_date :: text, 9, 2) AS integer) AS day,
	CAST(SUBSTRING(payment_date :: text, 6, 2) AS integer) AS month,
	CAST(SUBSTRING(payment_date :: text, 1, 4) AS integer) AS year
FROM
dvdrentals.payment) AS time
GROUP BY year, month
ORDER BY year, month
;
df5