Project 2

-- Q1. CREATE DATABASE alumni

CREATE DATABASE alumni;

-- Q2. Import all .csv files into MySQL

-- File > Open SQL Script--

-- Q3. Run SQL command to see the structure of six tables

DESCRIBE college_a_hs;

DESCRIBE college_a_se;

DESCRIBE college_a_sj;

DESCRIBE college_b_hs;

DESCRIBE college_b_se;

DESCRIBE college_b_sj;

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

SELECT * FROM college_a_hs LIMIT 1000;

SELECT * FROM college_a_se LIMIT 1000;

SELECT * FROM college_a_sj LIMIT 1000;

SELECT * FROM college_b_hs LIMIT 1000;

SELECT * FROM college_b_se LIMIT 1000;

SELECT * FROM college_b_sj LIMIT 1000;

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

/** Excel > data > SQL Editor > Select schema > select tables > import

-- (Excel file attached)

**/

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

SELECT *

FROM college_a_hs;

-- Let's check for empty strings and null values

SELECT *

FROM college_b_hs

WHERE RollNo = "" OR LastUpdate = "" OR Name = "" OR FatherName = "" OR MotherName = "" OR Branch = "" OR

Batch = "" OR Degree = "" OR PresentStatus = "" OR HSDegree = "" OR EntranceExam = "" OR Institute = "" OR Location = "";

UPDATE college_a_hs

SET LastUpdate = COALESCE(NULLIF(LastUpdate, ''), 'N/A'),

FatherName = COALESCE(NULLIF(FatherName, ''), 'N/A'),

MotherName = COALESCE(NULLIF(MotherName, ''), 'N/A'),

Batch = COALESCE(NULLIF(Batch, ''), 'N/A'),

Degree = COALESCE(NULLIF(Degree, ''), 'N/A'),

PresentStatus = COALESCE(NULLIF(PresentStatus, ''), 'N/A'),

HSDegree = COALESCE(NULLIF(HSDegree, ''), 'N/A'),

EntranceExam = COALESCE(NULLIF(EntranceExam, ''), 'No EXAM'),

Institute = COALESCE(NULLIF(Institute, ''), 'N/A'),

Location = COALESCE(NULLIF(Location, ''), 'N/A');

CREATE VIEW college_a_hs_v as

SELECT * FROM college_a_hs;

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

SELECT * FROM alumni.college_a_se;

-- Let's check for empty strings and null values

SELECT * FROM alumni.college_a_se

WHERE RollNo IS NULL OR LastUpdate IS NULL OR Name IS NULL OR FatherName IS NULL OR MotherName IS NULL OR Batch IS NULL

OR Degree IS NULL OR PresentStatus IS NULL OR Organization IS NULL OR Location IS NULL;

UPDATE college_a_se

SET LastUpdate = COALESCE(NULLIF(LastUpdate, ''), 'N/A'),

Name = COALESCE(NULLIF(Name, ''), 'N/A'),

FatherName = COALESCE(NULLIF(FatherName, ''), 'N/A'),

MotherName = COALESCE(NULLIF(MotherName, ''), 'N/A'),

Batch = COALESCE(NULLIF(Batch, ''), 'N/A'),

Degree = COALESCE(NULLIF(Degree, ''), 'N/A'),

PresentStatus = COALESCE(NULLIF(PresentStatus, ''), 'N/A'),

Organization = COALESCE(NULLIF(Organization, ''), 'N/A'),

Location = COALESCE(NULLIF(Location, ''), 'N/A');

CREATE VIEW college_a_se_v as

SELECT * FROM alumni.college_a_se;

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

SELECT * FROM alumni.college_a_sj;

-- Let's check for empty strings and null values

SELECT * FROM alumni.college_a_sj

WHERE RollNo = "" OR LastUpdate = "" OR Name = "" OR FatherName = "" OR MotherName = "" OR Batch = ""

OR Degree = "" OR PresentStatus = "" OR Organization = "" OR Designation = "" OR Location = "";

UPDATE college_a_sj

SET LastUpdate = COALESCE(NULLIF(LastUpdate, ''), 'N/A'),

Name = COALESCE(NULLIF(Name, ''), 'N/A'),

FatherName = COALESCE(NULLIF(FatherName, ''), 'N/A'),

MotherName = COALESCE(NULLIF(MotherName, ''), 'N/A'),

