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)
--
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_se limit 1000")
for a in testcursor:
print(a)
--
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_sj limit 1000")
for a in testcursor:
print(a)
--
import mysql.connector
testdb=mysql.connector.connect(host="localhost",
user="root",
password="harshu?799",
database="alumni")
testcursor=testdb.cursor()
testcursor.execute("select * from college_b_hs limit 1000")
for a in testcursor:
print(a)
--
import mysql.connector
testdb=mysql.connector.connect(host="localhost",
user="root",
password="harshu?799",
database="alumni")
testcursor=testdb.cursor()
testcursor.execute("select * from college_b_se limit 1000")
for a in testcursor:
print(a)
--
import mysql.connector
testdb=mysql.connector.connect(host="localhost",
user="root",
password="harshu?799",
database="alumni")
testcursor=testdb.cursor()
testcursor.execute("select * from college_b_sj 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.
DELIMITER $$
create 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.
DELIMITER $$
create 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;