Project 2
QUE 1.Create new schema as alumni.
ANS -
wrote a query to create a schema.
QUE.2 .Import all .csv files into MySQL.
ANS - To Import the .csv file in mysql workbench. right click on the created schema which is alumini.
After that select on Table data import wizard. and provide a path where .csv file is located.
After that select on Next it wil open the .csv file where it is located. and select a file which we want and click on open.
After That It Shows the destination folder. in that need to set database and set the file name.
After click on Next it Shows The Overview of The folder. and once it will be checked by over side. Click On The Next it Starts the importing the data.
importing process of data.
After refreshing the schema of alumini it shows the all 6 tables are succesfully imported.
QUE 3.Run SQL command to see the structure of six tables.
ANS -
QUE 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.
ANS -
for first college which is college_a_hs.
for next college which is college_a_se.
for college_a_sj.
for college_b_hs
for college_b_se.
for college_b_sj.
QUE.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.
ANS -
To importing 1500 rows from table college_a_hs,collegea_se,college_a_sj,collegeb_hs,college_B_se,college_b_sj opening a excel and click on mysql connector aftre that write a password to open it. and after that cleck on schema which alumini. after that click on import my sql data from schema alumini.
i am going to attach my excel sheet in bottom of the answer sheet.
Que 6. Perform data cleaning on table College_A_HS and store cleaned data in view College_A_HS_V, Remove null values.
ANS -
QUE 7. Perform data cleaning on table College_A_SE and store cleaned data in view College_A_SE_V, Remove null values.
ANS .
QUE 8. Perform data cleaning on table College_A_SJ and store cleaned data in view College_A_SJ_V, Remove null values.
ANS -
QUE 9. Perform data cleaning on table College_B_HS and store cleaned data in view College_B_HS_V, Remove null values.
ANS -
QUE 10. Perform data cleaning on table College_B_SE and store cleaned data in view College_B_SE_V, Remove null values.
ANS -
QUE 11. Perform data cleaning on table College_B_SJ and store cleaned data in view College_B_SJ_V, Remove null values.
ANS -
que 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)
ANS -
FOR ALL view which i have stored in last challenge i am using here to to convert name.fathername,mothername into lowercase to all 6 stored views.
for college_a_hs_v -
for college_a_se_v -
for college_a_sj -
for college_b_se -
for college_b_se_v
FOR COLLEGE_B_SJ_v
QUE 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.
ANS -
To Imorting The Created Views In The Excel of All Six Views Connect To Sql connector and and importing all the views. with pivet chart of location alumini.
1.
2.
