BlogFor BusinessTalk to a counsellor
CoursesIIT ProgramsPlacementsLog in
BlogFor Business
Talk to a counsellor
Challenge

Project 2

VVishal Pandit29 Aug 2022

# Project 2

# Que 1
/*
Create new schema as alumni
*/
drop database if exists alumni;
create database alumni;


# Que 2
/*
Import all .csv files into MySQL

Ans 2
Imported by right click on Schema "alumni"
select 'Table Data Import Wizard'
select path
click next -> next -> next -> next -> next -> finish
data imported
for all six .csv files
*/

# Que 3
/*
Run SQL command to see the structure of six tables
*/
# Ans 3
DESC alumni.college_a_hs;
DESC alumni.college_a_se;
DESC alumni.college_a_sj;
DESC alumni.college_b_hs;
DESC alumni.college_b_se;
DESC alumni.college_b_sj;


# Que 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.

screenshot

# Ans 4
# jupyter code
import mysql.connector
alumnidb = mysql.connector.connect(host = "localhost",
user = "root",
password = "2468",
database = "alumni")

college_a_hscursor = alumnidb.cursor()
college_a_hscursor.execute("SELECT * FROM alumni.college_a_hs limit 1000;")
for i in college_a_hscursor:
print(i)

college_a_secursor = alumnidb.cursor()
college_a_secursor.execute("SELECT * FROM alumni.college_a_se limit 1000;")
for a in college_a_secursor:
print(i)

college_a_sjcursor = alumnidb.cursor()
college_a_sjcursor.execute("SELECT * FROM alumni.college_a_sj limit 1000;")
for a in college_a_sjcursor:
print(i)


college_b_hscursor = alumnidb.cursor()
college_b_hscursor.execute("SELECT * FROM alumni.college_b_hs limit 1000;")
for i in college_b_hscursor:
print(i)


college_b_secursor = alumnidb.cursor()
college_b_secursor.execute("SELECT * FROM alumni.college_b_se limit 1000;")
for a in college_b_secursor:
print(i)

college_b_sjcursor = alumnidb.cursor()
college_b_sjcursor.execute("SELECT * FROM alumni.college_b_sj limit 1000;")
for a in college_b_sjcursor:
print(i)
*/
# for MySQL
SELECT * FROM alumni.college_a_hs limit 1000;
SELECT * FROM alumni.college_a_se limit 1000;
SELECT * FROM alumni.college_a_sj limit 1000;
SELECT * FROM alumni.college_b_hs limit 1000;
SELECT * FROM alumni.college_b_se limit 1000;
SELECT * FROM alumni.college_b_sj limit 1000;


# Que 5
/*
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.
*/

screenshots

# Que 6
/*
Perform data cleaning on table College_A_HS and store cleaned data in view College_A_HS_V, Remove null values.
*/
use alumni;
DROP VIEW IF exists College_A_HS_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 alumni.college_a_hs_v;


# Que 7
/*
Perform data cleaning on table College_A_SE and store cleaned data in view College_A_SE_V, Remove null values.
*/

use alumni;
DROP VIEW IF exists College_A_SE_V;
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 alumni.college_a_se_v;


# Que 8
/*
Perform data cleaning on table College_A_SJ and store cleaned data in view College_A_SJ_V, Remove null values.
*/
use alumni;
DROP VIEW IF exists College_A_SJ_V;
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 alumni.college_a_sj_v;

# Que 9
/*
Perform data cleaning on table College_B_HS and store cleaned data in view College_B_HS_V, Remove null values.
*/
use alumni;
DROP VIEW IF exists College_B_HS_V;
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 alumni.college_b_hs_v;


# Que 10
/*
Perform data cleaning on table College_B_SE and store cleaned data in view College_B_SE_V, Remove null values.
*/
use alumni;
DROP VIEW IF exists College_B_SE_V;
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 alumni.college_b_se_v;

# Que 11
/*
Perform data cleaning on table College_B_SJ and store cleaned data in view College_B_SJ_V, Remove null values.
*/
use alumni;
DROP VIEW IF exists College_B_SJ_V;
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 alumni.college_b_sj_v;


# Que 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)
*/
USE alumni;
DROP procedure IF EXISTS A_HS;
DELIMITER $$
CREATE PROCEDURE A_HS()
BEGIN
SELECT lower(name),lower(FatherName),lower(MotherName) FROM College_A_HS_V;
END $$
DELIMITER ;

call A_HS();

# Que 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.

*/

screenshot

# Que 14
/*
Write a query to create procedure get_name_collegeA using the cursor to fetch names of all students from college A.
*/
USE alumni;
DROP procedure IF EXISTS get_name_collegeA;
DELIMITER $$
CREATE PROCEDURE get_name_collegeA()
BEGIN
SELECT name FROM
college_a_hs
UNION SELECT name FROM
college_a_se
UNION SELECT name FROM
college_a_sj;
END $$
DELIMITER ;

call get_name_collegeA();


/*
# Que 15
Write a query to create procedure get_name_collegeB using the cursor to fetch names of all students from college B.
*/
USE alumni;
DROP procedure IF EXISTS get_name_collegeB;
DELIMITER $$
CREATE PROCEDURE get_name_collegeB()
BEGIN
SELECT name FROM
college_b_hs
UNION SELECT name FROM
college_b_se
UNION SELECT name FROM
college_b_sj;
END $$
DELIMITER ;

call get_name_collegeB();


/*
# Que 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.
*/
use alumni;
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_v)+(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_v) + (select count(*) from college_b_sj))*100 ;

Your portfolio, next

Ship work like this
in your own course.

Talk to a career counsellor

Free 20-minute call. We'll map the right course to where you want to work.

By submitting you agree to our Terms and Privacy Policy.