Skip to content

Data-Driven Decision Making in SQL

Here you can access every table used in the course. To access each table, you will need to specify the movies schema in your queries (e.g., movies.movies for the movies table, and movies.customers for the customers table).

Take Notes

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

Tables in the course

MOVIES: is the main schema and have 5 different tables

  1. Customers
  2. Movies
  3. Renting: All movies rentals
  4. Actors: Actors in the movies
  5. Actsin: MOVIES <> ACTORS -> Which actors appeared in each movie

Explore databases

Spinner
DataFrameas
df
variable
[2]
-- Add your own queries here
SELECT *
FROM movies.customers
LIMIT 5
Spinner
DataFrameas
df1
variable
-- Add your own queries here
SELECT *
FROM movies.movies
LIMIT 5
Spinner
DataFrameas
df2
variable
-- Add your own queries here
SELECT *
FROM movies.renting
LIMIT 5
Spinner
DataFrameas
actors
variable
-- Add your own queries here
SELECT *
FROM movies.actors
LIMIT 5

Money Spent per customers with sub-queries

  1. Renting price for rental and its customer id
  • Customer_ID
  • Renting_Price
  1. Sum of all renting per customer
  • Customer_ID
  • sum(Renting_Price)
Spinner
DataFrameas
df3
variable
SELECT rm.customer_id, SUM(rm.renting_price)

FROM (SELECT r.customer_id,  
	  	m.renting_price
	  FROM movies.renting AS r 
	  LEFT JOIN movies.movies AS m 
	  ON r.movie_id=m.movie_id) AS rm 
	  
GROUP BY rm.customer_id 
	  ;
Spinner
DataFrameas
df4
variable
SELECT extract(year from date_trunc('decade', date_of_birth))
from movies.customers

Nested Subquery

  • Who are the actors in the movie Ray?
  1. Filter Movie with title Ray
  • Get it's moview_id
  1. Using another table Actsin do the match MOVIES <> ACTOR
  • Get actor_id from the ones that participated in the movie
  1. Get the name given actor_id
Spinner
DataFrameas
df5
variable
SELECT c.name
FROM movies.actors as c
WHERE actor_id IN
	(SELECT actor_id
	FROM movies.actsin
	WHERE movie_id =
	    -- Filter Movie with title Ray
		(SELECT movie_id
		FROM movies.movies
		WHERE title='Ray'));

Correlated Queries

Definition= The Subquery is evaluated multiple times

  • In other words:

  • Condition in the WHERE clause of the inner query.

  • References some column of a table in the outer query.

Exists

  • Same as IN but for correlated queries
‌
‌
‌