Batch = COALESCE(NULLIF(Batch, ''), 'N/A'),

Degree = COALESCE(NULLIF(Degree, ''), 'N/A'),

PresentStatus = COALESCE(NULLIF(PresentStatus, ''), 'N/A'),

Organization = COALESCE(NULLIF(Organization, ''), 'N/A'),

Designation = COALESCE(NULLIF(Designation, ''), 'N/A'),

Location = COALESCE(NULLIF(Location, ''), 'N/A');

CREATE VIEW college_a_sj_v as

SELECT * FROM alumni.college_a_sj;

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

SELECT * FROM alumni.college_b_hs;

-- Let's check for empty strings and null values

SELECT *

FROM college_b_hs

WHERE RollNo = "" OR LastUpdate = "" OR Name = "" OR FatherName = "" OR MotherName = "" OR Branch = "" OR

Batch = "" OR Degree = "" OR PresentStatus = "" OR HSDegree = "" OR EntranceExam = "" OR Institute = "" OR Location = "";

UPDATE college_b_hs

SET LastUpdate = COALESCE(NULLIF(LastUpdate, ''), 'N/A'),

Name = COALESCE(NULLIF(Name, ''), 'N/A'),

FatherName = COALESCE(NULLIF(FatherName, ''), 'N/A'),

MotherName = COALESCE(NULLIF(MotherName, ''), 'N/A'),

Branch = COALESCE(NULLIF(Branch, ''), 'N/A'),

Batch = COALESCE(NULLIF(Batch, ''), 'N/A'),

Degree = COALESCE(NULLIF(Degree, ''), 'N/A'),

PresentStatus = COALESCE(NULLIF(PresentStatus, ''), 'N/A'),

HSDegree = COALESCE(NULLIF(HSDegree, ''), 'N/A'),

EntranceExam = COALESCE(NULLIF(EntranceExam, ''), 'No EXAM'),

Institute = COALESCE(NULLIF(Institute, ''), 'N/A'),

Location = COALESCE(NULLIF(Location, ''), 'N/A');

CREATE VIEW college_b_hs_v as

SELECT * FROM college_b_hs;

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

SELECT * FROM alumni.college_b_se;

-- Let's check for empty strings and null values

SELECT * FROM alumni.college_b_se

WHERE RollNo IS NULL OR LastUpdate IS NULL OR Name IS NULL OR FatherName IS NULL OR MotherName IS NULL OR Batch IS NULL

OR Degree IS NULL OR PresentStatus IS NULL OR Organization IS NULL OR Designation IS NULL OR Location IS NULL ;

UPDATE college_b_se

SET LastUpdate = COALESCE(NULLIF(LastUpdate, ''), 'N/A'),

Name = COALESCE(NULLIF(Name, ''), 'N/A'),

FatherName = COALESCE(NULLIF(FatherName, ''), 'N/A'),

MotherName = COALESCE(NULLIF(MotherName, ''), 'N/A'),

Batch = COALESCE(NULLIF(Batch, ''), 'N/A'),

Degree = COALESCE(NULLIF(Degree, ''), 'N/A'),

PresentStatus = COALESCE(NULLIF(PresentStatus, ''), 'N/A'),

Organization = COALESCE(NULLIF(Organization, ''), 'N/A'),

Location = COALESCE(NULLIF(Location, ''), 'N/A');

CREATE VIEW college_b_se_v as

SELECT * FROM alumni.college_b_se;

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

SELECT * FROM alumni.college_b_sj;

-- Let's check for empty strings and null values

SELECT * FROM alumni.college_b_sj

WHERE RollNo = "" OR LastUpdate = "" OR Name = "" OR FatherName = "" OR MotherName = "" OR Batch = ""

OR Degree = "" OR PresentStatus = "" OR Organization = "" OR Designation = "" OR Location = "";

UPDATE college_b_sj

SET LastUpdate = COALESCE(NULLIF(LastUpdate, ''), 'N/A'),

Name = COALESCE(NULLIF(Name, ''), 'N/A'),

FatherName = COALESCE(NULLIF(FatherName, ''), 'N/A'),

MotherName = COALESCE(NULLIF(MotherName, ''), 'N/A'),

Batch = COALESCE(NULLIF(Batch, ''), 'N/A'),

Degree = COALESCE(NULLIF(Degree, ''), 'N/A'),

PresentStatus = COALESCE(NULLIF(PresentStatus, ''), 'N/A'),

