Procedures.txt

    GET ALL MOVIES WITH RUNTIME GREATER THAN THE GIVEN VALUE
DELIMITER //
CREATE PROCEDURE MovieFromRuntime(IN rt SMALLINT)
    BEGIN
        SELECT Title
        FROM movies
        WHERE Runtime =
        ANY (
            SELECT Runtime
            FROM movies
            WHERE Runtime>rt
        );
    END //
DELIMITER ;


    COUNT NUMBER OF MALE AND FEMALE FROM PEOPLE
DELIMITER //
CREATE PROCEDURE CountGender()
    BEGIN

        DECLARE Male INT DEFAULT 0;
        DECLARE Female INT DEFAULT 0;

        SELECT COUNT(PID) INTO Male FROM people WHERE Gender = 'M';
        SELECT COUNT(PID) INTO Female FROM people WHERE Gender = 'F';

        SELECT Male, Female;
    END //
DELIMITER ;


    INSERT INTO CAST TABLE, EXCEPTION IF DUPLICATE KEY
DELIMITER //
CREATE PROCEDURE InsertCast(IN mi INT, IN pi INT, IN cn VARCHAR(255))
    BEGIN

        DECLARE CONTINUE HANDLER FOR 1062
        SELECT CONCAT('Duplicate Keys (',mi,',',pi,') Found') AS Error;

        INSERT INTO cast VALUES (mi, pi, cn);

    END //
DELIMITER ;

    INSERT INTO REVIEWS WITH VALID RATING VALUE
DELIMITER //
CREATE PROCEDURE AddReview(IN ui INT, IN mi INT, IN d date, IN s tinyint, IN c VARCHAR(1023))
    BEGIN

        IF(s > 5) THEN 
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Rating should be less than or equal to 5';
        ELSE
            INSERT INTO Reviews VALUES (ui, mi, d, s, c);
        END IF;

    END //
DELIMITER ;