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

Project 2

NNaveen Kumar Meda4 Sept 2023

Q3:-

use alumni;
desc college_a_hs;
desc college_a_se;
desc college_a_sj;
desc college_b_hs;
desc college_b_se;
desc college_b_sj;

Q4:-

import mysql.connector
import pandas as pd
from sqlalchemy import create_engine

# Connect to the database
testdb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="Sujathameda227!",
    database="alumni"
)

# Create an SQLAlchemy engine
engine = create_engine('mysql+mysqlconnector://root:Sujathameda227!@localhost/alumni')

# List of table names
table_names = ['college_a_hs', 'college_a_se', 'college_a_sj', 'college_b_hs', 'college_b_se', 'college_b_sj']

# Fetch and display the first 1000 rows from each table
for table_name in table_names:
    query = f'SELECT * FROM {table_name} LIMIT 1000'
    df = pd.read_sql(query, engine)
    print(f"First 1000 rows of {table_name}:")
    display(df)  # Use display() in Jupyter Notebook for a nicely formatted output
    print('\n' + '-'*80 + '\n')  # Separator between tables

Q5:- Please find the attachment

Q6:-

-- Create a view "College_A_HS_V" with cleaned data
CREATE VIEW College_A_HS_V AS
SELECT
    RollNo,
    IFNULL(PresentStatus, '') AS PresentStatus,
    IFNULL(Name, '') AS Name,
    IFNULL(MotherName, '') AS MotherName,
    IFNULL(Location, '') AS Location,
    LastUpdate,
    IFNULL(Institute, '') AS Institute,
    IFNULL(HSDegree, '') AS HSDegree,
    IFNULL(FatherName, '') AS FatherName,
    IFNULL(EntranceExam, '') AS EntranceExam,
    IFNULL(Degree, '') AS Degree,
    IFNULL(Batch, '') AS Batch
FROM College_A_HS
WHERE
    RollNo IS NOT NULL
    AND LastUpdate IS NOT NULL;

Q7:-

-- Create a view "College_A_SE_V" with cleaned data
CREATE VIEW College_A_SE_V AS
SELECT
    RollNo,
    IFNULL(PresentStatus, '') AS PresentStatus,
    IFNULL(Name, '') AS Name,
    IFNULL(MotherName, '') AS MotherName,
    IFNULL(Location, '') AS Location,
    LastUpdate,
    IFNULL(Organization, '') AS Organization,
    IFNULL(FatherName, '') AS FatherName,
    IFNULL(Degree, '') AS Degree,
    IFNULL(Batch, '') AS Batch
FROM College_A_SE
WHERE
    RollNo IS NOT NULL
    AND LastUpdate IS NOT NULL;

Q8:-

-- Create a view "College_A_SJ_V" with cleaned data
CREATE VIEW College_A_SJ_V AS
SELECT
    RollNo,
    IFNULL(PresentStatus, '') AS PresentStatus,
    IFNULL(Name, '') AS Name,
    IFNULL(MotherName, '') AS MotherName,
    IFNULL(Location, '') AS Location,
    LastUpdate,
    IFNULL(Organization, '') AS Organization,
        IFNULL(Designation, '') AS Designation,
    IFNULL(FatherName, '') AS FatherName,
    IFNULL(Degree, '') AS Degree,
    IFNULL(Batch, '') AS Batch
FROM College_A_SJ
WHERE
    RollNo IS NOT NULL
    AND LastUpdate IS NOT NULL;

Q9:-

-- Create a view "College_B_HS_V" with cleaned data
CREATE VIEW College_B_HS_V AS
SELECT
    RollNo,
    LastUpdate,
    IFNULL(Name, '') AS Name,
    IFNULL(FatherName, '') AS FatherName,
    IFNULL(MotherName, '') AS MotherName,
    IFNULL(Branch, '') AS Branch,
    IFNULL(Batch, '') AS Batch,
    IFNULL(Degree, '') AS Degree,
    IFNULL(PresentStatus, '') AS PresentStatus,
    IFNULL(HSDegree, '') AS HSDegree,
    IFNULL(EntranceExam, '') AS EntranceExam,
    IFNULL(Institute, '') AS Institute,
    IFNULL(Location, '') AS Location
