BlogFor BusinessTalk to a counsellor
CoursesIIT ProgramsPlacementsLog in
BlogFor Business
Talk to a counsellor
Challenge

Project 2

NNitish Varma18 Oct 2022

/* 1. Create new schema as alumni */

CREATE DATABASE alumni;

USE alumni;

/* 2. Import all .csv files into MySQL */

-- Open alumni schema
-- Right Click on tables
-- Table Data import wizad
-- Select The csv file and import the data

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

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

-- answer is in Jupyter Notebook attached below Mysql_py

/* 5. Import first 1500 rows of tables (College_A_HS, College_A_SE, College_A_SJ, College_B_HS, College_B_SE, College_B_SJ) into MS Excel. */

-- answer is in Jupyter Notebook attached below sql_project_q5

/* 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;

/* 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;


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

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;

/* 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;

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

SELECT * FROM College_B_Se;

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;

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

SELECT * FROM College_B_Sj;

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 AND
RollNo != ' ' AND
LastUpdate != ' ' AND
Name != ' ' AND
FatherName != ' ' AND
MotherName != ' ' AND
Branch != ' ' AND
Batch != ' ' AND
Degree != ' ' AND
PresentStatus != ' ' AND
Organization != ' ' AND
Designation != ' ' AND
Location != ' ' ;

/* 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) */

-- For College_A_HS_V, College_A_SE_V, College_A_SJ_V, College_B_HS_V, College_B_SE_V, College_B_SJ_V
DELIMITER //

CREATE PROCEDURE name_clg_a_hs()
BEGIN
SELECT LOWER(name) AS Name,
LOWER(fathername) AS father_name,
LOWER(mothername) AS Mother_name
FROM College_A_HS_V ;
END //
DELIMITER ;

CALL name_clg_a_hs();

-- For College_A_SE_V

DELIMITER //

CREATE PROCEDURE name_clg_a_se()
BEGIN
SELECT LOWER(name) AS Name,
LOWER(fathername) AS father_name,
LOWER(mothername) AS Mother_name
FROM College_A_SE_V ;
END //
DELIMITER ;

CALL name_clg_a_se();

-- For College_A_SJ_V
DELIMITER //

CREATE PROCEDURE name_clg_a_sj()
BEGIN
SELECT LOWER(name) AS Name,
LOWER(fathername) AS father_name,
LOWER(mothername) AS Mother_name
FROM College_A_SJ_V ;
END //
DELIMITER ;

CALL name_clg_a_sj();

-- For College_B_HS_V
DELIMITER //

CREATE PROCEDURE name_clg_b_hs()
BEGIN
SELECT LOWER(name) AS Name,
LOWER(fathername) AS father_name,
LOWER(mothername) AS Mother_name
FROM College_B_HS_V ;
END //
DELIMITER ;

CALL name_clg_b_hs();

-- For College_B_SE_V
DELIMITER //

CREATE PROCEDURE name_clg_b_se()
BEGIN
SELECT LOWER(name) AS Name,
LOWER(fathername) AS father_name,
LOWER(mothername) AS Mother_name
FROM College_B_SE_V ;
END //
DELIMITER ;

CALL name_clg_b_se();

-- For College_B_SJ_V
DELIMITER //

CREATE PROCEDURE name_clg_b_sj()
BEGIN
SELECT LOWER(name) AS Name,
LOWER(fathername) AS father_name,
LOWER(mothername) AS Mother_name
FROM College_B_SJ_V ;
END //
DELIMITER ;

CALL name_clg_b_sj();

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

-- Answer Excel Sheets is attached below Sql_project_q13

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

DELIMITER /
CREATE PROCEDURE get_name_collegea
(INOUT S_Name TEXT(40000))
BEGIN
DECLARE finish INT DEFAULT 0;
DECLARE f_name VARCHAR(500) DEFAULT "";

DECLARE namedetail
CURSOR FOR
SELECT * FROM
(SELECT name FROM college_a_hs_v
UNION
SELECT name FROM college_a_se_v
UNION
SELECT name FROM college_a_sj_v)temp;

DECLARE CONTINUE HANDLER
FOR NOT FOUND SET finish =1 ;

OPEN namedetail;
getname:
LOOP
FETCH namedetail INTO f_name;
IF finish = 1 THEN
LEAVE getname;
END IF;

SET S_Name = CONCAT(f_name,';',S_Name);
END LOOP getname;
CLOSE namedetail;
END /
DELIMITER ;

SET @S_Name = "";
CALL get_name_collegea(@S_Name);
SELECT @S_Name;

/* 15. Write a query to create procedure get_name_collegeB using the cursor to fetch names of all students from college B. */

DELIMITER /
CREATE PROCEDURE get_name_collegeb
(INOUT B_Name TEXT(40000))
BEGIN
DECLARE finish INT DEFAULT 0;
DECLARE f_name VARCHAR(500) DEFAULT "";

DECLARE namedetail
CURSOR FOR
SELECT * FROM
(SELECT name FROM college_b_hs_v
UNION
SELECT name FROM college_b_se_v
UNION
SELECT name FROM college_b_sj_v)temp;

DECLARE CONTINUE HANDLER
FOR NOT FOUND SET finish =1 ;

OPEN namedetail;
getname:
LOOP
FETCH namedetail INTO f_name;
IF finish = 1 THEN
LEAVE getname;
END IF;

SET B_Name = CONCAT(f_name,';',B_Name);
END LOOP getname;
CLOSE namedetail;
END /
DELIMITER ;

SET @B_Name = "";
CALL get_name_collegeb(@B_Name);
SELECT @B_Name;


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


SELECT 'Higher Studies', ((SELECT COUNT(*) FROM college_a_hs_v )/((SELECT COUNT(*) FROM college_a_hs_v ) + (SELECT COUNT(*) FROM college_a_se_v)+ (SELECT COUNT(*) FROM college_a_sj_v)))*100 college_A_Persentage ,
((SELECT COUNT(*) FROM college_b_hs_v )/((SELECT COUNT(*) FROM college_b_hs_v ) + (SELECT COUNT(*) FROM college_b_se_v)+ (SELECT COUNT(*) FROM college_b_sj_v)))*100 college_B_Persentage UNION
SELECT 'Self Employed', ((SELECT COUNT(*) FROM college_a_se_v )/((SELECT COUNT(*) FROM college_a_hs_v ) + (SELECT COUNT(*) FROM college_a_se_v)+ (SELECT COUNT(*) FROM college_a_sj_v)))*100 college_A_Persentage ,
((SELECT COUNT(*) FROM college_b_se_v )/((SELECT COUNT(*) FROM college_b_hs_v ) + (SELECT COUNT(*) FROM college_b_se_v)+ (SELECT COUNT(*) FROM college_b_sj_v)))*100 college_B_Persentage UNION
SELECT 'HS', ((SELECT COUNT(*) FROM college_a_sj_v )/((SELECT COUNT(*) FROM college_a_hs_v ) + (SELECT COUNT(*) FROM college_a_se_v)+ (SELECT COUNT(*) FROM college_a_sj_v)))*100 college_A_Persentage ,
((SELECT COUNT(*) FROM college_b_sj_v )/((SELECT COUNT(*) FROM college_b_hs_v ) + (SELECT COUNT(*) FROM college_b_se_v)+ (SELECT COUNT(*) FROM college_b_sj_v)))*100 college_B_Persentage ;

Your portfolio, next

Ship work like this
in your own course.

Talk to a career counsellor

Free 20-minute call. We'll map the right course to where you want to work.

By submitting you agree to our Terms and Privacy Policy.