Project 2

1.Create new schema as alumni

CREATE DATABASE Alumni;

2.Import all .csv files into MySQL

Imported

3.Run SQL command to see the structure of six tables

USE Alumni;
DESCRIBE College_A_HS;
DESCRIBE College_A_SE;
DESCRIBE College_A_SJ;
DESCRIBE College_B_HS;
DESCRIBE College_B_SE;
DESCRIBE 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)

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) 

• Open Microsoft Excel.
• Go to the "Data" tab and select "Get Data"
• Choose "From Other Sources" and then select "ODBC."
• In the "ODBC Data Source Administrator" dialog, "AlumniDB".
• MySQL database credentials (username and password) when prompted.
• After authentication, see the Navigator dialog.
• In the Navigator dialog, expand the list of tables to locate and select the first table you want to export College_A_HS.
• Click "Load" to import the data into Excel.
• The data from the selected table will be loaded into a new worksheet in Excel.
• Repeat the above steps for each of the remaining tables (College_A_SE, College_A_SJ, College_B_HS, College_B_SE, College_B_SJ). Each table will be loaded into a separate worksheet.

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

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

USE Alumni;
CREATE VIEW College_A_SJ_V AS
SELECT *
FROM 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 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
      Branch 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.

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

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

SELECT LOWER(Name), LOWER(FatherName), LOWER(MotherName) FROM College_A_HS_V;
SELECT LOWER(Name), LOWER(FatherName), LOWER(MotherName) FROM College_A_Se_V;
SELECT LOWER(Name), LOWER(FatherName), LOWER(MotherName) FROM College_A_Sj_V;
SELECT LOWER(Name), LOWER(FatherName), LOWER(MotherName) FROM College_B_HS_V;
SELECT LOWER(Name), LOWER(FatherName), LOWER(MotherName) FROM College_B_Se_V;
SELECT LOWER(Name), LOWER(FatherName), LOWER(MotherName) FROM College_B_Sj_V;

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.

•  Open Microsoft Excel.
• Go to the "Data" tab and select "Get Data"
• Choose "From Other Sources" and then select "ODBC."
• In the "ODBC Data Source Administrator" dialog,  choose "AlumniDB"
• Enter  MySQL database credentials (username and password) when prompted.
• After authentication, see the Navigator dialog.
• Select each of the 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) one by one and click "Load" to import the data into Excel.
• After loading each view, they will be loaded into separate worksheets.
• Create a new worksheet where  to combine the data from all views.
• In the new worksheet, go to the "Data" tab, click on "Get Data," and select "Combine Queries."
• Choose "Append" and select the queries representing each view 
• Click "OK" to combine the data.
• Select the combined data or the data from a single view
• Go to the "Insert" tab and click on "PivotChart."
• In the "Create PivotChart" dialog, choose the chart type that to use to display the location of alumni. For geographical data, a map chart or a geographical heat map might be suitable.
• Click "OK" to create the PivotChart.
• In the PivotChart Field List on the right, add the "Location" field (or the equivalent field from your views) to the appropriate area in the PivotChart. This field should represent the alumni's location.
• Customize the chart appearance, labels, and any other chart settings as needed.
• PivotChart displaying the location of alumni from the imported views is now ready.
• Save the Excel file to your desired location.
• To refresh the data in the PivotChart .

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

   DROP PROCEDURE 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

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.