Challenge

Project 2

SSatya Alekhya Devarakonda23 Jan 2023

PRINT (A)

PROJECT – 2

#project 2

#1 create schema alumni;

#2 imported all csv files into sql

use alumni;
# 3 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;

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.

import mysql.connector
testdb=mysql.connector.connect(host="localhost",
user="root",
password="harshu?799",
database="alumni")
testcursor=testdb.cursor()
testcursor.execute("select * from college_a_hs limit 1000")
for a in testcursor:
print(a)

# 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 EntranceExam<>"" AND Location<>"";
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 Organization<>"" AND Location<>"" ;
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.college_a_sj
create view college_a_sj_v as select * from college_a_sj where Organization<>"" AND Designation<>"" AND Location<>"";
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 EntranceExam<>"" AND Institute<>"" AND Location<>"";
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 MotherName<>"";
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.
create view college_b_sj_v as select * from college_b_sj where Designation<>"";
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)*/
select lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) MotherName from college_a_hs_v union
select lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) MotherName from college_a_se_v union
select lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) MotherName from college_a_sj_v union
select lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) MotherName from college_b_hs_v union
select lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) MotherName from college_b_se_v union
select lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) MotherName from college_b_sj_v;

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

DELIMITERcreate procedure getnamea() begin declare namelist1 text default ""; declare namelist2 text default ""; declare namelist3 text default ""; declare studentname_hs text default""; declare studentname_se text default ""; declare studentname_sj text default ""; declare finished integer default 0; declare namedetail_hs cursor for select Name from college_a_hs; declare namedetail_se cursor for select Name from college_a_se; declare namedetail_sj cursor for select Name from college_a_sj; declare continue handler for not found set finished = 1; open namedetail_hs; getname1:loop fetch namedetail_hs into namelist1; set studentname_hs = concat (namelist1," ; ",studentname_hs); if finished = 1 then leave getname1; end if; end loop getname1; close namedetail_hs; set finished = 0; open namedetail_se; getname2:loop fetch namedetail_se into namelist2; set studentname_se = concat (namelist2," ; ",studentname_se); if finished = 1 then leave getname2; end if; end loop getname2; close namedetail_se; set finished = 0; open namedetail_sj; getname3:loop fetch namedetail_sj into namelist3; set studentname_sj = concat (namelist3," ; ",studentname_sj); if finished = 1 then leave getname3; end if; end loop getname3; close namedetail_sj; set finished = 0; select studentname_hs,studentname_se,studentname_sj; endcreate procedure getnamea() begin declare namelist1 text default ""; declare namelist2 text default ""; declare namelist3 text default ""; declare studentname_hs text default""; declare studentname_se text default ""; declare studentname_sj text default ""; declare finished integer default 0; declare namedetail_hs cursor for select Name from college_a_hs; declare namedetail_se cursor for select Name from college_a_se; declare namedetail_sj cursor for select Name from college_a_sj; declare continue handler for not found set finished = 1; open namedetail_hs; getname1:loop fetch namedetail_hs into namelist1; set studentname_hs = concat (namelist1," ; ",studentname_hs); if finished = 1 then leave getname1; end if; end loop getname1; close namedetail_hs; set finished = 0; open namedetail_se; getname2:loop fetch namedetail_se into namelist2; set studentname_se = concat (namelist2," ; ",studentname_se); if finished = 1 then leave getname2; end if; end loop getname2; close namedetail_se; set finished = 0; open namedetail_sj; getname3:loop fetch namedetail_sj into namelist3; set studentname_sj = concat (namelist3," ; ",studentname_sj); if finished = 1 then leave getname3; end if; end loop getname3; close namedetail_sj; set finished = 0; select studentname_hs,studentname_se,studentname_sj; end
DELIMITER ;
call getnamea();
drop procedure getnamea;

