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