FROM College_B_HS
WHERE
    RollNo IS NOT NULL
    AND LastUpdate IS NOT NULL;

Q10:-

-- Create a view "College_B_SE_V" with cleaned data
CREATE VIEW College_B_SE_V AS
SELECT
    RollNo,
    LastUpdate,
    IFNULL(Name, '') AS Name,
    IFNULL(FatherName, '') AS FatherName,
    IFNULL(MotherName, '') AS MotherName,
    IFNULL(Branch, '') AS Branch,
    IFNULL(Batch, '') AS Batch,
    IFNULL(Degree, '') AS Degree,
    IFNULL(PresentStatus, '') AS PresentStatus,
    IFNULL(Organization, '') AS Organization,
    IFNULL(Location, '') AS Location
FROM College_B_SE
WHERE
    RollNo IS NOT NULL
    AND LastUpdate IS NOT NULL;

Q11:-

-- Create a view "College_B_SJ_V" with cleaned data
CREATE VIEW College_B_SJ_V AS
SELECT
    RollNo,
    LastUpdate,
    IFNULL(Name, '') AS Name,
    IFNULL(FatherName, '') AS FatherName,
    IFNULL(MotherName, '') AS MotherName,
    IFNULL(Branch, '') AS Branch,
    IFNULL(Batch, '') AS Batch,
    IFNULL(Degree, '') AS Degree,
    IFNULL(PresentStatus, '') AS PresentStatus,
    IFNULL(Organization, '') AS Organization,
    IFNULL(Designation, '') AS Designation,
    IFNULL(Location, '') AS Location
FROM College_B_SJ
WHERE
    RollNo IS NOT NULL
    AND LastUpdate IS NOT NULL;

Q12:-

DELIMITER $$

CREATE PROCEDURE ConvertNamesToLowerCase()
BEGIN
    
    CREATE VIEW College_A_HS_V_Lowercase AS
    SELECT
        RollNo,
        LastUpdate,
        LOWER(Name) AS Name,
        LOWER(FatherName) AS FatherName,
        LOWER(MotherName) AS MotherName
    FROM College_A_HS_V;
    
    CREATE VIEW College_A_SE_V_Lowercase AS
    SELECT
        RollNo,
        LastUpdate,
        LOWER(Name) AS Name,
        LOWER(FatherName) AS FatherName,
        LOWER(MotherName) AS MotherName
    FROM College_A_SE_V;

    CREATE VIEW College_A_SJ_V_Lowercase AS
    SELECT
        RollNo,
        LastUpdate,
        LOWER(Name) AS Name,
        LOWER(FatherName) AS FatherName,
        LOWER(MotherName) AS MotherName
    FROM College_A_SJ_V;
    
        CREATE VIEW College_B_HS_V_Lowercase AS
    SELECT
        RollNo,
        LastUpdate,
        LOWER(Name) AS Name,
        LOWER(FatherName) AS FatherName,
        LOWER(MotherName) AS MotherName
    FROM College_B_HS_V;

        CREATE VIEW College_B_SE_V_Lowercase AS
    SELECT
        RollNo,
        LastUpdate,
        LOWER(Name) AS Name,
        LOWER(FatherName) AS FatherName,
        LOWER(MotherName) AS MotherName
    FROM College_B_SE_V;

CREATE VIEW College_B_SJ_V_Lowercase AS
    SELECT
        RollNo,
        LastUpdate,
        LOWER(Name) AS Name,
        LOWER(FatherName) AS FatherName,
        LOWER(MotherName) AS MotherName
    FROM College_B_SJ_V;

    
END $$

DELIMITER ;

Q13:-

please find the attachment

Q14:-

 
DELIMITER $$
 
CREATE PROCEDURE get_names_from_tablesA()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE student_name VARCHAR(255);
    
    -- Declare a cursor for each table
DECLARE cursor_a_hs CURSOR FOR SELECT Name FROM College_A_HS_V;
    DECLARE cursor_a_se CURSOR FOR SELECT Name FROM College_A_SE_V;
    DECLARE cursor_a_sj CURSOR FOR SELECT Name FROM College_A_SJ_V;
    
    
    -- Error handler
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    -- Create a temporary table to store the results
    CREATE TEMPORARY TABLE IF NOT EXISTS StudentNames (Name VARCHAR(255));
    
    -- Open the cursors
