Project 2

CREATE DATABASE ALUMNI;
use alumni;
Desc college_a_hs;
Desc college_a_se;
Desc college_a_sj;
Desc college_b_hs;
Desc college_b_se;
Desc college_b_sj;

CREATE VIEW college_A_HS_V AS (SELECT * FROM college_a_hs WHERE ROLLNO IS NOT NULL AND LASTUPDATE IS NOT NULL AND NAME IS NOT NULL AND FATHERNAME IS NOT NULL AND MOTHERNAME IS NOT NULL AND BATCH IS NOT NULL AND DEGREE IS NOT NULL AND PRESENTSTATUS IS NOT NULL AND HSDEGREE IS NOT NULL AND ENTRANCEEXAM IS NOT NULL AND INSTITUTE IS NOT NULL AND LOCATION IS NOT NULL);
SELECT * FROM COLLEGE_A_HS_V;

CREATE VIEW COLLEGE_A_SE_V AS (SELECT * FROM COLLEGE_A_SE WHERE ROLLNO IS NOT NULL AND LASTUPDATE IS NOT NULL AND NAME IS NOT NULL AND FATHERNAME IS NOT NULL AND MOTHERNAME IS NOT NULL AND BATCH IS NOT NULL AND DEGREE IS NOT NULL AND PRESENTSTATUS IS NOT NULL AND ORGANIZATION IS NOT NULL AND LOCATION IS NOT NULL);
SELECT * FROM COLLEGE_A_SE_V;

CREATE VIEW COLLEGE_A_SJ_V AS (SELECT * FROM COLLEGE_A_SJ WHERE ROLLNO IS NOT NULL AND LASTUPDATE IS NOT NULL AND NAME IS NOT NULL AND FATHERNAME IS NOT NULL AND MOTHERNAME IS NOT NULL AND BATCH IS NOT NULL AND DEGREE IS NOT NULL AND PRESENTSTATUS IS NOT NULL AND ORGANIZATION IS NOT NULL AND DESIGNATION IS NOT NULL AND LOCATION IS NOT NULL);
SELECT* FROM COLLEGE_A_SJ_V;

CREATE VIEW COLLEGE_B_HS_V AS (SELECT * FROM COLLEGE_B_HS WHERE ROLLNO IS NOT NULL AND LASTUPDATE IS NOT NULL AND NAME IS NOT NULL AND FATHERNAME IS NOT NULL AND MOTHERNAME IS NOT NULL AND BRANCH IS NOT NULL AND BATCH IS NOT NULL AND DEGREE IS NOT NULL AND PRESENTSTATUS IS NOT NULL AND HSDEGREE IS NOT NULL AND ENTRANCEEXAM IS NOT NULL AND INSTITUTE IS NOT NULL AND LOCATION IS NOT NULL);
SELECT * FROM COLLEGE_B_HS_V;

CREATE VIEW COLLEGE_B_SE_V AS (SELECT * FROM COLLEGE_B_SE WHERE ROLLNO IS NOT NULL AND LASTUPDATE IS NOT NULL AND NAME IS NOT NULL AND FATHERNAME IS NOT NULL AND MOTHERNAME IS NOT NULL AND BRANCH IS NOT NULL AND BATCH IS NOT NULL AND DEGREE IS NOT NULL AND PRESENTSTATUS IS NOT NULL AND ORGANIZATION IS NOT NULL AND LOCATION IS NOT NULL);
SELECT * FROM COLLEGE_B_SE_V;

CREATE VIEW COLLEGE_B_SJ_V AS (SELECT * FROM COLLEGE_B_SJ WHERE ROLLNO IS NOT NULL AND LASTUPDATE IS NOT NULL AND NAME IS NOT NULL AND FATHERNAME IS NOT NULL AND MOTHERNAME IS NOT NULL AND BRANCH IS NOT NULL AND BATCH IS NOT NULL AND DEGREE IS NOT NULL AND PRESENTSTATUS IS NOT NULL AND ORGANIZATION IS NOT NULL AND DESIGNATION IS NOT NULL AND LOCATION IS NOT NULL);
SELECT * FROM COLLEGE_B_SJ_V;

