Project 2

Once a student is passed out from a Institute or College, he/she is known as Alumni of the Institute. Alumni’s career growth plays important role in Institute’s ranking and other networking activities. In this project, career choices of alumni of two Universities will be analyzed with respect to their passing year as well as the course they completed. 

Dataset: Six .csv file (Alumni record of College A and College B) Higher Studies, Self Employed and Service/Job record 

• College_A_HS ~ Higher Studies Record of College A
• College_A_SE ~ Self Employed Record of College A
• College_A_SJ ~ Service/Job Record of College A
• College_B_HS ~ Higher Studies Record of College B
• College_B_SE ~ Self-Employed Record of College B
• College_B_SJ ~ Service/Job respectively Record of College B

Tasks to be performed

1.Create new schema as alumni

ans: CREATE DATABASE alumni ;

2.Import all .csv files into MySQL

ans : All .csv files imported successfully

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

ans : USE alumni;

        DESC college_a_hs ;

        DESC college_a_se ;

        DESC college_a_sj ;

        DESC college_b_hs ;

        DESC college_b_se ;

        DESC 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)

ans : import mysql.connector

Q4 = mysql.connector.connect(host="localhost",

                              user ="root",

                              password ="VIKAS",

                              database="alumni")

cur = Q4.cursor()

cur.execute("SELECT * FROM COLLEGE_A_HS LIMIT 1000")

for i in cur:

    print(i)

cur.execute("SELECT * FROM COLLEGE_A_SE LIMIT 1000")

for i in cur:

    print(i)

cur.execute("SELECT * FROM COLLEGE_A_SJ LIMIT 1000")

for i in cur:

    print(i)

cur.execute("SELECT * FROM COLLEGE_B_HS LIMIT 1000")

for i in cur:

    print(i)

cur.execute("SELECT * FROM COLLEGE_B_SE LIMIT 1000")

for i in cur:

    print(i)

cur.execute("SELECT * FROM COLLEGE_B_SJ LIMIT 1000")

for i in cur:

    print(i)

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) 

ans: Excel file submitted as Q5

6.Perform data cleaning on table College_A_HS and store cleaned data in view College_A_HS_V, Remove null values. 

ans :

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;

7.Perform data cleaning on table College_A_SE and store cleaned data in view College_A_SE_V, Remove null values.

ans: 

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;

8. Perform data cleaning on table College_A_SJ and store cleaned data in view College_A_SJ_V, Remove null values.

ans :

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;

9. Perform data cleaning on table College_B_HS and store cleaned data in view College_B_HS_V, Remove null values.

ans:

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 

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;

10. Perform data cleaning on table College_B_SE and store cleaned data in view College_B_SE_V, Remove null values.

ans:

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 

Batch IS NOT NULL AND 

Degree IS NOT NULL AND 

PresentStatus IS NOT NULL AND 

Organization IS NOT NULL AND 

Location IS NOT NULL;

11. Perform data cleaning on table College_B_SJ and store cleaned data in view College_B_SJ_V, Remove null values.

ans :

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 

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;

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) 

ans :

DELIMITER //

CREATE PROCEDURE LOWERCASE()

BEGIN

SELECT lower(Name) Name,lower(FatherName) FatherName,Lower(MotherName) MotherName FROM college_a_hs_v ;

SELECT lower(Name) Name,lower(FatherName) FatherName,Lower(MotherName) MotherName FROM college_a_se_v ;

SELECT lower(Name) Name,lower(FatherName) FatherName,Lower(MotherName) MotherName FROM college_a_sj_v ;

SELECT lower(Name) Name,lower(FatherName) FatherName,Lower(MotherName) MotherName FROM college_b_hs_v ;

SELECT lower(Name) Name,lower(FatherName) FatherName,Lower(MotherName) MotherName FROM college_b_se_v ;

SELECT lower(Name) Name,lower(FatherName) FatherName,Lower(MotherName) MotherName FROM college_b_sj_v ;

END //

DELIMITER ;

call lowercase();

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. 

ans : answer submitted as excel file named as Q13

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

ans:

DELIMITER //

CREATE PROCEDURE get_name_collegeA()

BEGIN

    -- Declare variables

    DECLARE done INT DEFAULT 0;

    DECLARE student_name VARCHAR(255);

    DECLARE student_names text(65000) default ' ' ;

    DECLARE cur CURSOR FOR

        SELECT name FROM college_a_hs

        UNION ALL

        SELECT name FROM college_a_se

        UNION ALL

        SELECT name FROM college_a_sj;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    FETCH cur INTO student_name;

    WHILE NOT done DO

        SET student_names = CONCAT(student_names, student_name,',');

        FETCH cur INTO student_name;

    END WHILE;

    CLOSE cur;

    SET student_names = SUBSTRING(student_names, 2);

    SELECT student_names;

END;

//

DELIMITER ;

call get_name_collegeA();