Project 2

-- Ans1--
CREATE SCHEMA alumni;

-- Ans2 -- Imported all csv files into alumni--

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

-- Ans4--
-- Python jupyter file

-- Ans5--
-- Excel file--

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

-- Ans7--
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
Degree IS NOT NULL AND
Organization IS NOT NULL AND
Location IS NOT NULL ;

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

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

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

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

-- Ans12--
DELIMITER $
CREATE PROCEDURE V1()
BEGIN
SELECT LOWER(Name) Name,LOWER(FatherName) FatherName,LOWER(MotherName) MotherName
FROM College_A_HS_V;
END $
DELIMITER ;

CALL V1();

DELIMITER $
CREATE PROCEDURE V2()
BEGIN
SELECT LOWER(Name) Name,LOWER(FatherName) FatherName,LOWER(MotherName) MotherName
FROM College_A_SE_V;
END $
DELIMITER ;

CALL V2();

DELIMITER $
CREATE PROCEDURE V3()
BEGIN
SELECT LOWER(Name) Name,LOWER(FatherName) FatherName,LOWER(MotherName) MotherName
FROM College_A_SJ_V;
END $
DELIMITER ;

CALL V3();

DELIMITER $
CREATE PROCEDURE V4()
BEGIN
SELECT LOWER(Name) Name,LOWER(FatherName) FatherName,LOWER(MotherName) MotherName
FROM College_B_HS_V;
END $
DELIMITER ;

CALL V4();

DELIMITER $
CREATE PROCEDURE V5()
BEGIN
SELECT LOWER(Name) Name,LOWER(FatherName) FatherName,LOWER(MotherName) MotherName
FROM College_A_SE_V;
END $
DELIMITER ;

CALL V5();

DELIMITER $
CREATE PROCEDURE V6()
BEGIN
SELECT LOWER(Name) Name,LOWER(FatherName) FatherName,LOWER(MotherName) MotherName
FROM College_B_SJ_V;
END $
DELIMITER ;

CALL V6();

-- Ans13--
-- excel file--

-- Ans14--

DELIMITER $$
CREATE PROCEDURE get_name_collegeA(inout n text(20000))
BEGIN
DECLARE finished INT DEFAULT 0;
DECLARE namelist VARCHAR(400) DEFAULT '';

DECLARE namedetails 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 namedetails;
getname:
LOOP
FETCH namedetails INTO namelist;
IF finished = 1 THEN
LEAVE getname;
END IF;

SET n = concat(namelist,';',n);
END LOOP getname;
CLOSE namedetails;
END$$
DELIMITER ;

SET @l=' ';
CALL get_name_collegeA(@l);
SELECT @l student_names_get_name_collegeA;

-- Ans15--

DELIMITER $$
CREATE PROCEDURE get_name_collegeB(inout n text(20000))
BEGIN
DECLARE finished INT DEFAULT 0;
DECLARE namelist VARCHAR(400) DEFAULT '';

DECLARE namedetails 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 namedetails;
getname:
LOOP
FETCH namedetails INTO namelist;
IF finished = 1 THEN
LEAVE getname;
END IF;

SET n = concat(namelist,';',n);
END LOOP getname;
CLOSE namedetails;
END$$
DELIMITER ;

SET @l=' ';
CALL get_name_collegeB(@l);
SELECT @l student_names_get_name_collegeB;

-- Ans16--
SELECT "Higher studies" ,((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 Employed" ,((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" ,((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 ;