Project 2

1.create database alumni;

2.

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;

4.

5.

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;

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;

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;

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;

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;

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;

12.

DELIMITER $$
USE `alumni`$$
CREATE PROCEDURE `lower` ()
BEGIN
select lower(Name) Name,Lower(FatherName) FatherName,Lower(MotherName) MotherName from college_a_hs;
select lower(Name) Name,Lower(FatherName) FatherName,Lower(MotherName) MotherName from college_a_se;
select lower(Name) Name,Lower(FatherName)FatherName,Lower(MotherName) MotherName from college_a_sj;
select lower(Name) Name,Lower(FatherName)FatherName,Lower(MotherName)MotherName from college_b_hs;
select lower(Name)Name,Lower(FatherName)FatherName,Lower(MotherName) MotherName from college_b_se;
select lower(Name) Name,Lower(FatherName) FatherName,Lower(MotherName) MotherName from college_b_sj;
END$$
DELIMITER ;

#Calling Stored Procedure
CALL lower;

13. File attached.

14.