oracleSQL.txt

-- MySQL Workbench Forward Engineering

/* SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; */
/* SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; */
/* SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; */

-- -----------------------------------------------------
-- Schema sakila
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema sakila
-- -----------------------------------------------------
CREATE USER sakila IDENTIFIED BY sakila ;
ALTER SESSION SET CURRENT_SCHEMA = sakila ;

-- -----------------------------------------------------
-- Table `sakila`.`actor`
-- -----------------------------------------------------
CREATE TABLE sakila.actor (
  actor_id NUMBER(5) CHECK (actor_id > 0) NOT NULL,
  first_name VARCHAR2(45) NOT NULL,
  last_name VARCHAR2(45) NOT NULL,
  last_update TIMESTAMP(0) DEFAULT SYSTIMESTAMP NOT NULL,
  PRIMARY KEY (actor_id)
 )
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE sakila.actor_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER sakila.actor_seq_tr
 BEFORE INSERT ON sakila.actor FOR EACH ROW
 WHEN (NEW.actor_id IS NULL)
BEGIN
 SELECT sakila.actor_seq.NEXTVAL INTO :NEW.actor_id FROM DUAL;
END;
/

CREATE INDEX idx_actor_last_name ON sakila.actor (last_name ASC);


-- -----------------------------------------------------
-- Table `sakila`.`country`
-- -----------------------------------------------------
CREATE TABLE sakila.country (
  country_id NUMBER(5) CHECK (country_id > 0) NOT NULL,
  country VARCHAR2(50) NOT NULL,
  last_update TIMESTAMP(0) DEFAULT SYSTIMESTAMP NOT NULL,
  PRIMARY KEY (country_id))
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE sakila.country_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER sakila.country_seq_tr
 BEFORE INSERT ON sakila.country FOR EACH ROW
 WHEN (NEW.country_id IS NULL)
BEGIN
 SELECT sakila.country_seq.NEXTVAL INTO :NEW.country_id FROM DUAL;
END;
/


