Alumni Data Management and Analysis

Phase 1: Database and Data Import

1. Created MySQL Schema:

   • Defined a new schema named alumni to store and process alumni data.

2. Imported CSV Files:

   • Loaded all CSV files into MySQL tables.

   • Tables created: College_A_HS, College_A_SE, College_A_SJ, College_B_HS, College_B_SE, College_B_SJ.

3. Checked Table Structures:

   • Executed SQL command DESCRIBE table_name; for each table to confirm structure and data types.

4. Displayed First 1000 Rows Using Python:

   • Used Python (Jupyter Notebook) and the MySQL connector library to fetch and display the first 1000 rows of each table.

5. Exported Data to MS Excel:

   • Extracted all records from the six tables and saved them in an Excel file for further analysis.

---

Phase 2: Data Cleaning and View Creation

1. Performed Data Cleaning:

   • Removed null values from all six tables.

   • Created views for cleaned data:

     • College_A_HS_V

     • College_A_SE_V

     • College_A_SJ_V

     • College_B_HS_V

     • College_B_SE_V

     • College_B_SJ_V

2. Applied String Functions:

   • Created a stored procedure to convert Name, FatherName, and MotherName to lowercase for all views to maintain consistency.

---

Phase 3: Data Analysis and Visualization

1. Imported Views into MS Excel:

   • Exported cleaned views into MS Excel.

   • Created pivot charts to analyze alumni location distribution.

2. Created Stored Procedures for Fetching Student Names:

   • Developed procedures using MySQL cursors to fetch all student names from College A and College B.

   CREATE PROCEDURE get_name_collegeA()
   BEGIN
       DECLARE done INT DEFAULT FALSE;
       DECLARE student_name VARCHAR(255);
       DECLARE cur CURSOR FOR SELECT Name FROM College_A_HS_V UNION SELECT Name FROM College_A_SE_V UNION SELECT Name FROM College_A_SJ_V;
       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;

   • A similar procedure get_name_collegeB was created for College B students.

3. Calculated Career Choice Percentages:

   • Analyzed career distribution for alumni based on categories:

     • Higher Studies

     • Self-Employed

     • Service/Job

   • Used SQL queries to count occurrences and calculate percentages.

   SELECT 'Higher Studies' AS Career_Choice, (COUNT(*) * 100 / (SELECT COUNT(*) FROM College_A_HS_V)) AS Percentage FROM College_A_HS_V
   UNION
   SELECT 'Self Employed', (COUNT(*) * 100 / (SELECT COUNT(*) FROM College_A_SE_V)) FROM College_A_SE_V
   UNION
   SELECT 'Service/Job', (COUNT(*) * 100 / (SELECT COUNT(*) FROM College_A_SJ_V)) FROM College_A_SJ_V;

   • Repeated for College B.

---

Conclusion

This project successfully structured and cleaned alumni data, applied transformation functions, and performed analytical queries to derive insights. The pivot charts and SQL procedures provided a comprehensive view of alumni distribution and career choices, enabling better understanding and decision-making.