Challenge

Project 2

AAnanya Rao7 Feb 2023

1.Create new schema as alumni

=>create database alumni;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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.

Table:College_A_HS

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

table: College_A_SE

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

table: College_A_SJ

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

table: College_B_HS

import mysql.connector
testdb=mysql.connector.connect(
host="localhost",
user="root",
password="1803",
database="alumni")
testcursor=testdb.cursor()
testcursor.execute("select * from college_b_hs limit 1000")
a=testcursor.fetchall()
for b in a:
print(b)

table: College_B_SE:

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

table:College_B_SJ

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

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.

=>performed in Ms Excel file

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;

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.
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) 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.
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) 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.
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) 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.
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) 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.
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) 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:-

USE `alumni`;
DROP procedure IF EXISTS `alumni`.`converttolower_a_hs`;
;

DELIMITER $$
USE `alumni`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `converttolower_a_hs`()
BEGIN
SELECT LOWER(Name) Name , LOWER(FatherName) FatherName,LOWER(MotherName) MotherName FROM College_A_HS_V;
END$$

DELIMITER ;
;
call converttolower_a_hs;

College_A_SE_V:-

USE `alumni`;
DROP procedure IF EXISTS `alumni`.`converttolower_a_se`;
;

DELIMITER $$
USE `alumni`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `converttolower_a_se`()
BEGIN
SELECT LOWER(Name) Name , LOWER(FatherName) FatherName,LOWER(MotherName) MotherName FROM College_A_SE_V;
END$$

DELIMITER ;

call converttolower_a_se;

College_A_SJ_V:-
USE `alumni`;
DROP procedure IF EXISTS `alumni`.`converttolower_a_sj`;
;

DELIMITER $$
USE `alumni`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `converttolower_a_sj`()
BEGIN
SELECT LOWER(Name) Name , LOWER(FatherName) FatherName,LOWER(MotherName) MotherName FROM College_A_SJ_V;
END$$

DELIMITER ;

call converttolower_a_sj;

College_B_HS_V:-
USE `alumni`;
DROP procedure IF EXISTS `alumni`.`converttolower_b_hs`;
;

DELIMITER $$
USE `alumni`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `converttolower_b_hs`()
BEGIN
SELECT LOWER(Name) Name , LOWER(FatherName) FatherName,LOWER(MotherName) MotherName FROM College_B_HS_V;
END$$

DELIMITER ;

call converttolower_b_hs;

College_B_SE_V:-
USE `alumni`;
DROP procedure IF EXISTS `alumni`.`converttolower_b_se`;
;

DELIMITER $$
USE `alumni`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `converttolower_b_se`()
BEGIN
SELECT LOWER(Name) Name , LOWER(FatherName) FatherName,LOWER(MotherName) MotherName FROM College_B_SE_V;
END$$

DELIMITER ;

call converttolower_b_se;

College_B_SJ_V:-

USE `alumni`;
DROP procedure IF EXISTS `alumni`.`converttolower_b_sj`;
;

DELIMITER $$
USE `alumni`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `converttolower_b_sj`()
BEGIN
SELECT LOWER(Name) Name , LOWER(FatherName) FatherName,LOWER(MotherName) MotherName FROM College_B_SJ_V;
END$$

DELIMITER ;

call converttolower_b_sj;

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.

=>PIVOT CHART IN EXCEL FILE

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_collegeA
(INOUT nameA TEXT(40000))
BEGIN
DECLARE finished 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 finished =1;

OPEN namedetail;
getname :
LOOP
FETCH namedetail INTO namelist;
IF finished = 1 THEN
LEAVE getname;
END IF;
SET nameA = CONCAT(namelist,";",nameA);

END LOOP getname;
CLOSE namedetail;
END $$
DELIMITER ;

SET @NameA = "";
CALL get_name_collegeA (@NameA);
SELECT @NameA Name;

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_collegeB
(INOUT nameB TEXT(40000))
BEGIN
DECLARE finished 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 finished =1;

OPEN namedetail;
getname :
LOOP
FETCH namedetail INTO namelist;
IF finished = 1 THEN
LEAVE getname;
END IF;
SET nameB = CONCAT(namelist,";",nameB);

END LOOP getname;
CLOSE namedetail;
END $$
DELIMITER ;

SET @NameB = "";
CALL get_name_collegeB (@NameB);
SELECT @NameB Name;

16.Calculate the percentage of career choice of College A and College B Alumni
-- (w.r.t Higher Studies, Self Employed and Service/Job)

SELECT "Higher Studies" Higherstudies,
(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 '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 'college_b_percentage'
UNION
SELECT "Self Empolyed" Present_status,
(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 'college_a_percentage',
(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 'college_b_percentage'
UNION
SELECT "Service Job" Present_status,
(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 'college_a_percentage',
(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 'college_b_percentage';

Project 2

Ship work like this
in your own course.

Talk to a career counsellor

Ship work like thisin your own course.

Talk to a career counsellor

Ship work like this
in your own course.