Project 2
/***********************************Project-2*************************************/
/* 1. Create new schema as alumni */
CREATE DATABASE alumni;
----------------------------------------------------------------------------
/* 2. Import all .csv files into MySQL. */
-- 6 .csv file imported in mysql
----------------------------------------------------------------------------
/* 3. Run SQL command to see the structure of six tables */
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. */
-- jupyter Notebook Python file allready this project .
---------------------------------------------------------------------------------
/* 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. */
In the Limit Section Provide 1500 But in my PC not increase these limit so that is i submit this screen
shot
And These Process to import all other 5 table in excel through mysql.
---------------------------------------------------------------------------------
/* 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 EntranceExam IS NOT NULL
AND HSDegree IS NOT NULL
AND Institute IS NOT NULL
AND Location IS NOT NULL;
/* Show the View college_a_hs_v */
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 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;
/* Show the view college_a_se_v */
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. */
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;
/* Show the view college_a_sj_v */
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 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;
/* Show the view college_b_hs_v */
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. */
/* Show the table college_b_se */
SELECT * FROM college_b_se;
/* Create View college_b_se_v */
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;
/* Show the view college_b_se_v */
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 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;
/* Show the View college_b_sj_v */
SELECT * FROM college_b_sj;
----------------------------------------------------------------------------------
/* 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) */
/* 1. college_a_hs_v */
SELECT lower(Name),lower(FatherName),lower(MotherName) FROM
college_a_hs_v;
/* 2. college_a_se_v */
SELECT lower(Name),lower(FatherName),lower(MotherName) FROM
college_a_se_v;
/* 3. college_a_sj_v */
SELECT lower(Name),lower(FatherName),lower(MotherName) FROM
college_a_sj_v;
/* 4. college_b_hs_v */
SELECT lower(Name),lower(FatherName),lower(MotherName) FROM
college_b_hs_v;
/* 5. college_b_se_v */
SELECT lower(Name),lower(FatherName),lower(MotherName) FROM
college_b_se_v;
/* 6. college_b_sj_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. */
-- Excel File allready attch.
----------------------------------------------------------------------------------------
/* 14 Write 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. */
DROP PROCEDURE get_name_collegeB;
DELIMITER $$
CREATE PROCEDURE get_name_collegeB
(
INOUT name1 TEXT(40000)
)
BEGIN
DECLARE na INT DEFAULT 0;
DECLARE namelist VARCHAR(16000) DEFAULT "";
DECLARE namedetail
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 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_collegeB(@Name);
SELECT @Name 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)/
((SELECT count(*) FROM college_a_hs)+(SELECT count(*) FROM college_a_se)+(SELECT count(*) FROM college_a_sj)) * 100
College_A_Percentage,
(SELECT count(*) FROM college_b_hs)/
((SELECT count(*) FROM college_b_hs)+(SELECT count(*) FROM college_b_se)+(SELECT count(*) FROM college_b_sj)) * 100
College_B_Percentage
UNION
SELECT 'Self Employed' ,
(SELECT count(*) FROM college_a_se)/
((SELECT count(*) FROM college_a_se)+(SELECT count(*) FROM college_a_hs)+(SELECT count(*) FROM college_a_sj)) * 100
College_A_Percentage ,
(SELECT count(*) FROM college_b_se)/
((SELECT count(*) FROM college_b_hs)+(SELECT count(*) FROM college_b_se)+(SELECT count(*) FROM college_b_sj)) * 100
College_B_Percentage
UNION
SELECT 'Service Job' ,
(SELECT count(*) FROM college_a_sj)/
((SELECT count(*) FROM college_a_hs)+(SELECT count(*) FROM college_a_se)+(SELECT count(*) FROM college_a_sj)) * 100
College_A_Percentage ,
(SELECT count(*) FROM college_b_sj)/
((SELECT count(*) FROM college_b_hs)+(SELECT count(*) FROM college_b_se)+(SELECT count(*) FROM college_b_sj)) * 100
College_B_Percentage ;
-----------------------------------------------------------------------------------------------------------
Please Find Below project2.sql , project2.xlsx, project2.ipynb these are project 2 file .
-----------------------------------------------------END ------------------------------------------------------