Project 2
Q1. Create new schema as alumni
create database alumni;
Q2. Import all .csv files into MySQL
Q3. Run SQL command to see the structure of six tables
A) DESC college_a_hs;
B) DESC college_a_se;
C)DESC college_a_sj;
D) DESC college_b_hs;
E) DESC college_b_se;
F) 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.
A)
import mysql.connector
alumnidb = mysql.connector.connect (host = "localhost", user = "root", password = "0311", database = "alumni")
alumnicursor = alumnidb.cursor()
alumnicursor.execute('''SELECT * FROM college_a_hs LIMIT 1000''')
a = alumnicursor.fetchall()
print ('The first 1000 rows of table college_a_hs is : ', a)
B)
import mysql.connector
alumnidb = mysql.connector.connect (host = "localhost", user = "root", password = "0311", database = "alumni")
alumnicursor = alumnidb.cursor()
alumnicursor.execute('''SELECT * FROM college_a_se LIMIT 1000''')
a = alumnicursor.fetchall()
print ('The first 1000 rows of table college_a_se is : ', a)
C)
import mysql.connector
alumnidb = mysql.connector.connect (host = "localhost", user = "root", password = "0311", database = "alumni")
alumnicursor = alumnidb.cursor()
alumnicursor.execute('''SELECT * FROM college_a_sj LIMIT 1000''')
a = alumnicursor.fetchall()
print ('The first 1000 rows of table college_a_sj is : ', a)
D)
import mysql.connector
alumnidb = mysql.connector.connect (host = "localhost", user = "root", password = "0311", database = "alumni")
alumnicursor = alumnidb.cursor()
alumnicursor.execute('''SELECT * FROM college_b_hs LIMIT 1000''')
a = alumnicursor.fetchall()
print ('The first 1000 rows of table college_b_hs is : ', a)
E)
import mysql.connector
alumnidb = mysql.connector.connect (host = "localhost", user = "root", password = "0311", database = "alumni")
alumnicursor = alumnidb.cursor()
alumnicursor.execute('''SELECT * FROM college_b_se LIMIT 1000''')
a = alumnicursor.fetchall()
print ('The first 1000 rows of table college_b_se is : ', a)
F)
import mysql.connector
alumnidb = mysql.connector.connect (host = "localhost", user = "root", password = "0311", database = "alumni")
alumnicursor = alumnidb.cursor()
alumnicursor.execute('''SELECT * FROM college_b_sJ LIMIT 1000''')
a = alumnicursor.fetchall()
print ('The first 1000 rows of table college_b_sJ is : ', a)
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.
A) College_A_HS
B) College_A_SE
C) College_A_SJ
D) College_B_HS
E) College_B_SE
F)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.
CREATE OR REPLACE VIEW College_A_HS_V AS
SELECT * FROM college_A_hs
WHERE RollNo IS NOT NULL
OR LastUpdate IS NOT NULL
OR Name IS NOT NULL
OR FatherName IS NOT NULL
OR MotherName IS NOT NULL
OR Batch IS NOT NULL
OR Degree IS NOT NULL
OR PresentStatus IS NOT NULL
OR HSDegree IS NOT NULL
OR EntranceExam IS NOT NULL
OR Institute IS NOT NULL
OR Location IS NOT NULL;
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.
CREATE OR REPLACE VIEW College_A_SE_V AS
SELECT * FROM college_a_se
WHERE RollNo IS NOT NULL
OR LastUpdate IS NOT NULL
OR Name IS NOT NULL
OR FatherName IS NOT NULL
OR MotherName IS NOT NULL
OR Batch IS NOT NULL
OR Degree IS NOT NULL
OR PresentStatus IS NOT NULL
OR Organization IS NOT NULL
OR 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 OR REPLACE VIEW College_A_SJ_V AS
SELECT * FROM college_a_sj
WHERE RollNo IS NOT NULL
OR LastUpdate IS NOT NULL
OR Name IS NOT NULL
OR FatherName IS NOT NULL
OR MotherName IS NOT NULL
OR Batch IS NOT NULL
OR Degree IS NOT NULL
OR PresentStatus IS NOT NULL
OR Organization IS NOT NULL
OR Location IS NOT NULL ;
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.
CREATE VIEW College_B_HS_V AS
SELECT * FROM college_B_hs
WHERE RollNo IS NOT NULL
OR LastUpdate IS NOT NULL
OR Name IS NOT NULL
OR FatherName IS NOT NULL
OR MotherName IS NOT NULL
OR Batch IS NOT NULL
OR Degree IS NOT NULL
OR PresentStatus IS NOT NULL
OR HSDegree IS NOT NULL
OR EntranceExam IS NOT NULL
OR Institute IS NOT NULL
OR 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 OR REPLACE 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 OR REPLACE 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 Location IS NOT NULL;
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)
A)
DELIMITER |
CREATE PROCEDURE hs()
BEGIN
SELECT LOWER(Name) Name, LOWER(FatherName) FatherName, LOWER(MotherName) MotherName FROM college_a_hs_v;
END |
DELIMITER ;
DROP PROCEDURE hs;
B)
DELIMITER |
CREATE PROCEDURE se()
BEGIN
SELECT LOWER(Name) Name, LOWER(FatherName) FatherName, LOWER(MotherName) MotherName FROM college_a_se_v;
END |
DELIMITER ;
CALL se();
DROP PROCEDURE se;
C)
DELIMITER |
CREATE PROCEDURE sj()
BEGIN
SELECT LOWER(Name) Name, LOWER(FatherName) FatherName, LOWER(MotherName) MotherName FROM college_a_sj_v;
END |
DELIMITER ;
CALL sj();
DROP PROCEDURE sj;
D)
DELIMITER |
CREATE PROCEDURE hsb()
BEGIN
SELECT LOWER(Name) Name, LOWER(FatherName) FatherName, LOWER(MotherName) MotherName FROM college_b_hs_v;
END |
DELIMITER ;
CALL hsb();
DROP PROCEDURE hsb;
E)
DELIMITER |
CREATE PROCEDURE seb()
BEGIN
SELECT LOWER(Name) Name, LOWER(FatherName) FatherName, LOWER(MotherName) MotherName FROM college_b_se_v;
END |
DELIMITER ;
CALL seb();
DROP PROCEDURE seb;
F)
DELIMITER |
CREATE PROCEDURE sjb()
BEGIN
SELECT LOWER(Name) Name, LOWER(FatherName) FatherName, LOWER(MotherName) MotherName FROM college_b_sj_v;
END |
DELIMITER ;
CALL sjb();
DROP PROCEDURE sjb;
Q13. 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.
A)
B)
C)
D)
E)
F)
Q14. 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 Studentname TEXT(40000))
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE namelist VARCHAR(400) 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 Studentname= CONCAT(namelist," , ",Studentname);
END LOOP getname;
CLOSE namedetail;
END $$
DELIMITER ;
SET @Studentname="";
CALL get_name_collegeA(@Studentname);
SELECT @Studentname;
Q15. 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 Studentname TEXT(40000))
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE namelist VARCHAR(400) 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 Studentname= CONCAT(namelist," , ",Studentname);
END LOOP getname;
CLOSE namedetail;
END $$
DELIMITER ;
SET @Studentname="";
CALL get_name_collegeB(@Studentname);
SELECT @Studentname;
Q16.Calculate the percentage of career choice of College A and College B 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)+(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 ;
