要租借 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)