Project 2
# 1.Create new schema as alumni
CREATE DATABASE alumni;
# 2.Import all .csv files into MySQL
USE alumni;
# 3. Run SQL command to see the 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;
select * from college_a_hs;
# 4. 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.
-- Attached in below file with .ipynb
1
2
3
4
# 5.Import first 1500 rows of tables (College_A_HS, College_A_SE, College_A_SJ,
# College_B_HS, College_B_SE, College_B_SJ) into MS Excel.
# Below file is attached file with nameexcel.
# 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 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 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 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 and store cleaned data in view College_A_SJ_V, Remove null values.
DESC College_A_SJ ;
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 );
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.
DESC college_b_hs;
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);
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 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);
DESC college_b_se;
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.
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);
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_Se_V;
SELECT LOWER(Name) name ,LOWER(FatherName) fathername ,LOWER(MotherName) mothername FROM College_A_Sj_V;
SELECT LOWER(Name) name ,LOWER(FatherName) fathername ,LOWER(MotherName) mothername FROM College_B_HS_V;
SELECT LOWER(Name) name ,LOWER(FatherName) fathername ,LOWER(MotherName) mothername FROM College_B_Se_V;
SELECT LOWER(Name) name ,LOWER(FatherName) fathername ,LOWER(MotherName) mothername FROM College_B_Sj_V;
# 13.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.
# Below excel file is attached Name of Excel-project-2 file.
# 14. Write a query to create procedure get_name_collegeA using the cursor to fetch names of all students from college A.
DROP PROCEDURE get_name_collegeA;
DELIMITER %%
CREATE PROCEDURE get_name_collegeA ( INOUT Lname TEXT(40000) ) BEGIN DECLARE finished INT DEFAULT 0;
DECLARE Lnamelist VARCHAR (16000) DEFAULT "";
DECLARE Lnamedetails 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 Lnamedetails; getname1:
LOOP FETCH Lnamedetails INTO Lnamelist;
IF finished =1 THEN LEAVE getname1;
END IF; SET Lname = CONCAT (Lnamelist,";",Lname);
END LOOP getname1;
CLOSE Lnamedetails;
END %%
DELIMITER ;
SET @name1="";
CALL get_name_collegeA(@name1);
SELECT @name1 Name;
## 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 Fname TEXT(40000) )
BEGIN DECLARE finished INT DEFAULT 0;
DECLARE Fnamelist VARCHAR (16000) DEFAULT "";
DECLARE Fnamedetails
CURSOR FOR SELECT name FROM college_b_hs UNION ALL SELECT name FROM college_b_se
UNION ALL SELECT name FROM college_b_sj;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished=1;
OPEN Fnamedetails; getname2: LOOP FETCH Fnamedetails INTO Fnamelist;
IF finished =1 THEN LEAVE getname2;
END IF;
SET Fname = CONCAT (Fnamelist,";",Fname);
END LOOP getname2;
CLOSE Fnamedetails;
END $$
DELIMITER ;
SET @name2="";
CALL get_name_collegeB(@name2);
SELECT @name2 Name;
## 16 Calculate the percentage of career choice of College A and College B Alumni
-- (w.r.t Higher Studies, Self Employed and Service/Job)
SELECT "HigherStudies" PresentStatus,(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_Percentage,
(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_Percentage
UNION
SELECT "Self Employed" PresentStatus,(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
College_A_Percentage,
(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
College_B_Percentage
UNION
SELECT "Service Job" PresentStatus,(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
College_A_Percentage,
(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
College_B_Percentage;