Challenge

Project 2

SShalini Kanagasabapathy19 May 2023

#Answer 1

create database alumni;

# Answer 2-Table imported

#Answer 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;

# Answer 4

import mysql.connector
tesdb=mysql.connector.connect(host="localhost",user="root",password="12345",database="alumni")
tescursor=tesdb.cursor()
tescursor.execute("select * from college_a_hs limit 1000")
for x in tescursor:
print(x)

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;

7.CREATE VIEW college_A_SE_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_SE_V;

8.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;

9.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;

10.CREATE VIEW College_B_SE_VIEW 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_VIEW;

11.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;

12.

CREATE PROCEDURE `new_procedure` ()
BEGIN
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;
END

# ANSWER 13

#ANSWER 14

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;

#ANSWER 15

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;

#ANSWER 16

SELECT COUNT(RollNo) FROM COLLEGE_A_HS UNION SELECT COUNT(RollNo) FROM COLLEGE_A_SE UNION SELECT COUNT(RollNo) FROM COLLEGE_A_SJ ;
select 1158+725+4007;
SELECT COUNT(RollNo) FROM COLLEGE_b_HS UNION SELECT COUNT(RollNo) FROM COLLEGE_b_SE UNION SELECT COUNT(RollNo) FROM COLLEGE_b_SJ ;
select 199+201+1859;
SELECT "Higher Studies" Present_status,(1158 /5890)*100 College_A_Persentage,(199/2259)*100 College_B_Persentage UNION
SELECT "Self Empolyment" Present_status,(725/5890)*100 College_A_Persentage,(201/2259)*100 College_B_Persentage UNION
SELECT "Service Job" Present_status,(4007/5890)*100 College_A_Persentage,(1859/2259)*100 College_B_Persentage ;

Project 2

Ship work like this
in your own course.

Talk to a career counsellor

Ship work like thisin your own course.

Talk to a career counsellor

Ship work like this
in your own course.