Skip to content
Functions for Manipulating Data in PostgreSQL
  • AI Chat
  • Code
  • Report
  • Spinner

    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

    Unknown integration
    DataFrameavailable as
    movie_info
    variable
    -- Add your own queries here
    SELECT *
    FROM dvdrentals.film
    LIMIT 10
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

    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.").
    Unknown integration
    DataFrameavailable as
    df
    variable
    CONCAT(first_name, '', last_name) = first_name || '' || last_name
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
    Unknown integration
    DataFrameavailable as
    df1
    variable
    Kiedy potrzebujemy skórcić tekst do określonej liczby znaków (tutaj 50) ale nie chcemy mieć uciętego słowa. żeby znaleźć odpowiednią liczbę znaków dla funkcji LEFT odejmujemy od 50 pozycję, na kótrej znajduje się pierwsza spacja w odwróconym ciągu znaków. 
    
    SELECT 
      UPPER(c.name) || ': ' || f.title AS film_category, 
      -- Truncate the description without cutting off a word
      LEFT(description, 50 - 
        -- Subtract the position of the first whitespace character
        POSITION(
          ' ' IN REVERSE(LEFT(description, 50))
        )
      ) 
    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;
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.