BlogFor BusinessTalk to a counsellor
CoursesIIT ProgramsPlacementsLog in
BlogFor Business
Talk to a counsellor
Challenge

Project 2

TTummallapalli Benarji Kumar11 Aug 2023

*Q1.Create new schema as alumni*/

CREATE DATABASE  alumini;
USE alumini;


/*###############################################################################################################################################################*/
/*Q3.Run SQL command to see the structure of six tables*/

USE `alumini`;
DROP procedure IF EXISTS `alumini`.`DESCRIBE`;;

DELIMITER $$
USE `alumini`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `DESCRIBE`()
BEGIN
DESC college_a_se;
DESC college_a_hs;
DESC college_a_sj;
DESC college_b_hs;
DESC college_b_se;
DESC college_b_sj;


END$$

DELIMITER ;


CALL DESCRIBE_TABLES();

/*###############################################################################################################################################################*/
/*Q6.Perform data cleaning on table College_A_HS and store cleaned data in view College_A_HS_V, Remove null values. */

SELECT*FROM college_a_hs ;
CREATE VIEW College_A_HS_V
 AS
SELECT*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_V;

/*###############################################################################################################################################################*/
/*Q7.Perform data cleaning on table College_A_SE and store cleaned data in view College_A_SE_V, Remove null values.
*/


SELECT*FROM college_a_se ;
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;


CREATE VIEW College_A_SE_V 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;
SELECT*FROM College_A_SE_V;


SELECT*FROM college_a_se ;
SELECT*FROM college_a_se WHERE PresentStatus IS NULL;
/*###############################################################################################################################################################*/

/*Q8.Perform data cleaning on table College_A_SJ and store cleaned data in view College_A_SJ_V, Remove null values.*/
SELECT*FROM college_a_sj;
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;

CREATE VIEW College_A_SJ_V 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;

SELECT*FROM College_A_SJ_V;

/*###############################################################################################################################################################*/
/*Q9.Perform data cleaning on table College_B_HS and store cleaned data in view College_B_HS_V, Remove null values.*/
SELECT*FROM college_b_hs;

SELECT*FROM college_b_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
Branch 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 ;

CREATE VIEW College_B_HS_V AS 
SELECT*FROM college_b_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
Branch 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_B_HS_V;
/*###############################################################################################################################################################*/

/*Q10.Perform data cleaning on table College_B_SE and store cleaned data in view College_B_SE_V, Remove null values.
*/

SELECT*FROM college_b_se;
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 ;

CREATE VIEW College_B_SE_V 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 ;

/*###############################################################################################################################################################*/

/*Q11.Perform data cleaning on table College_B_SJ and store cleaned data in view College_B_SJ_V, Remove null values.*/

SELECT*FROM college_B_SJ;
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 ;

CREATE VIEW College_B_SJ_V 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 ;


SELECT*FROM College_B_SJ_V;

/*###############################################################################################################################################################*/

/*Q12.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) */
 
 SELECT lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) FROM College_A_HS_V;
  SELECT lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) FROM College_A_SE_V;
 SELECT lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) FROM College_A_SJ_V;
 SELECT lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) FROM College_B_HS_V;
  SELECT lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) FROM College_B_SE_V;
 SELECT lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) FROM College_B_SJ_V;


USE `alumini`;
DROP procedure IF EXISTS `lowercase_views`;

DELIMITER $$
USE `alumini`$$
CREATE PROCEDURE lowercase_views ()
BEGIN
 SELECT lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) FROM College_A_HS_V;
  SELECT lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) FROM College_A_SE_V;
 SELECT lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) FROM College_A_SJ_V;
 SELECT lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) FROM College_B_HS_V;
  SELECT lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) FROM College_B_SE_V;
 SELECT lower(Name) Name,lower(FatherName) FatherName,lower(MotherName) FROM College_B_SJ_V;
END$$

DELIMITER ;

CALL lowercase_views();

/*###############################################################################################################################################################*/

