Project 2

-- 1
create database alumni;
use alumni;
show databases;
show tables;
SELECT
COUNT(*)
FROM
college_b_sj;
-- 3
desc college_a_hs;
desc college_a_se;
desc college_a_sj;
desc college_b_hs;
desc college_b_se;
desc college_b_sj;
-- 4
SELECT
*
FROM
college_a_se
LIMIT 1000;
-- 6
SELECT
*
FROM
college_a_hs
WHERE
rollno IS NOT NULL
AND LastUpdate IS NOT NULL
AND alumni.college_a_hs.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;

..................................................

CREATE VIEW college_a_hs_v AS
SELECT
*
FROM
college_a_hs
WHERE
rollno IS NOT NULL
AND LastUpdate IS NOT NULL
AND alumni.college_a_hs.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 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
SELECT
*
FROM
college_a_sj;
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;

-- 9
SELECT
*
FROM
college_b_hs;
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;
-- 10
SELECT
*
FROM
college_b_se;
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;

-- 11
SELECT
*
FROM
college_b_sj;
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;
-- 12-------------------------------------------------------------------------------------------------------------------
delimiter //
create procedure lowerviewahs()
begin
select lower(name),lower(fathername),lower(mothername) from college_a_hs_V;
end //
delimiter $$
call lowerviewahs;
delimiter //
create procedure lowerviewase()
begin
select lower(name),lower(fathername),lower(mothername) from college_a_se_V;
end //
delimiter $$
call lowerviewase;
select * from college_a_sj_v;
delimiter //
create procedure viewasj()
begin
select lower(name),lower(fathername),lower(mothername) from college_a_sj_v;
end //
delimiter $$
call viewasj;
--
delimiter //
create procedure lowerviewbhs()
begin
select lower(name),lower(fathername),lower(mothername) from college_b_hs_V;
end //
delimiter $$
call lowerviewbhs;
delimiter //
create procedure lowerviewbse()
begin
select lower(name),lower(fathername),lower(mothername) from college_b_se_V;
end //
delimiter $$
call lowerviewbse;
delimiter //
create procedure lowerviewbsj()
begin
select lower(name),lower(fathername),lower(mothername) from college_b_sj_V;
end //
delimiter $$
call lowerviewbsj;

-- 14
use alumni;

drop procedure getalumniname;
delimiter //
create procedure getalumniname (inout arg1 mediumtext)
begin
declare finished int default 0;
declare alumninames text default "";
declare alumninamedetail cursor for
SELECT name FROM college_a_hs
union
select name from college_a_sj
union
select name from college_a_se;
declare continue handler for not found set finished = 1;
open alumninamedetail;
loopname: loop
fetch alumninamedetail into alumninames;
if finished = 1 then leave loopname;
end if;
set arg1 = concat(alumninames," ; ",arg1);
end loop;
close alumninamedetail;
end //
delimiter ;

set @alumninames = " ";
call getalumniname(@alumninames);
select @alumninames;

-- 15
drop procedure if exists getcollegeb ;
delimiter //
create procedure getcollegeb (inout arg1 mediumtext)
begin
declare finished int default 0;
declare alumnib varchar(16000) default "";
declare alumnicollegebdetail cursor for
SELECT name FROM college_b_hs
union
select name from college_b_sj
union
select name from college_b_se;
declare continue handler for not found set finished = 1;
open alumnicollegebdetail;
loopname: loop
fetch alumnicollegebdetail into alumnib;
if finished = 1 then leave loopname;
end if;
set arg1 = concat(alumnib," ; ",arg1);
end loop;
close alumnicollegebdetail;
end //
delimiter ;
set @alumnib = " ";
call getcollegeb(@alumnib);
select @alumnib;


-- 16
select "Higher Studies" as 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 as 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 as college_b_Percentage
union
select "self employed",(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 ,(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 as college_b_Percentage
union
select "service job",(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 ,(select count(*) from college_b_sj)/((select count(*) from college_b_se)+(select count(*) from college_b_sj)+(select count(*) from college_b_hs))*100 as college_b_Percentage;