DBoracle.txt

CREATE TABLE 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) 
 ) 

 CREATE SEQUENCE actor_seq START WITH 1 INCREMENT BY 1

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

CREATE INDEX idx_actor_last_name ON actor (last_name ASC)

CREATE TABLE 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)) 

CREATE SEQUENCE country_seq START WITH 1 INCREMENT BY 1

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


CREATE TABLE 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 country (country_id) 
    ON DELETE RESTRICT 
    ON UPDATE CASCADE) 

CREATE SEQUENCE city_seq START WITH 1 INCREMENT BY 1

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

CREATE INDEX idx_fk_country_id ON city (country_id ASC)

CREATE TABLE 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 country (country_id))


CREATE SEQUENCE city_seq START WITH 1 INCREMENT BY 1



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


CREATE INDEX idx_fk_country_id ON city (country_id ASC)

CREATE TABLE 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 city (city_id)) 


CREATE SEQUENCE address_seq START WITH 1 INCREMENT BY 1


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


CREATE INDEX idx_fk_city_id ON address (city_id ASC)

CREATE TABLE 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)) 

CREATE SEQUENCE category_seq START WITH 1 INCREMENT BY 1

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

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


CREATE TABLE 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 staff (staff_id) 
 , 
  CONSTRAINT fk_store_address 
    FOREIGN KEY (address_id) 
    REFERENCES address (address_id) 
  ) 

CREATE SEQUENCE store_seq START WITH 1 INCREMENT BY 1


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

CREATE INDEX idx_fk_address_id ON store (address_id ASC)




CREATE TABLE store ( 
  store_id NUMBER(3) CHECK (store_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 fk_store_address 
    FOREIGN KEY (address_id) 
    REFERENCES address (address_id) 
  ) 

CREATE SEQUENCE store_seq START WITH 1 INCREMENT BY 1

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

CREATE INDEX idx_fk_address_id ON store (address_id ASC)


CREATE TABLE 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, 
  store_id NUMBER(3) CHECK (store_id > 0) NOT NULL, 
  active NUMBER(3) NOT NULL, 
  username VARCHAR2(16) NOT NULL, 
  password VARCHAR2(40) DEFAULT NULL, 
  last_update TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL, 
  PRIMARY KEY (staff_id) 
 , 
  CONSTRAINT fk_staff_store 
    FOREIGN KEY (store_id) 
    REFERENCES store (store_id), 
  CONSTRAINT fk_staff_address 
    FOREIGN KEY (address_id) 
    REFERENCES address (address_id) 
    ) 

CREATE SEQUENCE staff_seq START WITH 1 INCREMENT BY 1

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

CREATE INDEX idx_fk_store_id ON staff (store_id ASC)


CREATE INDEX idx_fk_address_id ON staff (address_id ASC)

CREATE TABLE 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, 
  address_id NUMBER(5) CHECK (address_id > 0) NOT NULL, 
  active NUMBER(3) 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 address (address_id), 
  CONSTRAINT fk_customer_store 
    FOREIGN KEY (store_id) 
    REFERENCES store (store_id)) 



CREATE SEQUENCE customer_seq START WITH 1 INCREMENT BY 1

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

CREATE INDEX idx_fk_store_id ON customer (store_id ASC)

CREATE INDEX idx_fk_address_id ON customer (address_id ASC)

CREATE INDEX idx_last_name ON customer (last_name ASC)

CREATE SEQUENCE customer_seq START WITH 1 INCREMENT BY 1

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


CREATE TABLE film ( 
  film_id NUMBER(5) CHECK (film_id > 0) NOT NULL, 
  title VARCHAR2(255) NOT NULL, 
  description CLOB, 
  release_year NUMBER(4), 
  language_id NUMBER(3) CHECK (language_id > 0) NOT NULL, 
  original_language_id NUMBER(3) CHECK (original_language_id > 0), 
  rental_duration NUMBER(3) DEFAULT 3 CHECK (rental_duration > 0) NOT NULL, 
  rental_rate NUMBER(4,2) NOT NULL, 
  length_l NUMBER(5) CHECK (length_l > 0), 
  replacement_cost NUMBER(5,2) NOT NULL, 
  rating VARCHAR2(10) DEFAULT 'G', 
  CONSTRAINT const_rating CHECK( rating IN ('G','PG','PG-13','R','NC-17')),  
  special_features VARCHAR2(30), 
  CONSTRAINT const_special CHECK( special_features IN ('Trailers','Commentaries','Deleted Scenes','Behind the Scenes')), 
  last_update TIMESTAMP(0) DEFAULT SYSTIMESTAMP, 
  PRIMARY KEY (film_id), 
  CONSTRAINT fk_film_language 
    FOREIGN KEY (language_id) 
    REFERENCES language (language_id), 
  CONSTRAINT fk_film_language_original 
    FOREIGN KEY (original_language_id) 
    REFERENCES language (language_id)) 


CREATE SEQUENCE film_seq START WITH 1 INCREMENT BY 1

CREATE OR REPLACE TRIGGER film_seq_tr 
 BEFORE INSERT ON film FOR EACH ROW 
 WHEN (NEW.film_id IS NULL) 
