Project 2
-- Create new schema as alumni
create database alumni;
use alumni;
-- Import all .csv files into MySQL
show tables;
-- Run SQL command to see the structure of six tables
desc college_a_hs;
desc college_a_se;
desc college_a_sj;
desc college_b_hs;
desc college_b_se;
desc college_b_sj;
-- Display first 1000 rows of tables
-- (College_A_HS, College_A_SE, College_A_SJ, College_B_HS, College_B_SE, College_B_SJ) with Python.
/*
Perform data cleaning on table College_A_HS and
store cleaned data in view College_A_HS_V, Remove null values.
*/
use alumni;
SELECT * FROM college_a_hs;
CREATE or replace view TEST AS SELECT COUNT(*) FROM college_a_hs;
-- seperating rollno
CREATE VIEW rollnoview as select RollNo,
if(RollNo regexp '^[0-9]+$',cast(RollNo as unsigned),0) as Numerical_RollNo,
case when RollNo regexp '^[0-9]+$' then 0 else RollNo end as Non_Numerical_RollNo
from college_a_hs;
SELECT * FROM rollnoview;
-- converting dates
SELECT * FROM college_a_hs;
create or replace view Dates as select LastUpdate,str_to_date(LastUpdate,'%d-%b-%y') as Converted_dates
from college_a_hs;
SELECT * FROM Dates;
-- lower case convert
create or replace view lowercase as select lower(Name),lower(FatherName),lower(MotherName) from college_a_hs;
SELECT * FROM lowercase;
-- filtering in degree
SELECT * FROM college_a_hs;
create or replace view maatch as select * from college_a_hs where Degree='BE' ;
SELECT * FROM maatch;
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;
/*
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;
desc College_A_SE;
select count(*) from College_A_SE;
-- seperating rollno
CREATE or replace VIEW rollnoview_se as select RollNo,
if(RollNo regexp '^[0-9]+$',cast(RollNo as unsigned),0) as Numerical_RollNo,
case when RollNo regexp '^[0-9]+$' then 0 else RollNo end as Non_Numerical_RollNo
from College_A_SE;
SELECT * FROM rollnoview_se;
-- converting dates
SELECT * FROM College_A_SE;
create or replace view Dates_se as select LastUpdate,str_to_date(LastUpdate,'%d-%b-%y') as Converted_dates
from College_A_SE;
SELECT * FROM Dates_se;
-- lower case convert
create or replace view lowercase_se as select lower(Name),lower(FatherName),lower(MotherName) from College_A_SE;
SELECT * FROM lowercase_se;
-- filtering in degree
SELECT * FROM College_A_SE;
create or replace view maatch_SE as select * from college_a_hs where Degree='BARC' ;
SELECT * FROM maatch_SE;
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;
/*
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;
-- seperating rollno
CREATE or replace VIEW rollnoview_sJ as select RollNo,
if(RollNo regexp '^[0-9]+$',cast(RollNo as unsigned),0) as Numerical_RollNo,
case when RollNo regexp '^[0-9]+$' then 0 else RollNo end as Non_Numerical_RollNo
from College_A_SJ;
SELECT * FROM rollnoview_sJ;
-- converting dates
SELECT * FROM College_A_SJ;
create or replace view Dates_sJ as select LastUpdate,str_to_date(LastUpdate,'%d-%b-%y') as Converted_dates
from College_A_SJ;
SELECT * FROM Dates_sJ;
CREATE VIEW College_A_SJ_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_SJ_V;
/*
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;
-- seperating rollno
CREATE or replace VIEW rollnoview_HS as select RollNo,
if(RollNo regexp '^[0-9]+$',cast(RollNo as unsigned),0) as Numerical_RollNo,
case when RollNo regexp '^[0-9]+$' then 0 else RollNo end as Non_Numerical_RollNo
from College_B_HS;
SELECT * FROM rollnoview_HS;
-- converting dates
SELECT * FROM College_B_HS;
create or replace view Dates_HS as select LastUpdate,str_to_date(LastUpdate,'%d-%b-%y') as Converted_dates
from College_B_HS;
SELECT * FROM Dates_HS;
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 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;
/*
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;
-- seperating rollno
CREATE or replace VIEW rollnoview_sE as select RollNo,
if(RollNo regexp '^[0-9]+$',cast(RollNo as unsigned),0) as Numerical_RollNo,
case when RollNo regexp '^[0-9]+$' then 0 else RollNo end as Non_Numerical_RollNo
from College_B_SE;
SELECT * FROM rollnoview_sE;
-- converting dates
SELECT * FROM College_B_SE;
create or replace view Dates_sE as select LastUpdate,str_to_date(LastUpdate,'%d-%b-%y') as Converted_dates
from College_B_SE;
SELECT * FROM Dates_sE;
CREATE OR replace 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 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;
/*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;
-- seperating rollno
CREATE or replace VIEW rollnoview_sJ as select RollNo,
if(RollNo regexp '^[0-9]+$',cast(RollNo as unsigned),0) as Numerical_RollNo,
case when RollNo regexp '^[0-9]+$' then 0 else RollNo end as Non_Numerical_RollNo
from College_B_SJ;
SELECT * FROM rollnoview_sJ;
-- converting dates
SELECT * FROM College_B_SJ;
create or replace view Dates_sJ as select LastUpdate,str_to_date(LastUpdate,'%d-%b-%y') as Converted_dates
from College_B_SJ;
SELECT * FROM Dates_sJ;
CREATE OR replace 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 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_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)
*/
DELIMITER //
CREATE PROCEDURE lowercase_names()
BEGIN
-- Convert College A HS view columns to lowercase
UPDATE College_A_HS_V SET
Name = LOWER(Name),
FatherName = LOWER(FatherName),
MotherName = LOWER(MotherName);
-- Convert College A SE view columns to lowercase
UPDATE College_A_SE_V SET
Name = LOWER(Name),
FatherName = LOWER(FatherName),
MotherName = LOWER(MotherName);
-- Convert College A SJ view columns to lowercase
UPDATE College_A_SJ_V SET
Name = LOWER(Name),
FatherName = LOWER(FatherName),
MotherName = LOWER(MotherName);
-- Convert College B HS view columns to lowercase
UPDATE College_B_HS_V SET
Name = LOWER(Name),
FatherName = LOWER(FatherName),
MotherName = LOWER(MotherName);
-- Convert College B SE view columns to lowercase
UPDATE College_B_SE_V SET
Name = LOWER(Name),
FatherName = LOWER(FatherName),
MotherName = LOWER(MotherName);
-- Convert College B SJ view columns to lowercase
UPDATE College_B_SJ_V SET
Name = LOWER(Name),
FatherName = LOWER(FatherName),
MotherName = LOWER(MotherName);
END //
DELIMITER ;
CALL lowercase_names();
SELECT * FROM College_B_SJ_V;
/*
14 Write a query to create procedure get_name_collegeA using the cursor to fetch names
of all students from college A.
*/
SHOW TABLES;
DELIMITER //
CREATE PROCEDURE get_name_collegeA()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE student_name VARCHAR(255);
-- Declare cursor to fetch names from College A
DECLARE cursor_name CURSOR FOR
SELECT Name FROM college_a_hs
UNION
SELECT Name FROM college_a_se
UNION
SELECT Name FROM college_a_sj;
-- Declare handler for cursor
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- Open the cursor and fetch names
OPEN cursor_name;
read_loop: LOOP
-- Fetch next name
FETCH cursor_name INTO student_name;
-- If no more names, exit loop
IF done THEN
LEAVE read_loop;
END IF;
-- Output student name
SELECT student_name;
END LOOP;
-- Close the cursor
CLOSE cursor_name;
END //
DELIMITER ;
DROP PROCEDURE IF EXISTS get_name_collegeA;
CALL get_name_collegeA();
/*
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()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE student_name VARCHAR(255);
-- Declare cursor to fetch names from College A
DECLARE cursor_name CURSOR FOR
SELECT Name FROM college_b_hs
UNION
SELECT Name FROM college_b_se
UNION
SELECT Name FROM college_b_sj;
-- Declare handler for cursor
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- Open the cursor and fetch names
OPEN cursor_name;
read_loop: LOOP
-- Fetch next name
FETCH cursor_name INTO student_name;
-- If no more names, exit loop
IF done THEN
LEAVE read_loop;
END IF;
-- Output student name
SELECT student_name;
END LOOP;
-- Close the cursor
CLOSE cursor_name;
END //
DELIMITER ;
DROP PROCEDURE IF EXISTS get_name_collegeB;
CALL get_name_collegeB();