CALL lowercollege_a_hs;
CALL lowercollege_a_se;
CALL lowercollege_a_sj;
CALL lowercollege_b_hs;
CALL lowercollege_b_se;
CALL lowercollege_b_sj;

CREATE PROCEDURE get_name_collegeA ( INOUT Lname TEXT(40000) ) BEGIN DECLARE finished INT DEFAULT 0; DECLARE Lnamelist VARCHAR (16000) DEFAULT ""; DECLARE Lnamedetails CURSOR FOR SELECT name FROM college_a_hs UNION SELECT name FROM college_a_se UNION SELECT name FROM college_a_sj; DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished=1; OPEN Lnamedetails; getname1: LOOP FETCH Lnamedetails INTO Lnamelist; IF finished =1 THEN LEAVE getname1; END IF; SET Lname = CONCAT (Lnamelist,";",Lname); END LOOP getname1; CLOSE Lnamedetails; END

DELIMITER ;

SET @name1="";
CALL get_name_collegeA(@name1);
SELECT @name1 Name;

CREATE PROCEDURE get_name_collegeB ( INOUT Fname TEXT(40000) ) BEGIN DECLARE finished INT DEFAULT 0; DECLARE Fnamelist VARCHAR (16000) DEFAULT ""; DECLARE Fnamedetails CURSOR FOR SELECT name FROM college_b_hs UNION ALL SELECT name FROM college_b_se UNION ALL SELECT name FROM college_b_sj; DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished=1; OPEN Fnamedetails; getname2: LOOP FETCH Fnamedetails INTO Fnamelist; IF finished =1 THEN LEAVE getname2; END IF; SET Fname = CONCAT (Fnamelist,";",Fname); END LOOP getname2; CLOSE Fnamedetails; END

DELIMITER ;

SET @name2="";
CALL get_name_collegeB(@name2);
SELECT @name2 Name;

SELECT "HIGHERSTUDIES" PRESENTSTATUS,(SELECT COUNT(*) FROM COLLEGE_A_HS)/
((SELECT COUNT(*) FROM COLLEGE_A_HS) + (SELECT COUNT(*) FROM COLLEGE_A_SE) + (SELECT COUNT(*) FROM COLLEGE_A_SJ))*100 COLLEGE_A_PERCENTAGE,
(SELECT COUNT(*) FROM college_b_hs)/
((SELECT COUNT(*) FROM college_b_hs) + (SELECT COUNT(*) FROM college_b_se) + (SELECT COUNT(*) FROM college_b_sj))*100
College_B_Percentage
UNION
SELECT "Self Employed" PresentStatus,(SELECT COUNT(*) FROM college_a_se)/
((SELECT COUNT(*) FROM college_a_hs) + (SELECT COUNT(*) FROM college_a_se) + (SELECT COUNT(*) FROM college_a_sj))*100
College_A_Percentage,
(SELECT COUNT(*) FROM college_b_se)/
((SELECT COUNT(*) FROM college_b_hs) + (SELECT COUNT(*) FROM college_b_se) + (SELECT COUNT(*) FROM college_b_sj))*100
College_B_Percentage
UNION
SELECT "Service Job" PresentStatus,(SELECT COUNT(*) FROM college_a_sj)/
((SELECT COUNT(*) FROM college_a_hs) + (SELECT COUNT(*) FROM college_a_se) + (SELECT COUNT(*) FROM college_a_sj))*100
College_A_Percentage,
(SELECT COUNT(*) FROM college_b_sj)/
((SELECT COUNT(*) FROM college_b_hs) + (SELECT COUNT(*) FROM college_b_se) + (SELECT COUNT(*) FROM college_b_sj))*100
College_B_Percentage;