BEGIN 
 SELECT film_seq.NEXTVAL INTO :NEW.film_id FROM DUAL; 
END; 



CREATE INDEX idx_title ON film (title ASC)

CREATE INDEX idx_fk_language_id ON film (language_id ASC)

CREATE INDEX idx_fk_original_language_id ON film (original_language_id ASC)

CREATE TABLE 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, 
  PRIMARY KEY (actor_id, film_id) 
 , 
  CONSTRAINT fk_film_actor_actor 
    FOREIGN KEY (actor_id) 
    REFERENCES actor (actor_id), 
  CONSTRAINT fk_film_actor_film 
    FOREIGN KEY (film_id) 
    REFERENCES film (film_id)) 

CREATE INDEX idx_fk_film_id ON film_actor (film_id ASC)

CREATE INDEX fk_film_actor_actor_idx ON film_actor (actor_id ASC)

CREATE TABLE staff_store( 
  store_id NUMBER(3) CHECK (store_id > 0) NOT NULL, 
  staff_id NUMBER(3) CHECK (staff_id > 0) NOT NULL, 
  PRIMARY KEY (store_id, staff_id), 
  CONSTRAINT fk_staff_store_store 
    FOREIGN KEY (store_id) 
    REFERENCES store (store_id), 
  CONSTRAINT fk_staff_stor_staff 
    FOREIGN KEY (staff_id) 
    REFERENCES staff (staff_id)) 

CREATE TABLE 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 film (film_id), 
  CONSTRAINT fk_film_category_category 
    FOREIGN KEY (category_id) 
    REFERENCES category (category_id)) 

CREATE INDEX fk_film_category_category_idx ON film_category (category_id ASC)

CREATE INDEX fk_film_category_film_idx ON film_category (film_id ASC)

CREATE TABLE film_text ( 
  film_id NUMBER(5) CHECK (film_id > 0) NOT NULL, 
  title VARCHAR2(255) NOT NULL, 
  description CLOB NULL, 
  PRIMARY KEY (film_id))

CREATE TABLE 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 store (store_id),
  CONSTRAINT fk_inventory_film
    FOREIGN KEY (film_id)
    REFERENCES film (film_id))
;

CREATE SEQUENCE inventory_seq START WITH 1 INCREMENT BY 1;

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

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


CREATE TABLE 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 staff (staff_id),
  CONSTRAINT fk_rental_inventory
    FOREIGN KEY (inventory_id)
    REFERENCES inventory (inventory_id),
  CONSTRAINT fk_rental_customer
    FOREIGN KEY (customer_id)
    REFERENCES customer (customer_id))
;

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

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

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


CREATE TABLE 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 rental (rental_id),
  CONSTRAINT fk_payment_customer
    FOREIGN KEY (customer_id)
    REFERENCES customer (customer_id),
  CONSTRAINT fk_payment_staff
    FOREIGN KEY (staff_id)
    REFERENCES staff (staff_id))
;

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

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

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


CREATE TABLE 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 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`.`nicer_but_slower_film_list`
-- -----------------------------------------------------
CREATE TABLE 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 staff_list (IDS NUMBER(10), names NUMBER(10), address NUMBER(10), zip_code NUMBER(10), phone NUMBER(10), city NUMBER(10), country NUMBER(10), SIDs NUMBER(10));

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

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

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





























































#######################################################################################
















######################################################################################


































































#########################################################################################


CREATE OR REPLACE PROCEDURE sakila.rewards_report (
    min_monthly_purchases IN IS 
BEGIN 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) - NUMTODSINTERVAL('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 CURRENT_SCHEMA := sakila$$
/


CREATE OR REPLACE CREATE OR REPLACEFUNCTION sakila.get_customer_balance(p_customer_id INT, p_effective_date TIMESTAMP(0)) RETURN INT
    DETERMINISTIC
IS
 BEGIN

       --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
  end; ROWNUM5,2); --FEES PAID TO RENT THE VIDEOS INITIALLY
  v_overfees ROWNUM10);      --LATE FEES FOR PRIOR RENTALS
  v_payments ROWNUM5,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 CURRENT_SCHEMA := sakila$$


CREATE OR REPLACE PROCEDURE sakila.film_in_stock( p_film_id IN IS 
BEGIN 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 CURRENT_SCHEMA := sakila$$
/


CREATE OR CREATE OR REPLACE PROCEDURE sakila.film_not_in_stock( p_film_id IN IS 
 BEGIN 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 CURRENT_SCHEMA := sakila$$
/


CREATE OR REPLACE CREATE OR REPLACEFUNCTION sakila.inventory_held_by_customer(p_inventory_id INT) RETURN INT
IS
 BEGIN
  v_customer_id
  end; ROWNUM10);
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 CURRENT_SCHEMA := sakila$$


CREATE OR REPLACE FUNCTION sakila.inventory_in_stock(p_inventory_id INT) RETURN CHAR
IS
BEGIN
    v_rentals
    end; ROWNUM10);
    v_out     ROWNUM10);
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 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 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 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 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 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 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 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 CURRENT_SCHEMA := sakila;

ALTER SESSION 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 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 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$$