Challenge

Project 2

SS Lakshmi Priya11 Jan 2024

6.USE alumni;

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;

7. USE alumni;

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;

8.USE alumni;

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;

9. USE alumni;

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;

10. USE alumni;

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;

11. USE alumni;

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;

14. USE alumni;

DELIMITER $$

CREATE PROCEDURE get_name_collegeA

(

INOUT Name VARCHAR(16000)

)

BEGIN

DECLARE finished INT DEFAULT 0;

DECLARE Namelist VARCHAR(400) DEFAULT " ";

DECLARE Namedetail

CURSOR FOR

SELECT Name FROM college_a_hs;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

OPEN Namedetail;

getName :

LOOP

FETCH Namedetail INTO Namelist;

IF finished = 1 THEN

LEAVE getName;

END IF;

SET Name = CONCAT(Namelist," ",Name);

END LOOP getName;

CLOSE Namedetail;

END $$

DELIMITER ;

SET @Name = "";

CALL get_name_collegeA(@Name);

SELECT @Name;

15. USE alumni;