Project 2
#1) Create new schema as alumni
CREATE DATABASE alumni;
#2) Import all .csv files into MySQL
-- Imported
# 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.
# Jupyter notebook attached
# SELECT * FROM college_a_hs LIMIT 1000;
#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.
# Excel file attached
#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;
# 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;
#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;
#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;
#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
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) 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
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;
#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)
DELIMITER $
CREATE PROCEDURE V1()
BEGIN
SELECT LOWER(Name) Name, LOWER(FatherName) FatherName, LOWER(MotherName) MotherName
FROM College_A_HS_V;
END $
DELMITER ;
CALL V1();
DELIMITER $
CREATE PROCEDURE V2()
BEGIN
SELECT LOWER(Name) Name, LOWER(FatherName) FatherName, LOWER(MotherName) MotherName
FROM College_A_SE_V;
END $
DELIMITER ;
CALL V2();
DELIMITER $
CREATE PROCEDURE V3()
BEGIN
SELECT LOWER(Name) Name, LOWER(FatherName) FatherName, LOWER(MotherName) MotherName
FROM College_A_SJ_V;
END $
DELIMITER ;
CALL V3();
DELIMITER $
CREATE PROCEDURE V4()
BEGIN
SELECT LOWER(Name) Name, LOWER(FatherName) FatherName, LOWER(MotherName) MotherName
FROM College_B_HS_V;
END $
DELIMITER ;
CALL V4();
DELIMITER $
CREATE PROCEDURE V5()
BEGIN
SELECT LOWER(Name) Name, LOWER(FatherName) FatherName, LOWER(MotherName) MotherName
FROM College_A_SE_V;
END $
DELIMITER ;
CALL V5();
DELIMITER $
CREATE PROCEDURE V6()
BEGIN
SELECT LOWER(Name) Name, LOWER(FatherName) FatherName, LOWER(MotherName) MotherName
FROM College_B_SJ_V;
END $
DELIMITER ;
CALL V6();
#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.
# Excel file attached
#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 n text(20000))
begin
declare finished int default 0;
declare namelist varchar(400) 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 namelist;
if finished = 1 then
leave getname;
end if;
set n = concat(namelist,';',n);
end loop getname;
close namedetails;
end$$
delimiter ;
set @l=' ';
call get_name_collegeA(@l);
select @l student_names_get_name_collegeA;
#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 n text(20000))
begin
declare finished int default 0;
declare namelist varchar(400) 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 namelist;
if finished = 1 then
leave getname;
end if;
set n = concat(namelist,';',n);
end loop getname;
close namedetails;
end$$
delimiter ;
set @l=' ';
call get_name_collegeB(@l);
select @l student_names_get_name_collegeB;
#16)
select (1157+1016+5633) Total_No_of_alumni_College_A;
select 'Higher_studies' career_choice,count(*) No_of_students ,(count(*)/7806)*100 Percentage from College_A_HS
union
select 'Self Employed' career_choice,count(*) No_of_students,count(*)/7806*100 Percentage from College_A_SE
union
select 'Service/Job' career_choice,count(*) No_of_students,count(*)/7806*100 Percentage from College_A_SJ;
select (199+201+1859) Total_no_of_alumni_College_B;
select 'Higher_studies' career_choice,count(*) No_of_students,count(*)/2259*100 Percentage from College_B_HS
union
select 'Self Employed' career_choice,count(*) No_of_students,count(*)/2259*100 Percentage from College_B_SE
union
select 'Service/Job' career_choice,count(*) No_of_students,count(*)/2259*100 Percentage from College_B_SJ;