Project 2

Q4. 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.

Ans.

import mysql.connector

testdb=mysql.connector.connect(
host="localhost",
user="root",
password="Deepanshu@8109",
database="Alumni")
testcursor=testdb.cursor()
testcursor.execute("select*from college_A_HS Limit 1000")
for x in testcursor:
print(x)

testdb=mysql.connector.connect(
host="localhost",
user="root",
password="Deepanshu@8109",
database="Alumni")
testcursor=testdb.cursor()
testcursor.execute("select*from college_A_SE Limit 1000")
for x in testcursor:
print(x)

testdb=mysql.connector.connect(
host="localhost",
user="root",
password="Deepanshu@8109",
database="Alumni")
testcursor=testdb.cursor()
testcursor.execute("select*from college_A_SJ Limit 1000")
for x in testcursor:
print(x)

testdb=mysql.connector.connect(
host="localhost",
user="root",
password="Deepanshu@8109",
database="Alumni")
testcursor=testdb.cursor()
testcursor.execute("select*from college_B_HS Limit 1000")
for x in testcursor:
print(x)

testdb=mysql.connector.connect(
host="localhost",
user="root",
password="Deepanshu@8109",
database="Alumni")
testcursor=testdb.cursor()
testcursor.execute("select*from college_B_SE Limit 1000")
for x in testcursor:
print(x)

testdb=mysql.connector.connect(
host="localhost",
user="root",
password="Deepanshu@8109",
database="Alumni")
testcursor=testdb.cursor()
testcursor.execute("select*from college_B_SJ Limit 1000")
for x in testcursor:
print(x)

#Q14
DELIMITER $$
CREATE PROCEDURE get_name_collegeA
(
INOUT name1 MEDIUMTEXT
)
BEGIN
DECLARE finished INT DEFAULT 0;
DECLARE namelist VARCHAR(400) DEFAULT "";

DECLARE namedetail
CURSOR FOR
SELECT name from college_a_hs_v UNION SELECT name from college_a_se_v
UNION SELECT name from college_a_sj_v;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET finished = 1;

OPEN namedetail;

getname1:
LOOP
FETCH namedetail INTO namelist;
IF finished=1 THEN
LEAVE getname1;
END IF;

SET name1=CONCAT(namelist,";",name1);
END LOOP getname1;
CLOSE namedetail;
END $$
DELIMITER ;

SET @name1="";
CALL get_name_collegeA(@name1);
SELECT @name1 name;

#Q15
DELIMITER $$
CREATE PROCEDURE get_name_collegeB
(
INOUT name1 MEDIUMTEXT
)
BEGIN
DECLARE finished INT DEFAULT 0;
DECLARE namelist VARCHAR(400) DEFAULT "";

DECLARE namedetail
CURSOR FOR
SELECT name from college_b_hs_v UNION SELECT name from college_b_se_v
UNION SELECT name from college_b_sj_v;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET finished = 1;

OPEN namedetail;

getname1:
LOOP
FETCH namedetail INTO namelist;
IF finished=1 THEN
LEAVE getname1;
END IF;

SET name1=CONCAT(namelist,";",name1);
END LOOP getname1;
CLOSE namedetail;
END $$
DELIMITER ;

SET @name1="";
CALL get_name_collegeB(@name1);
SELECT @name1 name;

#Q16
SELECT "Higher Studies" PresentStatus, (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" PresentStatus, (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" PresentStatus, (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_se)/
((SELECT COUNT(*) FROM college_b_hs) + (SELECT COUNT(*) FROM college_b_se)+
(SELECT COUNT(*) FROM college_b_sj))*100 "College B Percentage";