{ "cells": [ { "cell_type": "markdown", "id": "fc870060", "metadata": {}, "source": [ "# Section 4.11 Practice: Movie Rental Database\n", "\n", "Ten problems, one for each corner of Section 4.11: date and time functions,\n", "numeric functions, string functions, and conversion functions. Every problem\n", "needs at least one function from the Chapter 4 review to answer; a plain\n", "`SELECT` is never enough on its own.\n", "\n", "## A note on running this in SQLite\n", "\n", "This notebook uses Python's built-in `sqlite3` module, so nothing has to be\n", "installed. The Chapter 4 review teaches function names in a MySQL-leaning\n", "style, and SQLite spells a few of them differently. Here is the mapping\n", "this notebook actually uses:\n", "\n", "| Chapter 4 review | This notebook (SQLite) |\n", "|---|---|\n", "| `YEAR(d)` | `strftime('%Y', d)` |\n", "| `DATE_ADD(d, INTERVAL n DAY)` | `date(d, '+n days')` |\n", "| `DATEDIFF(d1, d2)` | `julianday(d1) - julianday(d2)`, then `CAST( ... AS INTEGER)` |\n", "| `CONCAT(a, b)` | `a \\|\\| b` |\n", "| `SUBSTRING(s, start, len)` | `SUBSTR(s, start, len)` |\n", "| `ROUND()`, `CAST()`, `LENGTH()`, `UPPER()`, `LOWER()` | identical in SQLite |\n", "\n", "This is the same \"function names are the least portable part of SQL\" point\n", "the review page makes, just showing up in a second dialect firsthand.\n", "\n", "## The database\n", "\n", "Three tables: **MOVIE** (10 rows), **CUSTOMER** (8 rows), and **RENTAL**\n", "(20 rows). RENTAL stores only a `rental_date` and a `return_date`, nothing\n", "else; a due date, a late fee, and \"days rented\" all have to be computed with\n", "SQL, which is exactly the point of this notebook.\n", "\n", "Run the cell below once, first." ] }, { "cell_type": "code", "id": "e7e16367", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import sqlite3\n", "\n", "DB_FILENAME = \"movie_rental.db\"\n", "\n", "# (movie_id, title, genre, rating, release_date, rental_price)\n", "MOVIE_ROWS = [\n", " (1, \"The Great Heist\", \"Action\", \"PG-13\", \"2023-05-12\", 4.99),\n", " (2, \"Whispering Pines\", \"Drama\", \"PG\", \"2021-11-03\", 3.99),\n", " (3, \"Starlight Odyssey\", \"Sci-Fi\", \"PG-13\", \"2023-08-22\", 5.99),\n", " (4, \"Comedy Night Live\", \"Comedy\", \"PG-13\", \"2022-02-14\", 2.99),\n", " (5, \"The Last Lighthouse\", \"Drama\", \"PG\", \"2020-09-30\", 3.49),\n", " (6, \"Shadow Protocol\", \"Action\", \"R\", \"2023-01-18\", 4.99),\n", " (7, \"Sunflower Summer\", \"Family\", \"G\", \"2019-06-01\", 2.99),\n", " (8, \"Midnight Mystery\", \"Thriller\",\"PG-13\", \"2022-10-31\", 4.49),\n", " (9, \"Galactic Rangers\", \"Sci-Fi\", \"PG\", \"2023-03-09\", 5.49),\n", " (10, \"Quiet Storm\", \"Drama\", \"R\", \"2021-04-17\", 3.99),\n", "]\n", "\n", "# (customer_id, first_name, last_name, email, signup_date)\n", "CUSTOMER_ROWS = [\n", " (1, \"Sarah\", \"Sanchez\", \"sarah.sanchez@email.com\", \"2022-01-15\"),\n", " (2, \"James\", \"Kim\", \"james.kim@email.com\", \"2022-03-22\"),\n", " (3, \"Maria\", \"Alvarez\", \"maria.alvarez@email.com\", \"2021-11-05\"),\n", " (4, \"David\", \"Smith\", \"david.smith@email.com\", \"2023-02-10\"),\n", " (5, \"Priya\", \"Patel\", \"priya.patel@email.com\", \"2022-07-19\"),\n", " (6, \"Samuel\", \"Osei\", \"samuel.osei@email.com\", \"2023-05-01\"),\n", " (7, \"Linda\", \"Chen\", \"linda.chen@email.com\", \"2021-08-30\"),\n", " (8, \"Robert\", \"STEVENS\", \"robert.stevens@email.com\", \"2022-12-12\"),\n", "]\n", "\n", "# (rental_id, movie_id, customer_id, rental_date, return_date)\n", "# return_date of None means the movie has not been returned yet.\n", "# The standard rental period used throughout this notebook is 7 days.\n", "RENTAL_ROWS = [\n", " (1, 1, 1, \"2026-06-01\", \"2026-06-05\"),\n", " (2, 3, 2, \"2026-06-02\", \"2026-06-10\"),\n", " (3, 5, 3, \"2026-06-03\", \"2026-06-06\"),\n", " (4, 2, 4, \"2026-06-04\", None),\n", " (5, 6, 1, \"2026-06-05\", \"2026-06-09\"),\n", " (6, 9, 5, \"2026-06-06\", \"2026-06-15\"),\n", " (7, 1, 6, \"2026-06-07\", \"2026-06-12\"),\n", " (8, 8, 7, \"2026-06-08\", \"2026-06-20\"),\n", " (9, 4, 8, \"2026-06-09\", \"2026-06-11\"),\n", " (10, 10, 2, \"2026-06-10\", None),\n", " (11, 3, 3, \"2026-06-11\", \"2026-06-14\"),\n", " (12, 7, 4, \"2026-06-12\", \"2026-06-25\"),\n", " (13, 6, 5, \"2026-06-13\", \"2026-06-16\"),\n", " (14, 2, 6, \"2026-06-14\", \"2026-06-19\"),\n", " (15, 9, 7, \"2026-06-15\", \"2026-06-24\"),\n", " (16, 5, 8, \"2026-06-16\", \"2026-06-18\"),\n", " (17, 1, 3, \"2026-06-17\", None),\n", " (18, 8, 1, \"2026-06-18\", \"2026-06-21\"),\n", " (19, 10, 4, \"2026-06-19\", \"2026-06-28\"),\n", " (20, 4, 5, \"2026-06-20\", \"2026-06-22\"),\n", "]\n", "\n", "\n", "def create_database(filename=DB_FILENAME, quiet=False) :\n", " \"\"\"Create the database and return (connection, cursor). Safe to re-run.\"\"\"\n", " connection = sqlite3.connect(filename)\n", " cursor = connection.cursor()\n", "\n", " for table in [\"RENTAL\", \"CUSTOMER\", \"MOVIE\"] :\n", " cursor.execute(\"DROP TABLE IF EXISTS \" + table)\n", "\n", " cursor.execute(\"\"\"\n", " CREATE TABLE MOVIE (\n", " movie_id INTEGER PRIMARY KEY,\n", " title TEXT NOT NULL,\n", " genre TEXT,\n", " rating TEXT,\n", " release_date TEXT,\n", " rental_price REAL\n", " )\n", " \"\"\")\n", " cursor.execute(\"\"\"\n", " CREATE TABLE CUSTOMER (\n", " customer_id INTEGER PRIMARY KEY,\n", " first_name TEXT,\n", " last_name TEXT NOT NULL,\n", " email TEXT,\n", " signup_date TEXT\n", " )\n", " \"\"\")\n", " cursor.execute(\"\"\"\n", " CREATE TABLE RENTAL (\n", " rental_id INTEGER PRIMARY KEY,\n", " movie_id INTEGER,\n", " customer_id INTEGER,\n", " rental_date TEXT NOT NULL,\n", " return_date TEXT,\n", " FOREIGN KEY (movie_id) REFERENCES MOVIE (movie_id),\n", " FOREIGN KEY (customer_id) REFERENCES CUSTOMER (customer_id)\n", " )\n", " \"\"\")\n", "\n", " cursor.executemany(\"INSERT INTO MOVIE VALUES (?, ?, ?, ?, ?, ?)\", MOVIE_ROWS)\n", " cursor.executemany(\"INSERT INTO CUSTOMER VALUES (?, ?, ?, ?, ?)\", CUSTOMER_ROWS)\n", " cursor.executemany(\"INSERT INTO RENTAL VALUES (?, ?, ?, ?, ?)\", RENTAL_ROWS)\n", " connection.commit()\n", "\n", " if not quiet :\n", " print(\"Database ready:\", filename)\n", " print()\n", " for table in [\"MOVIE\", \"CUSTOMER\", \"RENTAL\"] :\n", " cursor.execute(\"SELECT COUNT(*) FROM \" + table)\n", " print(\" \" + table.ljust(10), cursor.fetchone()[0], \"rows\")\n", " print()\n", " cursor.execute(\"SELECT COUNT(*) FROM RENTAL WHERE return_date IS NULL\")\n", " print(\" Rentals still out: \", cursor.fetchone()[0])\n", " print()\n", " print(\"You are ready to start Problem 1.\")\n", "\n", " return connection, cursor\n", "\n", "\n", "connection, cursor = create_database()" ] }, { "cell_type": "markdown", "id": "005a44d2", "metadata": {}, "source": [ "## Problem 1. Movies released in 2023 *(4.11a Date and Time Functions)*\n", "\n", "Show the title and release date of every movie released in 2023, ordered by release date.\n", "\n", "**Hint.** SQLite has no `YEAR()` function like the one in the Chapter 4 review. Pull the year out of `release_date` with `strftime('%Y', release_date)` instead, and compare it to the text `'2023'`.\n", "\n", "*What to expect:* 4 rows." ] }, { "cell_type": "code", "id": "a96ee54e", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "cursor.execute(\"\"\"\n", "-- your SQL here\n", "\n", "\"\"\")\n", "for row in cursor.fetchall() :\n", " print(row)" ] }, { "cell_type": "markdown", "id": "6d3d8981", "metadata": {}, "source": [ "## Problem 2. Days rented, for completed rentals only *(4.11a Date and Time Functions)*\n", "\n", "For every rental that has already been returned, show the rental id, the rental date, the return date, and how many days the movie was kept.\n", "\n", "**Hint.** SQLite has no `DATEDIFF()`. Subtract two dates with `julianday(return_date) - julianday(rental_date)`, then wrap the result in `CAST( ... AS INTEGER)` so it prints as a whole number of days instead of a float. Don't forget to exclude rentals that have not come back yet.\n", "\n", "*What to expect:* 17 rows." ] }, { "cell_type": "code", "id": "52354150", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "cursor.execute(\"\"\"\n", "-- your SQL here\n", "\n", "\"\"\")\n", "for row in cursor.fetchall() :\n", " print(row)" ] }, { "cell_type": "markdown", "id": "95ffc7c6", "metadata": {}, "source": [ "## Problem 3. Every rental's due date *(4.11a Date and Time Functions)*\n", "\n", "The rental period is a flat 7 days. For the first 8 rentals (by rental_id), show the rental date and the due date, 7 days later.\n", "\n", "**Hint.** SQLite's version of `DATE_ADD()` is `date(rental_date, '+7 days')`. Note the due date is never stored anywhere in the table; it only exists once you compute it.\n", "\n", "*What to expect:* 8 rows." ] }, { "cell_type": "code", "id": "5d4519b9", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "cursor.execute(\"\"\"\n", "-- your SQL here\n", "\n", "\"\"\")\n", "for row in cursor.fetchall() :\n", " print(row)" ] }, { "cell_type": "markdown", "id": "e832858f", "metadata": {}, "source": [ "## Problem 4. Which rentals came back late *(4.11a Date and Time Functions)*\n", "\n", "Show every rental that was returned after its due date (rental date plus 7 days), along with the rental date, return date, and due date.\n", "\n", "**Hint.** Combine the idea from Problem 3 with a `WHERE` clause that compares `return_date` to the computed due date. Still returned rentals only; skip anything with a NULL return_date.\n", "\n", "*What to expect:* 6 rows." ] }, { "cell_type": "code", "id": "01dca9dd", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "cursor.execute(\"\"\"\n", "-- your SQL here\n", "\n", "\"\"\")\n", "for row in cursor.fetchall() :\n", " print(row)" ] }, { "cell_type": "markdown", "id": "3d41e638", "metadata": {}, "source": [ "## Problem 5. Rental prices, rounded to the nearest dollar *(4.11b Numeric Functions)*\n", "\n", "Show every movie's title, its exact rental price, and that price rounded to zero decimal places, for a simplified \"whole dollar\" pricing report.\n", "\n", "**Hint.** `ROUND(rental_price, 0)`, exactly as covered in Section 4.11b.\n", "\n", "*What to expect:* 10 rows." ] }, { "cell_type": "code", "id": "29006eab", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "cursor.execute(\"\"\"\n", "-- your SQL here\n", "\n", "\"\"\")\n", "for row in cursor.fetchall() :\n", " print(row)" ] }, { "cell_type": "markdown", "id": "31be6815", "metadata": {}, "source": [ "## Problem 6. Late fees at $0.75 per day *(4.11b Numeric Functions)*\n", "\n", "For every rental that came back late (Problem 4), show how many days late it was and a late fee of $0.75 per day late, rounded to the nearest cent.\n", "\n", "**Hint.** Reuse the days-rented calculation from Problem 2, subtract 7 to get days *late* instead of days rented, multiply by 0.75, and wrap the whole thing in `ROUND( ... , 2)`.\n", "\n", "*What to expect:* 6 rows." ] }, { "cell_type": "code", "id": "6f3beafc", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "cursor.execute(\"\"\"\n", "-- your SQL here\n", "\n", "\"\"\")\n", "for row in cursor.fetchall() :\n", " print(row)" ] }, { "cell_type": "markdown", "id": "7fe63508", "metadata": {}, "source": [ "## Problem 7. A \"Now Showing\" display list *(4.11c String Functions)*\n", "\n", "Build a single column that reads like \"The Great Heist (Action)\", combining each movie's title and genre into one string.\n", "\n", "**Hint.** SQLite does not have a `CONCAT()` function; use the `||` concatenation operator instead, the same operator used in the JOIN examples page's self join.\n", "\n", "*What to expect:* 10 rows." ] }, { "cell_type": "code", "id": "391f466d", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "cursor.execute(\"\"\"\n", "-- your SQL here\n", "\n", "\"\"\")\n", "for row in cursor.fetchall() :\n", " print(row)" ] }, { "cell_type": "markdown", "id": "b36bf854", "metadata": {}, "source": [ "## Problem 8. Customers whose last name starts with S *(4.11c String Functions)*\n", "\n", "Find every customer whose last name starts with the letter S, regardless of how it was typed in (upper, lower, or mixed case).\n", "\n", "**Hint.** Pull out the first character with `SUBSTR(last_name, 1, 1)`, then force it to one case with `UPPER()` before comparing it to `'S'`. Look closely at how `STEVENS` is stored in the CUSTOMER table.\n", "\n", "*What to expect:* 3 rows." ] }, { "cell_type": "code", "id": "b7dc597e", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "cursor.execute(\"\"\"\n", "-- your SQL here\n", "\n", "\"\"\")\n", "for row in cursor.fetchall() :\n", " print(row)" ] }, { "cell_type": "markdown", "id": "50fdaa76", "metadata": {}, "source": [ "## Problem 9. Shortening long titles for a receipt *(4.11c String Functions)*\n", "\n", "Show every movie's title next to a shortened version: titles longer than 15 characters should be cut to their first 15 characters with \"...\" appended; shorter titles should be left exactly as they are.\n", "\n", "**Hint.** This is the same `CASE` + `LENGTH()` + `SUBSTR()` pattern worked out in Section 4.11c of the Chapter 4 review, just applied to MOVIE.title instead of PRODUCT.P_DESCRIPT.\n", "\n", "*What to expect:* 10 rows, most of them shortened." ] }, { "cell_type": "code", "id": "dab53871", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "cursor.execute(\"\"\"\n", "-- your SQL here\n", "\n", "\"\"\")\n", "for row in cursor.fetchall() :\n", " print(row)" ] }, { "cell_type": "markdown", "id": "e9a2912e", "metadata": {}, "source": [ "## Problem 10. A one line receipt for each rental *(4.11d Conversion Functions)*\n", "\n", "For the first 5 rentals (by rental_id), build one text line that reads like: Sarah Sanchez rented 'The Great Heist' for $4.99\n", "\n", "**Hint.** You'll need a three table join (RENTAL, CUSTOMER, MOVIE), string concatenation with `||`, and `CAST(rental_price AS TEXT)` to explicitly convert the numeric price to text before it can be joined into the sentence. A doubled single quote `''` inside a string literal is how SQL writes a literal quote character.\n", "\n", "*What to expect:* 5 rows, one full sentence each." ] }, { "cell_type": "code", "id": "bac57b0f", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "cursor.execute(\"\"\"\n", "-- your SQL here\n", "\n", "\"\"\")\n", "for row in cursor.fetchall() :\n", " print(row)" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "name": "python", "version": "3.11.0" } }, "nbformat": 4, "nbformat_minor": 5 }