Project 2
-- Task 01
CREATE SCHEMA alumni;
use alumni;
-- Task 02
-- importing all files through table data import wizard
-- Task 03
DESC college_A_HS;
DESC college_A_SE;
DESC college_A_SJ;
DESC college_B_HS;
DESC college_B_SE;
DESC college_B_SJ;
-- Task 04
-- Use below SQL commands in python
/*
import mysql.connector
db=mysql.connector.connect(host="localhost",
user="root",
password="srisurya@5394",
database="alumni")
cur=db.cursor()
cur.execute("SELECT * FROM tablename LIMIT 1000")
rows=cur.fetchall()
for X in rows:
print(X)
*/
-- Task 05
-- Importing first 1500 of all tables through excel
-- Task 06
CREATE OR REPLACE VIEW College_A_HS_V AS
SELECT * FROM college_A_hs
WHERE RollNo IS NOT NULL
OR LastUpdate IS NOT NULL
OR Name IS NOT NULL
OR FatherName IS NOT NULL
OR MotherName IS NOT NULL
OR Batch IS NOT NULL
OR Degree IS NOT NULL
OR PresentStatus IS NOT NULL
OR HSDegree IS NOT NULL
OR EntranceExam IS NOT NULL
OR Institute IS NOT NULL
OR Location IS NOT NULL;
SELECT * FROM college_a_hs_v;
-- Task 07
CREATE OR REPLACE VIEW College_A_SE_V AS
SELECT * FROM college_a_se
WHERE RollNo IS NOT NULL
OR LastUpdate IS NOT NULL
OR Name IS NOT NULL
OR FatherName IS NOT NULL
OR MotherName IS NOT NULL
OR Batch IS NOT NULL
OR Degree IS NOT NULL
OR PresentStatus IS NOT NULL
OR Organization IS NOT NULL
OR Location IS NOT NULL ;
SELECT * FROM college_a_se_v;
-- Task 08
CREATE VIEW College_A_SJ_V AS
SELECT * FROM college_a_sj
WHERE RollNo IS NOT NULL
OR LastUpdate IS NOT NULL
OR Name IS NOT NULL
OR FatherName IS NOT NULL
OR MotherName IS NOT NULL
OR Batch IS NOT NULL
OR Degree IS NOT NULL
OR PresentStatus IS NOT NULL
OR Organization IS NOT NULL
OR Designation IS NOT NULL
OR Location IS NOT NULL ;
SELECT * FROM college_a_sj;
-- Task 09
CREATE VIEW College_B_HS_V AS
SELECT * FROM college_B_hs
WHERE RollNo IS NOT NULL
OR LastUpdate IS NOT NULL
OR Name IS NOT NULL
OR FatherName IS NOT NULL
OR MotherName IS NOT NULL
OR Batch IS NOT NULL
OR Degree IS NOT NULL
OR PresentStatus IS NOT NULL
OR HSDegree IS NOT NULL
OR EntranceExam IS NOT NULL
OR Institute IS NOT NULL
OR Location IS NOT NULL;
SELECT * FROM college_b_hs_v;
-- Task 10
CREATE VIEW College_B_SE_V AS
SELECT * FROM college_b_se
WHERE RollNo IS NOT NULL
OR LastUpdate IS NOT NULL
OR Name IS NOT NULL
OR FatherName IS NOT NULL
OR MotherName IS NOT NULL
OR Batch IS NOT NULL
OR Degree IS NOT NULL
OR PresentStatus IS NOT NULL
OR Organization IS NOT NULL
OR Location IS NOT NULL ;
SELECT * FROM college_b_se_v;
-- Task 11
CREATE VIEW College_B_SJ_V AS
SELECT * FROM college_b_sj
WHERE RollNo IS NOT NULL
OR LastUpdate IS NOT NULL
OR Name IS NOT NULL
OR FatherName IS NOT NULL
OR MotherName IS NOT NULL
OR Batch IS NOT NULL
OR Degree IS NOT NULL
OR PresentStatus IS NOT NULL
OR Organization IS NOT NULL
OR Designation IS NOT NULL
OR Location IS NOT NULL ;
SELECT * FROM college_b_sj;
-- Task 12
SELECT LOWER(Name),LOWER(FatherName),LOWER(MotherName) FROM college_A_HS_V;
SELECT LOWER(Name),LOWER(FatherName),LOWER(MotherName) FROM college_A_Se_V;
SELECT LOWER(Name),LOWER(FatherName),LOWER(MotherName) FROM college_A_Sj_V;
SELECT LOWER(Name),LOWER(FatherName),LOWER(MotherName) FROM college_B_HS_V;
SELECT LOWER(Name),LOWER(FatherName),LOWER(MotherName) FROM college_B_Se_V;
SELECT LOWER(Name),LOWER(FatherName),LOWER(MotherName) FROM college_B_Sj_V;
-- Task 13
-- this task performed in MsExcel
-- Task 14
DROP PROCEDURE IF EXISTS get_name_collegeA;
DELIMITER $$
CREATE PROCEDURE get_name_collegeA(INOUT Studentname TEXT(40000))
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE namelist VARCHAR(400) DEFAULT "";
DECLARE namedetail 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 namedetail;
getname:
LOOP
FETCH namedetail INTO namelist;
IF finished=1 THEN LEAVE getname;
END IF;
SET Studentname= CONCAT(namelist," , ",Studentname);
END LOOP getname;
CLOSE namedetail;
END $$
DELIMITER ;
SET @Studentname="";
CALL get_name_collegeA(@Studentname);
SELECT @Studentname;
-- Task 15
DROP PROCEDURE IF EXISTS get_name_collegeB;
DELIMITER $$
CREATE PROCEDURE get_name_collegeB(INOUT Studentname TEXT(40000))
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE namelist VARCHAR(400) DEFAULT "";
DECLARE namedetail 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 namedetail;
getname:
LOOP
FETCH namedetail INTO namelist;
IF finished=1 THEN LEAVE getname;
END IF;
SET Studentname= CONCAT(namelist," , ",Studentname);
END LOOP getname;
CLOSE namedetail;
END $$
DELIMITER ;
SET @Studentname="";
CALL get_name_collegeB(@Studentname);
SELECT @Studentname;
-- Task 16
use alumni;
SELECT COUNT(*) FROM college_a_hs; -- 117
SELECT COUNT(*) FROM college_a_se; -- 724
SELECT COUNT(*) FROM college_a_sj; -- 555
SELECT COUNT(*) FROM college_b_hs; -- 199
SELECT COUNT(*) FROM college_b_se; -- 201
SELECT COUNT(*) FROM college_b_sj; -- 1589
SELECT 'Higher studies',(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',(SELECT COUNT(*) FROM college_a_se)/((SELECT COUNT(*) FROM college_a_Se)+(SELECT COUNT(*) FROM college_a_hs)+(SELECT COUNT(*) FROM college_a_sj))*100 "College A Percentage",
(SELECT COUNT(*) FROM college_b_se)/((SELECT COUNT(*) FROM college_b_Se)+(SELECT COUNT(*) FROM college_b_hs)+(SELECT COUNT(*) FROM college_b_sj))*100 "College B Percentage"
UNION
SELECT 'Service Job',(SELECT COUNT(*) FROM college_a_sj)/((SELECT COUNT(*) FROM college_a_sj)+(SELECT COUNT(*) FROM college_a_hs)+(SELECT COUNT(*) FROM college_a_se))*100 "College A Percentage",
(SELECT COUNT(*) FROM college_b_sj)/((SELECT COUNT(*) FROM college_b_sj)+(SELECT COUNT(*) FROM college_b_hs)+(SELECT COUNT(*) FROM college_b_se))*100 "College B Percentage";
