若要租借 DVD，請先確認指定的庫存項目有庫存，然後在 rental 資料表中插入一列。建立 rental 資料表後，請在 payment 資料表中插入一列。根據業務規則，您可能還需要在處理租借前檢查客戶是否有未付款餘額。

mysql> SELECT inventory_in_stock(10);
+------------------------+
| inventory_in_stock(10) |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.01 sec)

mysql> INSERT INTO rental(rental_date, inventory_id, customer_id, staff_id) 
           VALUES(NOW(), 10, 3, 1);
Query OK, 1 row affected (0.00 sec)

mysql> SET @rentID = LAST_INSERT_ID(),
                  @balance = get_customer_balance(3, NOW());
Query OK, 0 rows affected (0.14 sec)

mysql> SELECT @rentID, @balance;
+---------+----------+
| @rentID | @balance |
+---------+----------+
|   16050 |     4.99 |
+---------+----------+
1 row in set (0.00 sec)

mysql> INSERT INTO payment (customer_id, staff_id, rental_id, amount,  payment_date)
           VALUES(3, 1, @rentID, @balance, NOW());
Query OK, 1 row affected (0.00 sec)

若要歸還 DVD，請更新 rental 資料表並設定歸還日期。為此，請先根據要歸還項目的 inventory_id 來找出要更新的 rental_id。視情況而定，可能需要檢查客戶餘額，並可能透過在 payment 資料表中插入一列來處理逾期費用付款。

mysql> SELECT rental_id
           FROM rental
           WHERE inventory_id = 10
           AND customer_id = 3
           AND return_date IS NULL
           INTO @rentID;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @rentID;
+---------+
| @rentID |
+---------+
|   16050 |
+---------+
1 row in set (0.00 sec)

mysql> UPDATE rental
           SET return_date = NOW()
           WHERE rental_id = @rentID;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT get_customer_balance(3, NOW());
+--------------------------------+
| get_customer_balance(3, NOW()) |
+--------------------------------+
|                           0.00 |
+--------------------------------+
1 row in set (0.13 sec)

許多 DVD 商店會產生每日逾期租借清單，以便聯絡客戶並要求他們歸還逾期 DVD。

若要建立此類清單，請在 rental 資料表中搜尋歸還日期為 NULL，且租借日期早於 film 資料表中指定的租借期限的影片。如果符合條件，則表示影片已逾期，我們應產生影片名稱以及客戶姓名和電話號碼。

mysql> SELECT CONCAT(customer.last_name, ', ', customer.first_name) AS customer,
           address.phone, film.title
           FROM rental INNER JOIN customer ON rental.customer_id = customer.customer_id
           INNER JOIN address ON customer.address_id = address.address_id
           INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id
           INNER JOIN film ON inventory.film_id = film.film_id
           WHERE rental.return_date IS NULL
           AND rental_date + INTERVAL film.rental_duration DAY < CURRENT_DATE()
           ORDER BY title
           LIMIT 5;
+----------------+--------------+------------------+
| customer       | phone        | title            |
+----------------+--------------+------------------+
| OLVERA, DWAYNE | 62127829280  | ACADEMY DINOSAUR |
| HUEY, BRANDON  | 99883471275  | ACE GOLDFINGER   |
| OWENS, CARMEN  | 272234298332 | AFFAIR PREJUDICE |
| HANNON, SETH   | 864392582257 | AFRICAN EGG      |
| COLE, TRACY    | 371490777743 | ALI FOREVER      |
+----------------+--------------+------------------+
5 rows in set (0.10 sec)