Project 2

-- 1
create database alumni;
-- 3
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;
-- 6
create or replace 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;
-- 7
create or replace 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;
-- 8
create or replace 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;
-- 9
create or replace 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;
-- 10
create or replace 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;
-- 11
create or replace 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;
-- 12
DELIMITER $$
USE `alumni`$$
CREATE PROCEDURE `lowercase` ()
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 lowercase();
-- 13
DELIMITER $$
create procedure get_name_collegeA (inout name1 text (40000))
begin
declare na int default 0;
declare namelist varchar(16000) default "";
declare namedetail 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 na =1;
open namedetail;
getame :
loop
fetch from namedetail into namelist;
if na = 1 then
leave getame;
end if ;
set name1 = CONCAT(namelist," ; ",name1);
end loop getame;
close namedetail;
end $$
DELIMITER ;

set @Name = "";
call get_name_collegeA(@Name);
select @Name CollegeA_Name;
-- 14
DELIMITER $$
create procedure get_name_collegeB (inout name1 text (40000))
begin
declare na int default 0;
declare namelist varchar(16000) default "";
declare namedetail cursor for
select Name from college_b_hs
union select Name from college_b_se
union select Name from college_b_sj;
declare continue handler for not found set na =1;
open namedetail;
getame :
loop
fetch from namedetail into namelist;
if na = 1 then
leave getame;
end if ;
set name1 = CONCAT(namelist," ; ",name1);
end loop getame;
close namedetail;
end $$
DELIMITER ;

set @Name = "";
call get_name_collegeB(@Name);
select @Name CollegeB_Name;
-- 15
select "Higher Studies", 100*count(*) /5889
as CollegeAPercentage , (select 100*count(*) /2259 from college_b_hs ) as CollegeBPercentage
from college_a_hs
union
select "Self Employed", 100*count(*) /5889
as CollegeAPercentage , (select 100*count(*) /2259 from college_b_se ) as CollegeBPercentage
from college_a_se
union
select "Service Job", 100*count(*) /5889
as CollegeAPercentage , (select 100*count(*) /2259 from college_b_sj ) as CollegeBPercentage
from college_a_sj;