OPEN cursor_a_hs;
    OPEN cursor_a_se;
    OPEN cursor_a_sj;
    
    -- Fetch data for each table
    FETCH NEXT FROM cursor_a_hs INTO student_name;
    WHILE NOT done DO
        INSERT INTO StudentNames(Name) VALUES (student_name);
        FETCH NEXT FROM cursor_a_hs INTO student_name;
    END WHILE;
    
    FETCH NEXT FROM cursor_a_se INTO student_name;
    WHILE NOT done DO
        INSERT INTO StudentNames(Name) VALUES (student_name);
        FETCH NEXT FROM cursor_a_se INTO student_name;
    END WHILE;
    
    FETCH NEXT FROM cursor_a_sj INTO student_name;
    WHILE NOT done DO
        INSERT INTO StudentNames(Name) VALUES (student_name);
        FETCH NEXT FROM cursor_a_sj INTO student_name;
    END WHILE;
    
    
    -- Close the cursors
    CLOSE cursor_a_hs;
    CLOSE cursor_a_se;
    CLOSE cursor_a_sj;
    
    
    -- Select and return the fetched data
    SELECT * FROM StudentNames;
    
    -- Drop the temporary table
    DROP TEMPORARY TABLE IF EXISTS StudentNames;
    
END $$
 
DELIMITER ;
 
call get_names_from_tablesA();

Q15:-

 
DELIMITER $$
 
CREATE PROCEDURE get_names_from_tablesB()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE student_name VARCHAR(255);
    
    -- Declare a cursor for each table
 
    DECLARE cursor_b_hs CURSOR FOR SELECT Name FROM College_B_HS_V;
    DECLARE cursor_b_se CURSOR FOR SELECT Name FROM College_B_SE_V;
    DECLARE cursor_b_sj CURSOR FOR SELECT Name FROM College_B_SJ_V;
    
    -- Error handler
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    -- Create a temporary table to store the results
    CREATE TEMPORARY TABLE IF NOT EXISTS StudentNames (Name VARCHAR(255));
    
    -- Open the cursors
    OPEN cursor_b_hs;
    OPEN cursor_b_se;
    OPEN cursor_b_sj;
    
    -- Fetch data for each table
 
    
    FETCH NEXT FROM cursor_b_hs INTO student_name;
    WHILE NOT done DO
        INSERT INTO StudentNames(Name) VALUES (student_name);
        FETCH NEXT FROM cursor_b_hs INTO student_name;
    END WHILE;
    
    FETCH NEXT FROM cursor_b_se INTO student_name;
    WHILE NOT done DO
        INSERT INTO StudentNames(Name) VALUES (student_name);
        FETCH NEXT FROM cursor_b_se INTO student_name;
    END WHILE;
    
    FETCH NEXT FROM cursor_b_sj INTO student_name;
    WHILE NOT done DO
        INSERT INTO StudentNames(Name) VALUES (student_name);
        FETCH NEXT FROM cursor_b_sj INTO student_name;
    END WHILE;
    
    -- Close the cursors
    CLOSE cursor_b_hs;
    CLOSE cursor_b_se;
    CLOSE cursor_b_sj;
    
    -- Select and return the fetched data
    SELECT * FROM StudentNames;
    
    -- Drop the temporary table
    DROP TEMPORARY TABLE IF EXISTS StudentNames;
    
END $$
DELIMITER ;call get_names_from_tablesB();
 
Q16:-
 
 

SELECT
    CareerChoice,
    MAX(CASE WHEN College = 'College A' THEN CollegeA_Percentage ELSE 0 END) AS 'College A Percentage',
    MAX(CASE WHEN College = 'College B' THEN CollegeB_Percentage ELSE 0 END) AS 'College B Percentage'
