Project 2
-- q1
create database alumni ;
use alumni ;
-- q2 import done useing gui
-- answer=> we created database then cilck on the table then you will get
-- get option to table dataimport wilzard cilck on there then you will get option to
-- you file in the fom of csv file then select then you will get many opetion
-- to change data type but we will cilck nest and so on nest our data will be import
-- q3
desc college_a_hs ;
desc college_a_se ;
desc college_a_sj ;
desc college_b_hs ;
desc college_b_se ;
desc college_b_sj ;
select * from college_a_hs ;
select * from college_a_se ;
select * from college_a_sj ;
select * from college_b_hs ;
select * from college_b_se ;
select * from college_b_sj ;
-- q4 i have done in the jupter file check my jupyter file
-- q5
-- in that question i am not able to send in excel fom because
-- my suscription has over it is too expensive for me i talked to mem
-- she said that not in issue you can write theoretical ans
/*
Create a new workbook in MS Excel.
Click on the DATA tab.
Select From Other Sources button.
Select From SQL Server as shown in the image above.
Enter the server name/IP address.
Choose the login type.
Once you are connected to the database server, a window will open, you have to enter all the details as shown in screenshot.
Select the database from the drop-down list.
Click on the table to select it.
Click on next button. It will open a data connection wizard to save data connection and finish the process of connecting to the employee’s data.
*/
-- q6
create view College_A_HS_View as (
select
RollNo , LastUpdate,Name,FatherName,MotherName,Batch,
Degree,PresentStatus,HSDegree,EntranceExam,Institute,Location
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_View ;
-- q7
create view College_A_SE_viewc 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 and location is not null
) ;
-- q8
create view College_A_sj_viewc 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
) ;
-- q9
-- we checked there is no null value
create view College_b_sj_viewc 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 branch 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
) ;
-- q10
-- we checked there is no null value
create view College_b_SE_viewc 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 branch 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 and location is not null
) ;
-- q11
-- we checked there is no null value
create view College_A_sj_viewc 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
) ;
--
-- q12
delimiter /
create procedure table_con_lower()
begin
select lower(name)name , lower(fathername)father , lower(mothername)
from College_A_HS_View ;
select lower(name)name , lower(fathername)father , lower(mothername)
from College_A_SE_viewc ;
select lower(name)name , lower(fathername)father , lower(mothername)
from College_A_sj_viewc ;
end / ;
delimiter ;
call table_con_lower() ;
-- q13
-- i am not able to do this question because my excel suscription has
-- over i writing to step how we can do it i tryed with google sheet
-- but therse was no faclity to connect my sql on there
/*Open MS Excel.
Click on the “Insert” tab.
Click on the “PivotChart” dropdown button within the “Charts” group.
Select where you want the PivotChart to appear.
Select the fields to display in the menu.
Create a chart from a PivotTable by selecting a cell in your table and then selecting PivotTable Tools > Analyze > PivotChart. Select a chart and click OK.
*/
-- q14
delimiter /
create procedure get_name_collegeA(inout lastname1 text(40000) )
begin
DECLARE finished INTEGER DEFAULT 0;
DECLARE lastnamelist VARCHAR(400) DEFAULT "";
DECLARE addressdetail 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 addressdetail;
get_address_detail : LOOP
FETCH addressdetail INTO lastnamelist;
IF finished = 1 THEN
LEAVE get_address_detail;
END IF;
SET lastname1 = CONCAT(lastnamelist, ":", lastname1);
END LOOP get_address_detail;
CLOSE addressdetail;
end /
delimiter ;
SET @lastname1 = '';
CALL get_name_collegeA(@lastname1);
select @lastname1 get_name_collegeA;
-- q15
delimiter /
create procedure get_name_collegeb(inout lastname1 text(40000) )
begin
DECLARE finished INTEGER DEFAULT 0;
DECLARE lastnamelist VARCHAR(400) DEFAULT "";
DECLARE addressdetail 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 addressdetail;
get_address_detail : LOOP
FETCH addressdetail INTO lastnamelist;
IF finished = 1 THEN
LEAVE get_address_detail;
END IF;
SET lastname1 = CONCAT(lastnamelist, ":", lastname1);
END LOOP get_address_detail;
CLOSE addressdetail;
end /
delimiter ;
SET @lastname1 = '';
CALL get_name_collegeb(@lastname1);
select @lastname1 get_name_collegeb;
-- q16
select 'higher studies' as career_choice, (select count(*) college_a_percentage from college_a_hs)
/ ((select count(*) from college_a_se )
+ (select count(*) from college_a_hs )
+ (select count(*) college_a_percentage from college_a_sj))*100 collageA_percetage
union
select 'self Employed' as career_choice, (select count(*) college_a_percentage from college_a_se)
/ ((select count(*) from college_a_se )
+ (select count(*) from college_a_hs )
+ (select count(*) college_a_percentage from college_a_sj))*100 collageA_percetage
union
select 'service job' as career_choice, (select count(*) college_a_percentage from college_a_sj)
/ ((select count(*) from college_a_se )
+ (select count(*) from college_a_hs )
+ (select count(*) college_a_percentage from college_a_sj))*100 collageA_percetage
union
select 'higher studies' as career_choice, (select count(*) college_b_percentage from college_b_hs)
/ ((select count(*) from college_b_se )
+ (select count(*) from college_b_hs )
+ (select count(*) college_b_percentage from college_b_sj))*100 collageb_percetage
union
select 'self Employed' as career_choice, (select count(*) college_b_percentage from college_b_se)
/ ((select count(*) from college_b_se )
+ (select count(*) from college_a_hs )
+ (select count(*) college_b_percentage from college_b_sj))*100 collage_b_percetage
union
select 'service job' as career_choice, (select count(*) college_b_percentage from college_b_sj)
/ ((select count(*) from college_b_se )
+ (select count(*) from college_b_hs )
+ (select count(*) college_b_percentage from college_b_sj))*100 collageb_percetage