Project 2
Solutions
1. create database alumni;
2. Imported done through MySQL for Excel
Selected csv file data > Export excel data to New Table
3. Select*from college_a_hs;
Select*from college_a_se;
Select*from college_a_sj;
Select*from college_b_hs;
Select*from college_b_se;
Select*from college_b_sj;
4. (ipynb) file attached
5. Excel file attached
6. create view College_A_HS_V as select college_a_hs_id2,rollNo,LastUpdate,Name,FatherName,MotherName,Batch,Degree,PresentStatus
,HSdegree,EntranceExam,Institute,Location from college_a_hs
where college_a_hs_id2 is not null
and 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 college_a_se_id,rollNo,LastUpdate,Name,FatherName,MotherName,Batch,Degree,PresentStatus
,Organization,Location from college_a_se
where college_a_se_id is not null
and 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 college_a_sj_id,rollNo,LastUpdate,Name,FatherName,MotherName,Batch,Degree,PresentStatus
,Organization,Designation,location from college_a_sj
where college_a_sj_id is not null
and 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 college_b_hs_id,rollNo,LastUpdate,Name,FatherName,MotherName,Batch,Degree,PresentStatus
,HSdegree,EntranceExam,Institute,Location from college_b_hs
where college_b_hs_id is not null
and 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_B_HS_V;
10. create view College_B_SE_V as select college_b_se_id,rollNo,LastUpdate,Name,FatherName,MotherName,Branch,Batch,Degree,PresentStatus
,Organization,Location from college_b_se
where college_b_se_id is not null
and 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 college_b_sj_id,rollNo,LastUpdate,Name,FatherName,MotherName,Branch,Batch,Degree,PresentStatus
,Organization,Designation,location from college_b_sj
where college_b_sj_id is not null
and 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 |
create procedure College_A_HS_P()
begin
select lower(Name),lower(FatherName),lower(MotherName)from College_A_HS_V;
end |
delimiter ;
call College_A_HS_P();
delimiter |
create procedure College_A_SE_P()
begin
select lower(Name),lower(FatherName),lower(MotherName)from College_A_SE_V;
end |
delimiter ;
call College_A_SE_P();
delimiter |
create procedure College_A_SJ_P()
begin
select lower(Name),lower(FatherName),lower(MotherName)from College_A_SJ_V;
end |
delimiter ;
call College_A_SJ_P();
delimiter |
create procedure College_B_HS_P()
begin
select lower(Name),lower(FatherName),lower(MotherName)from College_B_HS_V;
end |
delimiter ;
call College_B_HS_P();
delimiter |
create procedure College_B_SE_P()
begin
select lower(Name),lower(FatherName),lower(MotherName)from College_B_SE_V;
end |
delimiter ;
call College_B_SE_P();
delimiter |
create procedure College_B_SJ_P()
begin
select lower(Name),lower(FatherName),lower(MotherName)from College_B_SJ_V;
end |
delimiter ;
call College_B_SJ_P();
13. Excel file attached
14. 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;
select *From college_a_hs;
close namedetail;
end$$
DELIMITER ;
SET @name = "" ;
CALL get_name_collegeA(@name);
select @name name;
15. delimiter $$
create procedure get_name_collegeB
(
inout Name varchar(16000)
)
begin
declare finished int default 0;
declare Namelist varchar(400) default "";
declare namedetail
cursor for
select name from college_b_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;
select *From college_b_hs;
close namedetail;
end$$
DELIMITER ;
SET @name = "" ;
CALL get_name_collegeB(@name);
select @name name;
16. select (count(college_a_hs.PresentStatus)/ (count(college_a_hs.PresentStatus) + count(college_a_se.PresentStatus)
+ count(college_a_sj.PresentStatus)))*100 from college_a_hs,college_a_se,college_a_sj;