Project 2

1. Create a new schema as 'alumni'.

CREATE DATABASE alumni;
USE alumni;

2. Import all .csv files into MySQL.

SHOW TABLES;

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

4. Display the 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.

-- Completed and submitted separately as Python file.

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;

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.

-- Completed and submitted separately as an Excel file.

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

CREATE OR REPLACE 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 OR REPLACE 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.

CREATE OR REPLACE 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.

CREATE OR REPLACE 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;

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

CREATE OR REPLACE 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.

CREATE OR REPLACE 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;

12. Make a procedure to use string function(s) for converting records of Name, FatherName, MotherName into lowercase for views.

DELIMITER $$
CREATE PROCEDURE ConvertNamesToLowerCase()
BEGIN
CREATE OR REPLACE VIEW College_A_HS_V AS
SELECT LOWER(Name) AS Name, LOWER(FatherName) AS FatherName, LOWER(MotherName) AS MotherName,
RollNo, LastUpdate, Batch, Degree, PresentStatus, HSDegree, EntranceExam, Institute, Location
FROM College_A_HS;

-- Repeat for other views as needed.
END$$
DELIMITER ;

CALL ConvertNamesToLowerCase();

13. Import the created views into MS Excel and make a pivot chart for the location of alumni.

-- Completed and submitted separately in Excel.

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

DELIMITER $$
CREATE PROCEDURE get_name_collegeA()
BEGIN
CREATE TEMPORARY TABLE temp_names (Name VARCHAR(255));

INSERT INTO temp_names (Name)
SELECT Name FROM College_A_HS_V
UNION ALL
SELECT Name FROM College_A_SE_V;

SELECT * FROM temp_names;
END$$
DELIMITER ;

CALL get_name_collegeA();

15. Write a query to create procedure get_name_collegeB using a cursor to fetch names of all students from College B.

DELIMITER $$
CREATE PROCEDURE get_name_collegeB()
BEGIN
CREATE TEMPORARY TABLE temp_names (Name VARCHAR(255));

INSERT INTO temp_names (Name)
SELECT Name FROM College_B_HS_V;

SELECT * FROM temp_names;
END$$
DELIMITER ;

CALL get_name_collegeB();

16. Calculate the percentage of career choice of College A and College B Alumni.

WITH TotalAlumni AS (
SELECT 'A' AS College, COUNT(*) AS Total FROM College_A_HS
UNION ALL
SELECT 'B', COUNT(*) FROM College_B_HS
),
CareerChoiceCounts AS (
SELECT Career_choice,
SUM(CASE WHEN College = 'A' THEN AlumniCount ELSE 0 END) AS College_A_Alumni,
SUM(CASE WHEN College = 'B' THEN AlumniCount ELSE 0 END) AS College_B_Alumni
FROM (
SELECT 'Higher Studies' AS Career_choice, 'A' AS College, COUNT(*) AS AlumniCount FROM College_A_HS
UNION ALL
SELECT 'Higher Studies', 'B', COUNT(*) FROM College_B_HS
) AS CareerData
GROUP BY Career_choice
)
SELECT Career_choice,
ROUND((College_A_Alumni / (SELECT SUM(Total) FROM TotalAlumni WHERE College = 'A')) * 100, 2) AS College_A_Percentage,
ROUND((College_B_Alumni / (SELECT SUM(Total) FROM TotalAlumni WHERE College = 'B')) * 100, 2) AS College_B_Percentage
FROM CareerChoiceCounts;