Project 2
CREATE DATABASE alumni;
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;
/*Perform data cleaning on table College_A_HS and store cleaned data in view College_A_HS_V, Remove null values. */
DESC college_a_hs;
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;
desc college_A_HS_V;
/*Perform data cleaning on table College_A_SE and store cleaned data in view College_A_SE_V, Remove null values.*/
DESC college_a_se;
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;
desc College_A_SE_V;
/*Perform data cleaning on table College_A_SJ and store cleaned data in view College_A_SJ_V, Remove null values.*/
DESC college_a_sj;
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;
desc College_A_SJ_V;
/*Perform data cleaning on table College_B_HS and store cleaned data in view College_B_HS_V, Remove null values.*/
DESC college_b_hs;
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 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;
desc College_B_HS_V;
/*Perform data cleaning on table College_B_SE and store cleaned data in view College_B_SE_V, Remove null values.*/
desc college_b_se;
CREATE or replace 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);
SELECT * FROM College_B_SE_V;
desc College_B_SE_V;
/*Perform data cleaning on table College_B_SJ and store cleaned data in view College_B_SJ_V, Remove null values.*/
desc college_b_sj;
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 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);
SELECT * FROM College_B_SJ_V;
desc College_B_SJ_V;
/* 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 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_A_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();
/*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. */
/*Write a query to create procedure get_name_collegeA using the cursor to fetch names of all students from college A.*/
USE alumni;
DELIMITER $$
CREATE PROCEDURE get_name_collegeA(
INOUT Student_Names1 text(40000)
)
BEGIN
declare finished INT DEFAULT 0;
declare Namelist VARCHAR(16000) 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;
get_Names : LOOP
fetch namedetail into Namelist;
IF finished = 1 THEN
leave get_Names;
END IF;
SET Student_Names1 = CONCAT(Namelist,";",Student_Names1);
END LOOP get_Names;
CLOSE namedetail;
END$$
DELIMITER ;
SET @Student_Names1 = "";
call get_name_collegeA(@Student_Names1);
SELECT @Student_Names1;
/*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 Student_Names2 text(40000)
)
BEGIN
declare finished INT DEFAULT 0;
declare Namelist VARCHAR(16000) 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;
get_Names : LOOP
fetch namedetail into Namelist;
IF finished = 1 THEN
leave get_Names;
END IF;
SET Student_Names2 = CONCAT(Namelist,";",Student_Names2);
END LOOP get_Names;
CLOSE namedetail;
END$$
DELIMITER ;
SET @Student_Names2 = "";
call get_name_collegeB(@Student_Names2);
SELECT @Student_Names2 College_B_StudentName;
/* 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. */
use alumni;
SELEct "Higher Studies",((SELECT COUNT(*) FROM college_a_hs) /
((SELECT COUNT(*) FROM college_a_hs)+
(SELECT COUNT(*) FROM college_a_se)+
(SELECT COUNT(*) FROM college_a_sj)))*100 College_A_Percentages,
((SELECT COUNT(*) FROM college_b_hs) /
((SELECT COUNT(*) FROM college_b_hs)+
(SELECT COUNT(*) FROM college_b_se)+
(SELECT COUNT(*) FROM college_b_sj)))*100 College_B_Percentages UNION
SELEct "Self Empolyment", ((SELECT COUNT(*) FROM college_a_se) /
((SELECT COUNT(*) FROM college_a_hs)+
(SELECT COUNT(*) FROM college_a_se)+
(SELECT COUNT(*) FROM college_a_sj)))*100,
((SELECT COUNT(*) FROM college_b_se) /
((SELECT COUNT(*) FROM college_b_hs)+
(SELECT COUNT(*) FROM college_b_se)+
(SELECT COUNT(*) FROM college_b_sj)))*100 UNION
SELEct "Service Job",((SELECT COUNT(*) FROM college_a_sj) /
((SELECT COUNT(*) FROM college_a_hs)+
(SELECT COUNT(*) FROM college_a_se)+
(SELECT COUNT(*) FROM college_a_sj)))*100,
((SELECT COUNT(*) FROM college_b_sj) /
((SELECT COUNT(*) FROM college_b_hs)+
(SELECT COUNT(*) FROM college_b_se)+
(SELECT COUNT(*) FROM college_b_sj)))*100;