Project 2

Q1

Create new schema as alumni

create database alumini;

show databases;

Q2

import csv files

Rightclick alumini-table data import wizard-select path-next-next-finish

Q3

Run SQL command to see the structure of six tables

Table1

1.desc college_a_hs

table 2

desc college_a_se;

3.

desc college_a_sj;

table 4

desc college_b_hs;

5

desc college_b_se;

6

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.

1.College_A_hs 

import mysql.connector
testdb=mysql.connector.connect(host="localhost",user="root",password='1997',database="alumini")
testcursor=testdb.cursor()
testcursor.execute('select * from college_a_hs limit 1000')
for b in testcursor:
print(b)
 

 2. college_a_se;

import mysql.connector
testdb=mysql.connector.connect(host="localhost",user="root",password='1997',database="alumini")
testcursor=testdb.cursor()
testcursor.execute('select * from college_a_se limit 1000')
for b in testcursor:
print(b)

 3. college_A_sj;

import mysql.connector
testdb=mysql.connector.connect(host="localhost",user="root",password='1997',database="alumini")
testcursor=testdb.cursor()
testcursor.execute('select * from college_a_sj limit 1000')
for b in testcursor:
 print(b)
 


4. college_b_hs;
import mysql.connector
testdb=mysql.connector.connect(host="localhost",user="root",password='1997',database="alumini")
testcursor=testdb.cursor()
testcursor.execute('select * from college_b_hs limit 1000')
for b in testcursor:
 print(b)
 

5college_b_se;

import mysql.connector
testdb=mysql.connector.connect(host="localhost",user="root",password='1997',database="alumini")
testcursor=testdb.cursor()
testcursor.execute('select * from college_b_se limit 1000')
for b in testcursor:
print(b)

college_b_sj;

import mysql.connector
testdb=mysql.connector.connect(host="localhost",user="root",password='1997',database="alumini")
testcursor=testdb.cursor()
testcursor.execute('select * from college_b_sj limit 1000')
for b in testcursor:
print(b)

Q5

Import first 1500 rows of tables (College_A_HS, College_A_SE, College_A_SJ, College_B_HS, College_B_SE, College_B_SJ) into MS Excel.

College_A_HS

college_a_se

college_a_sj

college_b_hs;

college_b_se;

college _b_sj;

Q6

Perform data cleaning on table College_A_HS and store cleaned data in view College_A_HS_V, Remove null values. 

#Q6
use alumini;
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);
use alumini;
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.

use alumini;
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;

#Q8
#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;

 #Q9

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;

# Q10

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;

Q11

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;

Q12

 1.

use alumini;

drop procedure if exists CHA_HS;

Delimiter||

Begin

select lower(name),lower(fathername),lower(mothername) from college_a_hs_v;

End

Delimiter||

call CHA_HS();

2. 

use alumini;

drop procedure if exists CHA_SE;

Delimiter||

Begin

select lower(name),lower(fathername),lower(mothername) from college_a_se_v;

End

Delimiter||

call CHA_SE();

3

use alumini;

drop procedure if exists CHA_SJ;

Delimiter||

Begin

select lower(name),lower(fathername),lower(mothername) from college_a_sJ_v;

End

Delimiter||

call CHA_SJ();

4

use alumini;

drop procedure if exists CHB_HS;

Delimiter||

Begin

select lower(name),lower(fathername),lower(mothername) from college_b_hs_v;

End

Delimiter||

call CHB_HS();

5

use alumini;

drop procedure if exists CHB_SE;

Delimiter||

Begin

select lower(name),lower(fathername),lower(mothername) from college_b_se_v;

End

Delimiter||

call CHB_SE();

6

use alumini;

drop procedure if exists CHB_SJ;

Delimiter||

Begin

select lower(name),lower(fathername),lower(mothername) from college_b_sj_v;

End

Delimiter||

call CHB_SJ();

 Q13

for view college_a_hs_v

2. for view college_a_se_v

3. for college view college_a_sj_v

4. for view college_b_hs_v

for view college_b_se_v

for view  college _b_Sj_v

Q14

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

Q15

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 $$
create procedure get_name_collegeB(Inout name varchar(4000))
Begin
declare finished Int default 0;
declare namelist varchar(1000) default "";
declare names 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 names;
getnames:
loop
fetch names into namelist;
if finished =1 then leave getnames;
end if;
set name =concat(namelist,";",name);
end loop getnames;
close names;
end $$
Delimiter ;





set @Name_ =" ";
call get_name_collegeB(@Name_);
select @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',
(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 'Collage 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 'Collage 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 ;