Project 2

Once a student is passed out from a Institute or College, he/she is known as Alumni of the Institute. Alumni’s career growth plays important role in Institute’s ranking and other networking activities. In this project, career choices of alumni of two Universities will be analyzed with respect to their passing year as well as the course they completed.

Dataset: Six .csv file (Alumni record of College A and College B) Higher Studies, Self Employed and Service/Job record

• College_A_HS ~ Higher Studies Record of College A
• College_A_SE ~ Self Employed Record of College A
• College_A_SJ ~ Service/Job Record of College A
• College_B_HS ~ Higher Studies Record of College B
• College_B_SE ~ Higher Studies Record of College B
• College_B_SJ ~ Higher Studies Record of College B

1 question no 4 in python Formet

2 Question no 1,2 and 5 screenshot (rest question same procedure * only for question no 5)

3 rest sql (* question no 13)

-- question no 1
-- screenshort
create database alumni;
-- Question no 2
-- screenshort
-- question no 3 Run SQL command to see the structure of six tables
use alumni;
desc college_a_hs;
desc college_a_se;
desc college_a_sj;
desc college_b_hs;
desc college_b_se;
desc college_b_sj;
-- Question no 4 Display first 1000 rows of tables (College_A_HS, College_A_SE, College_A_SJ,
-- College_B_HS, College_B_SE, College_B_SJ) with Python.
-- Jupyter notebook
-- Question no 5
-- ms excel
-- question no 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;
-- Question no 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;
-- Question no 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;
-- Question no 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;
-- Question no 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;
-- Question no 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;
-- Question no 12
DELIMITER $$
CREATE PROCEDURE Lower()
begin
select LOWER(Name) Name ,LOWER(FatherName) FatherName,LOWER(MotherName) MotherName from college_a_hs_v;
select LOWER(Name) Name ,LOWER(FatherName) FatherName,LOWER(MotherName) MotherName from college_a_se_v;
select LOWER(Name) Name ,LOWER(FatherName) FatherName,LOWER(MotherName) MotherName from college_a_sj_v;
select LOWER(Name) Name ,LOWER(FatherName) FatherName,LOWER(MotherName) MotherName from college_b_hs_v;
select LOWER(Name) Name ,LOWER(FatherName) FatherName,LOWER(MotherName) MotherName from college_b_se_v;
select LOWER(Name) Name ,LOWER(FatherName) FatherName,LOWER(MotherName) MotherName from college_b_sj_V;
end $$
DELIMITER

-- Question no 14
DELIMITER $$
CREATE PROCEDURE get_name_CollegeA
(
INOUT Lname LONGTEXT
)
BEGIN
DECLARE finished int DEFAULT 0;
DECLARE Lnamelist Varchar(16000) DEFAULT "";
Declare Lnamedetail
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 finished= 1 ;
open Lnamedetail;
getLname:Loop
Fetch from Lnamedetail into Lnamelist;
if finished =1 then leave getLname ;
end if ;
set Lname=CONCAT
(Lnamelist,";",Lname);
End Loop getLname;
close Lnamedetail;
end $$
DELIMITER ;
set @Lname="";
call get_name_CollegeA(@Lname);
select @Lname Name;
-- Question no 15
DELIMITER $$
CREATE PROCEDURE get_name_CollegeB
(
INOUT Lname TEXT (4000)
)
BEGIN
DECLARE finished int DEFAULT 0;
DECLARE Lnamelist Varchar(16000) DEFAULT "";
Declare Lnamedetail
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 finished= 1 ;
open Lnamedetail;
getLname:Loop
Fetch from Lnamedetail into Lnamelist;
if finished =1 then leave getLname ;
end if ;
set Lname=CONCAT
(Lnamelist,";",Lname);
End Loop getLname;
close Lnamedetail;
end $$
DELIMITER ;
set @Lname = "";
call get_name_collegeB(@Lname);
select @Lname Name;
-- Question no 16
SELECT "HigherStudies" PresentStatus,(SELECT COUNT(Hsdegree) FROM college_a_hs)/
((SELECT COUNT(*) FROM college_a_hs) + (SELECT COUNT(*) FROM college_a_se) + (SELECT COUNT(*) FROM college_a_sj))*100
College_A_Percentage,
(SELECT COUNT(HsDegree) FROM college_b_hs)/
((SELECT COUNT(*) FROM college_b_hs) + (SELECT COUNT(*) FROM college_b_se) + (SELECT COUNT(*) FROM college_b_sj))*100
College_B_Percentage
UNION
SELECT "Self Employed" PresentStatus,(SELECT COUNT(Organization) FROM college_a_se)/
((SELECT COUNT(*) FROM college_a_hs) + (SELECT COUNT(*) FROM college_a_se) + (SELECT COUNT(*) FROM college_a_sj))*100
College_A_Percentage,
(SELECT COUNT(Organization) FROM college_b_se)/
((SELECT COUNT(*) FROM college_b_hs) + (SELECT COUNT(*) FROM college_b_se) + (SELECT COUNT(*) FROM college_b_sj))*100
College_B_Percentage
UNION
SELECT "Service Job" PresentStatus,(SELECT COUNT(Designation) FROM college_a_sj)/
((SELECT COUNT(*) FROM college_a_hs) + (SELECT COUNT(*) FROM college_a_se) + (SELECT COUNT(*) FROM college_a_sj))*100
College_A_Percentage,
(SELECT COUNT(Designation) FROM college_b_sj)/
((SELECT COUNT(*) FROM college_b_hs) + (SELECT COUNT(*) FROM college_b_se) + (SELECT COUNT(*) FROM college_b_sj))*100
College_B_Percentage;