assignment g5

a)
delimiter //
CREATE FUNCTION `m_count`(p_name char(30)) RETURNS int(11)
begin
declare m_count integer;
SELECT count(distinct movie_id) INTO m_count
FROM imdb_movie.casting c, imdb_movie.person p,
imdb_movie.movie m
WHERE c.person_id = p.id
 AND c.movie_id = m.id
 AND m.kind_id=1
 AND c.role_type_id=1
AND p.name like p_name;
return m_count;
end;//
delimiter ;

> SELECT m_count('Bacon, Kevin')

+ ---------------------------- +
| m_count('Bacon, Kevin')      |
+ ---------------------------- +
| 90                           |
+ ---------------------------- +
1 rows


b)
 delimiter //
create procedure movies(person_name char(20))
begin
SELECT distinct title
FROM imdb_movie.casting c, imdb_movie.person p,
imdb_movie.movie m
WHERE c.person_id = p.id
AND c.movie_id = m.id
AND m.kind_id=1
AND c.role_type_id=1
AND p.name like person_name;
end; //
delimiter ;

> CALL movies('Mikkelsen, Mads')

+ ---------- +
| title      |
+ ---------- +
| A Bond for Life: How James Bond Changed My Life |
| Adams æbler |
| Bleeder    |
| Blinkende lygter |
| Blomsterfangen |
| Café Hector |
| Casino Royale |
| Clash of the Titans |
| Coco Chanel & Igor Stravinsky |
| De grønne slagtere |
| Die Tür   |
| Doctor Strange |
| Dykkerdrengen |
| Efter brylluppet |
| Elsker dig for evigt |
| En kongelig affære |
| En kort en lang |
| Exit       |
| Flammen & Citronen |
| I Am Dina  |
| Jagten     |
| King Arthur |
| Michael Kohlhaas |
| Monas verden |
| Move On    |
| Muumi ja punainen pyrstötähti |
| Mænd & høns |
| Nattens engel |
| Nu         |
| Prag       |
| Pusher     |
| Pusher II  |
| Rogue One: A Star Wars Story |
| The Necessary Death of Charlie Countryman |
| The Salvation |
| The Three Musketeers |
| Tom Merritt |
| Torremolinos 73 |
| Valhalla Rising |
| Vildspor   |
| Wilbur Wants to Kill Himself |
+ ---------- +
41 rows
c)
delimiter //
CREATE DEFINER=`spiotrm`@`%` PROCEDURE `titlec`(title_name char(20))
BEGIN
SELECT title from imdb_movie.movie m
where m.title LIKE CONCAT('%',title_name,'%') order by production_year DESC limit 10 ;
END; //
delimiter ;

> CALL title('Titanic')

+ ---------- +
| title      |
+ ---------- +
| Killer Zombies from the Titanic |
| Titanic: The JonSong (Ft. Schmoyoho) |
| Titanic: The Legend Goes On |
| Titanic: The Legend Goes on - JonTron |
| Resurrecting the Titanic |
| Titanic in the Sky |
| Titanic Boy |
| Titanic    |
| Titanic 2 World Premiere |
| The Hidden Meaning in Titanic - Earthling Cinema |
+ ---------- +
10 rows

d)
delimiter //
CREATE FUNCTION `roles`(p_name char(30)) RETURNS varchar(100) CHARSET latin1
begin
declare m_count integer;
declare a varchar(24);
declare i int DEFAULT 0;
declare done int DEFAULT 0;
declare res_rows int DEFAULT 0;
declare temp varchar(100) DEFAULT '';
declare curl cursor for SELECT DISTINCT role
FROM imdb_movie.casting
 JOIN imdb_movie.person
 ON person_id = person.id
 JOIN imdb_movie.role_type
 ON role_type_id = role_type.id
WHERE name like p_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open curl;
    select FOUND_ROWS() into res_rows;
        myloop:WHILE res_rows > i DO
        fetch curl into a;
        SET i = i+1;
        IF i!=res_rows THEN
            SET temp =CONCAT(temp,a,',');
        ELSE
            SET temp =CONCAT(temp,a);
        END IF;
        IF done = 1 THEN
            LEAVE myloop;
        END IF;
    END WHILE;

close curl;
return temp;
end;//
delimiter ;

> SELECT name,roles(name)
FROM imdb_movie.person where name like 'De Niro, R%'

+ --------- + ---------------- +
| name      | roles(name)      |
+ --------- + ---------------- +
| De Niro, Raphael | actor,producer   |
| De Niro, Robert | actor,director,producer |
| De Niro, Rocco | actor            |
| de Niro, Ryan | cinematographer  |
+ --------- + ---------------- +
4 rows