# 15 Write a query to create procedure get_name using the cursor to fetch names of all students from college B.
DELIMITERcreate procedure getnameb() begin declare namelist1 text default ""; declare namelist2 text default ""; declare namelist3 text default ""; declare studentname_hs text default""; declare studentname_se text default ""; declare studentname_sj text default ""; declare finished integer default 0; declare namedetail_hs cursor for select Name from college_b_hs; declare namedetail_se cursor for select Name from college_b_se; declare namedetail_sj cursor for select Name from college_b_sj; declare continue handler for not found set finished = 1; open namedetail_hs; getname1:loop fetch namedetail_hs into namelist1; set studentname_hs = concat (namelist1," ; ",studentname_hs); if finished = 1 then leave getname1; end if; end loop getname1; close namedetail_hs; set finished = 0; open namedetail_se; getname2:loop fetch namedetail_se into namelist2; set studentname_se = concat (namelist2," ; ",studentname_se); if finished = 1 then leave getname2; end if; end loop getname2; close namedetail_se; set finished = 0; open namedetail_sj; getname3:loop fetch namedetail_sj into namelist3; set studentname_sj = concat (namelist3," ; ",studentname_sj); if finished = 1 then leave getname3; end if; end loop getname3; close namedetail_sj; set finished = 0; select studentname_hs,studentname_se,studentname_sj; endcreate procedure getnameb() begin declare namelist1 text default ""; declare namelist2 text default ""; declare namelist3 text default ""; declare studentname_hs text default""; declare studentname_se text default ""; declare studentname_sj text default ""; declare finished integer default 0; declare namedetail_hs cursor for select Name from college_b_hs; declare namedetail_se cursor for select Name from college_b_se; declare namedetail_sj cursor for select Name from college_b_sj; declare continue handler for not found set finished = 1; open namedetail_hs; getname1:loop fetch namedetail_hs into namelist1; set studentname_hs = concat (namelist1," ; ",studentname_hs); if finished = 1 then leave getname1; end if; end loop getname1; close namedetail_hs; set finished = 0; open namedetail_se; getname2:loop fetch namedetail_se into namelist2; set studentname_se = concat (namelist2," ; ",studentname_se); if finished = 1 then leave getname2; end if; end loop getname2; close namedetail_se; set finished = 0; open namedetail_sj; getname3:loop fetch namedetail_sj into namelist3; set studentname_sj = concat (namelist3," ; ",studentname_sj); if finished = 1 then leave getname3; end if; end loop getname3; close namedetail_sj; set finished = 0; select studentname_hs,studentname_se,studentname_sj; end
DELIMITER ;
call getnameb();
drop procedure getnameb;

#16 Calculate the percentage of career choice of College A and College B Alumni
#(w.r.t Higher Studies, Self Employed and Service/Job)
--- college A count
select 'higher_study_people_clg_a' ,count(*) higher_study from college_a_hs
union
select 'self_employed_people_clg_a',count(*) self_employed from college_a_se
union
select 'service_job_people_a',count(*) service_job from college_a_sj;
--- college B count
select 'higher_study_people_clg_b' ,count(*) higher_study from college_b_hs
union
select 'self_employed_people_clg_b',count(*) self_employed from college_b_se
union
select 'service_job_people_b',count(*) service_job from college_b_sj;
--- college A total
select 1157+727+4008 as "College A";
--- college B total
select 199+201+1859 as "College B";

---- calculating percentage of college A
create view collegeA as select 'higher_study',count(*)/5892*100 as "College A Percentage" from college_a_hs
union
select 'self_employed',(count(*)/5892)*100 from college_a_se
union
select 'sevice_job',(count(*)/5892)*100 from college_a_sj;

---- calculating percentage of college B
create view collegeB as select 'higher_study',(count(*)/2259)*100 "College B Percentage"from college_b_hs
union
select 'self_employed',(count(*)/2259)*100 from college_b_se
union
select 'sevice_job',(count(*)/2259)*100 from college_b_sj;
----

select * from collegeA
left join collegeB on collegeA.higher_study=collegeB.higher_study;

Project 2

Ship work like this
in your own course.

Talk to a career counsellor

Ship work like thisin your own course.

Talk to a career counsellor

Ship work like this
in your own course.