SQL Case Study: Real Problem

Review the essentials

In this exercise you must prepare the data you need (title & description) to run a promotion for the store's Italian & French language films from 2005.

To practice the essentials you will prepare this query one step at a time.

 

SELECT title, description

FROM film AS f

INNER JOIN language AS l

  ON f.language_id = l.language_id

WHERE l.name IN ('Italian', 'French')

  AND f.release_year = '2005' ;

 

Practice the essentials

In this exercise you are preparing list of your top paying active customers. The data you will need are the names of the customer sorted by the amount they paid.

 

SELECT c.first_name,

     c.last_name,

       p.amount

FROM payment AS p

INNER JOIN customer AS c --in inner join

--it does not really matter what 

-- you do with your shitty join placement

  ON p.customer_id = c.customer_id

WHERE c.active = 'true'

ORDER BY p.amount Desc;

 

 

Transform numeric & strings

For this exercise you are planning to run a 50% off promotion for films released prior to 2006. To prepare for this promotion you will need to return the films that qualify for this promotion, to make these titles easier to read you will convert them all to lower case. You will also need to return both the original_rate and the sale_rate.

 

SELECT lower(title) AS title, 

  rental_rate AS original_rate, 

  rental_rate * 0.5 AS sale_rate 

FROM film

-- Filter for films prior to 2006

Where release_year < '2006';

 

 

 In this exercise you would like to learn more about the differences in payments between the customers who are active and those who are not.

SELECT active, 

       count(p.payment_id) AS num_transactions, 

       avg(p.amount) AS avg_amount, 

       sum(p.amount) AS total_amount

FROM payment AS p

INNER JOIN customer AS c

  ON p.customer_id = c.customer_id

GROUP BY c.active;

 

 

A VIEW of all your columns

In this exercise you will create a new tool for finding the tables and columns you need. Using the system table information_schema.columns you will concatenate the list of each table's columns into a single entry.

Once you've done this you will make this query easily reusable by creating a new VIEW for it called table_columns.

-- Create a new view called table_columns

CREATE view table_columns AS

SELECT table_name, 

       STRING_AGG(column_name, ', ') AS columns

FROM information_schema.columns

WHERE table_schema = 'public'

GROUP BY table_name;

-- Query the newly created view table_columns

select * from table_columns;

 

Tools for finding your data

 

SELECT title, COUNT(title)

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

GROUP BY title

ORDER BY count DESC;

 Creating new tables:

-- Create a new table called oscars

CREATE TABLE oscars (

    title VARCHAR,

    award VARCHAR

);

-- Insert the data into the oscars table

INSERT INTO oscars (title, award)

VALUES

('TRANSLATION SUMMER', 'Best Film'),

('DORADO NOTTING', 'Best Film'),

('MARS ROMAN', 'Best Film'),

('CUPBOARD SINNERS', 'Best Film'),

('LONELY ELEPHANT', 'Best Film');

-- Confirm the table was created and is populated

SELECT * 

FROM oscars;

 

Using existing data

You are interested in identifying and storing information about films that are family-friendly. To do this, you will create a new table family_films using the data from the film table. This new table will contain a subset of films that have either the rating G or PG.

 

Create Table family_films AS

SELECT *

FROM film

WHERE rating IN ('G', 'PG');

 

A family friendly movie store

Your company has decided to become a family friendly store. As such, all R & NC-17 movies will be cleared from the inventory. You will take the steps necessary to clear these films from both the inventory and the film tables.

-- Use the list of film_id values to DELETE all R & NC-17 rated films from inventory.

DELETE FROM inventory

WHERE film_id IN (

  SELECT film_id FROM film

  WHERE rating IN ('R', 'NC-17')

);

-- Delete records from the `film` table that are either rated as R or NC-17.

DELETE FROM film

WHERE rating IN ('R', 'NC-17');