Project 2

# Answer1
-- Created Schema alumni
use alumni;

# Answer2
-- Imported all six .csv files

#Answer3
-- Description of all six tables of schema alumni
DESC college_a_se;
DESC college_a_hs;
DESC college_a_sj;
DESC college_b_hs;
DESC college_b_se;
DESC college_b_sj;

# Answer4
/* import mysql.connector
testdb = mysql.connector.connect(host="localhost", user="root", password ="2408", database="alumni")
testcursor = testdb.cursor()
testcursor.execute("SELECT * FROM college_a_se LIMIT 1000")
for x in testcursor:
/* print(x)
-- Same command for the remaining 5 tables with respective table names. Please find the attached python file

# Answer5
-- NOTE: For connectimg to the excel, I will have to uninstall MYSQL and
-- download a downgraded version which was done for challenge-11. After the challenge-11 was completed,
-- I uninstalled the downgraded version and again installed the latest version.
-- Hence, repeating the same process again will be time consuming. So skipping this question.

# Answer6
CREATE VIEW College_A_HS_V AS SELECT * from college_a_hs where
EntranceExam NOT IN('NULL', '', '-', '--', '--------','None','.','....','nil','No entrance test given','Hhh','NA','No',
'N/A','__','No test','...','Don’t know', 'no entrance','N-A','Nothing','not given', '..',
'No entrance test','?','No entrance. Test','Nill') AND HSDegree <> '' AND HSDegree <> '-';

SELECT * FROM College_A_HS_V;

# Answer7
SELECT count(*) FROM college_a_se;
SELECT DISTINCT Organization from college_a_se;
CREATE VIEW College_A_SE_V AS SELECT * from college_a_se where Organization <> '' AND
Organization <> '-' AND organization <> 'NA' AND organization <> 'None' AND
Location <> '';

SELECT * FROM College_A_SE_V;

# Answer8
CREATE VIEW College_A_SJ_V AS SELECT * FROM college_a_sj
where Organization <> '' AND Organization <> '.' AND Organization <> 'No one'
AND Designation <> 'None' AND Designation <> 'Nothing' AND Designation <> '.'
AND Designation <> '';

SELECT * FROM College_A_SJ_V;

# Answer9
CREATE VIEW College_B_HS_V AS SELECT * FROM college_b_hs WHERE HSDegree <> '' AND
HSDegree <> 'NA' AND Institute <> 'NA' AND Institute <> '' AND Institute <> '-'
AND EntranceExam NOT IN ('', 'nil', 'No', 'no test','Not required','NW','NA');

SELECT * FROM College_B_HS_V;

# Answer10
CREATE VIEW College_B_SE_V AS SELECT * FROM college_b_se WHERE Organization <> '' AND
Organization <> 'a@w';

SELECT * FROM College_B_SE_V;

# Answer11
CREATE VIEW College_B_SJ_V AS SELECT * FROM college_b_sj WHERE Organization <> ''
AND Designation NOT IN ('', '..', '...', 'NA', 'none', 'Not known yet') AND
Location <> '' AND Location <> 'Not given yet';

SELECT * FROM College_B_SJ_V;

# Answer12
Call lower_names ('college_b_sj_v');

# Answer13
-- NOTE: For connecting to the excel, I will have to uninstall MYSQL and
-- download a downgraded version which was done for challenge-11. After the challenge-11 was completed,
-- I uninstalled the downgraded version and again installed the latest version.
-- Hence, repeating the same process again will be time consuming. So skipping this question.

# Answer14
SET @nameA = " ";
Call get_name_collegeA(@nameA);
SELECT @nameA;

# Answer15
SET @nameB = " ";
Call get_name_collegeB(@nameB);
SELECT @nameB;

# Answer16

SELECT "Higher Studies" Present_status,
(COUNT(college_a_hs.PresentStatus) /count(college_a_hs.RollNo))*100 College_A_Persentage,
(COUNT(college_b_hs.RollNo)/count(college_b_hs.RollNo))*100 College_B_Persentage
FROM college_a_hs CROSS JOIN college_b_hs
UNION
SELECT "Self Empolyment" Present_status,
(COUNT(college_a_se.RollNo) / count(college_a_se.RollNo)) *100 College_A_Persentage,
(COUNT(college_b_se.RollNo)/count(college_b_se.RollNo))*100 College_B_Persentage
FROM college_a_se
CROSS JOIN
college_b_se
UNION
SELECT "Service Job" Present_status,
(COUNT(college_a_sj.RollNo) /count(college_a_sj.RollNo))*100 College_A_Persentage,
(COUNT(college_b_sj.RollNo)/count(college_b_sj.RollNo))*100 College_B_Persentage
FROM college_a_sj
CROSS JOIN
college_b_sj;