Project 2

# 1.) Create new schema as alumni
CREATE SCHEMA alumni;
#______________________________________________________________________________________________
# 2.) Import all .csv files into MySQL

-- see screen shot (import .csv in Mysql)
#______________________________________________________________________________________________
# 3.) Run SQL command to see the structure of six tables
DESC alumni.college_a_hs;
DESC alumni.college_a_se;
DESC alumni.college_a_sj;
DESC alumni.college_b_hs;
DESC alumni.college_b_se;
DESC alumni.college_b_sj;

#______________________________________________________________________________________________
# 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. (Submit the solution in jupyter notebook)

-- see (Project-2 MYSQL .prithvi)
#______________________________________________________________________________________________
# 5.) Import all the records of tables (College_A_HS, College_A_SE, College_A_SJ, College_B_HS,
# College_B_SE, College_B_SJ) into MS Excel. (submit Excel file for this question)

-- see (Project-2 SQL (5.) question .prithvi.xslx)
#______________________________________________________________________________________________# 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
alumni.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.
desc alumni.college_a_se;

CREATE VIEW College_A_SE_V AS
SELECT
*
FROM
alumni.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 alumni.college_a_sj;

CREATE VIEW College_A_SJ_V AS
SELECT
*
FROM
alumni.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.
CREATE VIEW College_B_HS_V AS
SELECT
*
FROM
alumni.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 and store cleaned data in view
# College_B_SE_V, Remove null values.
desc alumni.college_b_se;

CREATE VIEW College_B_SE_V AS
SELECT
*
FROM
alumni.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;

#______________________________________________________________________________________________
# 11.) Perform data cleaning on table College_B_SJ and store cleaned data in view
# College_B_SJ_V, Remove null values.

desc alumni.college_b_sj;

CREATE VIEW College_B_SJ_V AS
SELECT
*
FROM
alumni.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)

DELIMITER ||
CREATE PROCEDURE conventor1( )

BEGIN

SELECT 'NAME' AS college_a, "Father NAME" AS HigherStudies , "Mother NAME" as college_a_hs
UNION SELECT LOWER(Name), LOWER(FatherName), LOWER(MotherName) FROM college_a_hs_v;

SELECT 'NAME' AS college_a, "Father NAME" AS "Self Employed" , "Mother NAME" as college_a_se
UNION SELECT LOWER(Name), LOWER(FatherName), LOWER(MotherName) FROM college_a_se_v;

SELECT 'NAME' AS college_a, "Father NAME" AS "Service/Job" , "Mother NAME" as college_a_sj
UNION SELECT LOWER(Name) , LOWER(FatherName) , LOWER(MotherName) FROM college_a_sj_v;

END ||
DELIMITER ;
CALL conventor1( );
#-----------------------------------------------------------------------------------------

DELIMITER ||
CREATE PROCEDURE conventor2( )

BEGIN
SELECT 'NAME' AS college_b, "Father NAME" AS HigherStudies , "Mother NAME" as college_b_hs
UNION SELECT LOWER(Name) NAME, LOWER(FatherName) AS "Father NAME", LOWER(MotherName) AS "Mother NAME" FROM college_b_hs_v;

SELECT 'NAME' AS college_b, "Father NAME" AS "Self Employed" , "Mother NAME" as college_b_se
UNION SELECT LOWER(Name) NAME, LOWER(FatherName) AS "Father NAME", LOWER(MotherName) AS "Mother NAME" FROM college_b_se_v;

SELECT 'NAME' AS college_b, "Father NAME" AS "Service/Job" , "Mother NAME" as college_b_sj
UNION SELECT LOWER(Name) NAME, LOWER(FatherName) AS "Father NAME", LOWER(MotherName) AS "Mother NAME" FROM college_b_sj_v;

END ||
DELIMITER ;

CALL conventor2( );

#______________________________________________________________________________________________
# 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.

-- see project-2 SQL (13)

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

DROP PROCEDURE IF EXISTS get_name_collegeA;
DELIMITER $$
CREATE PROCEDURE get_name_collegeA
(
INOUT name1 TEXT(40000)
)
BEGIN
DECLARE na 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 na =1;

OPEN namedetail;

getame :
LOOP
FETCH FROM namedetail INTO namelist;
IF na = 1 THEN
LEAVE getame;
END IF;
SET name1 = CONCAT(namelist,";",name1);

END LOOP getame;
CLOSE namedetail;
END $$
DELIMITER ;

SET @Name = "";
CALL get_name_collegeA(@Name);
SELECT @Name Name;

#______________________________________________________________________________________________
# 15.) Write a query to create procedure get_name_collegeB using the cursor to fetch names of
# all students from college B.

DROP PROCEDURE IF EXISTS get_name_collegeB;
DELIMITER $$
CREATE PROCEDURE get_name_collegeB
(
INOUT name1 TEXT(40000)
)
BEGIN
DECLARE na 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 na =1;

OPEN namedetail;

getame :
LOOP
FETCH FROM namedetail INTO namelist;
IF na = 1 THEN
LEAVE getame;
END IF;
SET name1 = CONCAT(namelist,";",name1);

END LOOP getame;
CLOSE namedetail;
END $$
DELIMITER ;

SET @Name = "";
CALL get_name_collegeB(@Name);
SELECT @Name 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)
# Note: Approximate percentages are considered for career choices.

SELECT "Higher Studies" Present_status,
(COUNT(college_a_hs.RollNo) /(college_a_hs.RollNo))*100 College_A_Percentage,
(COUNT(college_b_hs.RollNo)/(college_b_hs.RollNo))*100 College_B_Percentage
FROM college_a_hs CROSS JOIN college_b_hs
UNION
SELECT "Self Empolyment" Present_status,
(COUNT(college_a_se.RollNo) /(college_a_se.RollNo))*100 College_A_Percentage,
(COUNT(college_b_se.RollNo)/(college_b_se.RollNo))*100 College_B_Percentage
FROM college_a_se
CROSS JOIN
college_b_se
UNION
SELECT "Service Job" Present_status,
(COUNT(college_a_sj.RollNo) /(college_a_sj.RollNo))*100 College_A_Percentage,
(COUNT(college_b_sj.RollNo)/(college_b_sj.RollNo))*100 College_B_Percentage
FROM college_a_sj
CROSS JOIN
college_b_sj;