Project 2
## Q1.Create new schema as alumn
create create alumni;
## Q2.Import all .csv files into MySQL
All csv files are imported
use alumni;
## Q3.Run SQL command to see the structure of six tables
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;
desc college_a_hs;
desc college_a_se;
desc college_a_sj;
desc college_b_hs;
desc college_b_se;
desc college_b_sj;
## Q4.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.
##(Submit the solution in jupyter notebook)
import mysql.connector
testdb = mysql.connector.connect(host="localhost",
user = "root",
password = "",
database = "alumni")
testcursor = testdb.cursor()
testcursor.execute("SELECT * FROM college_a_hs LIMIT 1000")
a = testcursor.fetchall()
for b in a:
print(b)
print('-'*100)
import mysql.connector
collegedb = mysql.connector.connect(host="localhost",user="root",password="***",database="alumni")
collegecursor = collegedb.cursor()
collegecursor.execute("select * from college_a_hs limit 1000")
nj = collegecursor.fetchall()
for a in nj :
print(a)
import mysql.connector
collegedb = mysql.connector.connect(host="localhost",user="root",password="Ammukrish",database="alumni")
collegecursor = collegedb.cursor()
collegecursor.execute("select * from college_a_se limit 1000")
nj = collegecursor.fetchall()
for a in nj :
print(a)
import mysql.connector
collegedb = mysql.connector.connect(host="localhost",user="root",password="Ammukrish",database="alumni")
collegecursor = collegedb.cursor()
collegecursor.execute("select * from college_a_sj limit 1000")
nj = collegecursor.fetchall()
for a in nj :
print(a)
import mysql.connector
collegedb = mysql.connector.connect(host="localhost",user="root",password="Ammukrish",database="alumni")
collegecursor = collegedb.cursor()
collegecursor.execute("select * from college_b_hs limit 1000")
nj = collegecursor.fetchall()
for a in nj :
print(a)
import mysql.connector
collegedb = mysql.connector.connect(host="localhost",user="root",password="Ammukrish",database="alumni")
collegecursor = collegedb.cursor()
collegecursor.execute("select * from college_b_se limit 1000")
nj = collegecursor.fetchall()
for a in nj :
print(a)
import mysql.connector
collegedb = mysql.connector.connect(host="localhost",user="root",password="Ammukrish",database="alumni")
collegecursor = collegedb.cursor()
collegecursor.execute("select * from college_b_sj limit 1000")
nj = collegecursor.fetchall()
for a in nj :
print(a)
## Q5 in pdf.
## Q6.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;
Create view college_a_hs_v as select * from college_a_hs Where RollNo IS NOT NULL AND
LastUpdate IS NOT NULL;
#Selecting Created Views
select * from college_a_hs_v;
## Q7.Perform data cleaning on table College_A_SE and store cleaned data in view College_A_SE_V, Remove null values.
select * from 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;
#Selecting Created Views
select * from college_a_se_v;
## Q8.Perform data cleaning on table College_A_SJ and store cleaned data in view College_A_SJ_V, Remove null values.
select * from 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;
#Selecting Created Views
select * from college_a_sj_v;
## Q9.Perform data cleaning on table College_B_HS and store cleaned data in view College_B_HS_V, Remove null values.
select * from 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;
#Selecting Created Views
select * from college_b_hs_v;
## Q10.Perform data cleaning on table College_B_SE and store cleaned data in view College_B_SE_V, Remove null values.
select * from 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;
#Selecting Created Views
select * from college_b_se_v;
## Q11.Perform data cleaning on table College_B_SJ and store cleaned data in view College_B_SJ_V, Remove null values.
select * from 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;
#Selecting Created Views
select * from college_b_sj_v;
## Q12.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)
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;
delimiter $$
create procedure ConvertNameToLower()
begin
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;
end $$
delimiter ;
#Calling Stored procedure
call ConvertNameToLower();
## Q13 in pdf.
## Q14.Write a query to create procedure get_name_collegeA using the cursor to fetch names of all students from college A.
drop procedure 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 Name;
## Q15.Write a query to create procedure get_name_collegeB using the cursor to fetch names of all students from college B.
drop procedure 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 Name;
## Q16.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,
(AVG(college_a_hs.RollNo) / (college_a_hs.RollNo)) * 100 College_A_Percentage,
(AVG(college_b_hs.RollNo) / (college_b_hs.RollNo)) * 100 College_B_Percentage
FROM
college_a_hs
CROSS JOIN
college_b_hs
UNION
SELECT
'Self Empolyment' Career_choice,
(AVG(college_a_se.RollNo) / (college_a_se.RollNo)) * 100 College_A_Percentage,
(AVG(college_b_se.RollNo) / (college_b_se.RollNo)) * 100 College_B_Percentage
FROM
college_a_se
CROSS JOIN
college_b_se
UNION
SELECT
'Service Job' Career_choice,
(AVG(college_a_sj.RollNo) / (college_a_sj.RollNo)) * 100 College_A_Percentage,
(AVG(college_b_sj.RollNo) / (college_b_sj.RollNo)) * 100 College_B_Percentage
FROM
college_a_sj
CROSS JOIN
college_b_sj;