Project 2

CREATE DATABASE alumni;
use alumni;

create table COLLEGE_A_HS(
ROLLNO INT,
LAST_UPDATE date,
NAME_ VARCHAR(50),
FATHERNAME VARCHAR(50),
MOTHERNAME VARCHAR(50),
BRANCH VARCHAR(120),
BATCH VARCHAR(50),
DEGREE VARCHAR(40),
PRESENT VARCHAR(50),
ORGANIZATION VARCHAR(130),
DESIGNATION VARCHAR(50),
LOCATION VARCHAR(80));
SELECT * FROM COLLEGE_A_HS;

create table COLLEGE_A_SE(
ROLLNO INT,
LAST_UPDATE date,
NAME_ VARCHAR(50),
FATHERNAME VARCHAR(50),
MOTHERNAME VARCHAR(50),
BRANCH VARCHAR(120),
BATCH VARCHAR(50),
DEGREE VARCHAR(40),
PRESENT VARCHAR(50),
ORGANIZATION VARCHAR(130),
DESIGNATION VARCHAR(50),
LOCATION VARCHAR(80));
SELECT * FROM COLLEGE_A_SE;

create table COLLEGE_A_SJ(
ROLLNO INT,
LAST_UPDATE date,
NAME_ VARCHAR(50),
FATHERNAME VARCHAR(50),
MOTHERNAME VARCHAR(50),
BRANCH VARCHAR(120),
BATCH VARCHAR(50),
DEGREE VARCHAR(40),
PRESENT VARCHAR(50),
ORGANIZATION VARCHAR(130),
DESIGNATION VARCHAR(50),
LOCATION VARCHAR(80));
SELECT * FROM COLLEGE_A_SJ;

create table COLLEGE_B_SJ(
ROLLNO INT,
LAST_UPDATE date,
NAME_ VARCHAR(50),
FATHERNAME VARCHAR(50),
MOTHERNAME VARCHAR(50),
BRANCH VARCHAR(120),
BATCH VARCHAR(50),
DEGREE VARCHAR(40),
PRESENT VARCHAR(50),
ORGANIZATION VARCHAR(130),
DESIGNATION VARCHAR(50),
LOCATION VARCHAR(80));
SELECT * FROM COLLEGE_B_SJ;

create table COLLEGE_B_SE(
ROLLNO INT,
LAST_UPDATE date,
NAME_ VARCHAR(50),
FATHERNAME VARCHAR(50),
MOTHERNAME VARCHAR(50),
BRANCH VARCHAR(120),
BATCH VARCHAR(50),
DEGREE VARCHAR(40),
PRESENT VARCHAR(50),
ORGANIZATION VARCHAR(130),
DESIGNATION VARCHAR(50),
LOCATION VARCHAR(80));
SELECT * FROM COLLEGE_B_SE;

create table COLLEGE_B_HS(
ROLLNO INT,
LAST_UPDATE date,
NAME_ VARCHAR(50),
FATHERNAME VARCHAR(50),
MOTHERNAME VARCHAR(50),
BRANCH VARCHAR(120),
BATCH VARCHAR(50),
DEGREE VARCHAR(40),
PRESENT VARCHAR(50),
ORGANIZATION VARCHAR(130),
DESIGNATION VARCHAR(50),
LOCATION VARCHAR(80));
SELECT * FROM COLLEGE_B_HS;

CREATE PROCEDURE CREATEGET_NAME
(
INOUT COLLEGE_A VARCHAR(16000)
)
BEGIN
DECLARE FINISHED INT DEFAULT 0;
DECLARE COLLEGE_ALIST VARCHAR(400) DEFAULT "";

DECLARE COLLEGE_ADETAIL
CURSOR FOR
SELECT COLLEGE_A FROM COLLEGE_A;

DECLARE CONTINUE HANDLER
FOR NOT FOUND SET FINISHED = 1;

OPEN COLLEGE_ADETAIL;

GETCOL:
LOOP
FETCH COLLEGE_ADETAIL INTO COLLEGE_ALIST;
IF FINISHED = 1 THEN
LEAVE GETCOL;
END IF;

SET GET_NAME = CONCAT(GET_NAME," ; ", COLLEGE_ALIST);
END LOOP GETCOL;

CLOSE COLLEGEDETAIL;

END $$
DELIMITER ;

SET @GET_NAME = "";
CALL CREATEGET_NAME(@GET_NAME);
SELECT @GET_NAME;

CREATE PROCEDURE CREATEGET_NAME
(
INOUT COLLEGE_B VARCHAR(16000)
)
BEGIN
DECLARE FINISHED INT DEFAULT 0;
DECLARE COLLEGE_BLIST VARCHAR(400) DEFAULT "";

DECLARE COLLEGE_BDETAIL
CURSOR FOR
SELECT COLLEGE_B FROM COLLEGE_B;

DECLARE CONTINUE HANDLER
FOR NOT FOUND SET FINISHED = 1;

OPEN COLLEGE_BDETAIL;

GETCOL:
LOOP
FETCH COLLEGE_BDETAIL INTO COLLEGE_BLIST;
IF FINISHED = 1 THEN
LEAVE GETCOL;
END IF;

SET GET_NAME = CONCAT(GET_NAME," ; ", COLLEGE_BLIST);
END LOOP GETCOL;

CLOSE COLLEGEDE_BDETAIL;

END $$
DELIMITER ;

SET @GET_NAME = "";
CALL CREATEGET_NAME(@GET_NAME);
SELECT @GET_NAME;