Project 2
Ques 1 : CREATE DATABASE alumni;
Ques 2 : csv file imported in mysql.
Ques 3 : 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;
Ques 4 : jupyter Notebook Python file allready this project .
Ques 5 : 
Ques 6 : 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;
Ques 7 : 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;
Ques 8 : 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;
Ques 9 : REATE 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;
Ques 10 : 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;
Ques 11 : 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;
Ques 12 : CALL lowercollege_a_hs;
CALL Lowercollege_a_se;
CALL lowercollege_a_sj;
CALL lowercollege_b_hs;
CALL lowercollege_b_se;
CALL lowercollege_b_sj;
Ques 14 :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;
Ques 15 : 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;
Ques 16 : SELECT "HigherStudies" PresentStatus,(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" PresentStatus,(SELECT COUNT(*) FROM college_a_se)/
((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_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" PresentStatus,(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;