{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ee35dfd7-fb09-45dc-8d6d-d166114d3cea",
   "metadata": {},
   "source": [
    "## PROBLEM 2 (5 points)\n",
    "### Each store maintains an inventory of films. Some films are inventoried multiple times. For each store, which inventory items, if any, represent films that have *not* been rented? Display in a dataframe the store ids, inventory ids, film ids, and film titles, sorted by store id, inventory id, and film id."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ba7877d-32d7-46fe-afeb-0baf8e2d9268",
   "metadata": {},
   "outputs": [],
   "source": [
    "from data201 import db_connection, df_query\n",
    "conn = db_connection(config_file='sakila.ini')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87d412b7-37df-4392-a666-8a6b81ba27f5",
   "metadata": {},
   "source": [
    "### Solution using a `LEFT OUTER JOIN`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "09920601-57c4-4e62-8e95-4ca35f938b94",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    SELECT store_id, inventory_id, film_id, title\n",
    "    FROM inventory\n",
    "    LEFT OUTER JOIN rental USING (inventory_id)\n",
    "    JOIN film USING (film_id)\n",
    "    WHERE rental_id IS NULL\n",
    "    ORDER BY store_id, inventory_id, film_id\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e46126fc-d766-43e6-bf0c-9402c4473dc3",
   "metadata": {},
   "source": [
    "### Solution using `GROUP BY HAVING` and the `COUNT()` function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eecfe4a1-bc64-4aaf-b325-97471673d459",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    SELECT store_id, inventory_id, film_id, title\n",
    "    FROM inventory\n",
    "    LEFT OUTER JOIN rental USING (inventory_id)\n",
    "    JOIN film USING (film_id)\n",
    "    GROUP BY store_id, inventory_id\n",
    "    HAVING COUNT(rental_id) = 0\n",
    "    ORDER BY store_id, inventory_id, film_id\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06a1990c-93c6-4614-81bb-ebd7586a523e",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ed264f67-c50d-482b-a6d1-43e90509989c",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}