Project 2

1.

create database alumni;

2.

show tables;

3.

desc college_a_hs;

Field	Type	Null	Key	Default	Extra
RollNo	text	YES
LastUpdate	text	YES
Name	text	YES
FatherName	text	YES
MotherName	text	YES
Batch	text	YES
Degree	text	YES
PresentStatus	text	YES
HSDegree	text	YES
EntranceExam	text	YES
Institute	text	YES
Location	text	YES

desc college_a_se;

Field	Type	Null	Key	Default	Extra
RollNo	text	YES
LastUpdate	text	YES
Name	text	YES
FatherName	text	YES
MotherName	text	YES
Batch	text	YES
Degree	text	YES
PresentStatus	text	YES
Organization	text	YES
Location	text	YES

desc college_a_sj;

Field	Type	Null	Key	Default	Extra
RollNo	text	YES
LastUpdate	text	YES
Name	text	YES
FatherName	text	YES
MotherName	text	YES
Batch	text	YES
Degree	text	YES
PresentStatus	text	YES
Organization	text	YES
Designation	text	YES
Location	text	YES

desc college_b_hs;

Field	Type	Null	Key	Default	Extra
RollNo	text	YES
LastUpdate	text	YES
Name	text	YES
FatherName	text	YES
MotherName	text	YES
Branch	text	YES
Batch	text	YES
Degree	text	YES
PresentStatus	text	YES
HSDegree	text	YES
EntranceExam	text	YES
Institute	text	YES
Location	text	YES

desc college_b_se;

Field	Type	Null	Key	Default	Extra
RollNo	text	YES
LastUpdate	text	YES
Name	text	YES
FatherName	text	YES
MotherName	text	YES
Branch	text	YES
Batch	text	YES
Degree	text	YES
PresentStatus	text	YES
Organization	text	YES
Location	text	YES

desc college_b_sj;

Field	Type	Null	Key	Default	Extra
RollNo	text	YES
LastUpdate	text	YES
Name	text	YES
FatherName	text	YES
MotherName	text	YES
Branch	text	YES
Batch	text	YES
Degree	text	YES
PresentStatus	text	YES
Organization	text	YES
Designation	text	YES
Location	text	YES

4.

import mysql.connector
S = mysql.connector.connect(host = "localhost", user = "root", password = "S@ntho$h!998",database = "alumni")

(i) College_A_HS ~ Higher Studies Record of College A :-

# College_A_HS
project2 = S.cursor()
project2.execute("select * from college_a_hs limit 1000")
for a in project2:
print(a)

(ii) College_A_SE ~ Self Employed Record of College A :-

# College_A_SE
project2 = S.cursor()
project2.execute("select * from college_a_se limit 1000")
for b in project2:
print(b)

(iii) College_A_SJ ~ Service/Job Record of College A :-

# College_A_SJ
project2 = S.cursor()
project2.execute("select * from college_a_sj limit 1000")
for c in project2:
print(c)

(iv) College_B_HS ~ Higher Studies Record of College B :-

# College_B_HS
project2 = S.cursor()
project2.execute("select * from college_b_hs limit 1000")
for d in project2:
print(d)

(v) College_B_SE ~ Higher Studies Record of College B :-

# College_B_SE
project2 = S.cursor()
project2.execute("select * from college_b_se limit 1000")
for e in project2:
print(e)

(vi) College_B_SJ ~ Higher Studies Record of College B :-

# College_B_SJ
project2 = S.cursor()
project2.execute("select * from college_b_sj limit 1000")
for f in project2:
print(f)

5.

Q5 is performed in MS Excel and file is attached in this challenge

(In My Excel, limit maximized upto 100 while importing records from MySql for excel because of that i imported all the records )

6.

create view college_a_hs_v as select * from college_a_hs
where (RollNo <> '' and LastUpdate <> '' and Name <> '' and FatherName <> '' and MotherName <> '' and Batch <> '' and
Degree <> '' and PresentStatus <> '' and HSDegree <> '' and EntranceExam <> '' and Institute <> '' and Location <> ' '
and length(Location) > 0 and Location is not null and Location <> ' ' and Location <> '');

