Project 2
-- Project 2: Alumni Career Choices Analysis
-- 1. Create new schema as alumni
CREATE SCHEMA IF NOT EXISTS alumni;
USE alumni;
-- 2. Import all .csv files into MySQL
-- Import the CSV files into the following tables:
-- College_A_HS, College_A_SE, College_A_SJ, College_B_HS, College_B_SE, College_B_SJ
-- This step is done using MySQL Workbench's 'Table Data Import Wizard'
-- 3. Run SQL command to see the structure of six tables
DESCRIBE College_A_HS;
DESCRIBE College_A_SE;
DESCRIBE College_A_SJ;
DESCRIBE College_B_HS;
DESCRIBE College_B_SE;
DESCRIBE College_B_SJ;
-- 6-11. Perform Data Cleaning and Create Views
-- Remove null values and create cleaned views for each table
CREATE VIEW College_A_HS_V AS
SELECT * FROM College_A_HS
WHERE Name IS NOT NULL AND FatherName IS NOT NULL AND MotherName IS NOT NULL;
CREATE VIEW College_A_SE_V AS
SELECT * FROM College_A_SE
WHERE Name IS NOT NULL AND FatherName IS NOT NULL AND MotherName IS NOT NULL;
CREATE VIEW College_A_SJ_V AS
SELECT * FROM College_A_SJ
WHERE Name IS NOT NULL AND FatherName IS NOT NULL AND MotherName IS NOT NULL;
CREATE VIEW College_B_HS_V AS
SELECT * FROM College_B_HS
WHERE Name IS NOT NULL AND FatherName IS NOT NULL AND MotherName IS NOT NULL;
CREATE VIEW College_B_SE_V AS
SELECT * FROM College_B_SE
WHERE Name IS NOT NULL AND FatherName IS NOT NULL AND MotherName IS NOT NULL;
CREATE VIEW College_B_SJ_V AS
SELECT * FROM College_B_SJ
WHERE Name IS NOT NULL AND FatherName IS NOT NULL AND MotherName IS NOT NULL;
-- 12. Procedure to convert names to lowercase in views
DELIMITER //
CREATE PROCEDURE convert_names_to_lowercase()
BEGIN
UPDATE College_A_HS_V SET Name = LOWER(Name), FatherName = LOWER(FatherName), MotherName = LOWER(MotherName);
UPDATE College_A_SE_V SET Name = LOWER(Name), FatherName = LOWER(FatherName), MotherName = LOWER(MotherName);
UPDATE College_A_SJ_V SET Name = LOWER(Name), FatherName = LOWER(FatherName), MotherName = LOWER(MotherName);
UPDATE College_B_HS_V SET Name = LOWER(Name), FatherName = LOWER(FatherName), MotherName = LOWER(MotherName);
UPDATE College_B_SE_V SET Name = LOWER(Name), FatherName = LOWER(FatherName), MotherName = LOWER(MotherName);
UPDATE College_B_SJ_V SET Name = LOWER(Name), FatherName = LOWER(FatherName), MotherName = LOWER(MotherName);
END //
DELIMITER ;
-- 14. Procedure to fetch names of all students from College A
DELIMITER //
CREATE PROCEDURE get_name_collegeA()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE student_name VARCHAR(255);
DECLARE cur CURSOR FOR
SELECT Name FROM (
SELECT Name FROM College_A_HS_V
UNION
SELECT Name FROM College_A_SE_V
UNION
SELECT Name FROM College_A_SJ_V
) AS all_names;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO student_name;
IF done THEN
LEAVE read_loop;
END IF;
SELECT student_name;
END LOOP;
CLOSE cur;
END //
DELIMITER ;
-- 15. Procedure to fetch names of all students from College B
DELIMITER //
CREATE PROCEDURE get_name_collegeB()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE student_name VARCHAR(255);
DECLARE cur CURSOR FOR
SELECT Name FROM (
SELECT Name FROM College_B_HS_V
UNION
SELECT Name FROM College_B_SE_V
UNION
SELECT Name FROM College_B_SJ_V
) AS all_names;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO student_name;
IF done THEN
LEAVE read_loop;
END IF;
SELECT student_name;
END LOOP;
CLOSE cur;
END //
DELIMITER ;
-- 16. Calculate the percentage of career choice for College A and College B
SELECT
'Higher Studies' AS Career_choice,
ROUND((COUNT(CASE WHEN Career='Higher Studies' THEN 1 END) / COUNT(*)) * 100, 4) AS College_A_Percentage,
ROUND((COUNT(CASE WHEN Career='Higher Studies' THEN 1 END) / COUNT(*)) * 100, 4) AS College_B_Percentage
FROM College_A_HS_V, College_B_HS_V
UNION ALL
SELECT
'Self Employed' AS Career_choice,
ROUND((COUNT(CASE WHEN Career='Self Employed' THEN 1 END) / COUNT(*)) * 100, 4) AS College_A_Percentage,
ROUND((COUNT(CASE WHEN Career='Self Employed' THEN 1 END) / COUNT(*)) * 100, 4) AS College_B_Percentage
FROM College_A_SE_V, College_B_SE_V
UNION ALL
SELECT
'Service Job' AS Career_choice,
ROUND((COUNT(CASE WHEN Career='Service Job' THEN 1 END) / COUNT(*)) * 100, 4) AS College_A_Percentage,
ROUND((COUNT(CASE WHEN Career='Service Job' THEN 1 END) / COUNT(*)) * 100, 4) AS College_B_Percentage
FROM College_A_SJ_V, College_B_SJ_V;