-- -----------------------------------------------------
-- Table `sakila`.`city`
-- -----------------------------------------------------
CREATE TABLE sakila.city (
  city_id NUMBER(5) CHECK (city_id > 0) NOT NULL,
  city VARCHAR2(50) NOT NULL,
  country_id NUMBER(5) CHECK (country_id > 0) NOT NULL,
  last_update TIMESTAMP(0) DEFAULT SYSTIMESTAMP NOT NULL,
  PRIMARY KEY (city_id)
 ,
  CONSTRAINT fk_city_country
    FOREIGN KEY (country_id)
    REFERENCES sakila.country (country_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE sakila.city_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER sakila.city_seq_tr
 BEFORE INSERT ON sakila.city FOR EACH ROW
 WHEN (NEW.city_id IS NULL)
BEGIN
 SELECT sakila.city_seq.NEXTVAL INTO :NEW.city_id FROM DUAL;
END;
/

CREATE INDEX idx_fk_country_id ON sakila.city (country_id ASC);


-- -----------------------------------------------------
-- Table `sakila`.`address`
-- -----------------------------------------------------
CREATE TABLE sakila.address (
  address_id NUMBER(5) CHECK (address_id > 0) NOT NULL,
  address VARCHAR2(50) NOT NULL,
  address2 VARCHAR2(50) DEFAULT NULL NULL,
  district VARCHAR2(20) NOT NULL,
  city_id NUMBER(5) CHECK (city_id > 0) NOT NULL,
  postal_code VARCHAR2(10) DEFAULT NULL NULL,
  phone VARCHAR2(20) NOT NULL,
  last_update TIMESTAMP(0) DEFAULT SYSTIMESTAMP NOT NULL,
  PRIMARY KEY (address_id)
 ,
  CONSTRAINT fk_address_city
    FOREIGN KEY (city_id)
    REFERENCES sakila.city (city_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE sakila.address_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER sakila.address_seq_tr
 BEFORE INSERT ON sakila.address FOR EACH ROW
 WHEN (NEW.address_id IS NULL)
BEGIN
 SELECT sakila.address_seq.NEXTVAL INTO :NEW.address_id FROM DUAL;
END;
/

CREATE INDEX idx_fk_city_id ON sakila.address (city_id ASC);


-- -----------------------------------------------------
-- Table `sakila`.`category`
-- -----------------------------------------------------
CREATE TABLE sakila.category (
  category_id NUMBER(3) CHECK (category_id > 0) NOT NULL,
  name VARCHAR2(25) NOT NULL,
  last_update TIMESTAMP(0) DEFAULT SYSTIMESTAMP NOT NULL,
  PRIMARY KEY (category_id))
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE sakila.category_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER sakila.category_seq_tr
 BEFORE INSERT ON sakila.category FOR EACH ROW
 WHEN (NEW.category_id IS NULL)
BEGIN
 SELECT sakila.category_seq.NEXTVAL INTO :NEW.category_id FROM DUAL;
END;
/


-- -----------------------------------------------------
-- Table `sakila`.`staff`
-- -----------------------------------------------------
CREATE TABLE sakila.staff (
  staff_id NUMBER(3) CHECK (staff_id > 0) NOT NULL,
  first_name VARCHAR2(45) NOT NULL,
  last_name VARCHAR2(45) NOT NULL,
  address_id NUMBER(5) CHECK (address_id > 0) NOT NULL,
  picture BLOB NULL,
  email VARCHAR2(50) DEFAULT NULL NULL,
  store_id NUMBER(3) CHECK (store_id > 0) NOT NULL,
  active NUMBER(3) DEFAULT TRUE NOT NULL,
  username VARCHAR2(16) NOT NULL,
  password VARCHAR2(40) DEFAULT NULL BINARY NULL,
  last_update TIMESTAMP(0) DEFAULT SYSTIMESTAMP NOT NULL,
  PRIMARY KEY (staff_id)
 ,
  CONSTRAINT fk_staff_store
    FOREIGN KEY (store_id)
    REFERENCES sakila.store (store_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT fk_staff_address
    FOREIGN KEY (address_id)
    REFERENCES sakila.address (address_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE sakila.staff_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER sakila.staff_seq_tr
 BEFORE INSERT ON sakila.staff FOR EACH ROW
 WHEN (NEW.staff_id IS NULL)
BEGIN
 SELECT sakila.staff_seq.NEXTVAL INTO :NEW.staff_id FROM DUAL;
END;
/

CREATE INDEX idx_fk_store_id ON sakila.staff (store_id ASC);
CREATE INDEX idx_fk_address_id ON sakila.staff (address_id ASC);


-- -----------------------------------------------------
-- Table `sakila`.`store`
-- -----------------------------------------------------
CREATE TABLE sakila.store (
  store_id NUMBER(3) CHECK (store_id > 0) NOT NULL,
  manager_staff_id NUMBER(3) CHECK (manager_staff_id > 0) NOT NULL,
  address_id NUMBER(5) CHECK (address_id > 0) NOT NULL,
  last_update TIMESTAMP(0) DEFAULT SYSTIMESTAMP NOT NULL,
  PRIMARY KEY (store_id),
  CONSTRAINT idx_unique_manager UNIQUE  (manager_staff_id)
 ,
  CONSTRAINT fk_store_staff
    FOREIGN KEY (manager_staff_id)
    REFERENCES sakila.staff (staff_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT fk_store_address
    FOREIGN KEY (address_id)
    REFERENCES sakila.address (address_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE sakila.store_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER sakila.store_seq_tr
 BEFORE INSERT ON sakila.store FOR EACH ROW
 WHEN (NEW.store_id IS NULL)
BEGIN
 SELECT sakila.store_seq.NEXTVAL INTO :NEW.store_id FROM DUAL;
END;
/

CREATE INDEX idx_fk_address_id ON sakila.store (address_id ASC);


-- -----------------------------------------------------
-- Table `sakila`.`customer`
-- -----------------------------------------------------
CREATE TABLE sakila.customer (
  customer_id NUMBER(5) CHECK (customer_id > 0) NOT NULL,
  store_id NUMBER(3) CHECK (store_id > 0) NOT NULL,
  first_name VARCHAR2(45) NOT NULL,
  last_name VARCHAR2(45) NOT NULL,
  email VARCHAR2(50) DEFAULT NULL NULL,
  address_id NUMBER(5) CHECK (address_id > 0) NOT NULL,
  active NUMBER(3) DEFAULT TRUE NOT NULL,
  create_date TIMESTAMP(0) NOT NULL,
  last_update TIMESTAMP(0) DEFAULT SYSTIMESTAMP NULL,
  PRIMARY KEY (customer_id)
 ,
  CONSTRAINT fk_customer_address
    FOREIGN KEY (address_id)
    REFERENCES sakila.address (address_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT fk_customer_store
    FOREIGN KEY (store_id)
    REFERENCES sakila.store (store_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
;

COMMENT ON TABLE sakila.customer IS 'Table storing all customers. Holds foreign keys to the address table and the store table where this customer is registered.nnBasic 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.'

-- Generate ID using sequence and trigger
CREATE SEQUENCE sakila.customer_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER sakila.customer_seq_tr
 BEFORE INSERT ON sakila.customer FOR EACH ROW
 WHEN (NEW.customer_id IS NULL)
BEGIN
 SELECT sakila.customer_seq.NEXTVAL INTO :NEW.customer_id FROM DUAL;
END;
/

CREATE INDEX idx_fk_store_id ON sakila.customer (store_id ASC);
CREATE INDEX idx_fk_address_id ON sakila.customer (address_id ASC);
CREATE INDEX idx_last_name ON sakila.customer (last_name ASC);


-- -----------------------------------------------------
-- Table `sakila`.`language`
-- -----------------------------------------------------
CREATE TABLE sakila.language (
  language_id NUMBER(3) CHECK (language_id > 0) NOT NULL,
  name CHAR(20) NOT NULL,
  last_update TIMESTAMP(0) DEFAULT SYSTIMESTAMP NOT NULL,
  PRIMARY KEY (language_id))
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE sakila.language_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER sakila.language_seq_tr
 BEFORE INSERT ON sakila.language FOR EACH ROW
 WHEN (NEW.language_id IS NULL)
BEGIN
 SELECT sakila.language_seq.NEXTVAL INTO :NEW.language_id FROM DUAL;
END;
/


-- -----------------------------------------------------
-- Table `sakila`.`film`
-- -----------------------------------------------------
CREATE TABLE sakila.film (
  film_id NUMBER(5) CHECK (film_id > 0) NOT NULL,
  title VARCHAR2(255) NOT NULL,
  description CLOB NULL,
  release_year NUMBER(4) NULL,
  language_id NUMBER(3) CHECK (language_id > 0) NOT NULL,
  original_language_id NUMBER(3) DEFAULT NULL CHECK (original_language_id > 0) NULL,
  rental_duration NUMBER(3) DEFAULT 3 CHECK (rental_duration > 0) NOT NULL,
  rental_rate NUMBER(4,2) DEFAULT 4.99 NOT NULL,
  length NUMBER(5) DEFAULT NULL CHECK (length > 0) NULL,
  replacement_cost NUMBER(5,2) DEFAULT 19.99 NOT NULL,
  rating ENUM('G','PG','PG-13','R','NC-17');

-- Generate ID using sequence and trigger
CREATE SEQUENCE sakila.film_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER sakila.film_seq_tr
 BEFORE INSERT ON sakila.film FOR EACH ROW
 WHEN (NEW.film_id IS NULL)
BEGIN
 SELECT sakila.film_seq.NEXTVAL INTO :NEW.film_id FROM DUAL;
END;
/ NULL DEFAULT 'G',
  special_features SET('Trailers''Commentaries''Deleted Scenes''Behind the Scenes') NULL,
  last_update TIMESTAMP NOT NULL DEFAULT SYSTIMESTAMP,
  INDEX `idx_title` (title ASC),
  INDEX `idx_fk_language_id` (language_id ASC),
  INDEX `idx_fk_original_language_id` (original_language_id ASC),
  PRIMARY KEY (film_id),
  CONSTRAINT fk_film_language
    FOREIGN KEY (language_id)
    REFERENCES `sakila`.`language` (language_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT fk_film_language_original
    FOREIGN KEY (original_language_id)
    REFERENCES `sakila`.`language` (language_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `sakila`.`film_actor`
-- -----------------------------------------------------
CREATE TABLE sakila.film_actor (
  actor_id NUMBER(5) CHECK (actor_id > 0) NOT NULL,
  film_id NUMBER(5) CHECK (film_id > 0) NOT NULL,
  last_update TIMESTAMP(0) DEFAULT SYSTIMESTAMP NOT NULL,
  PRIMARY KEY (actor_id, film_id)
 ,
  CONSTRAINT fk_film_actor_actor
    FOREIGN KEY (actor_id)
    REFERENCES sakila.actor (actor_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT fk_film_actor_film
    FOREIGN KEY (film_id)
    REFERENCES sakila.film (film_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
;

CREATE INDEX idx_fk_film_id ON sakila.film_actor (film_id ASC);
CREATE INDEX fk_film_actor_actor_idx ON sakila.film_actor (actor_id ASC);


-- -----------------------------------------------------
-- Table `sakila`.`film_category`
-- -----------------------------------------------------
CREATE TABLE sakila.film_category (
  film_id NUMBER(5) CHECK (film_id > 0) NOT NULL,
  category_id NUMBER(3) CHECK (category_id > 0) NOT NULL,
  last_update TIMESTAMP(0) DEFAULT SYSTIMESTAMP NOT NULL,
  PRIMARY KEY (film_id, category_id)
 ,
  CONSTRAINT fk_film_category_film
    FOREIGN KEY (film_id)
    REFERENCES sakila.film (film_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT fk_film_category_category
    FOREIGN KEY (category_id)
    REFERENCES sakila.category (category_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
;

CREATE INDEX fk_film_category_category_idx ON sakila.film_category (category_id ASC);
CREATE INDEX fk_film_category_film_idx ON sakila.film_category (film_id ASC);


-- -----------------------------------------------------
-- Table `sakila`.`film_text`
-- -----------------------------------------------------
CREATE TABLE sakila.film_text (
  film_id NUMBER(5) CHECK (film_id > 0) NOT NULL,
  title VARCHAR2(255) NOT NULL,
  description CLOB NULL,
  PRIMARY KEY (film_id),
  FULLTEXT INDEX `idx_title_description` (`title`, description))
;


-- -----------------------------------------------------
-- Table `sakila`.`inventory`
-- -----------------------------------------------------
CREATE TABLE sakila.inventory (
  inventory_id NUMBER(7) CHECK (inventory_id > 0) NOT NULL,
  film_id NUMBER(5) CHECK (film_id > 0) NOT NULL,
  store_id NUMBER(3) CHECK (store_id > 0) NOT NULL,
  last_update TIMESTAMP(0) DEFAULT SYSTIMESTAMP NOT NULL,
  PRIMARY KEY (inventory_id)
 ,
  CONSTRAINT fk_inventory_store
    FOREIGN KEY (store_id)
    REFERENCES sakila.store (store_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT fk_inventory_film
    FOREIGN KEY (film_id)
    REFERENCES sakila.film (film_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE sakila.inventory_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER sakila.inventory_seq_tr
 BEFORE INSERT ON sakila.inventory FOR EACH ROW
 WHEN (NEW.inventory_id IS NULL)
BEGIN
 SELECT sakila.inventory_seq.NEXTVAL INTO :NEW.inventory_id FROM DUAL;
END;
/

CREATE INDEX idx_fk_film_id ON sakila.inventory (film_id ASC);
CREATE INDEX idx_store_id_film_id ON sakila.inventory (store_id ASC, film_id ASC);
CREATE INDEX fk_inventory_store_idx ON sakila.inventory (store_id ASC);


-- -----------------------------------------------------
-- Table `sakila`.`rental`
-- -----------------------------------------------------
CREATE TABLE sakila.rental (
  rental_id NUMBER(10) NOT NULL,
  rental_date TIMESTAMP(0) NOT NULL,
  inventory_id NUMBER(7) CHECK (inventory_id > 0) NOT NULL,
  customer_id NUMBER(5) CHECK (customer_id > 0) NOT NULL,
  return_date TIMESTAMP(0) NULL,
  staff_id NUMBER(3) CHECK (staff_id > 0) NOT NULL,
  last_update TIMESTAMP(0) DEFAULT SYSTIMESTAMP NOT NULL,
  PRIMARY KEY (rental_id),
  CONSTRAINT idx_rental UNIQUE  (rental_date, inventory_id, customer_id)
 ,
  CONSTRAINT fk_rental_staff
    FOREIGN KEY (staff_id)
    REFERENCES sakila.staff (staff_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT fk_rental_inventory
    FOREIGN KEY (inventory_id)
    REFERENCES sakila.inventory (inventory_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT fk_rental_customer
    FOREIGN KEY (customer_id)
    REFERENCES sakila.customer (customer_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE sakila.rental_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER sakila.rental_seq_tr
 BEFORE INSERT ON sakila.rental FOR EACH ROW
 WHEN (NEW.rental_id IS NULL)
BEGIN
 SELECT sakila.rental_seq.NEXTVAL INTO :NEW.rental_id FROM DUAL;
END;
/

CREATE INDEX idx_fk_inventory_id ON sakila.rental (inventory_id ASC);
CREATE INDEX idx_fk_customer_id ON sakila.rental (customer_id ASC);
CREATE INDEX idx_fk_staff_id ON sakila.rental (staff_id ASC);


-- -----------------------------------------------------
-- Table `sakila`.`payment`
-- -----------------------------------------------------
CREATE TABLE sakila.payment (
  payment_id NUMBER(5) CHECK (payment_id > 0) NOT NULL,
  customer_id NUMBER(5) CHECK (customer_id > 0) NOT NULL,
  staff_id NUMBER(3) CHECK (staff_id > 0) NOT NULL,
  rental_id NUMBER(10) DEFAULT NULL NULL,
  amount NUMBER(5,2) NOT NULL,
  payment_date TIMESTAMP(0) NOT NULL,
  last_update TIMESTAMP(0) DEFAULT SYSTIMESTAMP NULL,
  PRIMARY KEY (payment_id)
 ,
  CONSTRAINT fk_payment_rental
    FOREIGN KEY (rental_id)
    REFERENCES sakila.rental (rental_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT fk_payment_customer
    FOREIGN KEY (customer_id)
    REFERENCES sakila.customer (customer_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT fk_payment_staff
    FOREIGN KEY (staff_id)
    REFERENCES sakila.staff (staff_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE sakila.payment_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER sakila.payment_seq_tr
 BEFORE INSERT ON sakila.payment FOR EACH ROW
 WHEN (NEW.payment_id IS NULL)
BEGIN
 SELECT sakila.payment_seq.NEXTVAL INTO :NEW.payment_id FROM DUAL;
END;
/

CREATE INDEX idx_fk_staff_id ON sakila.payment (staff_id ASC);
CREATE INDEX idx_fk_customer_id ON sakila.payment (customer_id ASC);
CREATE INDEX fk_payment_rental_idx ON sakila.payment (rental_id ASC);

ALTER SESSION SET CURRENT_SCHEMA = sakila ;

-- -----------------------------------------------------
-- Placeholder table for view `sakila`.`customer_list`
-- -----------------------------------------------------
CREATE TABLE sakila.customer_list (ID NUMBER(10), name NUMBER(10), address NUMBER(10), zip code NUMBER(10), phone NUMBER(10), city NUMBER(10), country NUMBER(10), notes NUMBER(10), SID NUMBER(10));

-- -----------------------------------------------------
-- Placeholder table for view `sakila`.`film_list`
-- -----------------------------------------------------
CREATE TABLE sakila.film_list (FID NUMBER(10), title NUMBER(10), description NUMBER(10), category NUMBER(10), price NUMBER(10), length NUMBER(10), rating NUMBER(10), actors NUMBER(10));

-- -----------------------------------------------------
-- Placeholder table for view `sakila`.`nicer_but_slower_film_list`
-- -----------------------------------------------------
CREATE TABLE sakila.nicer_but_slower_film_list (FID NUMBER(10), title NUMBER(10), description NUMBER(10), category NUMBER(10), price NUMBER(10), length NUMBER(10), rating NUMBER(10), actors NUMBER(10));

-- -----------------------------------------------------
-- Placeholder table for view `sakila`.`staff_list`
-- -----------------------------------------------------
CREATE TABLE sakila.staff_list (ID NUMBER(10), name NUMBER(10), address NUMBER(10), zip code NUMBER(10), phone NUMBER(10), city NUMBER(10), country NUMBER(10), SID NUMBER(10));

-- -----------------------------------------------------
-- Placeholder table for view `sakila`.`sales_by_store`
-- -----------------------------------------------------
CREATE TABLE sakila.sales_by_store (store NUMBER(10), manager NUMBER(10), total_sales NUMBER(10));

-- -----------------------------------------------------
-- Placeholder table for view `sakila`.`sales_by_film_category`
-- -----------------------------------------------------
CREATE TABLE sakila.sales_by_film_category (category NUMBER(10), total_sales NUMBER(10));

-- -----------------------------------------------------
-- Placeholder table for view `sakila`.`actor_info`
-- -----------------------------------------------------
CREATE TABLE sakila.actor_info (actor_id NUMBER(10), first_name NUMBER(10), last_name NUMBER(10), film_info NUMBER(10));

-- -----------------------------------------------------
-- procedure rewards_report
-- -----------------------------------------------------

ALTER SESSION SET CURRENT_SCHEMA = sakila$$


CREATE OR REPLACE PROCEDURE sakila.rewards_report (
    min_monthly_purchases IN NUMBER 
    , min_dollar_amount_purchased IN NUMBER 
    , count_rewardees OUT NUMBER, cur OUT SYS_REFCURSOR, cur2 OUT SYS_REFCURSOR, cur3 OUT SYS_REFCURSOR, cur4 OUT SYS_REFCURSOR
)
AUTHID DEFINER
 IS
    
    last_month_start DATE;
    last_month_end DATE;
 BEGIN

    /* Some sanity checks... */
    IF min_monthly_purchases = 0 THEN
        OPEN cur FOR SELECT 'Minimum monthly purchases parameter must be > 0' FROM dual;
        RETURN;
    END IF;
    IF min_dollar_amount_purchased = 0.00 THEN
        OPEN cur2 FOR SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00' FROM dual;
        RETURN;
    END IF;

    /* Determine start and end time periods */
    last_month_start := TRUNC(SYSDATE) - INTERVAL '1' MONTH;
    last_month_start := STR_TO_DATE(EXTRACT(YEAR FROM last_month_start) ||'-' ||EXTRACT(MONTH FROM last_month_start) ||'-01','%Y-%m-%d');
    last_month_end := LAST_DAY(last_month_start);

    /* 
        Create a temporary storage area for 
        Customer IDs.  
    */
    CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);

    /* 
        Find all customers meeting the 
        monthly purchase requirements
    */
    INSERT INTO tmpCustomer (customer_id)
    SELECT p.customer_id 
    FROM payment AS p
    WHERE TO_DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
    GROUP BY customer_id
    HAVING SUM(p.amount) > min_dollar_amount_purchased
    AND COUNT(customer_id) > min_monthly_purchases;

    /* Populate OUT parameter with count of found customers */
    OPEN cur3 FOR SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;

    /* 
        Output ALL customer information of matching rewardees.
        Customize output as needed.
    */
    OPEN cur4 FOR SELECT c.* 
    FROM tmpCustomer AS t   
    INNER JOIN customer AS c ON t.customer_id = c.customer_id;

    /* Clean up */
    DROP TABLE tmpCustomer;
END$$


-- -----------------------------------------------------
-- function get_customer_balance
-- -----------------------------------------------------

ALTER SESSION SET CURRENT_SCHEMA = sakila$$
/


CREATE OR REPLACE FUNCTION sakila.get_customer_balance(p_customer_id NUMBER, p_effective_date TIMESTAMP(0)) RETURN NUMBER
    DETERMINISTIC
IS

       --OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID AND A DATE
       --THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS:
       --   1) RENTAL FEES FOR ALL PREVIOUS RENTALS
       --   2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE
       --   3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST
       --   4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED

  v_rentfees NUMBER(5,2); --FEES PAID TO RENT THE VIDEOS INITIALLY
  v_overfees NUMBER(10);      --LATE FEES FOR PRIOR RENTALS
  v_payments NUMBER(5,2);
BEGIN --SUM OF PAYMENTS MADE PREVIOUSLY

  SELECT NVL(SUM(film.rental_rate),0) INTO v_rentfees
    FROM film, inventory, rental
    WHERE film.film_id = inventory.film_id
      AND inventory.inventory_id = rental.inventory_id
      AND rental.rental_date <= p_effective_date
      AND rental.customer_id = p_customer_id;

  SELECT NVL(SUM(CASE WHEN (TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration THEN 
        ((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration) ELSE 0 END),0) INTO v_overfees
    FROM rental, inventory, film
    WHERE film.film_id = inventory.film_id
      AND inventory.inventory_id = rental.inventory_id
      AND rental.rental_date <= p_effective_date
      AND rental.customer_id = p_customer_id;


  SELECT NVL(SUM(payment.amount),0) INTO v_payments
    FROM payment

    WHERE payment.payment_date <= p_effective_date
    AND payment.customer_id = p_customer_id;

  RETURN v_rentfees + v_overfees - v_payments;
END$$


-- -----------------------------------------------------
-- procedure film_in_stock
-- -----------------------------------------------------

ALTER SESSION SET CURRENT_SCHEMA = sakila$$


CREATE OR REPLACE PROCEDURE sakila.film_in_stock( p_film_id IN NUMBER, p_store_id IN NUMBER, p_film_count OUT NUMBER, cur OUT SYS_REFCURSOR)
IS
BEGIN
     OPEN cur FOR SELECT inventory_id
     FROM inventory
     WHERE film_id = p_film_id
     AND store_id = p_store_id
     AND inventory_in_stock(inventory_id);

     SELECT FOUND_ROWS() INTO p_film_count FROM dual;
END$$


-- -----------------------------------------------------
-- procedure film_not_in_stock
-- -----------------------------------------------------

ALTER SESSION SET CURRENT_SCHEMA = sakila$$
/


CREATE OR REPLACE PROCEDURE sakila.film_not_in_stock( p_film_id IN NUMBER, p_store_id IN NUMBER, p_film_count OUT NUMBER, cur OUT SYS_REFCURSOR)
IS
BEGIN
     OPEN cur FOR SELECT inventory_id
     FROM inventory
     WHERE film_id = p_film_id
     AND store_id = p_store_id
     AND NOT inventory_in_stock(inventory_id);

     SELECT FOUND_ROWS() INTO p_film_count FROM dual;
END$$


-- -----------------------------------------------------
-- function inventory_held_by_customer
-- -----------------------------------------------------

ALTER SESSION SET CURRENT_SCHEMA = sakila$$
/


CREATE OR REPLACE FUNCTION sakila.inventory_held_by_customer(p_inventory_id NUMBER) RETURN NUMBER
IS
  v_customer_id NUMBER(10);
BEGIN

  SELECT customer_id INTO v_customer_id
  FROM rental
  WHERE return_date IS NULL
  AND inventory_id = p_inventory_id;

  RETURN v_customer_id;
END$$


-- -----------------------------------------------------
-- function inventory_in_stock
-- -----------------------------------------------------

ALTER SESSION SET CURRENT_SCHEMA = sakila$$


CREATE OR REPLACE FUNCTION sakila.inventory_in_stock(p_inventory_id NUMBER) RETURN CHAR(1)
IS
    v_rentals NUMBER(10);
    v_out     NUMBER(10);
BEGIN

    --AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE
    --FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED

    SELECT COUNT(*) INTO v_rentals
    FROM rental
    WHERE inventory_id = p_inventory_id;

    IF v_rentals = 0 THEN
      RETURN TRUE;
    END IF;

    SELECT COUNT(rental_id) INTO v_out
    FROM inventory LEFT JOIN rental USING(inventory_id)
    WHERE inventory.inventory_id = p_inventory_id
    AND rental.return_date IS NULL;

    IF v_out > 0 THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
END$$


-- -----------------------------------------------------
-- View `sakila`.`customer_list`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE sakila.customer_list';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
ALTER SESSION SET CURRENT_SCHEMA = sakila;
--
-- View structure for view `customer_list`
--

CREATE  OR REPLACE VIEW customer_list 
AS 
SELECT cu.customer_id AS ID, cu.first_name || _utf8' ' || cu.last_name AS name, a.address AS address, a.postal_code AS `zip code`,
	a.phone AS phone, city.city AS city, country.country AS country, CASE WHEN cu.active THEN  _utf8'active' ELSE _utf8'' END AS notes, cu.store_id AS SID 
FROM customer AS cu JOIN address AS a ON cu.address_id = a.address_id JOIN city ON a.city_id = city.city_id 
	JOIN country ON city.country_id = country.country_id;

-- -----------------------------------------------------
-- View `sakila`.`film_list`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE sakila.film_list';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
ALTER SESSION SET CURRENT_SCHEMA = sakila;
--
-- View structure for view `film_list`
--

CREATE  OR REPLACE VIEW film_list 
AS 
SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price,
	film.length AS length, film.rating AS rating, GROUP_CONCAT(actor.first_name || _utf8' ' || actor.last_name SEPARATOR FROM dual ', ') AS actors 
FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id
        JOIN film_actor ON film.film_id = film_actor.film_id 
	JOIN actor ON film_actor.actor_id = actor.actor_id 
GROUP BY film.film_id, category.name;

-- -----------------------------------------------------
-- View `sakila`.`nicer_but_slower_film_list`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE sakila.nicer_but_slower_film_list';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
ALTER SESSION SET CURRENT_SCHEMA = sakila;
--
-- View structure for view `nicer_but_slower_film_list`
--

CREATE  OR REPLACE VIEW nicer_but_slower_film_list 
AS 
SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price, 
	film.length AS length, film.rating AS rating, GROUP_CONCAT(CONCAT(UPPER(SUBSTR(actor.first_name,1,1)) ||
	LOWER(SUBSTR(actor.first_name,2,LENGTH(actor.first_name))) ||_utf8' ' ||CONCAT(UPPER(SUBSTR(actor.last_name,1,1)),
	LOWER(SUBSTR(actor.last_name,2,LENGTH(actor.last_name))))) SEPARATOR FROM dual ', ') AS actors 
FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id
        JOIN film_actor ON film.film_id = film_actor.film_id
	JOIN actor ON film_actor.actor_id = actor.actor_id 
GROUP BY film.film_id, category.name;

-- -----------------------------------------------------
-- View `sakila`.`staff_list`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE sakila.staff_list';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
ALTER SESSION SET CURRENT_SCHEMA = sakila;
--
-- View structure for view `staff_list`
--

CREATE  OR REPLACE VIEW staff_list 
AS 
SELECT s.staff_id AS ID, s.first_name || _utf8' ' || s.last_name AS name, a.address AS address, a.postal_code AS `zip code`, a.phone AS phone,
	city.city AS city, country.country AS country, s.store_id AS SID 
FROM staff AS s JOIN address AS a ON s.address_id = a.address_id JOIN city ON a.city_id = city.city_id 
	JOIN country ON city.country_id = country.country_id;

-- -----------------------------------------------------
-- View `sakila`.`sales_by_store`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE sakila.sales_by_store';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
ALTER SESSION SET CURRENT_SCHEMA = sakila;
--
-- View structure for view `sales_by_store`
--

CREATE  OR REPLACE VIEW sales_by_store
AS 
SELECT 
c.city || _utf8',' || cy.country AS store
, m.first_name || _utf8' ' || m.last_name AS manager
, SUM(p.amount) AS total_sales
FROM payment AS p
INNER JOIN rental AS r ON p.rental_id = r.rental_id
INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
INNER JOIN store AS s ON i.store_id = s.store_id
INNER JOIN address AS a ON s.address_id = a.address_id
INNER JOIN city AS c ON a.city_id = c.city_id
INNER JOIN country AS cy ON c.country_id = cy.country_id
INNER JOIN staff AS m ON s.manager_staff_id = m.staff_id
GROUP BY s.store_id
ORDER BY cy.country, c.city;

-- -----------------------------------------------------
-- View `sakila`.`sales_by_film_category`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE sakila.sales_by_film_category';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
ALTER SESSION SET CURRENT_SCHEMA = sakila;
--
-- View structure for view `sales_by_film_category`
--
-- Note that total sales will add up to >100% because
-- some titles belong to more than 1 category
--

CREATE  OR REPLACE VIEW sales_by_film_category
AS 
SELECT 
c.name AS category
, SUM(p.amount) AS total_sales
FROM payment AS p
INNER JOIN rental AS r ON p.rental_id = r.rental_id
INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
INNER JOIN film AS f ON i.film_id = f.film_id
INNER JOIN film_category AS fc ON f.film_id = fc.film_id
INNER JOIN category AS c ON fc.category_id = c.category_id
GROUP BY c.name
ORDER BY total_sales DESC;

-- -----------------------------------------------------
-- View `sakila`.`actor_info`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE sakila.actor_info';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
ALTER SESSION SET CURRENT_SCHEMA = sakila;
--
-- View structure for view `actor_info`
--

CREATE  OR REPLACE DEFINER:=USER SQL SECURITY INVOKER VIEW actor_info 
AS
SELECT      
a.actor_id,
a.first_name,
a.last_name,
GROUP_CONCAT(DISTINCT ||(c.name, ': ',
		(SELECT GROUP_CONCAT(f.title FROM dual ORDER BY f.title SEPARATOR FROM dual ', ')
                    FROM sakila.film f
                    INNER JOIN sakila.film_category fc
                      ON f.film_id = fc.film_id
                    INNER JOIN sakila.film_actor fa
                      ON f.film_id = fa.film_id
                    WHERE fc.category_id = c.category_id
                    AND fa.actor_id = a.actor_id
                 )
             )
             ORDER BY c.name SEPARATOR '; ')
AS film_info
FROM sakila.actor a
LEFT JOIN sakila.film_actor fa
  ON a.actor_id = fa.actor_id
LEFT JOIN sakila.film_category fc
  ON fa.film_id = fc.film_id
LEFT JOIN sakila.category c
  ON fc.category_id = c.category_id
GROUP BY a.actor_id, a.first_name, a.last_name;

/* SET SQL_MODE=@OLD_SQL_MODE; */
/* SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; */
/* SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; */
ALTER SESSION SET CURRENT_SCHEMA = sakila;

ALTER SESSION SET CURRENT_SCHEMA = sakila$$

CREATE OR REPLACE TRIGGER ins_film AFTER INSERT ON film FOR EACH ROW BEGIN
    INSERT INTO film_text (film_id, title, description)
        VALUES (new.film_id, new.title, new.description);
  END$$

ALTER SESSION SET CURRENT_SCHEMA = sakila$$

CREATE OR REPLACE TRIGGER upd_film AFTER UPDATE ON film FOR EACH ROW BEGIN
    IF (old.title != new.title) or (old.description != new.description)
    THEN
        UPDATE film_text
            SET title=new.title,
                description=new.description,
                film_id=new.film_id
        WHERE film_id=old.film_id;
    END IF;
  END$$

ALTER SESSION SET CURRENT_SCHEMA = sakila$$

CREATE OR REPLACE TRIGGER del_film AFTER DELETE ON film FOR EACH ROW BEGIN
    DELETE FROM film_text WHERE film_id = old.film_id;
  END$$