Project 2
For #1,#2,#3,#4 and #5 and #13, documents are submitted
#6.
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
Location IS NOT NULL;
#7.
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
Organization IS NOT NULL
AND
Location IS NOT NULL;
#8.
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;
College B
#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
Institute IS NOT NULL
AND
Location IS NOT NULL;
#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;
#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
Location IS NOT NULL;
#12.
DELIMITER //
CREATE PROCEDURE ConvertNamesToLower()
BEGIN
-- Convert Name, FatherName, and MotherName to lowercase for College_A_HS_V
UPDATE College_A_HS_V
SET Name = LOWER(Name), FatherName = LOWER(FatherName), MotherName = LOWER(MotherName);
-- Convert Name, FatherName, and MotherName to lowercase for College_A_SE_V
UPDATE College_A_SE_V
SET Name = LOWER(Name), FatherName = LOWER(FatherName), MotherName = LOWER(MotherName);
-- Convert Name, FatherName, and MotherName to lowercase for College_A_SJ_V
UPDATE College_A_SJ_V
SET Name = LOWER(Name), FatherName = LOWER(FatherName), MotherName = LOWER(MotherName);
-- Convert Name, FatherName, and MotherName to lowercase for College_B_HS_V
UPDATE College_B_HS_V
SET Name = LOWER(Name), FatherName = LOWER(FatherName), MotherName = LOWER(MotherName);
-- Convert Name, FatherName, and MotherName to lowercase for College_B_SE_V
UPDATE College_B_SE_V
SET Name = LOWER(Name), FatherName = LOWER(FatherName), MotherName = LOWER(MotherName);
-- Convert Name, FatherName, and MotherName to lowercase for College_B_SJ_V
UPDATE College_B_SJ_V
SET Name = LOWER(Name), FatherName = LOWER(FatherName), MotherName = LOWER(MotherName);
END //
DELIMITER ;
#14.
DELIMITER //
CREATE PROCEDURE get_name_collegeA()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE student_name VARCHAR(255);
-- Declare a cursor to fetch student names
DECLARE cur CURSOR FOR
SELECT Name FROM college_a_hs_v;
-- Declare an exit handler for the cursor
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- Initialize an empty result set
SELECT 'No data found' AS Result WHERE done = 0;
OPEN cur;
FETCH cur INTO student_name;
WHILE NOT done DO
-- Process each fetched row
-- You can print or store the student_name here
-- Example: SELECT student_name;
FETCH cur INTO student_name;
END WHILE;
CLOSE cur;
END//
DELIMITER ;
CALL get_name_collegeA();