Project 2
Q1) Create new schema as alumni
MySQL script file attached.
Q2) Import all .csv files into MySQL
Q3) Run SQL command to see the structure of six tables
4) Display first 1000 rows of tables Jupyter notebook attached
import mysql.connector
testdb = mysql.connector.connect(
host = "localhost",
user = "root",
password = "we4r5tyu",
database = "alumni")
testcursor = testdb.cursor()
testcursor.execute("SELECT * FROM college_a_hs LIMIT 1000")
a = testcursor.fetchall()
for b in a:
print (b)
testcursor.execute("SELECT * FROM college_a_se LIMIT 1000")
a = testcursor.fetchall()
for b in a:
print (b)
testcursor.execute("SELECT * FROM college_a_sj LIMIT 1000")
a = testcursor.fetchall()
for b in a:
print (b)
testcursor.execute("SELECT * FROM college_b_hs LIMIT 1000")
a = testcursor.fetchall()
for b in a:
print (b)
testcursor.execute("SELECT * FROM college_b_se LIMIT 1000")
a = testcursor.fetchall()
for b in a:
print (b)
testcursor.execute("SELECT * FROM college_b_sj LIMIT 1000")
a = testcursor.fetchall()
for b in a:
print (b)
5) Import all the records of tables -- Excel file attached
6) Perform data cleaning on table College_A_HS and store cleaned data in view College_A_HS_V, Remove null values.
SQL script attached
7) Perform data cleaning on table College_A_SE and store cleaned data in view College_A_SE_V, Remove null values.
SQL script attached
8) Perform data cleaning on table College_A_SJ and store cleaned data in view College_A_SJ_V, Remove null values.
SQL script attached
9) Perform data cleaning on table College_B_HS and store cleaned data in view College_B_HS_V, Remove null values.
SQL script attached
10) Perform data cleaning on table College_B_SE and store cleaned data in view College_B_SE_V, Remove null values.
SQL script attached
11) Perform data cleaning on table College_B_SJ and store cleaned data in view College_B_SJ_V, Remove null values.
SQL script attached
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)
SQL script attached
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.
MS Excel file attached
14) Write a query to create procedure get_name_collegeA using the cursor to fetch names of all students from college A.
SQL script attached
15) Write a query to create procedure get_name_collegeB using the cursor to fetch names of all students from college B.
SQL script attached
16) Calculate the percentage of career choice of College A and College B Alumni
SQL script attached
