Project 2
-- ANSWER 1
CREATE SCHEMA alumni;
USE alumni;
-- ANSWER 3
DESC college_a_hs;
DESC college_a_se;
DESC college_a_sj;
DESC college_b_hs;
DESC college_b_se;
DESC college_b_sj;
-- ANSWER 4
-- answer in my python file
-- ANSWER 5
-- answer in my MS EXCEL file
-- ANSWER 6
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);
SELECT * FROM college_A_HS_V;
-- ANSWER 7
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_V;
-- ANSWER 8
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_V;
-- ANSWER 9
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_V;
-- ANSWER 10
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);
SELECT * FROM College_B_SE_V;
-- ANSWER 11
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);
SELECT * FROM College_B_SJ_V;
-- ANSWER 12
SELECT LOWER(Name),LOWER(FatherName),LOWER(MotherName) FROM College_A_HS_V;
SELECT LOWER(Name),LOWER(FatherName),LOWER(MotherName) FROM College_A_Se_V;
SELECT LOWER(Name),LOWER(FatherName),LOWER(MotherName) FROM College_A_Sj_V;
SELECT LOWER(Name),LOWER(FatherName),LOWER(MotherName) FROM College_B_HS_V;
SELECT LOWER(Name),LOWER(FatherName),LOWER(MotherName) FROM College_B_Se_V;
SELECT LOWER(Name),LOWER(FatherName),LOWER(MotherName) FROM College_B_Sj_V;
-- OR
DELIMITER //
CREATE PROCEDURE A_HS_V()
BEGIN SELECT LOWER(Name),LOWER(FatherName),LOWER(MotherName) FROM College_A_HS_V; END //
DELIMITER ;
CALL A_HS_V();
DELIMITER //
CREATE PROCEDURE A_Se_V()
BEGIN SELECT LOWER(Name),LOWER(FatherName),LOWER(MotherName) FROM College_A_Se_V; END //
DELIMITER ;
CALL A_Se_V();
DELIMITER //
CREATE PROCEDURE A_Sj_V()
BEGIN SELECT LOWER(Name),LOWER(FatherName),LOWER(MotherName) FROM College_A_Sj_V; END //
DELIMITER ;
CALL A_Sj_V();
DELIMITER //
CREATE PROCEDURE B_HS_V()
BEGIN SELECT LOWER(Name),LOWER(FatherName),LOWER(MotherName) FROM College_B_HS_V; END //
DELIMITER ;
CALL B_HS_V();
DELIMITER //
CREATE PROCEDURE B_Se_V()
BEGIN SELECT LOWER(Name),LOWER(FatherName),LOWER(MotherName) FROM College_B_Se_V; END //
DELIMITER ;
CALL B_Se_V();
DELIMITER //
CREATE PROCEDURE B_Sj_V()
BEGIN SELECT LOWER(Name),LOWER(FatherName),LOWER(MotherName) FROM College_B_Sj_V; END //
DELIMITER ;
CALL B_Sj_V();
-- ANSWER 13
-- answer in my MS EXCEL file
-- ANSWER 14
DELIMITER $$
CREATE PROCEDURE get_name_collegeA
(INOUT name1 TEXT(40000))
BEGIN
DECLARE finished 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 finished =1;
OPEN namedetail;
getame :
LOOP
FETCH FROM namedetail INTO namelist;
IF finished = 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;
-- ANSWER 15
DELIMITER $$
CREATE PROCEDURE get_name_collegeB
(INOUT name1 TEXT(40000))
BEGIN
DECLARE finished 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 finished =1;
OPEN namedetail;
getname :
LOOP
FETCH FROM namedetail INTO namelist;
IF finished = 1 THEN
LEAVE getname;
END IF;
SET name1 = CONCAT(namelist,";",name1);
END LOOP getname;
CLOSE namedetail;
END $$
DELIMITER ;
SET @Name = "";
CALL get_name_collegeB(@Name);
SELECT @Name Name;
-- ANSWER 16
SELECT "Higher Studies" Present_status,((SELECT COUNT(RollNo) FROM college_a_hs) / ((SELECT COUNT(RollNo) FROM college_a_hs)+
(SELECT COUNT(RollNo) FROM college_a_se)+(SELECT COUNT(RollNo) FROM college_a_sj)))*100 College_A_Percentage,((SELECT COUNT(RollNo) FROM college_b_hs)
/ ((SELECT COUNT(RollNo) FROM college_b_hs) + (SELECT COUNT(RollNo) FROM college_b_se )+(SELECT COUNT(RollNo) FROM college_b_sj)))*100 College_B_Percentage UNION
SELECT "Self Employeed" Present_status,((SELECT COUNT(RollNo) FROM college_a_se) / ((SELECT COUNT(RollNo) FROM college_a_hs)+
(SELECT COUNT(RollNo) FROM college_a_se )+(SELECT COUNT(RollNo) FROM college_a_sj)))*100 College_A_Percentage,((SELECT COUNT(RollNo) FROM college_b_se)
/ ((SELECT COUNT(RollNo) FROM college_b_hs) + (SELECT COUNT(RollNo) FROM college_b_se )+(SELECT COUNT(RollNo) FROM college_b_sj)))*100 College_B_Percentage UNION
SELECT "Service Job" Present_status,((SELECT COUNT(RollNo) FROM college_a_sj) / ((SELECT COUNT(RollNo) FROM college_a_hs)+
(SELECT COUNT(RollNo) FROM college_a_se )+(SELECT COUNT(RollNo) FROM college_a_sj)))*100 College_A_Percentage,((SELECT COUNT(RollNo) FROM college_b_sj)
/ ((SELECT COUNT(RollNo) FROM college_b_hs) + (SELECT COUNT(RollNo) FROM college_b_se )+(SELECT COUNT(RollNo) FROM college_b_sj)))*100 College_B_Percentage;