Project 2
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.
A detailed summary of project mentioned at bottom
Create schema alumni
2. Import all .csv files into MySQL
3. desc tables
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. (Submit the solution in jupyter notebook)
Refer to attached Python file Project II MySql.jpynb
5.
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)
Refer to attached Excel file Project II MySql
Connected mysql to Excel---Installation of Add-In https://downloads.mysql.com/archives > MySql for Excel version1.3.8
Data> MySql for Excel> New Connection
Imported all Tables through localhost connection from Excel.
## 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)
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `lowercasename`()
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$$
DELIMITER ;
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.
Attached is Excel File Project II Pivot Table
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_name_collegeA`(
inout nameA text(40000)
)
BEGIN
DECLARE na int Default 0;
Declare namelist varchar(10000) Default " ";
Declare namedetail 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 na=1;
Open namedetail;
getame: Loop
Fetch namedetail into namelist;
if na=1 then
Leave getame;
end if;
Set nameA=concat(namelist,";",nameA);
End Loop getame;
close namedetail;
END$$
DELIMITER ;
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.
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `CollegeACollegeBPercentage`()
BEGIN
Set @total=0, @val=0, @val1=0, @val2=0, @totalB=0, @valB=0, @val1B=0, @val2B=0;
select @val:= count(rollno) from college_a_hs;
select @val1:= count(rollno) from college_a_se;
select @val2:= count(rollno) from college_a_sj;
set @total=@val+@val1+@val2;
select @valB:= count(rollno) from college_b_hs;
select @val1B:= count(rollno) from college_b_se;
select @val2B:= count(rollno) from college_b_sj;
set @totalB=@valB+@val1B+@val2B;
select "Higher Studies" as Career_Choice, @val/@total*100 "College_A_Percentage", @valB/@totalB*100 "College_B_Percentage"
UNION
select "Self Employed" as Career_Choice, @val1/@total*100 "College_A_Percentage", @val1B/@totalB*100 "College_B_Percentage"
UNION
select "Service Job" as Career_Choice, @val2/@total*100 "College_A_Percentage", @val2B/@totalB*100 "College_B_Percentage";
END$$
DELIMITER ;
The project is completed satisfactorily. In all the tables rollno was not primary key so I included an autoincrement ID to uniquely identify a row of record.
The Charts made from the views were on location. A duplicate column of location is used to study the graph with more clarity.
For the question 16 I wrote a procedure and used temporary variables to store values for calculation to give the output
I have attached the script of MySql.