Project 2
- For creating schema alumni: CREATE SCHEMA alumni;
- Through the Graphical user interface of the workbench right click on the table data import wizard and then browse for the path where the file is stored and then import data.
3. use alumni;
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. import mysql.connector
testdb=mysql.connector.connect(host='127.0.0.1',user='root',password='mansi@1234',database='alumni')
testcursor=testdb.cursor()
testcursor.execute("SELECT * FROM college_a_hs LIMIT 1000 ")
a=testcursor.fetchall()
for b in a:
print(b)
Do this for all the existing tables.
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.
An Excel file is attached to the document for the imported tables of the database.
6. 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
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 OR REPLACE 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 OR REPLACE 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 OR REPLACE 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 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 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 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 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 Batch IS NOT NULL
AND Degree 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
DROP PROCEDURE IF EXISTS lowercase;
DELIMITER $$
CREATE PROCEDURE lowercase()
BEGIN
SELECT lower(Name),lower(FatherName),lower(MotherName) from college_a_hs_v;
SELECT lower(Name),lower(FatherName),lower(MotherName) from college_a_se_v;
SELECT lower(Name),lower(FatherName),lower(MotherName) from college_a_sj_v;
SELECT lower(Name),lower(FatherName),lower(MotherName) from college_b_hs_v;
SELECT lower(Name),lower(FatherName),lower(MotherName) from college_b_se_v;
SELECT lower(Name),lower(FatherName),lower(MotherName) from college_b_sj_v;
END $$
DELIMITER;
call lowercase;
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 the location of Alumni.
College_A_HS_V
College_A_SE_V
College_A_SJ_V
College_B_HS_V
College_B_SE_V
College_B_SJ_V
14.Create procedure get_name_collegea using the cursor to fetch names of all students from college A
drop procedure if exists get_name_collegeA;
DELIMITER $$
CREATE PROCEDURE get_name_collegeA
(
INOUT fname TEXT(40000)
)
BEGIN
DECLARE finished INT DEFAULT 0;
DECLARE flist VARCHAR(16000) DEFAULT "";
DECLARE fdetail
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 fdetail;
getadd:
LOOP
FETCH fdetail INTO flist;
IF finished = 1 THEN
LEAVE getadd;
END IF;
SET fname = concat( flist,";",fname);
END LOOP getadd;
CLOSE fdetail;
END $$
DELIMITER ;
set @fname = "" ;
call get_name_collegeA(@fname);
select @fname Name;
15. Create procedure get_name_collegeB using the cursor to fetch names of all students from college B
drop procedure if exists get_name_collegeB;
DELIMITER $$
CREATE PROCEDURE get_name_collegeB
(
INOUT fname TEXT(40000)
)
BEGIN
DECLARE finished INT DEFAULT 0;
DECLARE flist VARCHAR(16000) DEFAULT "";
DECLARE fdetail
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 fdetail;
getadd:
LOOP
FETCH fdetail INTO flist;
IF finished = 1 THEN
LEAVE getadd;
END IF;
SET fname = concat( flist,";",fname);
END LOOP getadd;
CLOSE fdetail;
END $$
DELIMITER ;
set @fname = "" ;
call get_name_collegeB(@fname);
select @fname Name;
16. 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
'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 Employeed',
(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',
(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'
;