FROM (
    SELECT
        CareerChoice,
        'College A' AS College,
        SUM(CASE WHEN College = 'College A' THEN CollegeA_Percentage ELSE 0 END) AS CollegeA_Percentage,
        SUM(CASE WHEN College = 'College B' THEN CollegeB_Percentage ELSE 0 END) AS CollegeB_Percentage
    FROM (
        SELECT
            'Higher Studies' AS CareerChoice,
            'College A' AS College,
            (SUM(CASE WHEN PresentStatus = 'Higher Studies' THEN 1 ELSE 0 END) / COUNT(*)) * 100 AS CollegeA_Percentage,
            0 AS CollegeB_Percentage
        FROM (
            SELECT PresentStatus FROM College_A_HS
            UNION ALL
            SELECT PresentStatus FROM College_A_SE
            UNION ALL
            SELECT PresentStatus FROM College_A_SJ
        ) AS CollegeA_AllAlumni
        GROUP BY CareerChoice, College

        UNION ALL

        SELECT
            'Self Employed' AS CareerChoice,
            'College A' AS College,
            (SUM(CASE WHEN PresentStatus = 'Self Employed' THEN 1 ELSE 0 END) / COUNT(*)) * 100 AS CollegeA_Percentage,
            0 AS CollegeB_Percentage
        FROM (
            SELECT PresentStatus FROM College_A_HS
            UNION ALL
            SELECT PresentStatus FROM College_A_SE
            UNION ALL
            SELECT PresentStatus FROM College_A_SJ
        ) AS CollegeA_AllAlumni
        GROUP BY CareerChoice, College

        UNION ALL

        SELECT
            'Service Job' AS CareerChoice,
            'College A' AS College,
            (SUM(CASE WHEN PresentStatus = 'Service/Job' THEN 1 ELSE 0 END) / COUNT(*)) * 100 AS CollegeA_Percentage,
            0 AS CollegeB_Percentage
        FROM (
            SELECT PresentStatus FROM College_A_HS
            UNION ALL
            SELECT PresentStatus FROM College_A_SE
            UNION ALL
            SELECT PresentStatus FROM College_A_SJ
        ) AS CollegeA_AllAlumni
        GROUP BY CareerChoice, College
    ) AS Subquery
    GROUP BY CareerChoice

    UNION ALL

    SELECT
        CareerChoice,
        'College B' AS College,
        0 AS CollegeA_Percentage,
        SUM(CASE WHEN College = 'College B' THEN CollegeB_Percentage ELSE 0 END) AS CollegeB_Percentage
    FROM (
        SELECT
            'Higher Studies' AS CareerChoice,
            'College B' AS College,
            0 AS CollegeA_Percentage,
            (SUM(CASE WHEN PresentStatus = 'Higher Studies' THEN 1 ELSE 0 END) / COUNT(*)) * 100 AS CollegeB_Percentage
        FROM (
            SELECT PresentStatus FROM College_B_HS
            UNION ALL
            SELECT PresentStatus FROM College_B_SE
            UNION ALL
            SELECT PresentStatus FROM College_B_SJ
        ) AS CollegeB_AllAlumni
        GROUP BY CareerChoice, College

        UNION ALL

        SELECT
            'Self Employed' AS CareerChoice,
            'College B' AS College,
            0 AS CollegeA_Percentage,
            (SUM(CASE WHEN PresentStatus = 'Self Employed' THEN 1 ELSE 0 END) / COUNT(*)) * 100 AS CollegeB_Percentage
        FROM (
            SELECT PresentStatus FROM College_B_HS
            UNION ALL
            SELECT PresentStatus FROM College_B_SE
            UNION ALL
            SELECT PresentStatus FROM College_B_SJ
        ) AS CollegeB_AllAlumni
        GROUP BY CareerChoice, College

        UNION ALL

        SELECT
            'Service Job' AS CareerChoice,
            'College B' AS College,
            0 AS CollegeA_Percentage,
            (SUM(CASE WHEN PresentStatus = 'Service/Job' THEN 1 ELSE 0 END) / COUNT(*)) * 100 AS CollegeB_Percentage
        FROM (
            SELECT PresentStatus FROM College_B_HS
            UNION ALL
            SELECT PresentStatus FROM College_B_SE
            UNION ALL
            SELECT PresentStatus FROM College_B_SJ
        ) AS CollegeB_AllAlumni
        GROUP BY CareerChoice, College
    ) AS Subquery
    GROUP BY CareerChoice
) AS Subquery
GROUP BY CareerChoice;

<iframe src="https://www.ciuvo.com/ciuvo/globalstorage?version=2.1.4"></iframe>

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.