CREATE DATABASE student_guide_15554;
USE student_guide_15554;
CREATE TABLE guides_15554(guide_number INT PRIMARY KEY NOT NULL, guide_name VARCHAR(20) NOT NULL, research_domain VARCHAR(20), contact_number VARCHAR(20));
CREATE TABLE students_15554(roll_no INT PRIMARY KEY NOT NULL, name VARCHAR(20) NOT NULL, semester INT NOT NULL, degree VARCHAR(7) NOT NULL, contact_number VARCHAR(10), guide_number INT NOT NULL, FOREIGN KEY(guide_number) REFERENCES guides_15554(guide_number));
CREATE TABLE projects_15554(project_number INT PRIMARY KEY NOT NULL, title VARCHAR(20) NOT NULL, area VARCHAR(20), guide_number INT NOT NULL, FOREIGN KEY (guide_number) REFERENCES guides_15554(guide_number));

/*POPULATING THE TABLES WITH MOCK DATA*/
INSERT INTO guides_15554 VALUES(101, "Rodrigo Sharma", "Android", "919191212");
INSERT INTO guides_15554 VALUES(102, "Mercer Nuke", "AI", "123191212");
INSERT INTO guides_15554 VALUES(103, "Ezio Butler", "Database", "3451112");
INSERT INTO guides_15554 VALUES(104, "Auditore Yagami", "Big data", "56121212");
INSERT INTO guides_15554 VALUES(121, "Firenze Kai", "Lazy programmer", "56711212");
INSERT INTO guides_15554 VALUES(181, "Alex Mercer", "Database", "41121212");
INSERT INTO guides_15554 VALUES(200, "Lelouch Lambrouch", "Network Security", "191911212");

INSERT INTO students_15554 VALUES(15551, "Alpha", 3, "B.Tech", "513123123", 200);
INSERT INTO students_15554 VALUES(101, "Beta", 1, "M.Tech", "5233123", 102);
INSERT INTO students_15554 VALUES(15553, "Gamma", 2, "B.Tech", "53212123", 101);
INSERT INTO students_15554 VALUES(15554, "Beta", 6, "B.Tech", "54212123", 103);
INSERT INTO students_15554 VALUES(15555, "Comma", 8, "M.Tech", "55213123", 121);
INSERT INTO students_15554 VALUES(103, "Full stop", 10, "P.hd", "562123123", 181);
INSERT INTO students_15554 VALUES(15557, "Exclamation", 9, "P.hd", "57223123", 200);

INSERT INTO projects_15554 VALUES(46, "Taxify", "Android", 200);
INSERT INTO projects_15554 VALUES(47, "Amahi android", "Android", 102);
INSERT INTO projects_15554 VALUES(48, "BuildmLearn toolkit", "Database", 101);
INSERT INTO projects_15554 VALUES(49, "Susi", "Database", 103);
INSERT INTO projects_15554 VALUES(50, "Lokalok", "Cloud", 121);
INSERT INTO projects_15554 VALUES(51, "Wallet", "Network security", 181);
INSERT INTO projects_15554 VALUES(52, "Amahi TV", "Android TV", 200);

/*GET A LIST OF GUIDES WHO MENTOR TWO OR MORE THAN TWO STUDENTS*/
SELECT guides_15554.guide_number, guides_15554.guide_name, guides_15554.research_domain FROM guides_15554, students_15554
	WHERE guides_15554.guide_number = students_15554.guide_number
    GROUP BY students_15554.guide_number
	HAVING count(students_15554.guide_number)>=2;

/*Get a list of project number, project name and name of guides who are guiding projects in project area database.*/
SELECT projects_15554.project_number, projects_15554.title, projects_15554.area, guides_15554.guide_name FROM projects_15554, guides_15554
	WHERE guides_15554.guide_number = projects_15554.guide_number
    HAVING projects_15554.area = "Database";

/*Update guide details of student with roll number 101 for a new guide as "Ram Sharma" and guide number "13"*/
INSERT INTO guides_15554 VALUES(13, "Ram Sharma", "Database", "41121212");
UPDATE students_15554
	SET guide_number = 13
	WHERE guide_number = 101;

SELECT * FROM students_15554;
SELECT * FROM guides_15554;

DROP DATABASE student_guide_15554;