Project 2

1. Create a New Schema and Import CSV Files into MySQL:

   • Create a new schema named "alumni" in your MySQL database.
   • Use MySQL's LOAD DATA INFILE statement or any other suitable method to import the six CSV files (College_A_HS, College_A_SE, College_A_SJ, College_B_HS, College_B_SE, College_B_SJ) into corresponding tables within the "alumni" schema. Ensure that the table structures match the CSV file columns.

2. Inspect Table Structures:

   • Run SQL queries to check the structure of the six tables (e.g., DESCRIBE alumni.College_A_HS, DESCRIBE alumni.College_A_SE, etc.).

3. Display First 1000 Rows with Python in Jupyter Notebook:

   • Use Python, Jupyter Notebook, and a library like pandas to connect to the MySQL database.
   • Fetch and display the first 1000 rows of each table (College_A_HS, College_A_SE, College_A_SJ, College_B_HS, College_B_SE, College_B_SJ) using SQL queries.

4. Export Data to MS Excel:

   • Use Python, pandas, and the same database connection to export the records of each table into separate Excel files.
   • Create one Excel file for each table (e.g., College_A_HS.xlsx, College_A_SE.xlsx, etc.).

5. Data Cleaning and View Creation:

   • For each of the six tables, perform data cleaning by removing null values.
   • Create corresponding views for each table with cleaned data. For example, create views College_A_HS_V, College_A_SE_V, etc.

6. Convert Names to Lower Case in Views:

   • Write SQL queries to create procedures that use string functions to convert the records of Name, FatherName, and MotherName into lowercase for each view (College_A_HS_V, College_A_SE_V, etc.).

7. Export Views to MS Excel:

   • Use Python and pandas to export the views (College_A_HS_V, College_A_SE_V, etc.) into separate Excel files.

8. Create Pivot Charts for Location of Alumni:

   • In Excel, import the Excel files containing the views.
   • Create pivot charts to analyze the location of alumni. You may need to have a "location" column in your data to create meaningful pivot charts.

9. Create Procedures to Fetch Names:

   • Write SQL queries to create two procedures (get_name_collegeA and get_name_collegeB) that use a cursor to fetch names of all students from College A and College B.

10. Calculate Career Choice Percentages:

    • Write SQL queries to calculate the percentage of career choices (Higher Studies, Self Employed, and Service/Job) for alumni of College A and College B. Approximate percentages can be calculated by dividing the count of each career choice by the total number of alumni.

These steps provide a high-level overview of how to approach your project. For each step, you'll need to write the specific SQL queries and Python code to accomplish the tasks. Additionally, you should adapt the code to your specific data and environment.