Project 2

01

.

12.

CREATE DEFINER=`root`@`localhost` PROCEDURE `LowercaseView`()
BEGIN
    
    DECLARE finished INT DEFAULT 0;
    DECLARE student_name VARCHAR(255);
    DECLARE student_FatherName VARCHAR(255);
    DECLARE student_MotherName VARCHAR(255);

    -- CODE TO ENTER - BEGIN
    DECLARE lower_name_cursor CURSOR FOR

    SELECT lower(Name),lower(FatherName),lower(MotherName) FROM college_a_hs_v ;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
    -- CODE TO ENTER - END

    DROP TEMPORARY TABLE IF EXISTS student_details;
    CREATE TEMPORARY TABLE student_details (Student_Name_column VARCHAR(255),Student_FatherName_column VARCHAR(255), Student_MotherName_column VARCHAR(255));

    OPEN lower_name_cursor;

    loop_lower_name: LOOP
        FETCH lower_name_cursor INTO student_name, student_FatherName, student_MotherName;
        IF finished = 1 THEN LEAVE loop_lower_name;
        END IF;
        
        INSERT INTO  student_details (Student_Name_column, Student_FatherName_column, Student_MotherName_column) VALUES (student_name, student_FatherName, student_MotherName);
    END LOOP loop_lower_name;
    
    CLOSE lower_name_cursor;
    
    SELECT Student_Name_column, Student_FatherName_column, Student_MotherName_column FROM student_details;

    DROP TEMPORARY TABLE IF EXISTS student_details;

END

14.

CREATE DEFINER=`root`@`localhost` PROCEDURE `college_a`()
BEGIN

    DECLARE student_name VARCHAR(255);
    DECLARE finished INT DEFAULT 0;

    -- CODE TO ENTER - BEGIN
    DECLARE student_name_cursor CURSOR FOR

    SELECT Name FROM college_a ;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
    -- CODE TO ENTER - END

 CREATE TEMPORARY TABLE College_Students (college_students_column VARCHAR(255));

    OPEN student_name_cursor;

    loop_student_name: LOOP
        FETCH student_name_cursor INTO student_name;
        IF finished = 1 THEN LEAVE loop_student_name;
        END IF;
        
        INSERT INTO College_Students (college_students_column) VALUES (student_name);
    END LOOP loop_student_name;
    
    CLOSE student_name_cursor;
    
    -- Select the student names from the temporary table as output
    SELECT college_students_column AS College_A_Students FROM College_Students;

    -- Drop the temporary table
    DROP TEMPORARY TABLE IF EXISTS College_Students;

END

15.

CREATE DEFINER=`root`@`localhost` PROCEDURE `college_b`()
BEGIN

    DECLARE student_name VARCHAR(255);
    DECLARE finished INT DEFAULT 0;

    -- CODE TO ENTER - BEGIN
    DECLARE student_name_cursor CURSOR FOR

    SELECT Name FROM college_b ;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
    -- CODE TO ENTER - END

 CREATE TEMPORARY TABLE College_Students (college_students_column VARCHAR(255));

    OPEN student_name_cursor;

    loop_student_name: LOOP
        FETCH student_name_cursor INTO student_name;
        IF finished = 1 THEN LEAVE loop_student_name;
        END IF;
        
        INSERT INTO College_Students (college_students_column) VALUES (student_name);
    END LOOP loop_student_name;
    
    CLOSE student_name_cursor;
    
    SELECT college_students_column AS College_B_Students FROM College_Students;

    DROP TEMPORARY TABLE IF EXISTS College_Students;

END