Project 2
# Ans 1
Create database alumni;
# Ans 2
use alumni;
# College A table Creating
Create table College_A_HS
(
RollNovarchar(20) primary Key not null , LastUpdatedate Not Null, Name varchar(100)Not Null, FatherNamevarchar(100) Not Null, MotherNamevarchar(100), Batch varchar(30) Not Null,
Degree varchar(30) Not Null, PresentStatus varchar(30) Not Null, HSDegree varchar(100) , EntranceExam varchar(100), Institute varchar(100), Location varchar(100)
);
Create table College_A_SE
(
RollNo int primary Key , LastUpdate date Not Null, Name varchar(100) Not Null, FatherName varchar(100) Not Null,
MotherName varchar(100), Batch varchar(30) Not Null, Degree varchar(30) Not Null, PresentStatus varchar(30) Not Null, Organization varchar(100), Location varchar(100)
);
Create table College_A_SJ
(
RollNo int primary Key , LastUpdate date Not Null, Name varchar(100) Not Null, FatherName varchar(100) Not Null, MotherName varchar(100), Batch varchar(30) Not Null,
Degree varchar(30) Not Null, PresentStatus varchar(30) Not Null, HSDegree varchar(100) , Organization varchar(100), Designation varchar(100), Location varchar(100)
);
# College B table Creating
Create table College_B_HS
(
RollNovarchar(50) primary Key , LastUpdatedate Not Null, Name varchar(100)Not Null, FatherNamevarchar(100) Not Null, MotherNamevarchar(100), Branch varchar(30) Not Null, Batch varchar(30) Not Null,
Degree varchar(30) Not Null, PresentStatus varchar(30) Not Null, HSDegree varchar(100) , EntranceExam varchar(100), Institute varchar(100), Location varchar(100)
);
Create table College_B_SE
(
RollNo int primary Key , LastUpdate date Not Null, Name varchar(100) Not Null, FatherName varchar(100) Not Null,
MotherName varchar(100), Batch varchar(30) Not Null, Degree varchar(30) Not Null, PresentStatus varchar(30) Not Null, Organization varchar(100), Location varchar(100)
);
Create table College_V_SJ
(
RollNo varchar(30) primary Key , LastUpdate date Not Null, Name varchar(100) Not Null, FatherName varchar(100) Not Null, MotherName varchar(100), Branch varchar(100) Not Null,
Batch varchar(30) Not Null, Degree varchar(30) Not Null, PresentStatus varchar(30) Not Null, Organization varchar(100), Designation varchar(100), Location varchar(100)
);
# Ans 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;
# Ans 4
# Ans 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 count(*) FROM college_A_HS;
SELECT count(*) FROM college_A_HS_V;
# Ans 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;
# Ans 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;
# Ans 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;
# Ans 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;
# Ans 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;
# Ans 12
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;
# Ans 14
Use Alumni;
DROP PROCEDURE IF EXISTS get_name_collegeA;
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;
getame :
LOOP
FETCH FROM namedetail INTO namelist;
IF na = 1 THEN
LEAVE getame;
END IF;
SET name1 = CONCAT(namelist,";",name1);
END LOOP getame;
CLOSE namedetail;
END $$
DELIMITER ;
SET @Name = "";
CALL get_name_collegeA(@Name);
SELECT @Name "NAVREET KAUR";
# Ans 15
use alumni;
DROP PROCEDURE IF EXISTS get_name_collegeB;
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;
getame :
LOOP
FETCH FROM namedetail INTO namelist;
IF na = 1 THEN
LEAVE getame;
END IF;
SET name1 = CONCAT(namelist,";",name1);
END LOOP getame;
CLOSE namedetail;
END $$
DELIMITER ;
SET @Name = "";
CALL get_name_collegeB(@Name);
SELECT @Name "NAVREET KAUR";