Project 2
-- 3)Run SQL commor 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;
-- 6)Perform data cleaning on table College_A_HS or store cleaned data in view College_A_HS_V, Remove null values.
update college_a_hs set RollNo=nullif(RollNo,""),LastUpdate = nullif(LastUpdate,""),Name = nullif(Name,""),
FatherName = nullif(FatherName,""), MotherName = nullif(MotherName,""),Batch = nullif(Batch,""),
Degree = nullif(Degree,""),PresentStatus = nullif(PresentStatus,""),HSDegree = nullif(HSDegree,""),
EntranceExam = nullif(EntranceExam,""),Institute = nullif(Institute,""),
Location =nullif(Location,"");
-- Above statement used to convert blank values to null values
create or replace 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 or store cleaned data in view College_A_SE_V, Remove null values.
update college_a_se set RollNo=nullif(RollNo,""),LastUpdate = nullif(LastUpdate,""),Name = nullif(Name,""),
FatherName = nullif(FatherName,""), MotherName = nullif(MotherName,""),Batch = nullif(Batch,""),
Degree = nullif(Degree,""),PresentStatus = nullif(PresentStatus,""),Organization = nullif(Organization,""),
Location =nullif(Location,"");
create or replace 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 or store cleaned data in view College_A_SJ_V, Remove null values.
update college_a_sj set RollNo=nullif(RollNo,""),LastUpdate = nullif(LastUpdate,""),Name = nullif(Name,""),
FatherName = nullif(FatherName,""), MotherName = nullif(MotherName,""),Batch = nullif(Batch,""),
Degree = nullif(Degree,""),PresentStatus = nullif(PresentStatus,""),Organization = nullif(Organization,""),
Designation = nullif(Designation,""),
Location =nullif(Location,"");
create or replace 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 or store cleaned data in view College_B_HS_V, Remove null values.
update college_b_hs set RollNo=nullif(RollNo,""),LastUpdate = nullif(LastUpdate,""),Name = nullif(Name,""),
FatherName = nullif(FatherName,""), MotherName = nullif(MotherName,""),Batch = nullif(Batch,""),
Degree = nullif(Degree,""),PresentStatus = nullif(PresentStatus,""),HSDegree = nullif(HSDegree,""),
EntranceExam = nullif(EntranceExam,""),Institute = nullif(Institute,""),
Location =nullif(Location,"");
create or replace 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 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 or store cleaned data in view College_B_SE_V, Remove null values.
update college_b_se set RollNo=nullif(RollNo,""),LastUpdate = nullif(LastUpdate,""),Name = nullif(Name,""),
FatherName = nullif(FatherName,""), MotherName = nullif(MotherName,""),Batch = nullif(Batch,""),
Degree = nullif(Degree,""),PresentStatus = nullif(PresentStatus,""),Organization = nullif(Organization,""),
Location =nullif(Location,"");
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.
update college_b_sj set RollNo=nullif(RollNo,""),LastUpdate = nullif(LastUpdate,""),Name = nullif(Name,""),
FatherName = nullif(FatherName,""), MotherName = nullif(MotherName,""),Batch = nullif(Batch,""),
Degree = nullif(Degree,""),PresentStatus = nullif(PresentStatus,""),Organization = nullif(Organization,""),
Designation = nullif(Designation,""),
Location =nullif(Location,"");
create or replace 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)
call lowercase();
-- 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 Name1 text(80000))
Begin
declare finished int default 0;
declare namelist varchar(800) 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 finished = 1;
open namedetail;
getname:
loop
fetch namedetail into namelist;
if finished = 1 then
leave getname;
end if;
set Name1 = concat(namelist,":",Name1);
end loop getname;
close namedetail;
end $$
Delimiter ;
set @Name1 = "";
call get_name_collegeA(@Name1);
select @Name1;
-- 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 Name1 text(80000))
Begin
declare finished int default 0;
declare namelist varchar(800) default "";
declare namedetail 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 namedetail;
getname:
loop
fetch namedetail into namelist;
if finished = 1 then
leave getname;
end if;
set Name1 = concat(namelist,":",Name1);
end loop getname;
close namedetail;
end $$
Delimiter ;
set @Name1 = "";
call get_name_collegeB(@Name1);
select @Name1;
-- 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.
create table college_A(RollNo varchar(55),PresentStatus varchar(55));
Insert into college_A(RollNo, PresentStatus)
select RollNo,PresentStatus from college_a_hs union select RollNo,PresentStatus from college_a_se union
select RollNo,PresentStatus from college_a_sj;
select * from college_A;
create table college_B(RollNo varchar(55),PresentStatus varchar(55));
Insert into college_B(RollNo, PresentStatus)
select RollNo,PresentStatus from college_b_hs union select RollNo,PresentStatus from college_b_se union
select RollNo,PresentStatus from college_b_sj;
select count(*) from college_B;
create table Table1(Career_Choice varchar(50),college_percentage_A float);
Insert into Table1(Career_Choice,college_percentage_A)
select PresentStatus, (count(PresentStatus)/(select count(PresentStatus) from college_A))*100 from college_A group by PresentStatus;
select * from Table1;
create table Table2(Career_Choice varchar(50),college_percentage_B float);
Insert into Table2(Career_Choice,college_percentage_B)
select PresentStatus, (count(PresentStatus)/(select count(PresentStatus) from college_B))*100 from college_B group by PresentStatus;
select * from Table2;
select a.Career_Choice, a.college_percentage_A, b.college_percentage_B from Table1 a inner join Table2 b on a.Career_Choice = b.Career_Choice;