Challenge

Project 2

RRotta Pavan Daya Kiran11 Aug 2023

NOTE:

In my system not working MySQL for Excel.

I did some thing for challenge also.

Also uninstall and install many time in my system.

Also joined for Live Office Hours Session or Group Session many time.

Not getting solution for problem. So iam doing this thing.

Answer.1

create schema alumni;

Answer.2

right click on alumni schema -> Table Data import Wizard -> Give path of the file -> Next -> choose options : Create a

new table , select delete if exist -> next -> next.

Answer.3

use alumni;

table-1

desc college_a_hs;

## table-2

desc college_a_se;

## table-3

desc college_a_sj;

## table-4

desc college_b_hs;

## table-5

desc college_b_se;

## table-6

desc college_b_sj;

Answer.4

# table-1

import mysql.connector

mydb = mysql.connector.connect(

host = "localhost",

user = "root",

password = "9849",

database = "alumni")

mycursor = mydb.cursor()

mycursor.execute('SELECT * FROM college_a_hs')

for a in mycursor:

print(a)

# table-2

import mysql.connector

mydb = mysql.connector.connect(

host = "localhost",

user = "root",

password = "9849",

database = "alumni")

mycursor = mydb.cursor()

mycursor.execute('SELECT * FROM college_a_se')

for a in mycursor:

print(a)

# table-3

import mysql.connector

mydb = mysql.connector.connect(

host = "localhost",

user = "root",

password = "9849",

database = "alumni")

mycursor = mydb.cursor()

mycursor.execute('SELECT * FROM college_a_sj')

for a in mycursor:

print(a)

# table-4

import mysql.connector

mydb = mysql.connector.connect(

host = "localhost",

user = "root",

password = "9849",

database = "alumni")

mycursor = mydb.cursor()

mycursor.execute('SELECT * FROM college_b_hs')

for a in mycursor:

print(a)

# table-5

import mysql.connector

mydb = mysql.connector.connect(

host = "localhost",

user = "root",

password = "9849",

database = "alumni")

mycursor = mydb.cursor()

mycursor.execute('SELECT * FROM college_b_se')

for a in mycursor:

print(a)

# table-6

import mysql.connector

mydb = mysql.connector.connect(

host = "localhost",

user = "root",

password = "9849",

database = "alumni")

mycursor = mydb.cursor()

mycursor.execute('SELECT * FROM college_b_sj')

for a in mycursor:

print(a)

Answer.5

step1: Open Excel.

step2: Selecl Data.

step3: Click on Local connecctions.

step3: Select on Local instance MySQL80.

step4: Give the root Password.

step5: Click on ok.

step6: Click the Schema.

step7: Select the table college_a_hs, college_a_se, college_a_sj, college_b_hs, college_b_se, college_b_sj.

step8: Click on Import MySQL Data.

step9: That one popup in screen (choose columns to import).

step10: Select the Options what you need (For answer we need 1.Crete a pivot Table with the and 2.Add Summary).

step11: In Excel Sheet we can see two tables (1) for selecl table ans (2) for pivot table.

step12: Click on pivot table.

step13: That one popup in screen right hand side (Pivot Table Field List).

step14: Do changes for Column labels, Row labels and Values (In values labels do which function you need).

step15: Then save it.

Answer.6

create or replace view college_a_hs_v as select * from college_a_hs;

select * from college_a_hs_v;

Answer.7

create or replace view college_a_se_v as select * from college_a_se;

select * from college_a_se_v;

Answer.8

create or replace view college_a_sj_v as select * from college_a_sj;

select * from college_a_sj_v;

Answer.9

create or replace view college_b_hs_v as select * from college_b_hs;

select * from college_b_hs_v;

Answer.10

create or replace view college_b_se_v as select * from college_b_se;

select * from college_b_se_v;

Answer.11

create or replace view college_b_sj_v as select * from college_b_sj;

select * from college_b_sj_v;

Answer.12

# table-1

call college_table_1;

# table-2

call college_table_2;

# table-3

call college_table_3;

# table-4

call college_table_4;

# table-5

call college_table_5;

# table-6

call college_table_6;

# for all tables

call college_all_tables;

Answer.13

step1: Open Excel.

step2: Selecl Data.

step3: Click on Local connecctions.

step3: Select on Local instance MySQL80.

step4: Give the root Password.

step5: Click on ok.

step6: Click the Schema.

step7: Select the table college_a_hs, college_a_se, college_a_sj, college_b_hs, college_b_se, college_b_sj.

step8: Click on Import MySQL Data.

step9: That one popup in screen (choose columns to import).

step10: Select the Options what you need (For answer we need 1.Crete a pivot Table with the and 2.Add Summary).

step11: In Excel Sheet we can see two tables (1) for selecl table ans (2) for pivot table.

step12: Click on pivot table.(then click on pivot chart)

step13: That one popup in screen right hand side (Pivot Table Field List).

step14: Do changes for Column labels, Row labels and Values (In values labels do which function you need).

step15: Then save it.

Answer.14

delimiter $$

create procedure getname_collegeA (inout namesofe longtext)

begin

declare finished int default 0;

declare earth varchar(8000) default " ";

declare namedetil

cursor for

select Name from college_a_hs_v union all

select Name from college_a_se_v union all

select Name from college_a_sj_v ;

declare continue handler

for not found set finished = 1;

open namedetil;

getmy: loop

fetch namedetil into earth;

if finished = 1 then leave getmy;

end if;

set namesofe = concat(earth,";",namesofe);

end loop getmy;

close namedetil;

end$$

delimiter ;

set @namesofe = " ";

call getname_collegeA(@namesofe);

select @namesofe Name_of_college_a;

Answer.15

delimiter $$

create procedure getname_collegeB (inout namesofe longtext)

begin

declare finished int default 0;

declare earth varchar(10000) default " ";

declare namedetil

cursor for

select Name from college_b_hs_v union all

select Name from college_b_se_v union all

select Name from college_b_sj_v ;

declare continue handler

for not found set finished = 1;

open namedetil;

getmy: loop

fetch namedetil into earth;

if finished = 1 then leave getmy;

end if;

set namesofe = concat(earth,";",namesofe);

end loop getmy;

close namedetil;

end$$

delimiter ;

set @namesofe = " ";

call getname_collegeA(@namesofe);

select @namesofe Name_of_college_b;

Answer.16

select 'Higher Studies', 100 * count(*)/5387 'College A Percentaga', (select 100 * count(*)/2259 from college_a_hs)

'College B Percentaga'

union all

select 'Self Employed', 100 * count(*)/5387 'College A Percentaga', (select 100 * count(*)/2259 from college_a_hs)

'College B Percentaga'

union all

select 'Service Job', 100 * count(*)/5387 'College A Percentaga', (select 100 * count(*)/2259 from college_a_hs)

'College B Percentaga' ;

Project 2

Ship work like this
in your own course.

Talk to a career counsellor

Ship work like thisin your own course.

Talk to a career counsellor

Ship work like this
in your own course.