select * from college_a_hs_v;

7.

create view college_a_se_v as select * from college_a_se
where RollNo <> '' and LastUpdate <> '' and Name <> '' and FatherName <> '' and MotherName <> '' and Batch <> '' and
Degree <> '' and PresentStatus <> '' and Organization <> '' and Location <> ' ' and length(Location) > 0
and Location is not null and Location <> ' ' and Location <> '';

select * from college_a_se_v;

8.

create view college_a_sj_v as select * from college_a_sj
where RollNo <> '' and LastUpdate <> '' and Name <> '' and FatherName <> '' and MotherName <> '' and Batch <> '' and
Degree <> '' and PresentStatus <> '' and Organization <> '' and Designation <> '' and Location <> '' and Location <> ' '
and length(Location) > 0 and Location is not null and Location <> ' ' ;

select * from college_a_sj_v;

9.

create view college_b_hs_v as select * from college_b_hs
where (RollNo <> '' and LastUpdate <> '' and Name <> '' and FatherName <> '' and MotherName <> '' and Batch <> '' and
Degree <> '' and PresentStatus <> '' and HSDegree <> '' and EntranceExam <> '' and Institute <> '' and Location <> ' '
and length(Location) > 0 and Location is not null and Location <> ' ' and Location <> '');

select * from college_b_hs_v;

10.

create view college_b_se_v as select * from college_b_se
where RollNo <> '' and LastUpdate <> '' and Name <> '' and FatherName <> '' and MotherName <> '' and Batch <> '' and
Degree <> '' and PresentStatus <> '' and Organization <> '' and Location <> ' ' and length(Location) > 0
and Location is not null and Location <> ' ' and Location <> '';

select * from college_b_se_v;

11.

create view college_b_sj_v as select * from college_b_sj
where RollNo <> '' and LastUpdate <> '' and Name <> '' and FatherName <> '' and MotherName <> '' and Branch <> '' and
Batch <> '' and Degree <> '' and PresentStatus <> '' and Organization <> '' and Designation <> '' and Location <> ' '
and length(Location) > 0 and Location is not null and Location <> ' ' and Location <> '' ;

select * from college_b_sj_v;

12.

Delimiter //
create procedure Q12_lower()
Begin

Select Lower(Name) as Name, Lower(FatherName) as FatherName, Lower(MotherName) as MotherName from college_a_hs_v;
Select Lower(Name) as Name, Lower(FatherName) as FatherName, Lower(MotherName) as MotherName from college_a_se_v;
Select Lower(Name) as Name, Lower(FatherName) as FatherName, Lower(MotherName) as MotherName from college_a_sj_v;
Select Lower(Name) as Name, Lower(FatherName) as FatherName, Lower(MotherName) as MotherName from college_b_hs_v;
Select Lower(Name) as Name, Lower(FatherName) as FatherName, Lower(MotherName) as MotherName from college_b_se_v;
Select Lower(Name) as Name, Lower(FatherName) as FatherName, Lower(MotherName) as MotherName from college_b_sj_v;

end//
Delimiter ;

call Q12_lower();

-------------------------------------------------------------- OR ----------------------------------------------------

Delimiter //
create procedure Q12_low()
Begin

Select RollNo,LastUpdate, Lower(Name) as Name, Lower(FatherName) as FatherName, Lower(MotherName) as MotherName,Batch,
Degree,PresentStatus,HSDegree,EntranceExam,Institute,Location from college_a_hs_v;
Select RollNo,LastUpdate, Lower(Name) as Name, Lower(FatherName) as FatherName, Lower(MotherName) as MotherName, Batch,Degree,PresentStatus,Organization,Location from college_a_se_v;
Select RollNo,LastUpdate, Lower(Name) as Name, Lower(FatherName) as FatherName, Lower(MotherName) as MotherName, Batch,Degree,PresentStatus,Organization,Designation,Location from college_a_sj_v;
Select RollNo,LastUpdate, Lower(Name) as Name, Lower(FatherName) as FatherName, Lower(MotherName) as MotherName, Batch,Degree,PresentStatus,HSDegree,EntranceExam,Institute,Location from college_b_hs_v;
Select RollNo,LastUpdate, Lower(Name) as Name, Lower(FatherName) as FatherName, Lower(MotherName) as MotherName, Branch,Batch,Degree,PresentStatus,Organization,Location from college_b_se_v;
Select RollNo,LastUpdate, Lower(Name) as Name, Lower(FatherName) as FatherName, Lower(MotherName) as MotherName, Branch,Batch,Degree,PresentStatus,Organization,Designation,Location from college_b_sj_v;

end//
Delimiter ;

call Q12_low();

13.

(i) College_A_HS_V :-

(ii) College_A_SE_V :-

(iii) College_A_SJ_V :-

(iv) College_B_HS_V :-

(v) College_B_SE_V :-

(vi) College_B_SJ_V :-

14.

DELIMITER $$
CREATE PROCEDURE get_name_collegeA (INOUT college_a text(500000))
BEGIN
DECLARE Stu_Name INT DEFAULT 0;
DECLARE Namelist varchar(5000) DEFAULT "";
DECLARE get_name 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 Stu_Name = 1;
OPEN get_name;
get_name_collegeA1:
LOOP
FETCH get_name INTO Namelist;
IF Stu_Name = 1 THEN
LEAVE get_name_collegeA1;
END IF;
SET college_a = CONCAT (Namelist," ; ",college_a);
END LOOP get_name_collegeA1;
CLOSE get_name;
END $$
DELIMITER ;

set @college_a = "";
call get_name_collegeA(@college_a);
select @college_a;

15.

DELIMITER $$
CREATE PROCEDURE get_name_collegeB (INOUT college_b text(500000))
BEGIN
DECLARE Stu_Nameb INT DEFAULT 0;
DECLARE Namelistb varchar(5000) DEFAULT "";
DECLARE get_nameb 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 Stu_Nameb = 1;
OPEN get_nameb;
get_name_collegeB1:
LOOP
FETCH get_nameb INTO Namelistb;
IF Stu_Nameb = 1 THEN
LEAVE get_name_collegeB1;
END IF;
SET college_b = CONCAT (Namelistb," ; ",college_b);
END LOOP get_name_collegeB1;
CLOSE get_nameb;
END $$
DELIMITER ;

set @college_b = "";
call get_name_collegeB(@college_b);
select @college_b;

16.

select 'Higher Studies', (select count(*) from college_a_hs) /((select count(*) from college_a_hs) +
(select count(*) from college_a_se) + (select count(*) from college_a_sj)) * 100 as College_A_Percentage,
(select count(*) from college_b_hs) / ((select count(*) from college_b_hs) + (select count(*) from college_b_se) +
(select count(*) from college_b_sj)) * 100 as College_B_Percentage
Union
select 'Self Employed', (select count(*) from college_a_se) /((select count(*) from college_a_hs) +
(select count(*) from college_a_se) + (select count(*) from college_a_sj)) * 100 as College_A_Percentage,
(select count(*) from college_b_se) / ((select count(*) from college_b_hs) + (select count(*) from college_b_se) +
(select count(*) from college_b_sj)) * 100 as College_B_Percentage
Union
select 'Service Job', (select count(*) from college_a_sj) /((select count(*) from college_a_hs) +
(select count(*) from college_a_se) + (select count(*) from college_a_sj)) * 100 as College_A_Percentage,
(select count(*) from college_b_sj) / ((select count(*) from college_b_hs) + (select count(*) from college_b_se) +
(select count(*) from college_b_sj)) * 100 as College_B_Percentage