Project 2

To tackle this project, we will break it down into several steps, each corresponding to the tasks outlined in the project brief. Here’s a detailed plan for how you can approach each of these steps.

Step 1: Create a New Schema in MySQL

You will need to create a new schema to store alumni-related tables.

SQL Query:

sql
Copy code
CREATE SCHEMA alumni;
USE alumni;

Step 2: Import CSV Files into MySQL

You can use MySQL Workbench or command-line tools to import the six .csv files into the new alumni schema. Ensure that the structure of the .csv files corresponds correctly to the tables.

SQL Queries:

sql
Copy code
LOAD DATA INFILE 'path_to_csv/College_A_HS.csv'
INTO TABLE College_A_HS
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- Similarly for other CSV files:
LOAD DATA INFILE 'path_to_csv/College_A_SE.csv' INTO TABLE College_A_SE;
LOAD DATA INFILE 'path_to_csv/College_A_SJ.csv' INTO TABLE College_A_SJ;
LOAD DATA INFILE 'path_to_csv/College_B_HS.csv' INTO TABLE College_B_HS;
LOAD DATA INFILE 'path_to_csv/College_B_SE.csv' INTO TABLE College_B_SE;
LOAD DATA INFILE 'path_to_csv/College_B_SJ.csv' INTO TABLE College_B_SJ;

Step 3: View Table Structures

After importing the CSV files, run the DESCRIBE command to check the structure of all six tables.

SQL Query:

sql
Copy code
DESCRIBE College_A_HS;
DESCRIBE College_A_SE;
DESCRIBE College_A_SJ;
DESCRIBE College_B_HS;
DESCRIBE College_B_SE;
DESCRIBE College_B_SJ;

Step 4: Display the First 1000 Rows with Python (Jupyter Notebook)

Use Python’s pandas and mysql-connector to fetch and display the first 1000 rows of each table.

Python Code:

python
Copy code
import mysql.connector
import pandas as pd

# Establish the connection
conn = mysql.connector.connect(
    host='localhost',
    user='root',
    password='your_password',
    database='alumni'
)

# Function to fetch and display first 1000 rows of a table
def display_table_data(table_name):
    query = f"SELECT * FROM {table_name} LIMIT 1000"
    df = pd.read_sql(query, conn)
    return df

# Display data for each table
college_a_hs = display_table_data('College_A_HS')
college_a_se = display_table_data('College_A_SE')
college_a_sj = display_table_data('College_A_SJ')
college_b_hs = display_table_data('College_B_HS')
college_b_se = display_table_data('College_B_SE')
college_b_sj = display_table_data('College_B_SJ')

# Display the data
print(college_a_hs.head())
print(college_a_se.head())
print(college_a_sj.head())
print(college_b_hs.head())
print(college_b_se.head())
print(college_b_sj.head())

# Close the connection
conn.close()

Step 5: Import All Records into MS Excel

Use MySQL Workbench’s export feature to export the six tables into Excel or use Python’s pandas to import the data and export to Excel.

Python Code for Export to Excel:

python
Copy code
# Export all the data to Excel
with pd.ExcelWriter('alumni_data.xlsx') as writer:
    college_a_hs.to_excel(writer, sheet_name='College_A_HS', index=False)
    college_a_se.to_excel(writer, sheet_name='College_A_SE', index=False)
    college_a_sj.to_excel(writer, sheet_name='College_A_SJ', index=False)
    college_b_hs.to_excel(writer, sheet_name='College_B_HS', index=False)
    college_b_se.to_excel(writer, sheet_name='College_B_SE', index=False)
    college_b_sj.to_excel(writer, sheet_name='College_B_SJ', index=False)

Step 6-11: Data Cleaning and Storing in Views

You will clean each table by removing NULL values and creating views for cleaned data.

SQL Queries:

sql
Copy code
CREATE VIEW College_A_HS_V AS
SELECT * FROM College_A_HS WHERE column_name IS NOT NULL;

CREATE VIEW College_A_SE_V AS
SELECT * FROM College_A_SE WHERE column_name IS NOT NULL;

CREATE VIEW College_A_SJ_V AS
SELECT * FROM College_A_SJ WHERE column_name IS NOT NULL;

CREATE VIEW College_B_HS_V AS
SELECT * FROM College_B_HS WHERE column_name IS NOT NULL;

