Project 2

1--
CREATE DATABASE alumni;
USE alumni;
---all files imported into schema alumni;---
3---
desc college_a_hs,
desc college_a_se,
desc college_a_sj,
desc college_a_se,
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;
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;

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;

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

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;

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_a_hs_v;
select * from college_b_se_v;
select * from college_a_sj_v;
select * from college_b_hs_v;
select * from college_b_se_v;
select * from college_b_sj_v;
12----

Delimiter $$
CREATE PROCEDURE `lowcolA1`()
BEGIN
SELECT LOWER(Name) Name,Lower(FatherName) FatherName,Lower(MotherName) MotherName
FROM college_a_hs_v;
END $$
Delimiter ;
Call lowcolA1;

Delimiter $$
CREATE PROCEDURE `lowcolA2`()
BEGIN
SELECT LOWER(Name) Name,Lower(FatherName) FatherName,Lower(MotherName) MotherName
FROM college_a_se_v;
END $$
Delimiter ;
Call lowcolA2;

Delimiter $$
CREATE PROCEDURE `lowcolA3`()
BEGIN
SELECT LOWER(Name) Name,Lower(FatherName) FatherName,Lower(MotherName) MotherName
FROM college_a_sj_v;
END $$
Delimiter ;
Call lowcolA3;

Delimiter $$
CREATE PROCEDURE `lowcolB1`()
BEGIN
SELECT LOWER(Name) Name,Lower(FatherName) FatherName,Lower(MotherName) MotherName
FROM college_b_hs_v;
END $$
Delimiter ;
Call lowcolB1;

Delimiter $$
CREATE PROCEDURE `lowcolB2`()
BEGIN
SELECT LOWER(Name) Name,Lower(FatherName) FatherName,Lower(MotherName) MotherName
FROM college_b_se_v;
END $$
Delimiter ;
Call lowcolB2;

Delimiter $$
CREATE PROCEDURE `lowcolB3`()
BEGIN
SELECT LOWER(Name) Name,Lower(FatherName) FatherName,Lower(MotherName) MotherName
FROM college_b_sj_v;
END $$
Delimiter ;
Call lowcolB3;

14----

DELIMITER $$
USE `alumni`$$
CREATE PROCEDURE `getname_collegeA`(INOUT studentn TEXT(40000))
BEGIN
DECLARE finished integer Default 0;
DECLARE student_info VARCHAR(16000) Default "";
DECLARE studentlist cursor for select Name from college_a_hs union ALL select name from college_a_se union all
select Name from college_a_sj;
DECLARE continue handler for not found set finished = 1;
OPEN studentlist ;
getname : LOOP
Fetch studentlist into student_info ;
If finished =1
THEN leave getname;
end if ;
SET studentn = concat(student_info,";",studentn);
END Loop;
close studentlist;
End$$
DELIMITER ;
set @studentn = "";
call getname_collegeA(@studentn);
select @studentn Name;


15----
DELIMITER $$
USE `alumni`$$
CREATE PROCEDURE `getname_collegeB`(INOUT studentn TEXT(40000))
BEGIN
DECLARE finished integer Default 0;
DECLARE student_info VARCHAR(16000) Default "";
DECLARE studentlist 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 studentlist ;
getname : LOOP
Fetch studentlist into student_info ;
If finished =1
THEN leave getname;
end if ;
SET studentn = concat(student_info,";",studentn);
END Loop;
close studentlist;
End$$
DELIMITER ;
set @studentn = "";
call getname_collegeB(@studentn);
select @studentn Name;

16---
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 "SelfEmployeed" 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 "ServiceJob" 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 ;