Project 2
Aim : 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.
- Create new schema as alumni
- Import all .csv files into MySQL
- Run SQL command to see the structure of six tables
- Display first 1000 rows of tables with Python.
- Import first 1500 rows of tables into MS Excel.
- Perform data cleaning on table Remove null values.
- Perform data cleaning on table Remove null values.
- Make procedure to use string function/s for converting record of Name, FatherName, MotherName into lower case for views (
- Import the created into MS Excel and make pivot chart for location of Alumni.
- Write a query to create procedure get_name_collegeA using the cursor to fetch names of all students from college A.
- Write a query to create procedure get_name_collegeB using the cursor to fetch names of all students from college B.
- Calculate the percentage of career choice of College A and College B Alumni.
Query are written as follows:
-- Q1 Ans
CREATE DATABASE alumni;
-- Q3 Ans
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;
--Q4 Ans
import mysql.connector
alumnidb=mysql.connector.connect(host="localhost",user="root",password="Hiwale@2001",database="alumni")
alumnicursor=alumnidb.cursor()
alumnicursor.execute("SELECT * FROM college_a_hs limit 1000")
for a in alumnicursor:
print(a)
-- Q6 Ans
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
college_a_sjMotherName 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 ;
-- Q7 ans
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 ;
-- Q 8 Ans
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 ;
-- Q9 Ans
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 ;
-- Q10 ans
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 ;
-- Q11 Ans
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 ;
-- Q12 ANS
delimiter $
create procedure v1()
begin
select lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) MotherName
from college_a_hs_v;
end $
delimiter ;
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_b_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();
-- Q14 Ans
delimiter $$
create procedure get_name1(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_name1(@l);
select @l student_names_college_a;
-- Q15 Ans
delimiter $$
create procedure get_name2(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_name2(@l);
select @l student_names_college_b;
-- Q16 Ans
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;