CREATE VIEW College_B_SE_V AS
SELECT * FROM College_B_SE WHERE column_name IS NOT NULL;

CREATE VIEW College_B_SJ_V AS
SELECT * FROM College_B_SJ WHERE column_name IS NOT NULL;

Step 12: Create a Procedure to Convert Names to Lower Case

We’ll use a stored procedure to convert Name, FatherName, and MotherName to lowercase.

SQL Query for Procedure:

sql
Copy code
DELIMITER //
CREATE PROCEDURE convert_names_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 ;

Step 13: Import the Views into Excel and Create a Pivot Chart

You can use pandas to export the views into Excel and create pivot charts using Excel’s built-in pivot table feature.

Python Code for Exporting Views:

python
Copy code
# Export views to Excel
with pd.ExcelWriter('alumni_views.xlsx') as writer:
    college_a_hs_v = pd.read_sql('SELECT * FROM College_A_HS_V', conn)
    college_a_se_v = pd.read_sql('SELECT * FROM College_A_SE_V', conn)
    college_a_sj_v = pd.read_sql('SELECT * FROM College_A_SJ_V', conn)
    college_b_hs_v = pd.read_sql('SELECT * FROM College_B_HS_V', conn)
    college_b_se_v = pd.read_sql('SELECT * FROM College_B_SE_V', conn)
    college_b_sj_v = pd.read_sql('SELECT * FROM College_B_SJ_V', conn)

    college_a_hs_v.to_excel(writer, sheet_name='College_A_HS_V', index=False)
    college_a_se_v.to_excel(writer, sheet_name='College_A_SE_V', index=False)
    college_a_sj_v.to_excel(writer, sheet_name='College_A_SJ_V', index=False)
    college_b_hs_v.to_excel(writer, sheet_name='College_B_HS_V', index=False)
    college_b_se_v.to_excel(writer, sheet_name='College_B_SE_V', index=False)
    college_b_sj_v.to_excel(writer, sheet_name='College_B_SJ_V', index=False)

You can then use Excel to create pivot charts based on location.

Step 14 & 15: Procedures to Fetch Names from College A & B

SQL Query:

sql
Copy code
DELIMITER //
CREATE PROCEDURE get_name_collegeA()
BEGIN
    DECLARE finished INT DEFAULT 0;
    DECLARE student_name VARCHAR(255);
    DECLARE cursor_name CURSOR FOR SELECT Name FROM College_A_HS_V;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    OPEN cursor_name;
    read_loop: LOOP
        FETCH cursor_name INTO student_name;
        IF finished THEN 
            LEAVE read_loop;
        END IF;
        SELECT student_name;
    END LOOP;
    CLOSE cursor_name;
END //
DELIMITER ;

-- Similarly, create procedure for College_B

Step 16: Calculate Career Choice Percentages

You can use SQL queries to calculate the percentage of alumni in each category (Higher Studies, Self Employed, Service/Job).

SQL Query:

sql
Copy code
SELECT 
    'Higher Studies' AS career_choice,
    (SELECT COUNT(*) FROM College_A_HS_V) / 
    (SELECT COUNT(*) FROM College_A_HS_V + College_A_SE_V + College_A_SJ_V) * 100 AS percentage_A,
    (SELECT COUNT(*) FROM College_B_HS_V) / 
    (SELECT COUNT(*) FROM College_B_HS_V + College_B_SE_V + College_B_SJ_V) * 100 AS percentage_B

UNION ALL

SELECT 
    'Self Employed',
    (SELECT COUNT(*) FROM College_A_SE_V) / 
    (SELECT COUNT(*) FROM College_A_HS_V + College_A_SE_V + College_A_SJ_V) * 100,
    (SELECT COUNT(*) FROM College_B_SE_V) / 
    (SELECT COUNT(*) FROM College_B_HS_V + College_B_SE_V + College_B_SJ_V) * 100

UNION ALL

SELECT 
    'Service/Job',
    (SELECT COUNT(*) FROM College_A_SJ_V) / 
    (SELECT COUNT(*) FROM College_A_HS_V + College_A_SE_V + College_A_SJ_V) * 100,
    (SELECT COUNT(*) FROM College_B_SJ_V) / 
    (SELECT COUNT(*) FROM College_B_HS_V + College_B_SE_V + College_B_SJ_V) * 100;

This will calculate the percentage of alumni career choices across both colleges.