Menu

Executive Programs

Workshops

Projects

Blogs

Careers

Placements

Student Reviews


For Business


More

Academic Training

Informative Articles

Find Jobs

We are Hiring!


All Courses

Choose a category

Mechanical

Electrical

Civil

Computer Science

Electronics

Offline Program

All Courses

All Courses

logo

CHOOSE A CATEGORY

Mechanical

Electrical

Civil

Computer Science

Electronics

Offline Program

Top Job Leading Courses

Automotive

CFD

FEA

Design

MBD

Med Tech

Courses by Software

Design

Solver

Automation

Vehicle Dynamics

CFD Solver

Preprocessor

Courses by Semester

First Year

Second Year

Third Year

Fourth Year

Courses by Domain

Automotive

CFD

Design

FEA

Tool-focused Courses

Design

Solver

Automation

Preprocessor

CFD Solver

Vehicle Dynamics

Machine learning

Machine Learning and AI

POPULAR COURSES

coursePost Graduate Program in Hybrid Electric Vehicle Design and Analysis
coursePost Graduate Program in Computational Fluid Dynamics
coursePost Graduate Program in CAD
coursePost Graduate Program in CAE
coursePost Graduate Program in Manufacturing Design
coursePost Graduate Program in Computational Design and Pre-processing
coursePost Graduate Program in Complete Passenger Car Design & Product Development
Executive Programs
Workshops
For Business

Success Stories

Placements

Student Reviews

More

Projects

Blogs

Academic Training

Find Jobs

Informative Articles

We're Hiring!

phone+91 9342691281Log in
  1. Home/
  2. Sushant Ovhal/
  3. Project 2

Project 2

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

    1. 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 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;   ENDCREATE 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;   ENDCREATE 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.

    Please  login to add a comment

    Other comments...

    No comments yet!
    Be the first to add a comment

    Read more Projects by Sushant Ovhal (22)

    Project 1 - Analyzing the Education trends in Tamilnadu

    Objective:

    This dashboard empowers mission driven organizations to harness the power of data visualization for social change. Women are tracked away from science and mathematics throughout their education, limiting their training and options to go into these fields as adults. The data set contains the data of women graduated by years,…

    calendar

    14 Nov 2023 01:32 PM IST

      Read more

      Project 1 - English Dictionary App & Library Book Management System

      Objective:

      Project 1) English dictionary app and Library Book Management system

      calendar

      06 Nov 2023 04:04 PM IST

        Read more

        Project 1 - Implement and deploy CNN model in real-time using python on Fashion MNIST dataset

        Objective:

         Implement and deploy CNN model in real-time using python on Fashion MNIST dataset

        calendar

        20 Dec 2022 07:04 AM IST

          Read more

          Project 2

          Objective:

          Project 2

          calendar

          30 Nov 2022 11:41 AM IST

            Read more

            Schedule a counselling session

            Please enter your name
            Please enter a valid email
            Please enter a valid number

            Related Courses

            coursecard

            Design loads considered on bridges

            Recently launched

            10 Hours of Content

            coursecard

            Design of Steel Superstructure in Bridges

            Recently launched

            16 Hours of Content

            coursecard

            Design for Manufacturability (DFM)

            Recently launched

            11 Hours of Content

            coursecard

            CATIA for Medical Product Design

            Recently launched

            5 Hours of Content

            coursecardcoursetype

            Accelerated Career Program in Embedded Systems (On-Campus) Courseware Partner: IT-ITes SSC nasscom

            Recently launched

            0 Hours of Content

            Schedule a counselling session

            Please enter your name
            Please enter a valid email
            Please enter a valid number

            logo

            Skill-Lync offers industry relevant advanced engineering courses for engineering students by partnering with industry experts.

            https://d27yxarlh48w6q.cloudfront.net/web/v1/images/facebook.svghttps://d27yxarlh48w6q.cloudfront.net/web/v1/images/insta.svghttps://d27yxarlh48w6q.cloudfront.net/web/v1/images/twitter.svghttps://d27yxarlh48w6q.cloudfront.net/web/v1/images/youtube.svghttps://d27yxarlh48w6q.cloudfront.net/web/v1/images/linkedin.svg

            Our Company

            News & EventsBlogCareersGrievance RedressalSkill-Lync ReviewsTermsPrivacy PolicyBecome an Affiliate
            map
            EpowerX Learning Technologies Pvt Ltd.
            4th Floor, BLOCK-B, Velachery - Tambaram Main Rd, Ram Nagar South, Madipakkam, Chennai, Tamil Nadu 600042.
            mail
            info@skill-lync.com
            mail
            ITgrievance@skill-lync.com

            Top Individual Courses

            Computational Combustion Using Python and CanteraIntroduction to Physical Modeling using SimscapeIntroduction to Structural Analysis using ANSYS WorkbenchIntroduction to Structural Analysis using ANSYS Workbench

            Top PG Programs

            Post Graduate Program in Hybrid Electric Vehicle Design and AnalysisPost Graduate Program in Computational Fluid DynamicsPost Graduate Program in CADPost Graduate Program in Electric Vehicle Design & Development

            Skill-Lync Plus

            Executive Program in Electric Vehicle Embedded SoftwareExecutive Program in Electric Vehicle DesignExecutive Program in Cybersecurity

            Trending Blogs

            Heat Transfer Principles in Energy-Efficient Refrigerators and Air Conditioners Advanced Modeling and Result Visualization in Simscape Exploring Simulink and Library Browser in Simscape Advanced Simulink Tools and Libraries in SimscapeExploring Simulink Basics in Simscape

            © 2025 Skill-Lync Inc. All Rights Reserved.

                        Do You Want To Showcase Your Technical Skills?
                        Sign-Up for our projects.