Project 2

## 1st Answer
create schema alumni;

## 3rd Answer
desc college_a_hs;
desc college_a_se;
desc college_a_sj;
desc college_b_hs;
desc college_b_se;
desc college_b_sj;

## 6TH Answer
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;

## 7TH ANSWER
create or replace view College_A_SE_V as select * from College_A_SE where rollno is not null and
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;

## 8TH ANSWER
create or replace view College_A_SJ_V as select * from College_A_SJ where rollno is not null and
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;

##9TH ANSWER
create or replace view College_B_HS_V as select * from College_B_HS where rollno is not null and
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_B_HS_V;

## 10 ANSWER
create or replace view College_B_SE_V as select * from College_B_SE where rollno is not null and
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_B_SE_V;

## 11ANSWER
create or replace view College_B_SJ_V as select * from College_B_SJ where rollno is not null and
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_B_SJ_V;

## 12ANSWER
delimiter //
create procedure college_info(in lowercase varchar(1600))
begin select lower(name) Name,lower(fathername) FatherName,lower(mothername) MotherName from college_a_hs_v;
select lower(name) Name,lower(fathername) FatherName,lower(mothername) MotherName from college_a_se_v;
select lower(name) Name,lower(fathername) FatherName,lower(mothername) MotherName from college_a_sj_v;
select lower(name) Name,lower(fathername) FatherName,lower(mothername) MotherName from college_b_hs_v;
select lower(name) Name,lower(fathername) FatherName,lower(mothername) MotherName from college_b_se_v;
select lower(name) Name,lower(fathername) FatherName,lower(mothername) MotherName from college_b_sj_v;
end //
delimiter ;

call college_info(@lowercase);

## 14
DELIMITER //
create procedure get_name_collegeA(inout lastname varchar(16000))
begin
declare finished int default 0;
declare lastnamelist varchar(16380) default "";
declare studentname cursor for select name from college_a_hs union select name from college_a_se union select name from college_a_sj limit 1000 ;
declare continue handler for not found set finished =1;
open studentname ;
loopname : loop
fetch studentname into lastnamelist;
if finished =1 then leave loopname;
end if ;
set lastname = concat(lastname,",",lastnamelist);
end loop loopname;
close studentname;
END //
DELIMITER ;
set @a =" ";
call get_name_collegeA(@a);
select @a as Names_of_collegeA;

## 15
DELIMITER //
create procedure get_name_collegeB(inout lastname varchar(16000))
begin
declare finished int default 0;
declare lastnamelist varchar(16380) default "";
declare studentname cursor for select name from college_b_hs union select name from college_b_se union select name from college_b_sj limit 1000 ;
declare continue handler for not found set finished =1;
open studentname ;
loopname : loop
fetch studentname into lastnamelist;
if finished =1 then leave loopname;
end if ;
set lastname = concat(lastname,",",lastnamelist);
end loop loopname;
close studentname;
END //
DELIMITER ;
set @b =" ";
call get_name_collegeB(@b);
select @b as Names_of_collegeB;

## 16
select 'Higher studies' Career_choice ,(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' , (select count(*) from college_a_se)/ ((select count(*) from college_a_se)+(select count(*) from college_a_hs)+
(select count(*) from college_a_sj))* 100 , (select count(*) from college_b_se)/((select count(*) from college_b_se)+
(select count(*) from college_b_hs)+(select count(*) from college_b_sj))*100 union
select 'Service Job', (select count(*) from college_a_sj)/((select count(*) from college_a_sj)+(select count(*) from college_a_se)+
(select count(*) from college_a_hs)) * 100 ,
(select count(*) from college_b_sj)/((select count(*) from college_b_sj)+(select count(*) from college_b_se)+(select count(*) from college_b_hs))*100 ;