/*Q14.Write a query to create procedure get_name_collegeA using the cursor to fetch names of all students from college A.*/

DELIMITER |
CREATE PROCEDURE get_name_collegeA(INOUT allname text(40000))
BEGIN
DECLARE finished INT default 0;
DECLARE namelist VARCHAR(16000) DEFAULT "";

DECLARE namelistdetail
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 namelistdetail;
looplist:LOOP
FETCH namelistdetail INTO namelist;
IF finished=1 THEN
leave looplist;
END IF;
SET allname=CONCAT(namelist,"",allname);
END LOOP looplist;

close namelistdetail;


END|
DELIMITER ;

SET @allname="";
CALL get_name_collegeA(@allname);
SELECT @allname;

/*###############################################################################################################################################################*/

/*Q15.Write a query to create procedure get_name_collegeB using the cursor to fetch names of all students from college B.*/
DELIMITER |
CREATE PROCEDURE get_name_collegeB(INOUT allnames text(40000))
BEGIN
DECLARE finished INT default 0;
DECLARE namelist VARCHAR(16000) DEFAULT "";

DECLARE namelistdetail
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 namelistdetail;
looplist:LOOP
FETCH namelistdetail INTO namelist;
IF finished=1 THEN
leave looplist;
END IF;
SET allnames=CONCAT(namelist,"",allnames);
END LOOP looplist;

close namelistdetail;


END|
DELIMITER ;


SET @allnames="";
CALL get_name_collegeB(@allnames);
SELECT @allnames;

/*###############################################################################################################################################################*/

/*Q16.Calculate the percentage of career choice of College A and College B Alumni
-- (w.r.t Higher Studies, Self Employed and Service/Job)
Note: Approximate percentages are considered for career choices.*/
SELECT*FROM college_a_hs;
SELECT*FROM college_a_se;
SELECT*FROM college_a_sj;

SELECT 'Higher studies' AS carrer_choice,
( SELECT COUNT(*) FROM college_a_hs)/

((SELECT COUNT(*) FROM COllege_a_hs)  +
(SELECT COUNT(*) FROM college_a_se)+
(SELECT COUNT(*) FROM college_a_sj)
) *100 AS college_A_percentage ,
( SELECT COUNT(*) FROM college_b_hs)/
(
(SELECT COUNT(*) FROMcollege_b_hs)+
(SELECT COUNT(*) FROM college_b_se)+
(SELECT COUNT(*) FROM college_b_sj)
) *100 AS college_B_percentage 
UNION
SELECT 'Self Employeed' AS carrer_choice,
( SELECT COUNT(*) FROM college_a_se)/
(
(SELECT COUNT(*) FROM college_a_hs)+
(SELECT COUNT(*) FROM college_a_se)+
(SELECT COUNT(*) FROM college_a_sj)
) *100 AS college_A_percentage ,
( SELECT COUNT(*) FROM college_b_se)/
(
(SELECT COUNT(*) FROM college_b_hs)+
(SELECT COUNT(*) FROM college_b_se)+
(SELECT COUNT(*) FROM  college_b_sj)
) *100 AS college_B_percentage 
UNION
SELECT 'servive job' AS carrer_choice,
( SELECT COUNT(*) FROM college_a_sj)/
(
(SELECT COUNT(*) FROM college_a_hs)+
(SELECT COUNT(*) FROM college_a_se)+
(SELECT COUNT(*) FROM college_a_sj)
) *100 AS college_A_percentage ,
( SELECT COUNT(*) FROM college_b_sj)/
(
(SELECT COUNT(*) FROM college_b_hs)+
(SELECT COUNT(*) FROM college_b_se)+
(SELECT COUNT(*) FROM college_b_sj)
) *100 AS college_B_percentage 

Your portfolio, next

Ship work like this
in your own course.

Talk to a career counsellor

Free 20-minute call. We'll map the right course to where you want to work.

By submitting you agree to our Terms and Privacy Policy.