{ "cells": [ { "cell_type": "markdown", "id": "08fee37e", "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.\n", "\n", "---\n", "### This is the solutions notebook\n", "\n", "Every problem below is filled in and has already been run once, so you can\n", "read the expected output without re-running anything. The matching blank\n", "notebook, for working the problems yourself first, is `movie_rental_practice.ipynb`." ] }, { "cell_type": "code", "id": "6d690813", "execution_count": 1, "metadata": {}, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "Database ready: movie_rental.db\n", "\n", " MOVIE 10 rows\n", " CUSTOMER 8 rows\n", " RENTAL 20 rows\n", "\n", " Rentals still out: 3\n", "\n", "You are ready to start Problem 1.\n" ] } ], "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": "032dafd2", "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": "74533be0", "execution_count": 2, "metadata": {}, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "('Shadow Protocol', '2023-01-18')\n", "('Galactic Rangers', '2023-03-09')\n", "('The Great Heist', '2023-05-12')\n", "('Starlight Odyssey', '2023-08-22')\n", "(4 rows)\n" ] } ], "source": [ "cursor.execute(\"\"\"\n", "SELECT title, release_date\n", "FROM MOVIE\n", "WHERE strftime('%Y', release_date) = '2023'\n", "ORDER BY release_date;\n", "\"\"\")\n", "for row in cursor.fetchall() :\n", " print(row)" ] }, { "cell_type": "markdown", "id": "057ed6b8", "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": "1b681d80", "execution_count": 3, "metadata": {}, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "(1, '2026-06-01', '2026-06-05', 4)\n", "(2, '2026-06-02', '2026-06-10', 8)\n", "(3, '2026-06-03', '2026-06-06', 3)\n", "(5, '2026-06-05', '2026-06-09', 4)\n", "(6, '2026-06-06', '2026-06-15', 9)\n", "(7, '2026-06-07', '2026-06-12', 5)\n", "(8, '2026-06-08', '2026-06-20', 12)\n", "(9, '2026-06-09', '2026-06-11', 2)\n", "(11, '2026-06-11', '2026-06-14', 3)\n", "(12, '2026-06-12', '2026-06-25', 13)\n", "(13, '2026-06-13', '2026-06-16', 3)\n", "(14, '2026-06-14', '2026-06-19', 5)\n", "(15, '2026-06-15', '2026-06-24', 9)\n", "(16, '2026-06-16', '2026-06-18', 2)\n", "(18, '2026-06-18', '2026-06-21', 3)\n", "(19, '2026-06-19', '2026-06-28', 9)\n", "(20, '2026-06-20', '2026-06-22', 2)\n", "(17 rows)\n" ] } ], "source": [ "cursor.execute(\"\"\"\n", "SELECT rental_id, rental_date, return_date,\n", " CAST(julianday(return_date) - julianday(rental_date) AS INTEGER) AS days_rented\n", "FROM RENTAL\n", "WHERE return_date IS NOT NULL\n", "ORDER BY rental_id;\n", "\"\"\")\n", "for row in cursor.fetchall() :\n", " print(row)" ] }, { "cell_type": "markdown", "id": "4031ce17", "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": "6e1e545e", "execution_count": 4, "metadata": {}, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "(1, '2026-06-01', '2026-06-08')\n", "(2, '2026-06-02', '2026-06-09')\n", "(3, '2026-06-03', '2026-06-10')\n", "(4, '2026-06-04', '2026-06-11')\n", "(5, '2026-06-05', '2026-06-12')\n", "(6, '2026-06-06', '2026-06-13')\n", "(7, '2026-06-07', '2026-06-14')\n", "(8, '2026-06-08', '2026-06-15')\n", "(8 rows)\n" ] } ], "source": [ "cursor.execute(\"\"\"\n", "SELECT rental_id, rental_date,\n", " date(rental_date, '+7 days') AS due_date\n", "FROM RENTAL\n", "ORDER BY rental_id\n", "LIMIT 8;\n", "\"\"\")\n", "for row in cursor.fetchall() :\n", " print(row)" ] }, { "cell_type": "markdown", "id": "30708a90", "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": "95305595", "execution_count": 5, "metadata": {}, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "(2, '2026-06-02', '2026-06-10', '2026-06-09')\n", "(6, '2026-06-06', '2026-06-15', '2026-06-13')\n", "(8, '2026-06-08', '2026-06-20', '2026-06-15')\n", "(12, '2026-06-12', '2026-06-25', '2026-06-19')\n", "(15, '2026-06-15', '2026-06-24', '2026-06-22')\n", "(19, '2026-06-19', '2026-06-28', '2026-06-26')\n", "(6 rows)\n" ] } ], "source": [ "cursor.execute(\"\"\"\n", "SELECT rental_id, rental_date, return_date,\n", " date(rental_date, '+7 days') AS due_date\n", "FROM RENTAL\n", "WHERE return_date IS NOT NULL\n", " AND return_date > date(rental_date, '+7 days')\n", "ORDER BY rental_id;\n", "\"\"\")\n", "for row in cursor.fetchall() :\n", " print(row)" ] }, { "cell_type": "markdown", "id": "0aadd4e1", "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": "3d0591d7", "execution_count": 6, "metadata": {}, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "('Comedy Night Live', 2.99, 3.0)\n", "('Galactic Rangers', 5.49, 5.0)\n", "('Midnight Mystery', 4.49, 4.0)\n", "('Quiet Storm', 3.99, 4.0)\n", "('Shadow Protocol', 4.99, 5.0)\n", "('Starlight Odyssey', 5.99, 6.0)\n", "('Sunflower Summer', 2.99, 3.0)\n", "('The Great Heist', 4.99, 5.0)\n", "('The Last Lighthouse', 3.49, 3.0)\n", "('Whispering Pines', 3.99, 4.0)\n", "(10 rows)\n" ] } ], "source": [ "cursor.execute(\"\"\"\n", "SELECT title, rental_price, ROUND(rental_price, 0) AS whole_dollar_price\n", "FROM MOVIE\n", "ORDER BY title;\n", "\"\"\")\n", "for row in cursor.fetchall() :\n", " print(row)" ] }, { "cell_type": "markdown", "id": "362a174e", "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": "7dd18bdd", "execution_count": 7, "metadata": {}, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "(2, '2026-06-02', '2026-06-10', 1, 0.75)\n", "(6, '2026-06-06', '2026-06-15', 2, 1.5)\n", "(8, '2026-06-08', '2026-06-20', 5, 3.75)\n", "(12, '2026-06-12', '2026-06-25', 6, 4.5)\n", "(15, '2026-06-15', '2026-06-24', 2, 1.5)\n", "(19, '2026-06-19', '2026-06-28', 2, 1.5)\n", "(6 rows)\n" ] } ], "source": [ "cursor.execute(\"\"\"\n", "SELECT rental_id, rental_date, return_date,\n", " CAST(julianday(return_date) - julianday(rental_date) AS INTEGER) - 7 AS days_late,\n", " ROUND((CAST(julianday(return_date) - julianday(rental_date) AS INTEGER) - 7) * 0.75, 2) AS late_fee\n", "FROM RENTAL\n", "WHERE return_date IS NOT NULL\n", " AND return_date > date(rental_date, '+7 days')\n", "ORDER BY rental_id;\n", "\"\"\")\n", "for row in cursor.fetchall() :\n", " print(row)" ] }, { "cell_type": "markdown", "id": "c91c7623", "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": "f6feb594", "execution_count": 8, "metadata": {}, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "('Comedy Night Live (Comedy)',)\n", "('Galactic Rangers (Sci-Fi)',)\n", "('Midnight Mystery (Thriller)',)\n", "('Quiet Storm (Drama)',)\n", "('Shadow Protocol (Action)',)\n", "('Starlight Odyssey (Sci-Fi)',)\n", "('Sunflower Summer (Family)',)\n", "('The Great Heist (Action)',)\n", "('The Last Lighthouse (Drama)',)\n", "('Whispering Pines (Drama)',)\n", "(10 rows)\n" ] } ], "source": [ "cursor.execute(\"\"\"\n", "SELECT title || ' (' || genre || ')' AS now_showing\n", "FROM MOVIE\n", "ORDER BY title;\n", "\"\"\")\n", "for row in cursor.fetchall() :\n", " print(row)" ] }, { "cell_type": "markdown", "id": "bef9d5dd", "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": "169fd455", "execution_count": 9, "metadata": {}, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "('Robert', 'STEVENS')\n", "('Sarah', 'Sanchez')\n", "('David', 'Smith')\n", "(3 rows)\n" ] } ], "source": [ "cursor.execute(\"\"\"\n", "SELECT first_name, last_name\n", "FROM CUSTOMER\n", "WHERE UPPER(SUBSTR(last_name, 1, 1)) = 'S'\n", "ORDER BY last_name;\n", "\"\"\")\n", "for row in cursor.fetchall() :\n", " print(row)" ] }, { "cell_type": "markdown", "id": "dcf93234", "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": "4dd040df", "execution_count": 10, "metadata": {}, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "('Comedy Night Live', 'Comedy Night Li...')\n", "('Galactic Rangers', 'Galactic Ranger...')\n", "('Midnight Mystery', 'Midnight Myster...')\n", "('Quiet Storm', 'Quiet Storm')\n", "('Shadow Protocol', 'Shadow Protocol')\n", "('Starlight Odyssey', 'Starlight Odyss...')\n", "('Sunflower Summer', 'Sunflower Summe...')\n", "('The Great Heist', 'The Great Heist')\n", "('The Last Lighthouse', 'The Last Lighth...')\n", "('Whispering Pines', 'Whispering Pine...')\n", "(10 rows)\n" ] } ], "source": [ "cursor.execute(\"\"\"\n", "SELECT title,\n", " CASE WHEN LENGTH(title) > 15\n", " THEN SUBSTR(title, 1, 15) || '...'\n", " ELSE title\n", " END AS short_title\n", "FROM MOVIE\n", "ORDER BY title;\n", "\"\"\")\n", "for row in cursor.fetchall() :\n", " print(row)" ] }, { "cell_type": "markdown", "id": "76d8c9ca", "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": "72110338", "execution_count": 11, "metadata": {}, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "(\"Sarah Sanchez rented 'The Great Heist' for $4.99\",)\n", "(\"James Kim rented 'Starlight Odyssey' for $5.99\",)\n", "(\"Maria Alvarez rented 'The Last Lighthouse' for $3.49\",)\n", "(\"David Smith rented 'Whispering Pines' for $3.99\",)\n", "(\"Sarah Sanchez rented 'Shadow Protocol' for $4.99\",)\n", "(5 rows)\n" ] } ], "source": [ "cursor.execute(\"\"\"\n", "SELECT c.first_name || ' ' || c.last_name || ' rented ''' || m.title ||\n", " ''' for $' || CAST(m.rental_price AS TEXT) AS receipt_line\n", "FROM RENTAL r\n", " JOIN CUSTOMER c ON r.customer_id = c.customer_id\n", " JOIN MOVIE m ON r.movie_id = m.movie_id\n", "ORDER BY r.rental_id\n", "LIMIT 5;\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 }