Project 2

-- 1Q
create database alumini;

-- 2Q
-- imported the files into alumini

-- 3Q
use alumini;
desc college_a_hs;
desc college_a_se;
desc college_a_sj;
desc college_b_hs;
desc college_b_se;
desc college_b_sj;

-- 4Q

-- imported to python

-- 5Q
-- IMPORTED TO PYTHON

-- 6Q
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;

-- 7Q
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;

-- 8Q
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 ;

-- 9Q
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 ;

-- 10Q
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 ;


-- 11Q
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 ;


-- 12Q
DELIMITER $$
CREATE PROCEDURE project_2_procedure()
BEGIN
select lower(name) , lower(FatherName), lower(MotherName) from college_a_hs_v ;
select lower(name) , lower(FatherName), lower(MotherName) from college_a_se_v ;
select lower(name) , lower(FatherName), lower(MotherName) from college_a_sj_v ;
select lower(name) , lower(FatherName), lower(MotherName) from college_b_hs_v ;
select lower(name) , lower(FatherName), lower(MotherName) from college_b_se_v ;
select lower(name) , lower(FatherName), lower(MotherName) from college_b_sj_v ;
END $$
DELIMITER ;
call project_2_procedure();

-- 13Q
-- done using the excel

pivot chart

-- 14Q
DELIMITER $$
CREATE PROCEDURE get_name_collegeA(INOUT obj LONGTEXT)
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE LIST_OBJ VARCHAR(9000) DEFAULT "";
DECLARE nameofa CURSOR FOR
select Name from college_a_hs_v union all select Name from college_a_se_v union all select Name from college_a_sj_v;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

OPEN nameofa;
lableloop:
LOOP
FETCH nameofa INTO LIST_OBJ;
IF finished = 1 THEN
LEAVE lableloop;
END IF;
SET obj =CONCAT(LIST_OBJ,";",obj);
END LOOP
lableloop;
CLOSE nameofa;
END $$
DELIMITER ;

SET @callingobj = "";
call get_name_collegeA(@callingobj);
select @callingobj list_of_names_A;

-- 15Q
DELIMITER $$
CREATE PROCEDURE get_name_collegeB(INOUT obj1 LONGTEXT)
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE LIST_OBJ2 VARCHAR(9000) DEFAULT "";
DECLARE nameofb CURSOR FOR
select Name from college_b_hs_v union all select Name from college_b_se_v union all select Name from college_b_sj_v;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

OPEN nameofb;
lableloop1:
LOOP
FETCH nameofb INTO LIST_OBJ2;
IF finished = 1 THEN
LEAVE lableloop1;
END IF;
SET obj1 =CONCAT(LIST_OBJ2,";",obj1);
END LOOP
lableloop1;
CLOSE nameofb;
END $$
DELIMITER ;

SET @callingobj2= "";
call get_name_collegeB(@callingobj2);
select @callingobj2 list_names_of_B;

-- 16

-- count of all the alumini of college a (select 1155 + 1016 + 5633; # is 7804)
-- count of all the alumini of college a (select 199 +201 + 1859; # is 2259)

select "Higher Studies",100 *(count(PresentStatus))/7804,(select 100*(count(PresentStatus))/2259 from college_b_hs_v )from college_a_hs_v union all
select "Self Employed",100 *(count(PresentStatus))/7804,(select 100*(count(PresentStatus))/2259 from college_b_se_v )from college_a_se_v union all
select "Service/Job",100 *(count(PresentStatus))/7804,(select 100*(count(PresentStatus))/2259 from college_b_sj_v )from college_a_sj_v ;