BlogFor BusinessTalk to a counsellor
CoursesIIT ProgramsPlacementsLog in
BlogFor Business
Talk to a counsellor
Challenge

Project 2

NNithish Mandha27 Apr 2023

# answers for 5th and 13th questions attached in the same Excel file

/*Once a student is passed out from a Institute or College, he/she is known as Alumni of the Institute.
Alumni’s career growth plays important role in Institute’s ranking and other networking activities.
In this project, career choices of alumni of two Universities will be analyzed with respect to their passing year
as well as the course they completed.

Dataset: Six .csv file (Alumni record of College A and College B) Higher Studies, Self Employed and Service/Job record

College_A_HS ~ Higher Studies Record of College A
College_A_SE ~ Self Employed Record of College A
College_A_SJ ~ Service/Job Record of College A
College_B_HS ~ Higher Studies Record of College B
College_B_SE ~ Self-Employed Record of College B
College_B_SJ ~ Service/Job respectively Record of College B*/

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

-- --------------------------------------------------------------------------------------------------------------

#2.Import all .csv files into MySQL
-- imported

-- --------------------------------------------------------------------------------------------------------------
#3.Run SQL command to see the structure of six tables

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_V, College_A_SE, College_A_SJ, College_B_HS, College_B_SE, College_B_SJ)
with Python. (Submit the solution in jupyter notebook)*/


-- --------------------------------------------------------------------------------------------------------------

#5.
/*Import all the records of tables (College_A_HS, College_A_SE, College_A_SJ, College_B_HS, College_B_SE, College_B_SJ)
into MS Excel.(submit Excel file for this question) */

-- --------------------------------------------------------------------------------------------------------------

#6. Perform data cleaning on table College_A_HS and store cleaned data in view College_A_HS_V, Remove null values.

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.Perform data cleaning on table College_A_SE and store cleaned data in view College_A_SE_V, Remove null values.

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.Perform data cleaning on table College_A_SJ and store cleaned data in view College_A_SJ_V, Remove null values.

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.Perform data cleaning on table College_B_HS and store cleaned data in view College_B_HS_V, Remove null values.

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.Perform data cleaning on table College_B_SE and store cleaned data in view College_B_SE_V, Remove null values.

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.Perform data cleaning on table College_B_SJ and store cleaned data in view College_B_SJ_V, Remove null values.

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.
/*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) */

-- # group procedure for all the views
DELIMITER $$
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$$
call lowercase();
-- procedure for individual views
DELIMITER $$
create procedure lowercase1 (IN arg1 text)
begin

if arg1= 'College_A_HS_V' then
select lower(Name),lower(FatherName),lower(MotherName) FROM College_A_HS_V;
elseif arg1='College_A_Se_V' then
select lower(Name),lower(FatherName),lower(MotherName) FROM College_A_Se_V;
elseif arg1='College_A_Sj_V' then
select lower(Name),lower(FatherName),lower(MotherName) FROM College_A_Sj_V;
elseif arg1='College_B_HS_V' then
select lower(Name),lower(FatherName),lower(MotherName) FROM College_B_HS_V;
elseif arg1='College_B_Se_V' then
select lower(Name),lower(FatherName),lower(MotherName) FROM College_B_Se_V;
elseif arg1='College_B_Sj_V' then
select lower(Name),lower(FatherName),lower(MotherName) FROM College_B_Sj_V;
else select 'something wrong' ;
end if;
end$$
call lowercase1('College_A_HS_V');

-- --------------------------------------------------------------------------------------------------------------

#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. */

-- --------------------------------------------------------------------------------------------------------------

#14.
/*Write a query to create procedure get_name_collegeA using the cursor to fetch names of all students from college A.*/

DELIMITER $$
CREATE PROCEDURE get_name_collegeA( inout adrr text(50000))
begin
declare finished int default 0;
declare lastlist varchar(16000) default "";

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

getname:loop
fetch namedetails into lastlist;
if finished =1 then
leave getname;
end if;

set adrr = concat(lastlist,";",adrr) ;
end loop getname;
close namedetails;
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.*/

DELIMITER $$
CREATE PROCEDURE get_name_collegeB( inout adrr text(50000))
begin
declare finished int default 0;
declare lastlist varchar(16000) default "";

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

getname:loop
fetch namedetails into lastlist;
if finished =1 then
leave getname;
end if;

set adrr = concat(lastlist,";",adrr) ;
end loop getname;
close namedetails;
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)
Note: Approximate percentages are considered for career choices.
*/

select "Higher Studies" Present_status,
((select count( *) from college_a_hs )/ (select sum(hk ) from (select count(*) hk from college_a_hs union
select count(*) from college_a_se union
select count(*) from college_a_sj ) tbh ) )*100 College_A_Persentage,
((select count( *) from college_b_hs ) / (select sum(hk ) from (select count(*) hk from college_b_hs union
select count(*) from college_b_se union
select count(*) from college_b_sj ) tbh ) )*100 College_B_Persentage
union
select "Self Empolyment" Present_status,
((select count( *) from college_a_se )/ (select sum(hk ) from (select count(*) hk from college_a_hs union
select count(*) from college_a_se union
select count(*) from college_a_sj ) tbh ) )*100 College_A_Persentage,
((select count( *) from college_b_se ) / (select sum(hk ) from (select count(*) hk from college_b_hs union
select count(*) from college_b_se union
select count(*) from college_b_sj ) tbh ) )*100 College_B_Persentage
union
select "Service Job" Present_status,
((select count( *) from college_a_sj )/ (select sum(hk ) from (select count(*) hk from college_a_hs union
select count(*) from college_a_se union
select count(*) from college_a_sj ) tbh ) )*100 College_A_Persentage,
((select count( *) from college_b_sj ) / (select sum(hk ) from (select count(*) hk from college_b_hs union
select count(*) from college_b_se union
select count(*) from college_b_sj ) tbh ) )*100 College_B_Persentage ;

Your portfolio, next

Ship work like this
in your own course.

Talk to a career counsellor

Free 20-minute call. We'll map the right course to where you want to work.

By submitting you agree to our Terms and Privacy Policy.