· 9 years ago · Nov 04, 2016, 08:26 AM
1-- Sakila Sample Database Schema
2-- Version 1.0
3
4-- Copyright (c) 2006, 2015, Oracle and/or its affiliates.
5-- All rights reserved.
6
7-- Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
8
9-- * Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
10-- * Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
11-- * Neither the name of Oracle nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.
12
13-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
14
15SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
16SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
17SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
18
19DROP SCHEMA IF EXISTS sakila;
20CREATE SCHEMA sakila;
21USE sakila;
22
23--
24-- Table structure for table `actor`
25--
26
27CREATE TABLE actor (
28 actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
29 first_name VARCHAR(45) NOT NULL,
30 last_name VARCHAR(45) NOT NULL,
31 last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
32 PRIMARY KEY (actor_id),
33 KEY idx_actor_last_name (last_name)
34)ENGINE=InnoDB DEFAULT CHARSET=utf8;
35
36--
37-- Table structure for table `address`
38--
39
40CREATE TABLE address (
41 address_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
42 address VARCHAR(50) NOT NULL,
43 address2 VARCHAR(50) DEFAULT NULL,
44 district VARCHAR(20) NOT NULL,
45 city_id SMALLINT UNSIGNED NOT NULL,
46 postal_code VARCHAR(10) DEFAULT NULL,
47 phone VARCHAR(20) NOT NULL,
48 /*!50705 location GEOMETRY NOT NULL,*/
49 last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
50 PRIMARY KEY (address_id),
51 KEY idx_fk_city_id (city_id),
52 /*!50705 SPATIAL KEY `idx_location` (location),*/
53 CONSTRAINT `fk_address_city` FOREIGN KEY (city_id) REFERENCES city (city_id) ON DELETE RESTRICT ON UPDATE CASCADE
54)ENGINE=InnoDB DEFAULT CHARSET=utf8;
55
56--
57-- Table structure for table `category`
58--
59
60CREATE TABLE category (
61 category_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
62 name VARCHAR(25) NOT NULL,
63 last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
64 PRIMARY KEY (category_id)
65)ENGINE=InnoDB DEFAULT CHARSET=utf8;
66
67--
68-- Table structure for table `city`
69--
70
71CREATE TABLE city (
72 city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
73 city VARCHAR(50) NOT NULL,
74 country_id SMALLINT UNSIGNED NOT NULL,
75 last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
76 PRIMARY KEY (city_id),
77 KEY idx_fk_country_id (country_id),
78 CONSTRAINT `fk_city_country` FOREIGN KEY (country_id) REFERENCES country (country_id) ON DELETE RESTRICT ON UPDATE CASCADE
79)ENGINE=InnoDB DEFAULT CHARSET=utf8;
80
81--
82-- Table structure for table `country`
83--
84
85CREATE TABLE country (
86 country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
87 country VARCHAR(50) NOT NULL,
88 last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
89 PRIMARY KEY (country_id)
90)ENGINE=InnoDB DEFAULT CHARSET=utf8;
91
92--
93-- Table structure for table `customer`
94--
95
96CREATE TABLE customer (
97 customer_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
98 store_id TINYINT UNSIGNED NOT NULL,
99 first_name VARCHAR(45) NOT NULL,
100 last_name VARCHAR(45) NOT NULL,
101 email VARCHAR(50) DEFAULT NULL,
102 address_id SMALLINT UNSIGNED NOT NULL,
103 active BOOLEAN NOT NULL DEFAULT TRUE,
104 create_date DATETIME NOT NULL,
105 last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
106 PRIMARY KEY (customer_id),
107 KEY idx_fk_store_id (store_id),
108 KEY idx_fk_address_id (address_id),
109 KEY idx_last_name (last_name),
110 CONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE,
111 CONSTRAINT fk_customer_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE
112)ENGINE=InnoDB DEFAULT CHARSET=utf8;
113
114--
115-- Table structure for table `film`
116--
117
118CREATE TABLE film (
119 film_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
120 title VARCHAR(255) NOT NULL,
121 description TEXT DEFAULT NULL,
122 release_year YEAR DEFAULT NULL,
123 language_id TINYINT UNSIGNED NOT NULL,
124 original_language_id TINYINT UNSIGNED DEFAULT NULL,
125 rental_duration TINYINT UNSIGNED NOT NULL DEFAULT 3,
126 rental_rate DECIMAL(4,2) NOT NULL DEFAULT 4.99,
127 length SMALLINT UNSIGNED DEFAULT NULL,
128 replacement_cost DECIMAL(5,2) NOT NULL DEFAULT 19.99,
129 rating ENUM('G','PG','PG-13','R','NC-17') DEFAULT 'G',
130 special_features SET('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
131 last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
132 PRIMARY KEY (film_id),
133 KEY idx_title (title),
134 KEY idx_fk_language_id (language_id),
135 KEY idx_fk_original_language_id (original_language_id),
136 CONSTRAINT fk_film_language FOREIGN KEY (language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE,
137 CONSTRAINT fk_film_language_original FOREIGN KEY (original_language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE
138)ENGINE=InnoDB DEFAULT CHARSET=utf8;
139
140--
141-- Table structure for table `film_actor`
142--
143
144CREATE TABLE film_actor (
145 actor_id SMALLINT UNSIGNED NOT NULL,
146 film_id SMALLINT UNSIGNED NOT NULL,
147 last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
148 PRIMARY KEY (actor_id,film_id),
149 KEY idx_fk_film_id (`film_id`),
150 CONSTRAINT fk_film_actor_actor FOREIGN KEY (actor_id) REFERENCES actor (actor_id) ON DELETE RESTRICT ON UPDATE CASCADE,
151 CONSTRAINT fk_film_actor_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE
152)ENGINE=InnoDB DEFAULT CHARSET=utf8;
153
154--
155-- Table structure for table `film_category`
156--
157
158CREATE TABLE film_category (
159 film_id SMALLINT UNSIGNED NOT NULL,
160 category_id TINYINT UNSIGNED NOT NULL,
161 last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
162 PRIMARY KEY (film_id, category_id),
163 CONSTRAINT fk_film_category_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE,
164 CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category (category_id) ON DELETE RESTRICT ON UPDATE CASCADE
165)ENGINE=InnoDB DEFAULT CHARSET=utf8;
166
167--
168-- Table structure for table `film_text`
169--
170-- InnoDB added FULLTEXT support in 5.6.10. If you use an
171-- earlier version, then consider upgrading (recommended) or
172-- changing InnoDB to MyISAM as the film_text engine
173--
174
175CREATE TABLE film_text (
176 film_id SMALLINT NOT NULL,
177 title VARCHAR(255) NOT NULL,
178 description TEXT,
179 PRIMARY KEY (film_id),
180 FULLTEXT KEY idx_title_description (title,description)
181)ENGINE=InnoDB DEFAULT CHARSET=utf8;
182
183--
184-- Triggers for loading film_text from film
185--
186
187DELIMITER ;;
188CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
189 INSERT INTO film_text (film_id, title, description)
190 VALUES (new.film_id, new.title, new.description);
191 END;;
192
193
194CREATE TRIGGER `upd_film` AFTER UPDATE ON `film` FOR EACH ROW BEGIN
195 IF (old.title != new.title) OR (old.description != new.description) OR (old.film_id != new.film_id)
196 THEN
197 UPDATE film_text
198 SET title=new.title,
199 description=new.description,
200 film_id=new.film_id
201 WHERE film_id=old.film_id;
202 END IF;
203 END;;
204
205
206CREATE TRIGGER `del_film` AFTER DELETE ON `film` FOR EACH ROW BEGIN
207 DELETE FROM film_text WHERE film_id = old.film_id;
208 END;;
209
210DELIMITER ;
211
212--
213-- Table structure for table `inventory`
214--
215
216CREATE TABLE inventory (
217 inventory_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
218 film_id SMALLINT UNSIGNED NOT NULL,
219 store_id TINYINT UNSIGNED NOT NULL,
220 last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
221 PRIMARY KEY (inventory_id),
222 KEY idx_fk_film_id (film_id),
223 KEY idx_store_id_film_id (store_id,film_id),
224 CONSTRAINT fk_inventory_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE,
225 CONSTRAINT fk_inventory_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE
226)ENGINE=InnoDB DEFAULT CHARSET=utf8;
227
228--
229-- Table structure for table `language`
230--
231
232CREATE TABLE language (
233 language_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
234 name CHAR(20) NOT NULL,
235 last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
236 PRIMARY KEY (language_id)
237)ENGINE=InnoDB DEFAULT CHARSET=utf8;
238
239--
240-- Table structure for table `payment`
241--
242
243CREATE TABLE payment (
244 payment_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
245 customer_id SMALLINT UNSIGNED NOT NULL,
246 staff_id TINYINT UNSIGNED NOT NULL,
247 rental_id INT DEFAULT NULL,
248 amount DECIMAL(5,2) NOT NULL,
249 payment_date DATETIME NOT NULL,
250 last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
251 PRIMARY KEY (payment_id),
252 KEY idx_fk_staff_id (staff_id),
253 KEY idx_fk_customer_id (customer_id),
254 CONSTRAINT fk_payment_rental FOREIGN KEY (rental_id) REFERENCES rental (rental_id) ON DELETE SET NULL ON UPDATE CASCADE,
255 CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE,
256 CONSTRAINT fk_payment_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE
257)ENGINE=InnoDB DEFAULT CHARSET=utf8;
258
259
260--
261-- Table structure for table `rental`
262--
263
264CREATE TABLE rental (
265 rental_id INT NOT NULL AUTO_INCREMENT,
266 rental_date DATETIME NOT NULL,
267 inventory_id MEDIUMINT UNSIGNED NOT NULL,
268 customer_id SMALLINT UNSIGNED NOT NULL,
269 return_date DATETIME DEFAULT NULL,
270 staff_id TINYINT UNSIGNED NOT NULL,
271 last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
272 PRIMARY KEY (rental_id),
273 UNIQUE KEY (rental_date,inventory_id,customer_id),
274 KEY idx_fk_inventory_id (inventory_id),
275 KEY idx_fk_customer_id (customer_id),
276 KEY idx_fk_staff_id (staff_id),
277 CONSTRAINT fk_rental_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE,
278 CONSTRAINT fk_rental_inventory FOREIGN KEY (inventory_id) REFERENCES inventory (inventory_id) ON DELETE RESTRICT ON UPDATE CASCADE,
279 CONSTRAINT fk_rental_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE
280)ENGINE=InnoDB DEFAULT CHARSET=utf8;
281
282--
283-- Table structure for table `staff`
284--
285
286CREATE TABLE staff (
287 staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
288 first_name VARCHAR(45) NOT NULL,
289 last_name VARCHAR(45) NOT NULL,
290 address_id SMALLINT UNSIGNED NOT NULL,
291 picture BLOB DEFAULT NULL,
292 email VARCHAR(50) DEFAULT NULL,
293 store_id TINYINT UNSIGNED NOT NULL,
294 active BOOLEAN NOT NULL DEFAULT TRUE,
295 username VARCHAR(16) NOT NULL,
296 password VARCHAR(40) BINARY DEFAULT NULL,
297 last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
298 PRIMARY KEY (staff_id),
299 KEY idx_fk_store_id (store_id),
300 KEY idx_fk_address_id (address_id),
301 CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE,
302 CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE
303)ENGINE=InnoDB DEFAULT CHARSET=utf8;
304
305--
306-- Table structure for table `store`
307--
308
309CREATE TABLE store (
310 store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
311 manager_staff_id TINYINT UNSIGNED NOT NULL,
312 address_id SMALLINT UNSIGNED NOT NULL,
313 last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
314 PRIMARY KEY (store_id),
315 UNIQUE KEY idx_unique_manager (manager_staff_id),
316 KEY idx_fk_address_id (address_id),
317 CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE,
318 CONSTRAINT fk_store_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE
319)ENGINE=InnoDB DEFAULT CHARSET=utf8;
320
321--
322-- View structure for view `customer_list`
323--
324
325CREATE VIEW customer_list
326AS
327SELECT cu.customer_id AS ID, CONCAT(cu.first_name, _utf8' ', cu.last_name) AS name, a.address AS address, a.postal_code AS `zip code`,
328 a.phone AS phone, city.city AS city, country.country AS country, IF(cu.active, _utf8'active',_utf8'') AS notes, cu.store_id AS SID
329FROM customer AS cu JOIN address AS a ON cu.address_id = a.address_id JOIN city ON a.city_id = city.city_id
330 JOIN country ON city.country_id = country.country_id;
331
332--
333-- View structure for view `film_list`
334--
335
336CREATE VIEW film_list
337AS
338SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price,
339 film.length AS length, film.rating AS rating, GROUP_CONCAT(CONCAT(actor.first_name, _utf8' ', actor.last_name) SEPARATOR ', ') AS actors
340FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id
341 JOIN film_actor ON film.film_id = film_actor.film_id
342 JOIN actor ON film_actor.actor_id = actor.actor_id
343GROUP BY film.film_id, category.name;
344
345--
346-- View structure for view `nicer_but_slower_film_list`
347--
348
349CREATE VIEW nicer_but_slower_film_list
350AS
351SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price,
352 film.length AS length, film.rating AS rating, GROUP_CONCAT(CONCAT(CONCAT(UCASE(SUBSTR(actor.first_name,1,1)),
353 LCASE(SUBSTR(actor.first_name,2,LENGTH(actor.first_name))),_utf8' ',CONCAT(UCASE(SUBSTR(actor.last_name,1,1)),
354 LCASE(SUBSTR(actor.last_name,2,LENGTH(actor.last_name)))))) SEPARATOR ', ') AS actors
355FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id
356 JOIN film_actor ON film.film_id = film_actor.film_id
357 JOIN actor ON film_actor.actor_id = actor.actor_id
358GROUP BY film.film_id, category.name;
359
360--
361-- View structure for view `staff_list`
362--
363
364CREATE VIEW staff_list
365AS
366SELECT s.staff_id AS ID, CONCAT(s.first_name, _utf8' ', s.last_name) AS name, a.address AS address, a.postal_code AS `zip code`, a.phone AS phone,
367 city.city AS city, country.country AS country, s.store_id AS SID
368FROM staff AS s JOIN address AS a ON s.address_id = a.address_id JOIN city ON a.city_id = city.city_id
369 JOIN country ON city.country_id = country.country_id;
370
371--
372-- View structure for view `sales_by_store`
373--
374
375CREATE VIEW sales_by_store
376AS
377SELECT
378CONCAT(c.city, _utf8',', cy.country) AS store
379, CONCAT(m.first_name, _utf8' ', m.last_name) AS manager
380, SUM(p.amount) AS total_sales
381FROM payment AS p
382INNER JOIN rental AS r ON p.rental_id = r.rental_id
383INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
384INNER JOIN store AS s ON i.store_id = s.store_id
385INNER JOIN address AS a ON s.address_id = a.address_id
386INNER JOIN city AS c ON a.city_id = c.city_id
387INNER JOIN country AS cy ON c.country_id = cy.country_id
388INNER JOIN staff AS m ON s.manager_staff_id = m.staff_id
389GROUP BY s.store_id
390ORDER BY cy.country, c.city;
391
392--
393-- View structure for view `sales_by_film_category`
394--
395-- Note that total sales will add up to >100% because
396-- some titles belong to more than 1 category
397--
398
399CREATE VIEW sales_by_film_category
400AS
401SELECT
402c.name AS category
403, SUM(p.amount) AS total_sales
404FROM payment AS p
405INNER JOIN rental AS r ON p.rental_id = r.rental_id
406INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
407INNER JOIN film AS f ON i.film_id = f.film_id
408INNER JOIN film_category AS fc ON f.film_id = fc.film_id
409INNER JOIN category AS c ON fc.category_id = c.category_id
410GROUP BY c.name
411ORDER BY total_sales DESC;
412
413--
414-- View structure for view `actor_info`
415--
416
417CREATE DEFINER=CURRENT_USER SQL SECURITY INVOKER VIEW actor_info
418AS
419SELECT
420a.actor_id,
421a.first_name,
422a.last_name,
423GROUP_CONCAT(DISTINCT CONCAT(c.name, ': ',
424 (SELECT GROUP_CONCAT(f.title ORDER BY f.title SEPARATOR ', ')
425 FROM sakila.film f
426 INNER JOIN sakila.film_category fc
427 ON f.film_id = fc.film_id
428 INNER JOIN sakila.film_actor fa
429 ON f.film_id = fa.film_id
430 WHERE fc.category_id = c.category_id
431 AND fa.actor_id = a.actor_id
432 )
433 )
434 ORDER BY c.name SEPARATOR '; ')
435AS film_info
436FROM sakila.actor a
437LEFT JOIN sakila.film_actor fa
438 ON a.actor_id = fa.actor_id
439LEFT JOIN sakila.film_category fc
440 ON fa.film_id = fc.film_id
441LEFT JOIN sakila.category c
442 ON fc.category_id = c.category_id
443GROUP BY a.actor_id, a.first_name, a.last_name;
444
445--
446-- Procedure structure for procedure `rewards_report`
447--
448
449DELIMITER //
450
451CREATE PROCEDURE rewards_report (
452 IN min_monthly_purchases TINYINT UNSIGNED
453 , IN min_dollar_amount_purchased DECIMAL(10,2) UNSIGNED
454 , OUT count_rewardees INT
455)
456LANGUAGE SQL
457NOT DETERMINISTIC
458READS SQL DATA
459SQL SECURITY DEFINER
460COMMENT 'Provides a customizable report on best customers'
461proc: BEGIN
462
463 DECLARE last_month_start DATE;
464 DECLARE last_month_end DATE;
465
466 /* Some sanity checks... */
467 IF min_monthly_purchases = 0 THEN
468 SELECT 'Minimum monthly purchases parameter must be > 0';
469 LEAVE proc;
470 END IF;
471 IF min_dollar_amount_purchased = 0.00 THEN
472 SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
473 LEAVE proc;
474 END IF;
475
476 /* Determine start and end time periods */
477 SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
478 SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
479 SET last_month_end = LAST_DAY(last_month_start);
480
481 /*
482 Create a temporary storage area for
483 Customer IDs.
484 */
485 CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
486
487 /*
488 Find all customers meeting the
489 monthly purchase requirements
490 */
491 INSERT INTO tmpCustomer (customer_id)
492 SELECT p.customer_id
493 FROM payment AS p
494 WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
495 GROUP BY customer_id
496 HAVING SUM(p.amount) > min_dollar_amount_purchased
497 AND COUNT(customer_id) > min_monthly_purchases;
498
499 /* Populate OUT parameter with count of found customers */
500 SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;
501
502 /*
503 Output ALL customer information of matching rewardees.
504 Customize output as needed.
505 */
506 SELECT c.*
507 FROM tmpCustomer AS t
508 INNER JOIN customer AS c ON t.customer_id = c.customer_id;
509
510 /* Clean up */
511 DROP TABLE tmpCustomer;
512END //
513
514DELIMITER ;
515
516DELIMITER $$
517
518CREATE FUNCTION get_customer_balance(p_customer_id INT, p_effective_date DATETIME) RETURNS DECIMAL(5,2)
519 DETERMINISTIC
520 READS SQL DATA
521BEGIN
522
523 #OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID AND A DATE
524 #THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS:
525 # 1) RENTAL FEES FOR ALL PREVIOUS RENTALS
526 # 2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE
527 # 3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST
528 # 4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED
529
530 DECLARE v_rentfees DECIMAL(5,2); #FEES PAID TO RENT THE VIDEOS INITIALLY
531 DECLARE v_overfees INTEGER; #LATE FEES FOR PRIOR RENTALS
532 DECLARE v_payments DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIOUSLY
533
534 SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees
535 FROM film, inventory, rental
536 WHERE film.film_id = inventory.film_id
537 AND inventory.inventory_id = rental.inventory_id
538 AND rental.rental_date <= p_effective_date
539 AND rental.customer_id = p_customer_id;
540
541 SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration,
542 ((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees
543 FROM rental, inventory, film
544 WHERE film.film_id = inventory.film_id
545 AND inventory.inventory_id = rental.inventory_id
546 AND rental.rental_date <= p_effective_date
547 AND rental.customer_id = p_customer_id;
548
549
550 SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
551 FROM payment
552
553 WHERE payment.payment_date <= p_effective_date
554 AND payment.customer_id = p_customer_id;
555
556 RETURN v_rentfees + v_overfees - v_payments;
557END $$
558
559DELIMITER ;
560
561DELIMITER $$
562
563CREATE PROCEDURE film_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
564READS SQL DATA
565BEGIN
566 SELECT inventory_id
567 FROM inventory
568 WHERE film_id = p_film_id
569 AND store_id = p_store_id
570 AND inventory_in_stock(inventory_id);
571
572 SELECT FOUND_ROWS() INTO p_film_count;
573END $$
574
575DELIMITER ;
576
577DELIMITER $$
578
579CREATE PROCEDURE film_not_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
580READS SQL DATA
581BEGIN
582 SELECT inventory_id
583 FROM inventory
584 WHERE film_id = p_film_id
585 AND store_id = p_store_id
586 AND NOT inventory_in_stock(inventory_id);
587
588 SELECT FOUND_ROWS() INTO p_film_count;
589END $$
590
591DELIMITER ;
592
593DELIMITER $$
594
595CREATE FUNCTION inventory_held_by_customer(p_inventory_id INT) RETURNS INT
596READS SQL DATA
597BEGIN
598 DECLARE v_customer_id INT;
599 DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;
600
601 SELECT customer_id INTO v_customer_id
602 FROM rental
603 WHERE return_date IS NULL
604 AND inventory_id = p_inventory_id;
605
606 RETURN v_customer_id;
607END $$
608
609DELIMITER ;
610
611DELIMITER $$
612
613CREATE FUNCTION inventory_in_stock(p_inventory_id INT) RETURNS BOOLEAN
614READS SQL DATA
615BEGIN
616 DECLARE v_rentals INT;
617 DECLARE v_out INT;
618
619 #AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE
620 #FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED
621
622 SELECT COUNT(*) INTO v_rentals
623 FROM rental
624 WHERE inventory_id = p_inventory_id;
625
626 IF v_rentals = 0 THEN
627 RETURN TRUE;
628 END IF;
629
630 SELECT COUNT(rental_id) INTO v_out
631 FROM inventory LEFT JOIN rental USING(inventory_id)
632 WHERE inventory.inventory_id = p_inventory_id
633 AND rental.return_date IS NULL;
634
635 IF v_out > 0 THEN
636 RETURN FALSE;
637 ELSE
638 RETURN TRUE;
639 END IF;
640END $$
641
642DELIMITER ;
643
644SET SQL_MODE=@OLD_SQL_MODE;
645SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
646SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;