Project 2

##1.Create new schema as alumni
create database alumni;

##2.Import all .csv files into MySQL
## six csv files are imported into the mysql.

## 3.Run SQL command to see the structure of six tables
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;

## 4.Display first 1000 rows of tables (College_A_HS, College_A_SE, College_A_SJ, College_B_HS, College_B_SE, College_B_SJ) with Python.
## attached python file for question-4.

## 5.Import first 1500 rows of tables (College_A_HS, College_A_SE, College_A_SJ, College_B_HS, College_B_SE, College_B_SJ) into MS Excel.
## attached the excel file for question-5.alter

## 6.Perform data cleaning on table College_A_HS and store cleaned data in view College_A_HS_V, Remove null values.
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);
select * from College_A_HS_V;

## 7.Perform data cleaning on table College_A_SE and store cleaned data in view College_A_SE_V, Remove null values.
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.Perform data cleaning on table College_A_SJ and store cleaned data in view College_A_SJ_V, Remove null values.
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.Perform data cleaning on table College_B_HS and store cleaned data in view College_B_HS_V, Remove null values.
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. Perform data cleaning on table College_B_SE and store cleaned data in view College_B_SE_V, Remove null values.
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
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.Perform data cleaning on table College_B_SJ and store cleaned data in view College_B_SJ_V, Remove null values
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
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.Make procedure to use string function/s for converting record of Name, FatherName, MotherName into lower case for views (College_A_HS_V, College_A_SE_V, College_A_SJ_V, College_B_HS_V, College_B_SE_V, College_B_SJ_V)
## for college_a_hs
use alumni;
delimiter //
create procedure names_college_a_hs()
begin
select lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) MotherName from college_a_hs;
end //
delimiter ;
call names_college_a_hs;

## for college_a_se
delimiter //
create procedure names_college_a_se()
begin
select lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) MotherName from college_a_se;
end //
delimiter ;
call names_college_a_se;

## for college_a_sj
delimiter //
create procedure names_college_a_sj()
begin
select lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) MotherName from college_a_sj;
end //
delimiter ;
call names_college_a_sj;

## for college_b_hs
delimiter //
create procedure names_college_b_hs()
begin
select lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) MotherName from college_b_hs;
end //
delimiter ;
call names_college_b_hs;

## for college_b_se
delimiter //
create procedure names_college_b_se()
begin
select lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) MotherName from college_b_se;
end //
delimiter ;
call names_college_b_se;
## for college_b_sj
delimiter //
create procedure names_college_b_sj()
begin
select lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) MotherName from college_b_sj;
end //
delimiter ;
call names_college_b_sj;

##13.Import the created views (College_A_HS_V, College_A_SE_V, College_A_SJ_V, College_B_HS_V, College_B_SE_V, College_B_SJ_V) into MS Excel and make pivot chart for location of Alumni.
## Attach the excel file.

## 14.Write a query to create procedure get_name_collegeA using the cursor to fetch names of all students from college A.
use alumni;
drop procedure if exists get_name_collegeA
delimiter $$
create procedure get_name_collegeA
(
inout Aname Text(20000)
)
begin
declare finished int default 0;
declare Anamelist varchar(16000) default "";
declare Anamedetails
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 finished = 1;
open Anamedetails;

get_Name1:loop
fetch Anamedetails into Anamelist;
if finished = 1 then leave get_Name1;
end if;
set Aname = concat(Anamelist,";",Aname);
end loop get_Name1;
close Anamedetails;
end$$
delimiter ;
set @name="";
call get_name_collegeA(@name);
select@name name;
## 15.Write a query to create procedure get_name_collegeB using the cursor to fetch names of all students from college B.
use alumni;
drop procedure if exists get_name_collegeB
delimiter $$
create procedure get_name_collegeB
(
inout Bname Text(20000)
)
begin
declare finished int default 0;
declare Bnamelist varchar(16000) default "";
declare Bnamedetails
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 finished = 1;
open Bnamedetails;

get_Name1:loop
fetch Bnamedetails into Bnamelist;
if finished = 1 then leave get_Name1;
end if;
set Bname = concat(Bnamelist,";",Bname);
end loop get_Name1;
close Bnamedetails;
end$$
delimiter ;
set @name="";
call get_name_collegeB(@name);
select@name name;

##16.Calculate the percentage of career choice of College A and College B Alumni
-- (w.r.t Higher Studies, Self Employed and Service/Job)
use alumni;
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 'Self Employed'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 'Service Job'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