Organization = COALESCE(NULLIF(Organization, ''), 'N/A'),

Designation = COALESCE(NULLIF(Designation, ''), 'N/A'),

Location = COALESCE(NULLIF(Location, ''), 'N/A');

CREATE VIEW college_b_sj_v as

SELECT * FROM alumni.college_b_sj;

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

**/

DELIMITER $$

CREATE PROCEDURE ConvertLowerCaseForViews()

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

SELECT Name, FatherName, MotherName

FROM college_a_hs_v;

SELECT Name, FatherName, MotherName

FROM college_a_se;

SELECT Name, FatherName, MotherName

FROM college_a_sj;

SELECT Name, FatherName, MotherName

FROM college_b_hs;

SELECT Name, FatherName, MotherName

FROM college_b_se;

SELECT Name, FatherName, MotherName

FROM college_b_sj;

END$$

DELIMITER ;

CALL ConvertLowerCaseForViews();

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

Done on MS EXCEL. File attached with the submission.

-- Q14. 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()

BEGIN

-- Declare variables

DECLARE student_name VARCHAR(100);

DECLARE done INT DEFAULT 0;

-- Declare a cursor for the SELECT query

DECLARE cur CURSOR FOR

SELECT Name

FROM college_a_hs

UNION

SELECT Name

FROM college_a_se

UNION

SELECT Name

FROM college_a_sj;

-- Declare a handler for when the cursor is done

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

-- Create a temporary table to store names

CREATE TEMPORARY TABLE IF NOT EXISTS temp_names (Name VARCHAR(100));

-- Clear any previous data from the temporary table

DELETE FROM temp_names;

-- Open the cursor

OPEN cur;

-- Fetch and store names in the temporary table

read_loop: LOOP

FETCH cur INTO student_name;

IF done THEN

LEAVE read_loop;

END IF;

-- Insert the name into the temporary table

INSERT INTO temp_names VALUES (student_name);

END LOOP;

-- Close the cursor

CLOSE cur;

-- Return all names as a single result set

SELECT * FROM temp_names;

-- Drop the temporary table

DROP TEMPORARY TABLE temp_names;

END$$

DELIMITER ;

CALL get_name_collegeA();

-- Q15. 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()

BEGIN

DECLARE student_name VARCHAR(55);

DECLARE done INT DEFAULT 0;

DECLARE cur_name CURSOR FOR

SELECT Name FROM college_b_hs

UNION

SELECT Name FROM college_b_se

UNION

SELECT Name FROM college_b_sj;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

CREATE TEMPORARY TABLE IF NOT EXISTS temp_names (NAME VARCHAR(55));

DELETE FROM temp_names;

OPEN cur_name;

new_loop: LOOP

FETCH cur_name INTO student_name;

IF done THEN

LEAVE new_loop;

END IF;

INSERT INTO temp_names VALUES (student_name);

END LOOP;

CLOSE cur_name;

SELECT * FROM temp_names;

DROP TEMPORARY TABLE temp_names;

END$$

DELIMITER ;

CALL get_name_collegeB();

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

**/

WITH

career_data AS (

SELECT 'College A' AS College, PresentStatus

FROM college_a_hs

UNION ALL

SELECT 'College A', PresentStatus

FROM college_a_se

UNION ALL

SELECT 'College A', PresentStatus

FROM college_a_sj

UNION ALL

-- Combine data from all College B tables

SELECT 'College B', PresentStatus

FROM college_b_hs

UNION ALL

SELECT 'College B', PresentStatus

FROM college_b_se

UNION ALL

SELECT 'College B', PresentStatus

FROM college_b_sj)

,

career_summary AS (

-- Count the number of alumni in each career choice for each college

SELECT College, PresentStatus, COUNT(*) AS TotalCount

FROM career_data

GROUP BY College, PresentStatus

),

college_totals AS (

-- Calculate total alumni for each college

SELECT College, SUM(TotalCount) AS CollegeTotal

FROM career_summary

GROUP BY College

)

-- Calculate percentage for each career choice

SELECT cs.College, cs.PresentStatus, cs.TotalCount, ct.CollegeTotal, ROUND((cs.TotalCount / ct.CollegeTotal) * 100, 2) AS Percentage

FROM career_summary cs

JOIN college_totals ct

ON cs.College = ct.College

ORDER BY cs.College, cs.PresentStatus;

-- THANK YOU --