Project 2
# 1.Create new schema as alumni
create schema alumini;
use alumini;
2. Import all .csv files into MySQL
3.Run SQL command to see the structure of six tables
desc college_a_hs;
desc college_a_se;
desc college_a_sj;
desc college_b_hs;
desc college_b_se;
desc college_b_sj;
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. (Submit the solution in jupyter notebook)
import mysql.connector as sc
testdb=sc.connect(host="localhost",user="root",password="123456",database="alumini")
A_HS = "SELECT * FROM college_a_hs LIMIT 1000"
A_SE = "SELECT * FROM college_a_se LIMIT 1000"
A_SJ = "SELECT * FROM college_a_sj LIMIT 1000"
B_HS = "SELECT * FROM college_b_hs LIMIT 1000"
B_SE = "SELECT * FROM college_b_se LIMIT 1000"
B_SJ = "SELECT * FROM college_b_sj LIMIT 1000"
value=testdb.cursor()
value.execute(A_HS)
for x in value:
print(x)
value.execute(A_SE)
for x in value:
print(x)
value.execute(A_SJ)
for x in value:
print(x)
value.execute(B_HS)
for x in value:
print(x)
value.execute(B_SE)
for x in value:
print(x)
value.execute(B_SJ)
for x in value:
print(x)
5. Import all the records of tables (College_A_HS, College_A_SE, College_A_SJ, College_B_HS, College_B_SE, College_B_SJ) into MS Excel. (submit Excel file for this question)
6.Perform data cleaning on table College_A_HS and store cleaned data in view College_A_HS_V, Remove null values.
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;
7.Perform data cleaning on table College_A_SE and store cleaned data in view College_A_SE_V, Remove null values.
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;
8.Perform data cleaning on table College_A_SJ and store cleaned data in view College_A_SJ_V, Remove null values.
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;
9.Perform data cleaning on table College_B_HS and store cleaned data in view College_B_HS_V, Remove null values.
create view College_B_HS_V as (select * 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
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;
10.Perform data cleaning on table College_B_SE and store cleaned data in view College_B_SE_V, Remove null values.
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;
11.Perform data cleaning on table College_B_SJ and store cleaned data in view College_B_SJ_V, Remove null values.
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;\
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 convertNamesToLower()
begin
update college_a_hs_v
set name = lower(name),
fathername = lower(fathername),
mothername = lower(mothername);
update college_a_se_v
set name = lower(name),
fathername = lower(fathername),
mothername = lower(mothername);
update college_a_sj_v
set name = lower(name),
fathername = lower(fathername),
mothername = lower(mothername);
update college_b_hs_v
set name = lower(name),
fathername = lower(fathername),
mothername = lower(mothername);
update college_b_se_v
set name = lower(name),
fathername = lower(fathername),
mothername = lower(mothername);
update college_b_sj_v
set name = lower(name),
fathername = lower(fathername),
mothername = lower(mothername);
end $$
DELIMITER ;
CALL convertNamesToLower() ;
13. Import the created 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) into MS Excel and make pivot chart for location of Alumni
14. Write a query to create procedure get_name_collegeA using the cursor to fetch names of all students from college A.
USE `alumini`;
DROP procedure IF EXISTS `get_name_college`;
DELIMITER $$
USE `alumini`$$
CREATE PROCEDURE get_name_college (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;
getname: LOOP
FETCH from namedetail
INTO namelist;
IF na = 1 then
leave getname;
END IF ;
Set name1 = concat(namelist,";",name1);
End loop getname;
close namedetail;
END$$
DELIMITER ;
set @name= "";
call get_name_college(@name);
select @name name;
15. Write a query to create procedure get_name_collegeB using the cursor to fetch names of all students from college B.
USE `alumini`;
DROP procedure IF EXISTS `get_name_collegeB`;
DELIMITER $$
USE `alumini`$$
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;
getname: LOOP
FETCH from namedetail
INTO namelist;
IF na = 1 then
leave getname;
END IF ;
Set name1 = concat(namelist,";",name1);
End loop getname;
close namedetail;
END$$
DELIMITER ;
set @name= "";
call get_name_collegeB(@name);
select @name name;