Applying_SQL_to _Real_World_Problems.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Applying SQL to Real-World Problems\n",
    "\n",
    "### Transform numeric & strings\n",
    "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.\n",
    "\n",
    "**Original data columns from film database**\n",
    "* film_id\t\n",
    "* title\t\n",
    "* description\t\n",
    "* release_year\t\n",
    "* language_id\t\n",
    "* rental_duration\t\n",
    "* rental_rate\t\n",
    "* length\t\n",
    "* replacement_cost\t\n",
    "* rating\t\n",
    "* special_features\n",
    "\n",
    "```SQL\n",
    "SELECT LOWER(title) AS title, \n",
    "  rental_rate AS original_rate, \n",
    "  rental_rate * 0.5 AS sale_rate \n",
    "FROM film\n",
    "-- Filter for films prior to 2006\n",
    "WHERE release_year < 2006;\n",
    "\n",
    "--------------------------------------------------------------------------------------------\n",
    "title\t          original_rate\t      sale_rate\n",
    "adaptation holes\t2.99\t           1.495\n",
    "annie identity      0.99\t           0.495\n",
    "--------------------------------------------------------------------------------------------\n",
    "```\n",
    "### Extract what you need\n",
    "In this exercise you will practice preparing date/time elements by using the EXTRACT() function.\n",
    "\n",
    "```SQL\n",
    "SELECT payment_date,\n",
    "  EXTRACT(DAY FROM payment_date) AS payment_day \n",
    "  EXTRACT(YEAR FROM payment_date) AS payment_YEAR \n",
    "FROM payment;\n",
    "--------------------------------------------------------------------------------------------\n",
    "payment_date\t        payment_day      payment_year\n",
    "2017-01-25 02:40:19+00:00\t25             2017\n",
    "2017-01-25 20:16:50+00:00\t25             2017\n",
    "2017-01-29 02:44:14+00:00\t29             2017\n",
    "--------------------------------------------------------------------------------------------\n",
    "```\n",
    "### Aggregating finances\n",
    "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.\n",
    "We have **customer** and **payment** databases as follow\n",
    "\n",
    "* customer\n",
    "    * customer_id\t\n",
    "    * first_name\t\n",
    "    * last_name\t\n",
    "    * email\t\n",
    "    * address_id\t\n",
    "    * active\n",
    "    \n",
    "* payment\n",
    "     * payment_id\t\n",
    "     * customer_id\t\n",
    "     * rental_id\t\n",
    "     * amount\t\n",
    "     * payment_date\n",
    "\n",
    "```SQL\n",
    "SELECT active, \n",
    "       --Identify the number of active and inactive customers by COUNT()ing them.\n",
    "       COUNT(active) AS num_active, \n",
    "       --Find out the differences in their average payments by using AVG().\n",
    "       AVG(amount) AS avg_amount, \n",
    "       --Find out the differences in their total payments by using SUM()\n",
    "       SUM(amount) AS total_amount\n",
    "FROM payment AS p\n",
    "INNER JOIN customer AS c\n",
    "  ON p.customer_id = c.customer_id\n",
    "--GROUP BY whether customer payments are active.\n",
    "GROUP BY active;\n",
    "\n",
    "-------------------------------------------------------------------------------------------\n",
    "active\tnum_active\tavg_amount\ttotal_amount\n",
    "false\t3278\t4.23893227577773\t13895.2199999994\n",
    "true\t12771\t4.19084566596158\t53521.2899999954\n",
    "-------------------------------------------------------------------------------------------\n",
    "```\n",
    "### Aggregating strings\n",
    "You are planning to update your storefront window to demonstrate how family-friendly and multi-lingual your DVD collection is. To prepare for this you need to prepare a comma-separated list G-rated film titles by language released in 2010.\n",
    "\n",
    "We have language and film databases\n",
    "* language\n",
    "    * language_id\t\n",
    "    * name\t\n",
    "    * last_update\n",
    "\n",
    "* film\n",
    "    * film_id\t\n",
    "    * title\t\n",
    "    * description\t\n",
    "    * release_year\t\n",
    "    * language_id\t\n",
    "    * rental_duration\t\n",
    "    * rental_rate\t\n",
    "    * length\t\n",
    "    * replacement_cost\t\n",
    "    * rating\t\n",
    "    * special_features\n",
    "    \n",
    "\n",
    "\n",
    "```SQL\n",
    "SELECT name, \n",
    "       -- Return a column with a list of comma-separated film titles by using the STRING_AGG() function.\n",
    "\t   STRING_AGG(title, ',') AS film_titles\n",
    "FROM film AS f\n",
    "INNER JOIN language AS l\n",
    "  ON f.language_id = l.language_id\n",
    "-- limiting results with release year 2010 and rating with 'G'\n",
    "WHERE release_year = 2010\n",
    "  AND rating = 'G'\n",
    "-- grouped by the language name\n",
    "GROUP BY name;\n",
    "\n",
    "--------------------------------------------------------------------------------------------\n",
    "name\t  film_titles\n",
    "English\t  ACE GOLDFINGER,VALLEY PACKER\n",
    "Japanese\t  AMISTAD MIDSUMMER,BUGSY SONG,DOCTOR GRAIL,MARRIED GO\n",
    "German\t  BEAUTY GREASE\n",
    "Mandarin\t  ATLANTIS CAUSE,AUTUMN CROW,CASUALTIES ENCINO,GARDEN ISLAND,RINGS HEARTBREAKERS,SAMURAI LION,SUICIDES SILENCE\n",
    "French\t  CAT CONEHEADS,DANCING FEVER,LUST LOCK\n",
    "Italian\t  DESPERATE TRAINSPOTTING,DWARFS ALTER,GRAPES FURY,JAWS HARRY,PACIFIC AMISTAD,PANIC CLUB\n",
    "--------------------------------------------------------------------------------------------\n",
    "```\n",
    "### Limiting your search\n",
    "\n",
    "You may find yourself working with tables that contain so many records that simple queries can take forever to load. This is especially challenging when you're searching for the right table to use and just need a quick result. For these scenarios the trick is to LIMIT the number of rows that your query returns. In this exercise you will practice using this function.\n",
    "\n",
    "```SQL\n",
    "SELECT * \n",
    "FROM payment\n",
    "--Before we limit we ORDER BY in the DESCending amount to select highest amount payed\n",
    "ORDER BY amount DESC \n",
    "LIMIT 10;\n",
    "\n",
    "--------------------------------------------------------------------------------------------\n",
    "payment_id\tcustomer_id\trental_id\tamount\tpayment_date\n",
    "24866\t237\t11479\t   11.99\t2017-03-03 01:46:39+00:00\n",
    "28814\t592\t3973\t    11.99\t2017-04-07 01:26:57+00:00\n",
    "23757\t116\t14763\t   11.99\t2017-03-22 02:02:26+00:00\n",
    "--------------------------------------------------------------------------------------------\n",
    "\n",
    "```\n",
    "### What tables are in your database?\n",
    "You don't have to rely solely on knowing what tables exist. Instead, you can query the pg_catalog.pg_tables to list all of the tables that exist in your database.\n",
    "\n",
    "Of course, this will list every table, including system tables so ideally, you want to limit your results to the schema where your data resides which in this case is 'public'.\n",
    "\n",
    "Note: This system table is specific to PostgreSQL but similar tables exist for other databases (see slides).\n",
    "\n",
    "```SQL\n",
    "SELECT * \n",
    "FROM pg_catalog.pg_tables\n",
    "WHERE schemaname = 'public';\n",
    "--------------------------------------------------------------------------------------------\n",
    "schemaname\ttablename\ttableowner\ttablespace\thasindexes\thasrules\thastriggers\trowsecurity\n",
    "public\t    actor\t      postgres\t  null\t    false\t    false\t    false\t    false\n",
    "public\t    address\t    postgres\t  null\t    false\t    false\t    false\t    false\n",
    "--------------------------------------------------------------------------------------------\n",
    "```\n",
    "### Determine the monthly income\n",
    "Now that you know how to find the table that you need to answer a question and how to use SQL to answer that question let's practice these skills end-to-end.\n",
    "\n",
    "How much does this business makes per month?\n",
    "\n",
    "First, you will need to use pg_catalog.pg_tables to find the possible tables and determine which tables & columns you need to answer that question. Second, you will leverage the tools you learned in the previous chapter to prepare the answer.\n",
    "```SQL\n",
    "-- List all tables in the public schema\n",
    "SELECT * \n",
    "FROM pg_catalog.pg_tables\n",
    "WHERE schemaname = 'public';\n",
    "\n",
    "-- Explore the tables and fill in the correct one\n",
    "SELECT * \n",
    "FROM payment \n",
    "LIMIT 10;\n",
    "\n",
    "-- Prepare the result\n",
    "SELECT EXTRACT(MONTH FROM payment_date) AS month, \n",
    "       SUM(amount) AS total_payment\n",
    "FROM payment\n",
    "GROUP BY MONTH;\n",
    "\n",
    "--------------------------------------------------------------------------------------------\n",
    "month\ttotal_payment\n",
    "1\t4781.54999999986\n",
    "4\t27664.4900000036\n",
    "3\t23886.5600000021\n",
    "5\t1409.15\n",
    "2\t9674.7599999996\n",
    "Showing 5 out of 5 rows\n",
    "--------------------------------------------------------------------------------------------\n",
    "```\n",
    "### What columns are in your database?\n",
    "Just like pg_catalog.pg_tables can be incredibly helpful for listing all the tables in your database, information_schema.columns can be used to list the columns of these tables. In this exercise, you will combine these system tables to get a list of all of the columns for all your tables (in the 'public' schema).\n",
    "\n",
    "Note: These system tables are specific to PostgreSQL but similar tables exist for other databases (see slides).\n",
    "```SQL\n",
    "SELECT table_name, column_name\n",
    "FROM information_schema.columns\n",
    "WHERE table_schema= 'public';\n",
    "--------------------------------------------------------------------------------------------\n",
    "table_name\t   column_name\n",
    "film\t      film_id\n",
    "rental\t      rental_date\n",
    "inventory\t  inventory_id\n",
    "customer\t  last_name\n",
    "payment        payment_date\n",
    "film\t       release_year\n",
    "film\t      replacement_cost\n",
    "customer\t  customer_id\n",
    "film\t      rental_rate\n",
    "film\t      description\n",
    "address        phone\n",
    "customer\t   email\n",
    "address\t      city\n",
    "customer\t   active\n",
    "film_actor\t   actor_id\n",
    "film\t       length\n",
    "address\t       address_id\n",
    "actor\t       first_name\n",
    "film\t        rental_duration\n",
    "language\t     name\n",
    "address\t          district\n",
    "rental\t         return_date\n",
    "--------------------------------------------------------------------------------------------\n",
    "```\n",
    "### A VIEW of all your columns\n",
    "In this exercise you will create a new tool for finding the tables and columns you need. Using the system tables information_schema.columns and pg_catalog.pg_tables you will concatenate the list of each table's columns into a single entry.\n",
    "\n",
    "Once you've done this you will make this query easily reusable by creating a new VIEW for it called table_columns.\n",
    "```SQL\n",
    "-- Create a new view called table_columns\n",
    "CREATE VIEW table_columns AS\n",
    "SELECT table_name, \n",
    "\t   STRING_AGG(column_name, ', ') AS columns\n",
    "FROM information_schema.columns\n",
    "WHERE table_schema = 'public'\n",
    "GROUP BY table_name\n",
    "ORDER BY columns;\n",
    "\n",
    "-- Query the newly created view table_columns\n",
    "SELECT *\n",
    "FROM table_columns;\n",
    "\n",
    "--------------------------------------------------------------------------------------------\n",
    "table_name\t        columns\n",
    "film_actor\t        actor_id, film_id\n",
    "category\t        category, film_id\n",
    "table_columns       columns, table_name\n",
    "film\t            film_id, release_year, replacement_cost, rental_rate, description, length, rental_duration,                       language_id, rating, title, special_features\n",
    "actor\t            first_name, actor_id, last_name\n",
    "inventory\t        inventory_id, film_id\n",
    "customer\t        last_name, customer_id, email, active, address_id, first_name\n",
    "language\t        name, language_id, last_update\n",
    "payment\t            payment_date, customer_id, amount, payment_id, rental_id\n",
    "address\t            phone, city, address_id, district, address, postal_code\n",
    "rental\t            rental_date, return_date, inventory_id, rental_id, customer_id\n",
    "--------------------------------------------------------------------------------------------\n",
    "```\n",
    "### The average length of films by category\n",
    "From the previous exercise you've learned that the tables film and category have the necessary information to calculate the average movie length for every category. You've also learned that they share a common field film_id which can be used to join these tables. Now you will use this information to query a list of average length for each category.\n",
    "```SQL\n",
    "-- Calculate the average_length for each category\n",
    "SELECT category, \n",
    "\t   AVG(length) AS average_length\n",
    "FROM film AS f\n",
    "-- Join the tables film & category\n",
    "INNER JOIN category AS c\n",
    "  ON f.film_id = c.film_id\n",
    "GROUP BY category\n",
    "-- Sort the results in ascending order by length\n",
    "ORDER BY average_length DESC;\n",
    "--------------------------------------------------------------------------------------------\n",
    "category\taverage_length\n",
    "Sports\t128.2027027027027027\n",
    "Games\t127.8360655737704918\n",
    "Foreign\t121.6986301369863014\n",
    "Drama\t120.8387096774193548\n",
    "Comedy\t115.8275862068965517\n",
    "Family\t114.7826086956521739\n",
    "Music\t113.6470588235294118\n",
    "Travel\t113.3157894736842105\n",
    "Horror\t112.4821428571428571\n",
    "Classics\t111.6666666666666667\n",
    "Action\t111.6093750000000000\n",
    "New\t111.1269841269841270\n",
    "Animation\t111.0151515151515152\n",
    "Children\t109.8000000000000000\n",
    "Documentary\t108.7500000000000000\n",
    "Sci-Fi\t108.1967213114754098\n",
    "--------------------------------------------------------------------------------------------\n",
    "```\n",
    "### Which films are most frequently rented?\n",
    "Now that you've figured out the relationships between the tables and their columns, you are ready to answer the question we started with:\n",
    "\n",
    "**Which films are most frequently rented?**\n",
    "\n",
    "*Use the relationship diagram to answer this question.*\n",
    "\n",
    "\n",
    "|film|inevntory|rental|\t\n",
    "|---|---|---|\n",
    "|film_id|film_id|rental_id|\n",
    "|release_year|inventory_id|inventory_id|\n",
    "|title|--|rental_date|\n",
    "|replacement_cost|--|customer_id|\n",
    "......\n",
    "\n",
    "\n",
    "```SQL\n",
    "SELECT title, COUNT(title)\n",
    "FROM film AS f\n",
    "INNER JOIN inventory AS i\n",
    "  ON f.film_id = i.film_id\n",
    "INNER JOIN rental AS r\n",
    "  ON i.inventory_id = r.inventory_id\n",
    "GROUP BY title\n",
    "ORDER BY count DESC;\n",
    "--------------------------------------------------------------------------------------------\n",
    "title\t            count\n",
    "BUCKET BROTHERHOOD\t34\n",
    "ROCKETEER MOTHER\t  33\n",
    "GRIT CLOCKWORK\t     32\n",
    "--------------------------------------------------------------------------------------------\n",
    "```\n",
    "Storing new data\n",
    "You're planing to run a promotion on movies that won a best film academy award in the last 5 years. To do this you need to add a table in your database containing the movies which won an Oscar for best film.\n",
    "\n",
    "The data you need for this exercise is provided in the table below:\n",
    "\n",
    "|title\t|award|\n",
    "|---|---|\n",
    "|'TRANSLATION SUMMER'\t|'Best Film'|\n",
    "|'DORADO NOTTING'|\t'Best Film'|\n",
    "|'MARS ROMAN'\t|'Best Film'|\n",
    "|'CUPBOARD SINNERS'|\t'Best Film'|\n",
    "|'LONELY ELEPHANT'\t|'Best Film'|\n",
    "\n",
    "\n",
    "```SQL\n",
    "-- Create a new table called oscars\n",
    "CREATE TABLE oscars (\n",
    "    title VARCHAR,\n",
    "    award VARCHAR\n",
    ");\n",
    "\n",
    "-- Insert the data into the oscars table\n",
    "INSERT INTO oscars (title, award)\n",
    "VALUES\n",
    "('TRANSLATION SUMMER', 'Best Film'),\n",
    "('DORADO NOTTING', 'Best Film'),\n",
    "('MARS ROMAN', 'Best Film'),\n",
    "('CUPBOARD SINNERS', 'Best Film'),\n",
    "('LONELY ELEPHANT', 'Best Film');\n",
    "\n",
    "-- Confirm the table was created and is populated\n",
    "SELECT * \n",
    "FROM oscars;\n",
    "--------------------------------------------------------------------------------------------\n",
    "title\t               award\n",
    "TRANSLATION SUMMER   Best Film\n",
    "DORADO NOTTING  \t Best Film\n",
    "MARS ROMAN      \t Best Film\n",
    "CUPBOARD SINNERS \tBest Film\n",
    "LONELY ELEPHANT  \tBest Film\n",
    "--------------------------------------------------------------------------------------------\n",
    "```\n",
    "**Using existing data**\n",
    "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.\n",
    "```SQL\n",
    "-- Create a new table named family_films using this query\n",
    "CREATE TABLE family_films AS\n",
    "SELECT *\n",
    "FROM film\n",
    "WHERE rating IN ('G', 'PG');\n",
    "\n",
    "```\n",
    "\n",
    "### Update the price of rentals\n",
    "You just learned that there have been some updates for the rental pricing of your films. In this exercise you will leverage the UPDATE command to modify the rental prices by increasing the rental_rate with the following logic.\n",
    "\n",
    "All films now cost 50 cents more to rent.\n",
    "R Rated films will go up by an additional 1 dollar.\n",
    "```SQL\n",
    "-- Increase rental_rate by 0.5 in the film table\n",
    "UPDATE film\n",
    "SET rental_rate = rental_rate + 0.5;\n",
    "\n",
    "UPDATE  film\n",
    "SET rental_rate = rental_rate +1\n",
    "WHERE rating = 'R';\n",
    "\n",
    "```\n",
    "### Updated based on other tables\n",
    "The rental company is running a promotion and needs you to lower the rental costs by 1 dollar of films who star the actors/actresses with the following last names: WILLIS, CHASE, WINSLET, GUINESS, HUDSON.\n",
    "\n",
    "To UPDATE this data in the film table you will need to identify the film_id for these actors.\n",
    "```SQL\n",
    "UPDATE  film\n",
    "SET rental_rate = rental_rate -1\n",
    "WHERE film_id IN\n",
    "  (SELECT film_id from actor AS a\n",
    "   INNER JOIN film_actor AS f\n",
    "      ON a.actor_id = f.actor_id\n",
    "   WHERE last_name IN ('WILLIS', 'CHASE', 'WINSLET', 'GUINESS', 'HUDSON'));\n",
    "\n",
    "```\n",
    "### Delete selected records\n",
    "You've discovered that some films are just not worth keeping your inventory, for cases where the replacement_cost is greater than 25 dollars. As such you'd like to remove them from you film table.\n",
    "```SQL\n",
    "-- Delete films that cost most than 25 dollars\n",
    "DELETE FROM film\n",
    "WHERE replacement_cost > 25\n",
    "\n",
    "```\n",
    "# A family friendly video store\n",
    "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.\n",
    "```SQL\n",
    "-- Use the list of film_id values to DELETE all R & NC-17 rated films from inventory.\n",
    "DELETE FROM inventory\n",
    "WHERE film_id IN (\n",
    "  SELECT film_id FROM film\n",
    "  WHERE rating IN ('R', 'NC-17')\n",
    ");\n",
    "\n",
    "-- Delete records from the `film` table that are either rated as R or NC-17.\n",
    "DELETE FROM film\n",
    "WHERE rating IN ('R', 'NC-17');\n",
    "\n",
    "```\n",
    "* WRITE READABLE CODE\n",
    "    * CAPITALIZE THE SQL COMMENT \n",
    "    * USE NEW LINE AND INTENTATION\n",
    "    * USE snake_case meaning use the undercode for space when you are naming tables and columns\n",
    "    * USE the *IN* comment instead of using multiple *OR* command\n",
    "    * USE the *BETWEEN* command is posible.\n",
    "\n",
    "\n",
    "```SQL\n",
    "\n",
    "--------------------------------------------------------------------------------------------\n",
    "\n",
    "--------------------------------------------------------------------------------------------\n",
    "```\n",
    "```SQL\n",
    "\n",
    "--------------------------------------------------------------------------------------------\n",
    "\n",
    "--------------------------------------------------------------------------------------------\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}