Project 2
#Question1
CREATE DATABASE alumni ;
USE alumni ;
#Question3
DESC college_a_hs;
DESC college_a_se;
DESC college_a_sj;
DESC college_b_hs;
DESC college_b_se;
DESC college_b_sj;
#Question4
--Respective Python File is attached
import mysql.connector
testdb=mysql.connector.connect(host="localhost",user='root',password='Saurabh@123',database='alumni')
testcursor=testdb.cursor()
testcursor.execute("SELECT * FROM college_a_hs LIMIT 1000")
a=testcursor.fetchall()
for b in a:
print(b)
testcursor.execute("SELECT * FROM college_a_se LIMIT 1000")
c=testcursor.fetchall()
for d in c:
print(c)
testcursor.execute("SELECT * FROM college_a_sj LIMIT 1000")
e=testcursor.fetchall()
for f in e:
print(f)
testcursor.execute("SELECT * FROM college_b_hs LIMIT 1000")
g=testcursor.fetchall()
for h in g:
print(h)
testcursor.execute("SELECT * FROM college_b_se LIMIT 1000")
i=testcursor.fetchall()
for j in i:
print(j)
testcursor.execute("SELECT * FROM college_b_sj LIMIT 1000")
k=testcursor.fetchall()
for l in k:
print(l)
#Question5
--Excel File is attached
#Question-6
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
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 ;
#Question-7
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;
#Question8
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;
#Question9
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;
#Question10
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;
#QuestioN11
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;
#Question12
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;
#Question-14
DROP PROCEDURE IF EXISTS get_name_collegeA;
DELIMITER $$
CREATE PROCEDURE get_name_collegeA
(
INOUT name1 TEXT(40000)
)
BEGIN
DECLARE na INT DEFAULT 0;
DECLARE namelist VARCHAR(16000) 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 na =1;
OPEN namedetail;
getame :
LOOP
FETCH FROM namedetail INTO namelist;
IF na = 1 THEN
LEAVE getame;
END IF;
SET name1 = CONCAT(namelist,";",name1);
END LOOP getame;
CLOSE namedetail;
END $$
DELIMITER ;
SET @Name = "";
CALL get_name_collegeA(@Name);
SELECT @Name Name;
#Question 15
DROP PROCEDURE IF EXISTS get_name_collegeB;
DELIMITER $$
CREATE PROCEDURE get_name_collegeB
(
INOUT name1 TEXT(40000)
)
BEGIN
DECLARE na INT DEFAULT 0;
DECLARE namelist VARCHAR(16000) 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 na =1;
OPEN namedetail;
getame :
LOOP
FETCH FROM namedetail INTO namelist;
IF na = 1 THEN
LEAVE getame;
END IF;
SET name1 = CONCAT(namelist,";",name1);
END LOOP getame;
CLOSE namedetail;
END $$
DELIMITER ;
SET @Name = "";
CALL get_name_collegeB(@Name);
SELECT @Name Name;
#Question-16
SELECT "Higher Studies" Present_status,(COUNT(college_a_hs.RollNo) /(college_a_hs.RollNo))*100 College_A_Persentage,(COUNT(college_b_hs.RollNo)/(college_b_hs.RollNo))*100 College_B_Persentage FROM college_a_hs CROSS JOIN college_b_hs UNION
SELECT "Self Empolyment" Present_status,(COUNT(college_a_se.RollNo) /(college_a_se.RollNo))*100 College_A_Persentage,(COUNT(college_b_se.RollNo)/(college_b_se.RollNo))*100 College_B_Persentage FROM college_a_se CROSS JOIN college_b_se UNION
SELECT "Service Job" Present_status,(COUNT(college_a_sj.RollNo) /(college_a_sj.RollNo))*100 College_A_Persentage,(COUNT(college_b_sj.RollNo)/(college_b_sj.RollNo))*100 College_B_Persentage FROM college_a_sj CROSS JOIN college_b_sj;