All Courses
All Courses
Create new schema as alumni ANS:- 2) Import all .csv files into MySQL ANS:- 3) Run SQL command to see the structure of six tables ANS- DESC college_a_hs;DESC college_a_se;DESC college_a_sj;DESC college_b_hs;DESC college_b_se;DESC college_b_sj; 6) Perform data cleaning on table College_A_HS and store…
Sushant Ovhal
updated on 09 Sep 2022
ANS:-
2) Import all .csv files into MySQL
ANS:-
3) Run SQL command to see the structure of six tables
ANS-
DESC college_a_hs;
DESC college_a_se;
DESC college_a_sj;
DESC college_b_hs;
DESC college_b_se;
DESC college_b_sj;
6) Perform data cleaning on table College_A_HS and store cleaned data in view College_A_HS_V, Remove null values.
ANS:-
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;
7)Perform data cleaning on table College_A_SE and store cleaned data in view College_A_SE_V, Remove null values.
ANS:-
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;
8) Perform data cleaning on table College_A_SJ and store cleaned data in view College_A_SJ_V, Remove null values.
ANS:-
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;
9)Perform data cleaning on table College_B_HS and store cleaned data in view College_B_HS_V, Remove null values.
ANS:-
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;
10)Perform data cleaning on table College_B_SE and store cleaned data in view College_B_SE_V, Remove null values.
ANS:-
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);
SELECT * FROM College_b_se_v;
11)Perform data cleaning on table College_B_SJ and store cleaned data in view College_B_SJ_V, Remove null values.
ANS:-
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;
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:-
CALL lowercollege_a_hs;
CALL Lowercollege_a_se;
CALL lowercollege_a_sj;
CALL lowercollege_b_hs;
CALL lowercollege_b_se;
CALL lowercollege_b_sj;
14) Write a query to create procedure get_name_collegeA using the cursor to fetch names of all students from college A.
ANS:-
DELIMITER CREATE PROCEDURE get_name_collegeA ( INOUT Lname TEXT(40000) ) BEGIN DECLARE finished INT DEFAULT 0; DECLARE Lnamelist VARCHAR (16000) DEFAULT ""; DECLARE Lnamedetails 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 Lnamedetails; getname1: LOOP FETCH Lnamedetails INTO Lnamelist; IF finished =1 THEN LEAVE getname1; END IF; SET Lname = CONCAT (Lnamelist,";",Lname); END LOOP getname1; CLOSE Lnamedetails; END
DELIMITER ;
SET @name1="";
CALL get_name_collegeA(@name1);
SELECT @name1 Name;
15) Write a query to create procedure get_name_collegeB using the cursor to fetch names of all students from college B.
ANS:-
DELIMITER CREATE PROCEDURE get_name_collegeB ( INOUT Fname TEXT(40000) ) BEGIN DECLARE finished INT DEFAULT 0; DECLARE Fnamelist VARCHAR (16000) DEFAULT ""; DECLARE Fnamedetails CURSOR FOR SELECT name FROM college_b_hs UNION ALL SELECT name FROM college_b_se UNION ALL SELECT name FROM college_b_sj; DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished=1; OPEN Fnamedetails; getname2: LOOP FETCH Fnamedetails INTO Fnamelist; IF finished =1 THEN LEAVE getname2; END IF; SET Fname = CONCAT (Fnamelist,";",Fname); END LOOP getname2; CLOSE Fnamedetails; END
DELIMITER ;
SET @name2="";
CALL get_name_collegeB(@name2);
SELECT @name2 Name;
16) 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
ANS:-
SELECT "HigherStudies" PresentStatus,(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
College_A_Percentage,
(SELECT COUNT(*) FROM college_b_hs)/
((SELECT COUNT(*) FROM college_b_hs) + (SELECT COUNT(*) FROM college_b_se) + (SELECT COUNT(*) FROM college_b_sj))*100
College_B_Percentage
UNION
SELECT "Self Employed" PresentStatus,(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
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
College_B_Percentage
UNION
SELECT "Service Job" PresentStatus,(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
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
College_B_Percentage;
Leave a comment
Thanks for choosing to leave a comment. Please keep in mind that all the comments are moderated as per our comment policy, and your email will not be published for privacy reasons. Please leave a personal & meaningful conversation.
Other comments...
Project 1 - Implement and deploy CNN model in real-time using python on Fashion MNIST dataset
Implement and deploy CNN model in real-time using python on Fashion MNIST dataset
20 Dec 2022 07:04 AM IST
Project 2
Project 2
30 Nov 2022 11:41 AM IST
Project 1
Project 1
30 Nov 2022 05:44 AM IST
Project 2 - Supply and Demand Gap Analysis
Aim The aim of this project is to collect and analyze the data in detail of the Pickup point of the Airport and City Request id in Uber Request Data. Introduction Uber provides the 2016 data. By using this data find out the demand and supply Analysis gap of the cab. The main objective of this project is to analyze…
28 Oct 2022 01:16 PM IST
Related Courses
0 Hours of Content