· 6 years ago · Jul 15, 2019, 08:18 PM
1-- MySQL Script generated by MySQL Workbench
2-- Mon Jul 15 23:14:06 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
390
391-- -----------------------------------------------------
392-- Table `sakila`.`table1_copy1`
393-- -----------------------------------------------------
394CREATE TABLE IF NOT EXISTS `sakila`.`table1_copy1` (
395 `id` INT NOT NULL,
396 `name` VARCHAR(45) NULL,
397 PRIMARY KEY (`id`))
398ENGINE = InnoDB;
399
400
401-- -----------------------------------------------------
402-- Table `sakila`.`table1`
403-- -----------------------------------------------------
404CREATE TABLE IF NOT EXISTS `sakila`.`table1` (
405 `id` INT NOT NULL,
406 `name` VARCHAR(45) NULL,
407 `table1col` VARCHAR(45) BINARY NULL,
408 `table1_copy1_id` INT NOT NULL,
409 PRIMARY KEY (`id`),
410 INDEX `fk_table1_table1_copy11_idx` (`table1_copy1_id` ASC),
411 CONSTRAINT `fk_table1_table1_copy11`
412 FOREIGN KEY (`table1_copy1_id`)
413 REFERENCES `sakila`.`table1_copy1` (`id`)
414 ON DELETE NO ACTION
415 ON UPDATE NO ACTION)
416ENGINE = InnoDB;
417
418USE `sakila` ;
419
420-- -----------------------------------------------------
421-- Placeholder table for view `sakila`.`customer_list`
422-- -----------------------------------------------------
423CREATE 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);
424
425-- -----------------------------------------------------
426-- Placeholder table for view `sakila`.`film_list`
427-- -----------------------------------------------------
428CREATE TABLE IF NOT EXISTS `sakila`.`film_list` (`FID` INT, `title` INT, `description` INT, `category` INT, `price` INT, `length` INT, `rating` INT, `actors` INT);
429
430-- -----------------------------------------------------
431-- Placeholder table for view `sakila`.`nicer_but_slower_film_list`
432-- -----------------------------------------------------
433CREATE 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);
434
435-- -----------------------------------------------------
436-- Placeholder table for view `sakila`.`staff_list`
437-- -----------------------------------------------------
438CREATE TABLE IF NOT EXISTS `sakila`.`staff_list` (`ID` INT, `name` INT, `address` INT, `zip code` INT, `phone` INT, `city` INT, `country` INT, `SID` INT);
439
440-- -----------------------------------------------------
441-- Placeholder table for view `sakila`.`sales_by_store`
442-- -----------------------------------------------------
443CREATE TABLE IF NOT EXISTS `sakila`.`sales_by_store` (`store` INT, `manager` INT, `total_sales` INT);
444
445-- -----------------------------------------------------
446-- Placeholder table for view `sakila`.`sales_by_film_category`
447-- -----------------------------------------------------
448CREATE TABLE IF NOT EXISTS `sakila`.`sales_by_film_category` (`category` INT, `total_sales` INT);
449
450-- -----------------------------------------------------
451-- Placeholder table for view `sakila`.`actor_info`
452-- -----------------------------------------------------
453CREATE TABLE IF NOT EXISTS `sakila`.`actor_info` (`actor_id` INT, `first_name` INT, `last_name` INT, `film_info` INT);
454
455-- -----------------------------------------------------
456-- procedure rewards_report
457-- -----------------------------------------------------
458
459DELIMITER $$
460USE `sakila`$$
461
462
463CREATE PROCEDURE `sakila`.`rewards_report` (
464 IN min_monthly_purchases TINYINT UNSIGNED
465 , IN min_dollar_amount_purchased DECIMAL(10,2) UNSIGNED
466 , OUT count_rewardees INT
467)
468LANGUAGE SQL
469NOT DETERMINISTIC
470READS SQL DATA
471SQL SECURITY DEFINER
472COMMENT 'Provides a customizable report on best customers'
473proc: BEGIN
474
475 DECLARE last_month_start DATE;
476 DECLARE last_month_end DATE;
477
478 /* Some sanity checks... */
479 IF min_monthly_purchases = 0 THEN
480 SELECT 'Minimum monthly purchases parameter must be > 0';
481 LEAVE proc;
482 END IF;
483 IF min_dollar_amount_purchased = 0.00 THEN
484 SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
485 LEAVE proc;
486 END IF;
487
488 /* Determine start and end time periods */
489 SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
490 SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
491 SET last_month_end = LAST_DAY(last_month_start);
492
493 /*
494 Create a temporary storage area for
495 Customer IDs.
496 */
497 CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
498
499 /*
500 Find all customers meeting the
501 monthly purchase requirements
502 */
503 INSERT INTO tmpCustomer (customer_id)
504 SELECT p.customer_id
505 FROM payment AS p
506 WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
507 GROUP BY customer_id
508 HAVING SUM(p.amount) > min_dollar_amount_purchased
509 AND COUNT(customer_id) > min_monthly_purchases;
510
511 /* Populate OUT parameter with count of found customers */
512 SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;
513
514 /*
515 Output ALL customer information of matching rewardees.
516 Customize output as needed.
517 */
518 SELECT c.*
519 FROM tmpCustomer AS t
520 INNER JOIN customer AS c ON t.customer_id = c.customer_id;
521
522 /* Clean up */
523 DROP TABLE tmpCustomer;
524END$$
525
526DELIMITER ;
527
528-- -----------------------------------------------------
529-- function get_customer_balance
530-- -----------------------------------------------------
531
532DELIMITER $$
533USE `sakila`$$
534
535
536CREATE FUNCTION `sakila`.`get_customer_balance`(p_customer_id INT, p_effective_date DATETIME) RETURNS DECIMAL(5,2)
537 DETERMINISTIC
538 READS SQL DATA
539BEGIN
540
541 #OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID AND A DATE
542 #THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS:
543 # 1) RENTAL FEES FOR ALL PREVIOUS RENTALS
544 # 2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE
545 # 3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST
546 # 4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED
547
548 DECLARE v_rentfees DECIMAL(5,2); #FEES PAID TO RENT THE VIDEOS INITIALLY
549 DECLARE v_overfees INTEGER; #LATE FEES FOR PRIOR RENTALS
550 DECLARE v_payments DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIOUSLY
551
552 SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees
553 FROM film, inventory, rental
554 WHERE film.film_id = inventory.film_id
555 AND inventory.inventory_id = rental.inventory_id
556 AND rental.rental_date <= p_effective_date
557 AND rental.customer_id = p_customer_id;
558
559 SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration,
560 ((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees
561 FROM rental, inventory, film
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
568 SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
569 FROM payment
570
571 WHERE payment.payment_date <= p_effective_date
572 AND payment.customer_id = p_customer_id;
573
574 RETURN v_rentfees + v_overfees - v_payments;
575END$$
576
577DELIMITER ;
578
579-- -----------------------------------------------------
580-- procedure film_in_stock
581-- -----------------------------------------------------
582
583DELIMITER $$
584USE `sakila`$$
585
586
587CREATE PROCEDURE `sakila`.`film_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
588READS SQL DATA
589BEGIN
590 SELECT inventory_id
591 FROM inventory
592 WHERE film_id = p_film_id
593 AND store_id = p_store_id
594 AND inventory_in_stock(inventory_id);
595
596 SELECT FOUND_ROWS() INTO p_film_count;
597END$$
598
599DELIMITER ;
600
601-- -----------------------------------------------------
602-- procedure film_not_in_stock
603-- -----------------------------------------------------
604
605DELIMITER $$
606USE `sakila`$$
607
608
609CREATE PROCEDURE `sakila`.`film_not_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
610READS SQL DATA
611BEGIN
612 SELECT inventory_id
613 FROM inventory
614 WHERE film_id = p_film_id
615 AND store_id = p_store_id
616 AND NOT inventory_in_stock(inventory_id);
617
618 SELECT FOUND_ROWS() INTO p_film_count;
619END$$
620
621DELIMITER ;
622
623-- -----------------------------------------------------
624-- function inventory_held_by_customer
625-- -----------------------------------------------------
626
627DELIMITER $$
628USE `sakila`$$
629
630
631CREATE FUNCTION `sakila`.`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
647-- -----------------------------------------------------
648-- function inventory_in_stock
649-- -----------------------------------------------------
650
651DELIMITER $$
652USE `sakila`$$
653
654
655CREATE FUNCTION `sakila`.`inventory_in_stock`(p_inventory_id INT) RETURNS BOOLEAN
656READS SQL DATA
657BEGIN
658 DECLARE v_rentals INT;
659 DECLARE v_out INT;
660
661 #AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE
662 #FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED
663
664 SELECT COUNT(*) INTO v_rentals
665 FROM rental
666 WHERE inventory_id = p_inventory_id;
667
668 IF v_rentals = 0 THEN
669 RETURN TRUE;
670 END IF;
671
672 SELECT COUNT(rental_id) INTO v_out
673 FROM inventory LEFT JOIN rental USING(inventory_id)
674 WHERE inventory.inventory_id = p_inventory_id
675 AND rental.return_date IS NULL;
676
677 IF v_out > 0 THEN
678 RETURN FALSE;
679 ELSE
680 RETURN TRUE;
681 END IF;
682END$$
683
684DELIMITER ;
685
686-- -----------------------------------------------------
687-- View `sakila`.`customer_list`
688-- -----------------------------------------------------
689DROP TABLE IF EXISTS `sakila`.`customer_list`;
690USE `sakila`;
691--
692-- View structure for view `customer_list`
693--
694
695CREATE OR REPLACE VIEW customer_list
696AS
697SELECT 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`,
698 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
699FROM customer AS cu JOIN address AS a ON cu.address_id = a.address_id JOIN city ON a.city_id = city.city_id
700 JOIN country ON city.country_id = country.country_id;
701
702-- -----------------------------------------------------
703-- View `sakila`.`film_list`
704-- -----------------------------------------------------
705DROP TABLE IF EXISTS `sakila`.`film_list`;
706USE `sakila`;
707--
708-- View structure for view `film_list`
709--
710
711CREATE OR REPLACE VIEW film_list
712AS
713SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price,
714 film.length AS length, film.rating AS rating, GROUP_CONCAT(CONCAT(actor.first_name, _utf8' ', actor.last_name) SEPARATOR ', ') AS actors
715FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id
716 JOIN film_actor ON film.film_id = film_actor.film_id
717 JOIN actor ON film_actor.actor_id = actor.actor_id
718GROUP BY film.film_id, category.name;
719
720-- -----------------------------------------------------
721-- View `sakila`.`nicer_but_slower_film_list`
722-- -----------------------------------------------------
723DROP TABLE IF EXISTS `sakila`.`nicer_but_slower_film_list`;
724USE `sakila`;
725--
726-- View structure for view `nicer_but_slower_film_list`
727--
728
729CREATE OR REPLACE VIEW nicer_but_slower_film_list
730AS
731SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price,
732 film.length AS length, film.rating AS rating, GROUP_CONCAT(CONCAT(CONCAT(UCASE(SUBSTR(actor.first_name,1,1)),
733 LCASE(SUBSTR(actor.first_name,2,LENGTH(actor.first_name))),_utf8' ',CONCAT(UCASE(SUBSTR(actor.last_name,1,1)),
734 LCASE(SUBSTR(actor.last_name,2,LENGTH(actor.last_name)))))) SEPARATOR ', ') AS actors
735FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id
736 JOIN film_actor ON film.film_id = film_actor.film_id
737 JOIN actor ON film_actor.actor_id = actor.actor_id
738GROUP BY film.film_id, category.name;
739
740-- -----------------------------------------------------
741-- View `sakila`.`staff_list`
742-- -----------------------------------------------------
743DROP TABLE IF EXISTS `sakila`.`staff_list`;
744USE `sakila`;
745--
746-- View structure for view `staff_list`
747--
748
749CREATE OR REPLACE VIEW staff_list
750AS
751SELECT 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,
752 city.city AS city, country.country AS country, s.store_id AS SID
753FROM staff AS s JOIN address AS a ON s.address_id = a.address_id JOIN city ON a.city_id = city.city_id
754 JOIN country ON city.country_id = country.country_id;
755
756-- -----------------------------------------------------
757-- View `sakila`.`sales_by_store`
758-- -----------------------------------------------------
759DROP TABLE IF EXISTS `sakila`.`sales_by_store`;
760USE `sakila`;
761--
762-- View structure for view `sales_by_store`
763--
764
765CREATE OR REPLACE VIEW sales_by_store
766AS
767SELECT
768CONCAT(c.city, _utf8',', cy.country) AS store
769, CONCAT(m.first_name, _utf8' ', m.last_name) AS manager
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 store AS s ON i.store_id = s.store_id
775INNER JOIN address AS a ON s.address_id = a.address_id
776INNER JOIN city AS c ON a.city_id = c.city_id
777INNER JOIN country AS cy ON c.country_id = cy.country_id
778INNER JOIN staff AS m ON s.manager_staff_id = m.staff_id
779GROUP BY s.store_id
780ORDER BY cy.country, c.city;
781
782-- -----------------------------------------------------
783-- View `sakila`.`sales_by_film_category`
784-- -----------------------------------------------------
785DROP TABLE IF EXISTS `sakila`.`sales_by_film_category`;
786USE `sakila`;
787--
788-- View structure for view `sales_by_film_category`
789--
790-- Note that total sales will add up to >100% because
791-- some titles belong to more than 1 category
792--
793
794CREATE OR REPLACE VIEW sales_by_film_category
795AS
796SELECT
797c.name AS category
798, SUM(p.amount) AS total_sales
799FROM payment AS p
800INNER JOIN rental AS r ON p.rental_id = r.rental_id
801INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
802INNER JOIN film AS f ON i.film_id = f.film_id
803INNER JOIN film_category AS fc ON f.film_id = fc.film_id
804INNER JOIN category AS c ON fc.category_id = c.category_id
805GROUP BY c.name
806ORDER BY total_sales DESC;
807
808-- -----------------------------------------------------
809-- View `sakila`.`actor_info`
810-- -----------------------------------------------------
811DROP TABLE IF EXISTS `sakila`.`actor_info`;
812USE `sakila`;
813--
814-- View structure for view `actor_info`
815--
816
817CREATE OR REPLACE DEFINER=CURRENT_USER SQL SECURITY INVOKER VIEW actor_info
818AS
819SELECT
820a.actor_id,
821a.first_name,
822a.last_name,
823GROUP_CONCAT(DISTINCT CONCAT(c.name, ': ',
824 (SELECT GROUP_CONCAT(f.title ORDER BY f.title SEPARATOR ', ')
825 FROM sakila.film f
826 INNER JOIN sakila.film_category fc
827 ON f.film_id = fc.film_id
828 INNER JOIN sakila.film_actor fa
829 ON f.film_id = fa.film_id
830 WHERE fc.category_id = c.category_id
831 AND fa.actor_id = a.actor_id
832 )
833 )
834 ORDER BY c.name SEPARATOR '; ')
835AS film_info
836FROM sakila.actor a
837LEFT JOIN sakila.film_actor fa
838 ON a.actor_id = fa.actor_id
839LEFT JOIN sakila.film_category fc
840 ON fa.film_id = fc.film_id
841LEFT JOIN sakila.category c
842 ON fc.category_id = c.category_id
843GROUP BY a.actor_id, a.first_name, a.last_name;
844USE `sakila`;
845
846DELIMITER $$
847USE `sakila`$$
848
849CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
850 INSERT INTO film_text (film_id, title, description)
851 VALUES (new.film_id, new.title, new.description);
852 END$$
853
854USE `sakila`$$
855
856CREATE TRIGGER `upd_film` AFTER UPDATE ON `film` FOR EACH ROW BEGIN
857 IF (old.title != new.title) or (old.description != new.description)
858 THEN
859 UPDATE film_text
860 SET title=new.title,
861 description=new.description,
862 film_id=new.film_id
863 WHERE film_id=old.film_id;
864 END IF;
865 END$$
866
867USE `sakila`$$
868
869CREATE TRIGGER `del_film` AFTER DELETE ON `film` FOR EACH ROW BEGIN
870 DELETE FROM film_text WHERE film_id = old.film_id;
871 END$$
872
873
874DELIMITER ;
875
876SET SQL_MODE=@OLD_SQL_MODE;
877SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
878SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;