Skip to content

Functions for Manipulating Data in PostgreSQL

👋  Welcome to your new workspace! Here, you can experiment with the dvd rentals data you used in Functions for Manipulating Data in PostgreSQL and practice your newly learned skills with some challenges. You can find out more about DataCamp Workspace here.

This workspace takes about 30 minutes to complete, but you are free to experiment as long as you like!

1. Get Started

Below is a SQL cell. It is used to execute SQL queries. There is already a pre-written query to get you started that filters for movies where "space" is mentioned in the description.

You can click the "Browse tables" button in the upper righthand corner of the cell below to view the available tables.

Note: The databases from different PostgreSQL courses are available, which you can preview using the dropdown menu to the left. To access the dvdrentals data, you will need to add this schema name to access the tables (e.g., dvdrentals.film for the film table and dvdrentals.country for the country table).

Spinner
DataFrameas
movie_info
variable
SELECT 
	INITCAP(title), 
    release_year,
    name AS category,
    rating,
    description
FROM dvdrentals.film
INNER JOIN dvdrentals.film_category USING(film_id)
INNER JOIN dvdrentals.category USING(category_id)
WHERE to_tsvector(description) @@ to_tsquery('space')

2. Your Turn

Now it's your turn to try out some challenge queries using the cells below. To start, update the sample query below to:

  • 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".

🏃  To execute a query, click inside the cell to select it and click "Run" or the ► icon. You can also use Shift-Enter to run a selected cell and automatically navigate to the next cell.

Spinner
DataFrameas
df
variable
SELECT *
FROM dvdrentals.film
Where description

3. Keep going!

Continue to flex your SQL skills and update the sample query below to:

  • 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).
Spinner
DataFrameas
df
variable
SELECT *
FROM dvdrentals.payment
LIMIT 5

4. Final Challenge!

Great work! Now let's throw one final challenge your way. Update the sample query below to:

  • 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 *
FROM dvdrentals.film
LIMIT 5

5. Next Steps

Great work! Feel free to continue to experiment with these tables by creating a new SQL cell below, or if you're interested in more, try the following options: