Project 2

answer -4

Answer - 5

answer 13

(5,13) answer in excel worksheet

-- Question 1

create schema alumni;

--- Question 2

use alumni;

desc college_a_sj;

show tables;

-- Question 3
-- Run SQL command to see the structure of six tables

desc college_a_hs;
desc college_a_se;
desc college_a_sj;
describe college_b_hs;
describe college_b_se;
describe college_b_sj;

# Question - 4
--
/* testdb = mysql.connector.connect(host="localhost",user ="root",password ="###", database ="alumni")
testcursor= testdb.cursor()
testcursor.execute("select * from college_a_se limit 1000")
for x in testcursor:
print(x)
print("n")
testdb = mysql.connector.connect(host="localhost",user ="root",password ="Anita@12345", database ="alumni")
testcursor= testdb.cursor()
testcursor.execute("select * from college_hs limit 1000")
for x in testcursor:
print(x)
print("n")

follow on for all file */

-- Question 5

-- excel file

-- Question 6
-- Perform data cleaning on table College_A_HS and store cleaned data in
-- view College_A_HS_V, Remove null values.
select * from college_a_hs limit 10;
desc college_a_hs;
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;
select count(*) from College_A_HS_V;
select Count(*) from college_a_hs;

-- Question 7
-- Perform data cleaning on table College_A_SE and store
-- cleaned data in view College_A_SE_V, Remove null values.
use alumni;
desc college_a_se;
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;
select count(*) from college_a_se;
select count(*) from College_A_SE_V;

-- Question 8
-- Perform data cleaning on table College_A_SJ and store cleaned data in
-- view College_A_SJ_V, Remove null values.
desc college_a_sj;
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;
select count(*) from college_a_sj;
select count(*) from college_A_SJ_V;

-- Question 9
-- Perform data cleaning on table College_B_HS and store cleaned data in
-- view College_B_HS_V, Remove null values.

desc college_b_hs;

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;
select count(*) from college_b_hs;
select count(*) from College_B_HS_V;

-- Question 10
-- Perform data cleaning on table College_B_SE and store cleaned data in
-- view College_B_SE_V, Remove null values.
desc college_b_se;
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;
select count(*) from College_B_SE_V;
select count(*) from College_b_se;

-- Question 11
-- Perform data cleaning on table College_B_SJ and store cleaned data in
-- view College_B_SJ_V, Remove null values.
desc college_b_sj;
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;
select count(*) from College_B_SJ_V;
select count(*) from College_b_sj;

-- Question 12
-- Make procedure to use string function/s for converting record of Name,
-- FatherName, MotherName into lower case for views (College_A_HS_V,
-- College_A_SE_V, College_A_SJ_V, College_B_HS_V, College_B_SE_V, College_B_SJ_V)

delimiter &&
create procedure College_A_HS_P_try()
begin
select Name ,fatherName, MotherName from College_A_HS_V;
end &&
delimiter ;

call College_A_HS_p_try;

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

;

-- Question 14
-- Write a query to create procedure get_name_collegeA using the cursor
-- to fetch names of all students from college A.

DELIMITER &&
CREATE PROCEDURE get_name_college_A ( INOUT Lname TEXT(50000) )
BEGIN
DECLARE finished INT DEFAULT 0;
DECLARE Lnamelist VARCHAR (16000) DEFAULT "";
DECLARE Lnamedetails
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 Lnamedetails;
getname1: LOOP FETCH Lnamedetails INTO Lnamelist;
IF finished =1
THEN
LEAVE getname1;
END IF;
SET Lname = CONCAT (Lnamelist,";",Lname);

END LOOP getname1;
CLOSE Lnamedetails;
END &&

DELIMITER ;

SET @name1="";
CALL get_name_college_A(@name1);
SELECT @name1 Name;
DELIMITER ;

-- Question 15
-- Write a query to create procedure get_name_collegeB using the cursor
-- to fetch names of all students from college B.

DELIMITER &&
CREATE PROCEDURE get_name_college_B ( INOUT Fname TEXT(10000) )
BEGIN
DECLARE finished INT DEFAULT 0;
DECLARE Fnamelist VARCHAR (8000) DEFAULT "";
DECLARE Fnamedetails 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 Fnamedetails;
getname: LOOP FETCH Fnamedetails INTO Fnamelist;
IF finished =1
THEN
LEAVE getname;
END IF;
SET Fname = CONCAT (Fnamelist,";",Fname);
END LOOP getname;
CLOSE Fnamedetails;
END&&

DELIMITER ;

SET @name="";
CALL get_name_college_B(@name);
SELECT @name Name;

-- Question 16
-- Calculate the percentage of career choice of College A and College B Alumni
-- (w.r.t Higher Studies, Self Employed and Service/Job)
-- Note: Approximate percentages are considered for career choices.

select "Higher Studies" career_Choice ,(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 ,
(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
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 ,
(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 ;