Project 2
Question 1.
CREATE DATABASE alumni;
Question 2.
Question 3.
DESC college_a_hs;
DESC college_a_se;
DESC college_a_sj;
DESC college_b_hs;
DESC college_b_se;
DESC college_b_sj;
Question 4.
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="root@725",
database="alumni"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM college_a_hs LIMIT 1000")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Question 5.
College_A_HS,
College_A_SE,
College_A_SJ,
College_B_HS,
College_B_SE,
College_B_SJ
Question 6.
-- 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 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.
DESC college_a_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;
Question 8.
-- Question 8
DESC college_a_sj;
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;
Question 9.
-- Question 9
DESC college_b_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 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;
Question 10.
-- Question 10
DESC college_b_se;
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;
Question 11.
-- Question 11
DESC college_b_sj;
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_SE_V;
Question 12.
CALL lowercollege_a_hs_v;
CALL lowercollege_a_se_v;
CALL lowercollege_a_sj_v;
CALL lowercollege_b_hs_v;
CALL lowercollege_b_se_v;
CALL lowercollege_b_sj_v;
Question 13.
College_A_HS_V
College_A_SE_V
College_A_SJ_V
College_B_HS_V
College_B_SE_V
College_B_SJ_V
Question 14.
-- Question 14
DELIMITER $$
CREATE PROCEDURE get_name_collegeA (INOUT name1 TEXT(40000))
BEGIN
DECLARE done 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 done =TRUE;
OPEN namedetail;
getame :
LOOP
FETCH FROM namedetail INTO namelist;
IF done = TRUE 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.
-- Question 15
DELIMITER $$
CREATE PROCEDURE get_name_collegeB (INOUT name1 TEXT(40000))
BEGIN
DECLARE done 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 done =TRUE;
OPEN namedetail;
getame :
LOOP
FETCH FROM namedetail INTO namelist;
IF done = TRUE 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.
-- Question 16
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_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',
(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';
