ALUMNI OF THE INSTITUTIONS
AIM: Carrier choices of alumni of two universities will be analyzed with respect to their passing year and course completed.
#Q1
CREATE DATABASE alumni ;
#Q2
#ALL FILES IMPORTED
#Q3
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;
#Q4
#attached screen short for college a hs remaining are same
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_hs LIMIT 1000;
SELECT * FROM college_b_hs LIMIT 1000;
#Q5Perform in excel screen short attached
#Q6
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;
#Q7
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;
#Q8
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;
#Q9
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;
#Q10
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 Batch IS NOT NULL AND Branch 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;
#Q11
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 Batch IS NOT NULL AND Branch 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;
#Q12
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;
#Q13
#Performed in excel screen short attached
#Q14Write a query to create procedure get_name_collegeA using the cursor to fetch
#names of all students from college A
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;
getname:
LOOP
FETCH FROM namedetail INTO namelist;
IF na =1 THEN
LEAVE getname;
END IF;
SET name1 = concat(namelist,";",name1);
END LOOP getname;
CLOSE namedetail;
END $$
SET @Name ="";
CALL get_name_collegeA(@Name);
SELECT @Name Name;
#Q15
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;
getname:
LOOP
FETCH FROM namedetail INTO namelist;
IF na =1 THEN
LEAVE getname;
END IF;
SET name1 = concat(Namelist,";",name1);
END LOOP getname;
CLOSE namedetail;
END $$
SET @Name ="";
CALL get_name_collegeB(@Name);
SELECT @Name Name;
