CS MySQL question

/*CS5200 hwk6

Yukai Li*/

set global log_bin_trust_function_creators=TRUE;

use lotrfinal_1;

select * from lotr_character;

select * from lotr_book;

select * from lotr_first_encounter;

select * from lotr_region;

select * from lotr_species;

/*1.Write a procedure track_character(character) that accepts a character name and

returns a result set that contains a list of the other characters that the provided character has encountered.

The result set should contain the character’s name, the region name, the book name, and the name of the encountered character. (10 points)*/

DROP PROCEDURE IF EXISTS track_character;

DELIMITER $$

CREATE PROCEDURE track_character(ch varchar(45))

BEGIN

SELECT character1_name as character_name, region_name, b.title , character2_name as encountered_character from lotr_first_encounter fe

JOIN lotr_book b

on fe.book_id=b.book_id

WHERE character1_name = ch GROUP BY character_name, fe.region_name, b.title;

END $$

DELIMITER ;

call track_character('Frodo');

/*2.Write a procedure track_region(region) that accepts a region name and returns a result set that contains the region name,

the book name, the number of encounters for that region and the leader of that region. (10 points)

*/

DROP PROCEDURE IF EXISTS track_region;

DELIMITER $$

CREATE PROCEDURE track_region(IN re varchar(45))

BEGIN

SELECT fe.region_name, b.title, count(character1_name) as no_encounters, r.leader FROM lotr_first_encounter fe

JOIN lotr_book b ON fe.book_id = b.book_id

JOIN lotr_region r ON fe.region_name=r.region_name

WHERE fe.region_name = re GROUP BY fe.region_name;

END $$

DELIMITER ;

/* 3.Write a function named strongerSpecie(sp1,sp2).

It accepts 2 species and returns 1 if sp1 has a size larger than sp2, 0 if they have equal sizes, else -1 (10 points)

*/

DROP FUNCTION IF EXISTS strongerSpecie;

DELIMITER $$

CREATE FUNCTION strongerSpecie(sp1 varchar(45) , sp2 varchar(45))

RETURNS int

BEGIN

DECLARE size1 int;

DECLARE size2 int;

SELECT size into size1 from lotr_species where species_name= sp1;

SELECT size into size2 from lotr_species where species_name= sp2;

IF size1 > size2

THEN RETURN 1;

ELSEIF size1 = size2

THEN RETURN 0;

ELSE RETURN -1;

END IF;

END $$

DELIMITER ;

select strongerSpecie('elf','ent');

/*4. Write a function named region_most_encounters(character)

that accepts a character name and returns the name of the region where the character has had the most encounters. (10 points)*/

DROP FUNCTION IF EXISTS region_most_encounters;

DELIMITER $$

CREATE FUNCTION region_most_encounters(ch varchar(45))

RETURNS varchar(45)

BEGIN

DECLARE reg varchar(45);

SELECT region_name INTO reg FROM lotr_first_encounter

WHERE character1_name = ch or character2_name = ch

GROUP BY region_name ORDER BY SUM(region_name) DESC LIMIT 1;

RETURN reg;

END $$

DELIMITER ;

/*5. Write a function named home_region_encounter(character) that accepts a character name and returns

TRUE if the character has had a first encounter in his homeland. FALSE if the character has not had a first encounter in his homeland.

or NULL if the character’s homeland is not known. (10 points)*/

DROP FUNCTION IF EXISTS home_region_encounter;

DELIMITER $$

CREATE FUNCTION home_region_encounter(ch varchar(45))

RETURNS bool

BEGIN

DECLARE hl varchar(45);

DECLARE first_c varchar(45);

SELECT c.homeland, fe.region_name INTO hl, first_c FROM lotr_character c

JOIN lotr_first_encounter fe ON c.homeland = fe.region_name

WHERE c.character_name = ch;

IF ISNULL(hl)

THEN RETURN NULL;

ELSE RETURN NOT isnull(first_c);

END IF;

END $$

DELIMITER ;

/*6. Write a function named encounters_in_num_region(region_name)

that accepts a region’s name as an argument and returns the number of encounters for that region. (10 points)

*/

DROP FUNCTION IF EXISTS encounters_in_num_region;

DELIMITER $$

CREATE FUNCTION encounters_in_num_region(el varchar(45))

RETURNS int

BEGIN

DECLARE n int;

SELECT count(character1_name) INTO n FROM lotr_first_encounter WHERE region_name = el;

RETURN n;

END $$

DELIMITER ;

/*7.Write a procedure named fellowship_encounters(book) that accepts a book’s name and returns the fellowship characters

(all fields in the character table) having first encounters in that book. (10 points)

*/

DROP PROCEDURE IF EXISTS fellowship_encounters;

DELIMITER $$

CREATE PROCEDURE fellowship_encounters(bn varchar(45))

BEGIN

SELECT * FROM lotr_character c

JOIN lotr_book b ON b.book_id=c.book_number_introduction WHERE b.title = bn;

END $$

DELIMITER ;

/*8. Modify the books table to contain a field called encounters_in_book and

write a procedure called initialize_encounters_count(book) that accepts a book id and

initializes the field to the number of encounters that occur in that book for the current encounters table.

The book table modification can occur outside or inside of the procedure. (10 points)*/

ALTER Table lotr_book

ADD COLUMN encounters_in_book INT;

DROP PROCEDURE IF EXISTS initialize_encounters_count;

DELIMITER $$

CREATE PROCEDURE initialize_encounters_count(bi int)

BEGIN

SELECT count(character1_name) FROM lotr_first_encounter fe

WHERE fe.book_id = bi;

END $$

DELIMITER ;

/*

9.Write a trigger that updates the field encounters_in_book for the book records in the lotr_book table.

The field should contain the number of first encounters for that book.

Call the trigger firstencounters_after_insert. Insert the following records into the database.

Insert a first encounter in Rivendell between Legolas and Frodo for book 1 . Ensure that the sencounters_in_book field is properly updated for this data. (10 points)

*/

DROP TRIGGER IF EXISTS firstencounters_after_insert;

DELIMITER $$

CREATE TRIGGER firstencounters_after_insert

AFTER INSERT ON lotr_first_encounter

FOR EACH ROW BEGIN

DECLARE m int;

SELECT count(character1_name) INTO m FROM lotr_first_encounter fe WHERE book_id = NEW.book_id;

UPDATE lotr_book set encounters_in_book = m WHERE book_id = NEW.book_id;

END $$

DELIMITER ;

INSERT INTO lotr_first_encounter (character1_name, character2_name, book_id, region_name)

VALUES ('Legolas', 'Frodo', 1,'Rivendell');

/*10. Create and execute a prepared statement from the SQL workbench that calls

home_region_encounter with the argument ‘Aragorn’. Use a user session variable to

pass the argument to the function. (5 points)

*/

PREPARE hre FROM 'Call home_region_encounter(?)';

SET @Aragorn = 'Aragorn';

EXECUTE hre USING @Aragorn;

DEALLOCATE PREPARE hre;

/*11. Create and execute a prepared statement that calls region_most_encounters() with the argument ‘Aragorn’.

Once again use a user session variable to pass the argument to the function. (5 points)*/

PREPARE rme FROM 'SELECT region_most_encounters(?)';

SET @Aragorn = 'Aragorn';

EXECUTE rme USING @Aragorn;

DEALLOCATE PREPARE rme;