· 6 years ago · Jun 05, 2019, 07:32 PM
1-- MySQL Script generated by MySQL Workbench
2-- Wed Jun 5 14:25:44 2019
3-- Model: Sakila Full Version: 2.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema sakila
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema sakila
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `sakila` ;
18USE `sakila` ;
19
20-- -----------------------------------------------------
21-- Table `sakila`.`actor`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `sakila`.`actor` (
24 `actor_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
25 `first_name` VARCHAR(45) NOT NULL,
26 `last_name` VARCHAR(45) NOT NULL,
27 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
28 PRIMARY KEY (`actor_id`),
29 INDEX `idx_actor_last_name` (`last_name` ASC))
30ENGINE = InnoDB
31DEFAULT CHARACTER SET = utf8;
32
33
34-- -----------------------------------------------------
35-- Table `sakila`.`country`
36-- -----------------------------------------------------
37CREATE TABLE IF NOT EXISTS `sakila`.`country` (
38 `country_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
39 `country` VARCHAR(50) NOT NULL,
40 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
41 PRIMARY KEY (`country_id`))
42ENGINE = InnoDB
43DEFAULT CHARACTER SET = utf8;
44
45
46-- -----------------------------------------------------
47-- Table `sakila`.`city`
48-- -----------------------------------------------------
49CREATE TABLE IF NOT EXISTS `sakila`.`city` (
50 `city_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
51 `city` VARCHAR(50) NOT NULL,
52 `country_id` SMALLINT UNSIGNED NOT NULL,
53 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
54 PRIMARY KEY (`city_id`),
55 INDEX `idx_fk_country_id` (`country_id` ASC),
56 CONSTRAINT `fk_city_country`
57 FOREIGN KEY (`country_id`)
58 REFERENCES `sakila`.`country` (`country_id`)
59 ON DELETE RESTRICT
60 ON UPDATE CASCADE)
61ENGINE = InnoDB
62DEFAULT CHARACTER SET = utf8;
63
64
65-- -----------------------------------------------------
66-- Table `sakila`.`address`
67-- -----------------------------------------------------
68CREATE TABLE IF NOT EXISTS `sakila`.`address` (
69 `address_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
70 `address` VARCHAR(50) NOT NULL,
71 `address2` VARCHAR(50) NULL DEFAULT NULL,
72 `district` VARCHAR(20) NOT NULL,
73 `city_id` SMALLINT UNSIGNED NOT NULL,
74 `postal_code` VARCHAR(10) NULL DEFAULT NULL,
75 `phone` VARCHAR(20) NOT NULL,
76 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
77 PRIMARY KEY (`address_id`),
78 INDEX `idx_fk_city_id` (`city_id` ASC),
79 CONSTRAINT `fk_address_city`
80 FOREIGN KEY (`city_id`)
81 REFERENCES `sakila`.`city` (`city_id`)
82 ON DELETE RESTRICT
83 ON UPDATE CASCADE)
84ENGINE = InnoDB
85DEFAULT CHARACTER SET = utf8;
86
87
88-- -----------------------------------------------------
89-- Table `sakila`.`category`
90-- -----------------------------------------------------
91CREATE TABLE IF NOT EXISTS `sakila`.`category` (
92 `category_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
93 `name` VARCHAR(25) NOT NULL,
94 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
95 PRIMARY KEY (`category_id`))
96ENGINE = InnoDB
97DEFAULT CHARACTER SET = utf8;
98
99
100-- -----------------------------------------------------
101-- Table `sakila`.`staff`
102-- -----------------------------------------------------
103CREATE TABLE IF NOT EXISTS `sakila`.`staff` (
104 `staff_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
105 `first_name` VARCHAR(45) NOT NULL,
106 `last_name` VARCHAR(45) NOT NULL,
107 `address_id` SMALLINT UNSIGNED NOT NULL,
108 `picture` BLOB NULL,
109 `email` VARCHAR(50) NULL DEFAULT NULL,
110 `store_id` TINYINT UNSIGNED NOT NULL,
111 `active` TINYINT(1) NOT NULL DEFAULT TRUE,
112 `username` VARCHAR(16) NOT NULL,
113 `password` VARCHAR(40) BINARY NULL DEFAULT NULL,
114 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
115 PRIMARY KEY (`staff_id`),
116 INDEX `idx_fk_store_id` (`store_id` ASC),
117 INDEX `idx_fk_address_id` (`address_id` ASC),
118 CONSTRAINT `fk_staff_store`
119 FOREIGN KEY (`store_id`)
120 REFERENCES `sakila`.`store` (`store_id`)
121 ON DELETE RESTRICT
122 ON UPDATE CASCADE,
123 CONSTRAINT `fk_staff_address`
124 FOREIGN KEY (`address_id`)
125 REFERENCES `sakila`.`address` (`address_id`)
126 ON DELETE RESTRICT
127 ON UPDATE CASCADE)
128ENGINE = InnoDB
129DEFAULT CHARACTER SET = utf8;
130
131
132-- -----------------------------------------------------
133-- Table `sakila`.`store`
134-- -----------------------------------------------------
135CREATE TABLE IF NOT EXISTS `sakila`.`store` (
136 `store_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
137 `manager_staff_id` TINYINT UNSIGNED NOT NULL,
138 `address_id` SMALLINT UNSIGNED NOT NULL,
139 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
140 PRIMARY KEY (`store_id`),
141 UNIQUE INDEX `idx_unique_manager` (`manager_staff_id` ASC),
142 INDEX `idx_fk_address_id` (`address_id` ASC),
143 CONSTRAINT `fk_store_staff`
144 FOREIGN KEY (`manager_staff_id`)
145 REFERENCES `sakila`.`staff` (`staff_id`)
146 ON DELETE RESTRICT
147 ON UPDATE CASCADE,
148 CONSTRAINT `fk_store_address`
149 FOREIGN KEY (`address_id`)
150 REFERENCES `sakila`.`address` (`address_id`)
151 ON DELETE RESTRICT
152 ON UPDATE CASCADE)
153ENGINE = InnoDB
154DEFAULT CHARACTER SET = utf8;
155
156
157-- -----------------------------------------------------
158-- Table `sakila`.`customer`
159-- -----------------------------------------------------
160CREATE TABLE IF NOT EXISTS `sakila`.`customer` (
161 `customer_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
162 `store_id` TINYINT UNSIGNED NOT NULL,
163 `first_name` VARCHAR(45) NOT NULL,
164 `last_name` VARCHAR(45) NOT NULL,
165 `email` VARCHAR(50) NULL DEFAULT NULL,
166 `address_id` SMALLINT UNSIGNED NOT NULL,
167 `active` TINYINT(1) NOT NULL DEFAULT TRUE,
168 `create_date` DATETIME NOT NULL,
169 `last_update` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
170 PRIMARY KEY (`customer_id`),
171 INDEX `idx_fk_store_id` (`store_id` ASC),
172 INDEX `idx_fk_address_id` (`address_id` ASC),
173 INDEX `idx_last_name` (`last_name` ASC),
174 CONSTRAINT `fk_customer_address`
175 FOREIGN KEY (`address_id`)
176 REFERENCES `sakila`.`address` (`address_id`)
177 ON DELETE RESTRICT
178 ON UPDATE CASCADE,
179 CONSTRAINT `fk_customer_store`
180 FOREIGN KEY (`store_id`)
181 REFERENCES `sakila`.`store` (`store_id`)
182 ON DELETE RESTRICT
183 ON UPDATE CASCADE)
184ENGINE = InnoDB
185DEFAULT CHARACTER SET = utf8
186COMMENT = 'Table storing all customers. Holds foreign keys to the address table and the store table where this customer is registered.\n\nBasic information about the customer like first and last name are stored in the table itself. Same for the date the record was created and when the information was last updated.';
187
188
189-- -----------------------------------------------------
190-- Table `sakila`.`language`
191-- -----------------------------------------------------
192CREATE TABLE IF NOT EXISTS `sakila`.`language` (
193 `language_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
194 `name` CHAR(20) NOT NULL,
195 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
196 PRIMARY KEY (`language_id`))
197ENGINE = InnoDB
198DEFAULT CHARACTER SET = utf8;
199
200
201-- -----------------------------------------------------
202-- Table `sakila`.`film`
203-- -----------------------------------------------------
204CREATE TABLE IF NOT EXISTS `sakila`.`film` (
205 `film_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
206 `title` VARCHAR(255) NOT NULL,
207 `description` TEXT NULL,
208 `release_year` YEAR NULL,
209 `language_id` TINYINT UNSIGNED NOT NULL,
210 `original_language_id` TINYINT UNSIGNED NULL DEFAULT NULL,
211 `rental_duration` TINYINT UNSIGNED NOT NULL DEFAULT 3,
212 `rental_rate` DECIMAL(4,2) NOT NULL DEFAULT 4.99,
213 `length` SMALLINT UNSIGNED NULL DEFAULT NULL,
214 `replacement_cost` DECIMAL(5,2) NOT NULL DEFAULT 19.99,
215 `rating` ENUM('G','PG','PG-13','R','NC-17') NULL DEFAULT 'G',
216 `special_features` SET('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') NULL,
217 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
218 INDEX `idx_title` (`title` ASC),
219 INDEX `idx_fk_language_id` (`language_id` ASC),
220 INDEX `idx_fk_original_language_id` (`original_language_id` ASC),
221 PRIMARY KEY (`film_id`),
222 CONSTRAINT `fk_film_language`
223 FOREIGN KEY (`language_id`)
224 REFERENCES `sakila`.`language` (`language_id`)
225 ON DELETE RESTRICT
226 ON UPDATE CASCADE,
227 CONSTRAINT `fk_film_language_original`
228 FOREIGN KEY (`original_language_id`)
229 REFERENCES `sakila`.`language` (`language_id`)
230 ON DELETE RESTRICT
231 ON UPDATE CASCADE)
232ENGINE = InnoDB
233DEFAULT CHARACTER SET = utf8;
234
235
236-- -----------------------------------------------------
237-- Table `sakila`.`film_actor`
238-- -----------------------------------------------------
239CREATE TABLE IF NOT EXISTS `sakila`.`film_actor` (
240 `actor_id` SMALLINT UNSIGNED NOT NULL,
241 `film_id` SMALLINT UNSIGNED NOT NULL,
242 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
243 PRIMARY KEY (`actor_id`, `film_id`),
244 INDEX `idx_fk_film_id` (`film_id` ASC),
245 INDEX `fk_film_actor_actor_idx` (`actor_id` ASC),
246 CONSTRAINT `fk_film_actor_actor`
247 FOREIGN KEY (`actor_id`)
248 REFERENCES `sakila`.`actor` (`actor_id`)
249 ON DELETE RESTRICT
250 ON UPDATE CASCADE,
251 CONSTRAINT `fk_film_actor_film`
252 FOREIGN KEY (`film_id`)
253 REFERENCES `sakila`.`film` (`film_id`)
254 ON DELETE RESTRICT
255 ON UPDATE CASCADE)
256ENGINE = InnoDB
257DEFAULT CHARACTER SET = utf8;
258
259
260-- -----------------------------------------------------
261-- Table `sakila`.`film_category`
262-- -----------------------------------------------------
263CREATE TABLE IF NOT EXISTS `sakila`.`film_category` (
264 `film_id` SMALLINT UNSIGNED NOT NULL,
265 `category_id` TINYINT UNSIGNED NOT NULL,
266 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
267 PRIMARY KEY (`film_id`, `category_id`),
268 INDEX `fk_film_category_category_idx` (`category_id` ASC),
269 INDEX `fk_film_category_film_idx` (`film_id` ASC),
270 CONSTRAINT `fk_film_category_film`
271 FOREIGN KEY (`film_id`)
272 REFERENCES `sakila`.`film` (`film_id`)
273 ON DELETE RESTRICT
274 ON UPDATE CASCADE,
275 CONSTRAINT `fk_film_category_category`
276 FOREIGN KEY (`category_id`)
277 REFERENCES `sakila`.`category` (`category_id`)
278 ON DELETE RESTRICT
279 ON UPDATE CASCADE)
280ENGINE = InnoDB
281DEFAULT CHARACTER SET = utf8;
282
283
284-- -----------------------------------------------------
285-- Table `sakila`.`film_text`
286-- -----------------------------------------------------
287CREATE TABLE IF NOT EXISTS `sakila`.`film_text` (
288 `film_id` SMALLINT UNSIGNED NOT NULL,
289 `title` VARCHAR(255) NOT NULL,
290 `description` TEXT NULL,
291 PRIMARY KEY (`film_id`),
292 FULLTEXT INDEX `idx_title_description` (`title`, `description`))
293ENGINE = InnoDB;
294
295
296-- -----------------------------------------------------
297-- Table `sakila`.`inventory`
298-- -----------------------------------------------------
299CREATE TABLE IF NOT EXISTS `sakila`.`inventory` (
300 `inventory_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
301 `film_id` SMALLINT UNSIGNED NOT NULL,
302 `store_id` TINYINT UNSIGNED NOT NULL,
303 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
304 PRIMARY KEY (`inventory_id`),
305 INDEX `idx_fk_film_id` (`film_id` ASC),
306 INDEX `idx_store_id_film_id` (`store_id` ASC, `film_id` ASC),
307 INDEX `fk_inventory_store_idx` (`store_id` ASC),
308 CONSTRAINT `fk_inventory_store`
309 FOREIGN KEY (`store_id`)
310 REFERENCES `sakila`.`store` (`store_id`)
311 ON DELETE RESTRICT
312 ON UPDATE CASCADE,
313 CONSTRAINT `fk_inventory_film`
314 FOREIGN KEY (`film_id`)
315 REFERENCES `sakila`.`film` (`film_id`)
316 ON DELETE RESTRICT
317 ON UPDATE CASCADE)
318ENGINE = InnoDB
319DEFAULT CHARACTER SET = utf8;
320
321
322-- -----------------------------------------------------
323-- Table `sakila`.`rental`
324-- -----------------------------------------------------
325CREATE TABLE IF NOT EXISTS `sakila`.`rental` (
326 `rental_id` INT NOT NULL AUTO_INCREMENT,
327 `rental_date` DATETIME NOT NULL,
328 `inventory_id` MEDIUMINT UNSIGNED NOT NULL,
329 `customer_id` SMALLINT UNSIGNED NOT NULL,
330 `return_date` DATETIME NULL,
331 `staff_id` TINYINT UNSIGNED NOT NULL,
332 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
333 PRIMARY KEY (`rental_id`),
334 UNIQUE INDEX `idx_rental` (`rental_date` ASC, `inventory_id` ASC, `customer_id` ASC),
335 INDEX `idx_fk_inventory_id` (`inventory_id` ASC),
336 INDEX `idx_fk_customer_id` (`customer_id` ASC),
337 INDEX `idx_fk_staff_id` (`staff_id` ASC),
338 CONSTRAINT `fk_rental_staff`
339 FOREIGN KEY (`staff_id`)
340 REFERENCES `sakila`.`staff` (`staff_id`)
341 ON DELETE RESTRICT
342 ON UPDATE CASCADE,
343 CONSTRAINT `fk_rental_inventory`
344 FOREIGN KEY (`inventory_id`)
345 REFERENCES `sakila`.`inventory` (`inventory_id`)
346 ON DELETE RESTRICT
347 ON UPDATE CASCADE,
348 CONSTRAINT `fk_rental_customer`
349 FOREIGN KEY (`customer_id`)
350 REFERENCES `sakila`.`customer` (`customer_id`)
351 ON DELETE RESTRICT
352 ON UPDATE CASCADE)
353ENGINE = InnoDB
354DEFAULT CHARACTER SET = utf8;
355
356
357-- -----------------------------------------------------
358-- Table `sakila`.`payment`
359-- -----------------------------------------------------
360CREATE TABLE IF NOT EXISTS `sakila`.`payment` (
361 `payment_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
362 `customer_id` SMALLINT UNSIGNED NOT NULL,
363 `staff_id` TINYINT UNSIGNED NOT NULL,
364 `rental_id` INT NULL DEFAULT NULL,
365 `amount` DECIMAL(5,2) NOT NULL,
366 `payment_date` DATETIME NOT NULL,
367 `last_update` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
368 PRIMARY KEY (`payment_id`),
369 INDEX `idx_fk_staff_id` (`staff_id` ASC),
370 INDEX `idx_fk_customer_id` (`customer_id` ASC),
371 INDEX `fk_payment_rental_idx` (`rental_id` ASC),
372 CONSTRAINT `fk_payment_rental`
373 FOREIGN KEY (`rental_id`)
374 REFERENCES `sakila`.`rental` (`rental_id`)
375 ON DELETE SET NULL
376 ON UPDATE CASCADE,
377 CONSTRAINT `fk_payment_customer`
378 FOREIGN KEY (`customer_id`)
379 REFERENCES `sakila`.`customer` (`customer_id`)
380 ON DELETE RESTRICT
381 ON UPDATE CASCADE,
382 CONSTRAINT `fk_payment_staff`
383 FOREIGN KEY (`staff_id`)
384 REFERENCES `sakila`.`staff` (`staff_id`)
385 ON DELETE RESTRICT
386 ON UPDATE CASCADE)
387ENGINE = InnoDB
388DEFAULT CHARACTER SET = utf8;
389
390USE `sakila` ;
391
392-- -----------------------------------------------------
393-- Placeholder table for view `sakila`.`customer_list`
394-- -----------------------------------------------------
395CREATE TABLE IF NOT EXISTS `sakila`.`customer_list` (`ID` INT, `name` INT, `address` INT, `zip code` INT, `phone` INT, `city` INT, `country` INT, `notes` INT, `SID` INT);
396
397-- -----------------------------------------------------
398-- Placeholder table for view `sakila`.`film_list`
399-- -----------------------------------------------------
400CREATE TABLE IF NOT EXISTS `sakila`.`film_list` (`FID` INT, `title` INT, `description` INT, `category` INT, `price` INT, `length` INT, `rating` INT, `actors` INT);
401
402-- -----------------------------------------------------
403-- Placeholder table for view `sakila`.`nicer_but_slower_film_list`
404-- -----------------------------------------------------
405CREATE TABLE IF NOT EXISTS `sakila`.`nicer_but_slower_film_list` (`FID` INT, `title` INT, `description` INT, `category` INT, `price` INT, `length` INT, `rating` INT, `actors` INT);
406
407-- -----------------------------------------------------
408-- Placeholder table for view `sakila`.`staff_list`
409-- -----------------------------------------------------
410CREATE TABLE IF NOT EXISTS `sakila`.`staff_list` (`ID` INT, `name` INT, `address` INT, `zip code` INT, `phone` INT, `city` INT, `country` INT, `SID` INT);
411
412-- -----------------------------------------------------
413-- Placeholder table for view `sakila`.`sales_by_store`
414-- -----------------------------------------------------
415CREATE TABLE IF NOT EXISTS `sakila`.`sales_by_store` (`store` INT, `manager` INT, `total_sales` INT);
416
417-- -----------------------------------------------------
418-- Placeholder table for view `sakila`.`sales_by_film_category`
419-- -----------------------------------------------------
420CREATE TABLE IF NOT EXISTS `sakila`.`sales_by_film_category` (`category` INT, `total_sales` INT);
421
422-- -----------------------------------------------------
423-- Placeholder table for view `sakila`.`actor_info`
424-- -----------------------------------------------------
425CREATE TABLE IF NOT EXISTS `sakila`.`actor_info` (`actor_id` INT, `first_name` INT, `last_name` INT, `film_info` INT);
426
427-- -----------------------------------------------------
428-- procedure rewards_report
429-- -----------------------------------------------------
430
431DELIMITER $$
432USE `sakila`$$
433
434
435CREATE PROCEDURE `sakila`.`rewards_report` (
436 IN min_monthly_purchases TINYINT UNSIGNED
437 , IN min_dollar_amount_purchased DECIMAL(10,2) UNSIGNED
438 , OUT count_rewardees INT
439)
440LANGUAGE SQL
441NOT DETERMINISTIC
442READS SQL DATA
443SQL SECURITY DEFINER
444COMMENT 'Provides a customizable report on best customers'
445proc: BEGIN
446
447 DECLARE last_month_start DATE;
448 DECLARE last_month_end DATE;
449
450 /* Some sanity checks... */
451 IF min_monthly_purchases = 0 THEN
452 SELECT 'Minimum monthly purchases parameter must be > 0';
453 LEAVE proc;
454 END IF;
455 IF min_dollar_amount_purchased = 0.00 THEN
456 SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
457 LEAVE proc;
458 END IF;
459
460 /* Determine start and end time periods */
461 SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
462 SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
463 SET last_month_end = LAST_DAY(last_month_start);
464
465 /*
466 Create a temporary storage area for
467 Customer IDs.
468 */
469 CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
470
471 /*
472 Find all customers meeting the
473 monthly purchase requirements
474 */
475 INSERT INTO tmpCustomer (customer_id)
476 SELECT p.customer_id
477 FROM payment AS p
478 WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
479 GROUP BY customer_id
480 HAVING SUM(p.amount) > min_dollar_amount_purchased
481 AND COUNT(customer_id) > min_monthly_purchases;
482
483 /* Populate OUT parameter with count of found customers */
484 SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;
485
486 /*
487 Output ALL customer information of matching rewardees.
488 Customize output as needed.
489 */
490 SELECT c.*
491 FROM tmpCustomer AS t
492 INNER JOIN customer AS c ON t.customer_id = c.customer_id;
493
494 /* Clean up */
495 DROP TABLE tmpCustomer;
496END$$
497
498DELIMITER ;
499
500-- -----------------------------------------------------
501-- function get_customer_balance
502-- -----------------------------------------------------
503
504DELIMITER $$
505USE `sakila`$$
506
507
508CREATE FUNCTION `sakila`.`get_customer_balance`(p_customer_id INT, p_effective_date DATETIME) RETURNS DECIMAL(5,2)
509 DETERMINISTIC
510 READS SQL DATA
511BEGIN
512
513 #OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID AND A DATE
514 #THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS:
515 # 1) RENTAL FEES FOR ALL PREVIOUS RENTALS
516 # 2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE
517 # 3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST
518 # 4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED
519
520 DECLARE v_rentfees DECIMAL(5,2); #FEES PAID TO RENT THE VIDEOS INITIALLY
521 DECLARE v_overfees INTEGER; #LATE FEES FOR PRIOR RENTALS
522 DECLARE v_payments DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIOUSLY
523
524 SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees
525 FROM film, inventory, rental
526 WHERE film.film_id = inventory.film_id
527 AND inventory.inventory_id = rental.inventory_id
528 AND rental.rental_date <= p_effective_date
529 AND rental.customer_id = p_customer_id;
530
531 SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration,
532 ((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees
533 FROM rental, inventory, film
534 WHERE film.film_id = inventory.film_id
535 AND inventory.inventory_id = rental.inventory_id
536 AND rental.rental_date <= p_effective_date
537 AND rental.customer_id = p_customer_id;
538
539
540 SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
541 FROM payment
542
543 WHERE payment.payment_date <= p_effective_date
544 AND payment.customer_id = p_customer_id;
545
546 RETURN v_rentfees + v_overfees - v_payments;
547END$$
548
549DELIMITER ;
550
551-- -----------------------------------------------------
552-- procedure film_in_stock
553-- -----------------------------------------------------
554
555DELIMITER $$
556USE `sakila`$$
557
558
559CREATE PROCEDURE `sakila`.`film_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
560READS SQL DATA
561BEGIN
562 SELECT inventory_id
563 FROM inventory
564 WHERE film_id = p_film_id
565 AND store_id = p_store_id
566 AND inventory_in_stock(inventory_id);
567
568 SELECT FOUND_ROWS() INTO p_film_count;
569END$$
570
571DELIMITER ;
572
573-- -----------------------------------------------------
574-- procedure film_not_in_stock
575-- -----------------------------------------------------
576
577DELIMITER $$
578USE `sakila`$$
579
580
581CREATE PROCEDURE `sakila`.`film_not_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
582READS SQL DATA
583BEGIN
584 SELECT inventory_id
585 FROM inventory
586 WHERE film_id = p_film_id
587 AND store_id = p_store_id
588 AND NOT inventory_in_stock(inventory_id);
589
590 SELECT FOUND_ROWS() INTO p_film_count;
591END$$
592
593DELIMITER ;
594
595-- -----------------------------------------------------
596-- function inventory_held_by_customer
597-- -----------------------------------------------------
598
599DELIMITER $$
600USE `sakila`$$
601
602
603CREATE FUNCTION `sakila`.`inventory_held_by_customer`(p_inventory_id INT) RETURNS INT
604READS SQL DATA
605BEGIN
606 DECLARE v_customer_id INT;
607 DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;
608
609 SELECT customer_id INTO v_customer_id
610 FROM rental
611 WHERE return_date IS NULL
612 AND inventory_id = p_inventory_id;
613
614 RETURN v_customer_id;
615END$$
616
617DELIMITER ;
618
619-- -----------------------------------------------------
620-- function inventory_in_stock
621-- -----------------------------------------------------
622
623DELIMITER $$
624USE `sakila`$$
625
626
627CREATE FUNCTION `sakila`.`inventory_in_stock`(p_inventory_id INT) RETURNS BOOLEAN
628READS SQL DATA
629BEGIN
630 DECLARE v_rentals INT;
631 DECLARE v_out INT;
632
633 #AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE
634 #FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED
635
636 SELECT COUNT(*) INTO v_rentals
637 FROM rental
638 WHERE inventory_id = p_inventory_id;
639
640 IF v_rentals = 0 THEN
641 RETURN TRUE;
642 END IF;
643
644 SELECT COUNT(rental_id) INTO v_out
645 FROM inventory LEFT JOIN rental USING(inventory_id)
646 WHERE inventory.inventory_id = p_inventory_id
647 AND rental.return_date IS NULL;
648
649 IF v_out > 0 THEN
650 RETURN FALSE;
651 ELSE
652 RETURN TRUE;
653 END IF;
654END$$
655
656DELIMITER ;
657
658-- -----------------------------------------------------
659-- View `sakila`.`customer_list`
660-- -----------------------------------------------------
661DROP TABLE IF EXISTS `sakila`.`customer_list`;
662USE `sakila`;
663--
664-- View structure for view `customer_list`
665--
666
667CREATE OR REPLACE VIEW customer_list
668AS
669SELECT 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`,
670 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
671FROM customer AS cu JOIN address AS a ON cu.address_id = a.address_id JOIN city ON a.city_id = city.city_id
672 JOIN country ON city.country_id = country.country_id;
673
674-- -----------------------------------------------------
675-- View `sakila`.`film_list`
676-- -----------------------------------------------------
677DROP TABLE IF EXISTS `sakila`.`film_list`;
678USE `sakila`;
679--
680-- View structure for view `film_list`
681--
682
683CREATE OR REPLACE VIEW film_list
684AS
685SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price,
686 film.length AS length, film.rating AS rating, GROUP_CONCAT(CONCAT(actor.first_name, _utf8' ', actor.last_name) SEPARATOR ', ') AS actors
687FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id
688 JOIN film_actor ON film.film_id = film_actor.film_id
689 JOIN actor ON film_actor.actor_id = actor.actor_id
690GROUP BY film.film_id, category.name;
691
692-- -----------------------------------------------------
693-- View `sakila`.`nicer_but_slower_film_list`
694-- -----------------------------------------------------
695DROP TABLE IF EXISTS `sakila`.`nicer_but_slower_film_list`;
696USE `sakila`;
697--
698-- View structure for view `nicer_but_slower_film_list`
699--
700
701CREATE OR REPLACE VIEW nicer_but_slower_film_list
702AS
703SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price,
704 film.length AS length, film.rating AS rating, GROUP_CONCAT(CONCAT(CONCAT(UCASE(SUBSTR(actor.first_name,1,1)),
705 LCASE(SUBSTR(actor.first_name,2,LENGTH(actor.first_name))),_utf8' ',CONCAT(UCASE(SUBSTR(actor.last_name,1,1)),
706 LCASE(SUBSTR(actor.last_name,2,LENGTH(actor.last_name)))))) SEPARATOR ', ') AS actors
707FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id
708 JOIN film_actor ON film.film_id = film_actor.film_id
709 JOIN actor ON film_actor.actor_id = actor.actor_id
710GROUP BY film.film_id, category.name;
711
712-- -----------------------------------------------------
713-- View `sakila`.`staff_list`
714-- -----------------------------------------------------
715DROP TABLE IF EXISTS `sakila`.`staff_list`;
716USE `sakila`;
717--
718-- View structure for view `staff_list`
719--
720
721CREATE OR REPLACE VIEW staff_list
722AS
723SELECT 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,
724 city.city AS city, country.country AS country, s.store_id AS SID
725FROM staff AS s JOIN address AS a ON s.address_id = a.address_id JOIN city ON a.city_id = city.city_id
726 JOIN country ON city.country_id = country.country_id;
727
728-- -----------------------------------------------------
729-- View `sakila`.`sales_by_store`
730-- -----------------------------------------------------
731DROP TABLE IF EXISTS `sakila`.`sales_by_store`;
732USE `sakila`;
733--
734-- View structure for view `sales_by_store`
735--
736
737CREATE OR REPLACE VIEW sales_by_store
738AS
739SELECT
740CONCAT(c.city, _utf8',', cy.country) AS store
741, CONCAT(m.first_name, _utf8' ', m.last_name) AS manager
742, SUM(p.amount) AS total_sales
743FROM payment AS p
744INNER JOIN rental AS r ON p.rental_id = r.rental_id
745INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
746INNER JOIN store AS s ON i.store_id = s.store_id
747INNER JOIN address AS a ON s.address_id = a.address_id
748INNER JOIN city AS c ON a.city_id = c.city_id
749INNER JOIN country AS cy ON c.country_id = cy.country_id
750INNER JOIN staff AS m ON s.manager_staff_id = m.staff_id
751GROUP BY s.store_id
752ORDER BY cy.country, c.city;
753
754-- -----------------------------------------------------
755-- View `sakila`.`sales_by_film_category`
756-- -----------------------------------------------------
757DROP TABLE IF EXISTS `sakila`.`sales_by_film_category`;
758USE `sakila`;
759--
760-- View structure for view `sales_by_film_category`
761--
762-- Note that total sales will add up to >100% because
763-- some titles belong to more than 1 category
764--
765
766CREATE OR REPLACE VIEW sales_by_film_category
767AS
768SELECT
769c.name AS category
770, SUM(p.amount) AS total_sales
771FROM payment AS p
772INNER JOIN rental AS r ON p.rental_id = r.rental_id
773INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
774INNER JOIN film AS f ON i.film_id = f.film_id
775INNER JOIN film_category AS fc ON f.film_id = fc.film_id
776INNER JOIN category AS c ON fc.category_id = c.category_id
777GROUP BY c.name
778ORDER BY total_sales DESC;
779
780-- -----------------------------------------------------
781-- View `sakila`.`actor_info`
782-- -----------------------------------------------------
783DROP TABLE IF EXISTS `sakila`.`actor_info`;
784USE `sakila`;
785--
786-- View structure for view `actor_info`
787--
788
789CREATE OR REPLACE DEFINER=CURRENT_USER SQL SECURITY INVOKER VIEW actor_info
790AS
791SELECT
792a.actor_id,
793a.first_name,
794a.last_name,
795GROUP_CONCAT(DISTINCT CONCAT(c.name, ': ',
796 (SELECT GROUP_CONCAT(f.title ORDER BY f.title SEPARATOR ', ')
797 FROM sakila.film f
798 INNER JOIN sakila.film_category fc
799 ON f.film_id = fc.film_id
800 INNER JOIN sakila.film_actor fa
801 ON f.film_id = fa.film_id
802 WHERE fc.category_id = c.category_id
803 AND fa.actor_id = a.actor_id
804 )
805 )
806 ORDER BY c.name SEPARATOR '; ')
807AS film_info
808FROM sakila.actor a
809LEFT JOIN sakila.film_actor fa
810 ON a.actor_id = fa.actor_id
811LEFT JOIN sakila.film_category fc
812 ON fa.film_id = fc.film_id
813LEFT JOIN sakila.category c
814 ON fc.category_id = c.category_id
815GROUP BY a.actor_id, a.first_name, a.last_name;
816USE `sakila`;
817
818DELIMITER $$
819USE `sakila`$$
820
821CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
822 INSERT INTO film_text (film_id, title, description)
823 VALUES (new.film_id, new.title, new.description);
824 END$$
825
826USE `sakila`$$
827
828CREATE TRIGGER `upd_film` AFTER UPDATE ON `film` FOR EACH ROW BEGIN
829 IF (old.title != new.title) or (old.description != new.description)
830 THEN
831 UPDATE film_text
832 SET title=new.title,
833 description=new.description,
834 film_id=new.film_id
835 WHERE film_id=old.film_id;
836 END IF;
837 END$$
838
839USE `sakila`$$
840
841CREATE TRIGGER `del_film` AFTER DELETE ON `film` FOR EACH ROW BEGIN
842 DELETE FROM film_text WHERE film_id = old.film_id;
843 END$$
844
845
846DELIMITER ;
847
848SET SQL_MODE=@OLD_SQL_MODE;
849SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
850SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;