4.sql

CREATE DATABASE THESIS;
USE THESIS;

CREATE TABLE STUDENT(
    SRollno varchar(20),
    Sname varchar(20),
    Saddress varchar(20),
    Semailid varchar(20),
    Sphoneno BIGINT CONSTRAINT tendigits CHECK(Sphoneno BETWEEN 1000000000 AND 9999999999),
    PRIMARY KEY(SRollno)
);

-- Insert student data
INSERT INTO STUDENT VALUES('CS2401M', 'Sunil', '123 Main St', 'sunil@univ.edu', 9876543210);
INSERT INTO STUDENT VALUES('IT2502P', 'Suresh Nair', '456 Park Ave', 'suresh@univ.edu', 9123456789);
INSERT INTO STUDENT VALUES('CS2402M', 'Geetha R', '789 Oak Rd', 'geetha@univ.edu', 9988776655);
INSERT INTO STUDENT VALUES('ME2503P', 'Hari Mohan', '321 Maple St', 'hari@univ.edu', 9765432101);
INSERT INTO STUDENT VALUES('CS2403M', 'Anita', '555 Elm St', 'anita@univ.edu', 9654321987);
INSERT INTO STUDENT VALUES('IT2504P', 'Rajesh', '111 Cedar Rd', 'rajesh@univ.edu', 9543218765);


CREATE TABLE GUIDE(
    Gid varchar(20),
    Gname varchar(20),
    Gresearcharea varchar(20),
    Gdept varchar(20),
    Gdesignation varchar(20),
    Gmailid varchar(20),
    Gphoneno BIGINT CONSTRAINT decdigits CHECK(Gphoneno BETWEEN 1000000000 AND 9999999999),
    PRIMARY KEY(Gid)
);

-- Insert guide data
INSERT INTO GUIDE VALUES('G101', 'Ram', 'AI and ML', 'CS', 'Professor', 'ram@univ.edu', 9876543210);
INSERT INTO GUIDE VALUES('G102', 'Raju', 'Data Science', 'IT', 'Associate Professor', 'raju@univ.edu', 9123456789);
INSERT INTO GUIDE VALUES('G103', 'Ravi', 'Cybersecurity', 'CS', 'Assistant Professor', 'ravi@univ.edu', 9988776655);
INSERT INTO GUIDE VALUES('G104', 'Hari', 'Robotics', 'ME', 'Professor', 'hari@univ.edu', 9765432101);
INSERT INTO GUIDE VALUES('G105', 'Neha', 'Algorithms', 'CS', 'Lecturer', 'neha@univ.edu', 9654321098);
INSERT INTO GUIDE VALUES('G105', 'Neha', 'AI and ML', 'CS', 'Lecturer', 'neha@univ.edu', 9654321098);
INSERT INTO GUIDE VALUES('G106', 'Sita', 'Cybersecurity', 'CS', 'Professor', 'sita@univ.edu', 1234567890);
INSERT INTO GUIDE VALUES('G107', 'Amit', 'Data Science', 'IT', 'Associate Professor', 'amit@univ.edu', 2345678901);



CREATE TABLE THESIS(
    SRollno varchar(20),
    Gid varchar(20),
    Thesisid int,
    Thesistitle varchar(50),	
    Thesiscentralarea varchar(50),
    Thesisdate date,
    Thesistype VARCHAR(20),
    PRIMARY KEY(Thesisid),
    FOREIGN KEY(SRollno) REFERENCES STUDENT(SRollno) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(Gid) REFERENCES GUIDE(Gid) ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO THESIS VALUES('CS2401M', 'G101', 1001, 'AI for Healthcare', 'AI and ML', '2023-06-15', 'PhD');
INSERT INTO THESIS VALUES('CS2401M', 'G102', 1022, 'AI for Healthcare', 'AI and ML', '2023-06-15', 'MS');
INSERT INTO THESIS VALUES('IT2502P', 'G102', 1002, 'Big Data Analytics', 'Data Science', '2023-07-10', 'PhD');
INSERT INTO THESIS VALUES('CS2402M', 'G102', 1003, 'Machine Learning Applications', 'Data Science', '2023-08-05', 'PhD');
INSERT INTO THESIS VALUES('ME2503P', 'G103', 1004, 'Cyber Defense Mechanisms', 'Cybersecurity', '2023-09-12', 'MS');
INSERT INTO THESIS VALUES('CS2403M', 'G104', 1005, 'Robotics for Beginners', 'Robotics', '2023-10-20', 'MS');
INSERT INTO THESIS VALUES('IT2504P', 'G104', 1006, 'Cloud Computing Security', 'Robotics', '2023-11-15', 'MS');
INSERT INTO THESIS VALUES('ME2505P', 'G104', 1007, 'Advanced Robotics', 'Robotics', '2023-11-30', 'MS');
INSERT INTO THESIS VALUES('IT2502P', 'G104', 1008, 'Robotic Process Automation', 'Robotics', '2024-01-15', 'PhD');
INSERT INTO THESIS VALUES('CS2402M', 'G101', 1009, 'Approximation Algorithms', 'Algorithms', '2023-12-01', 'PhD');
INSERT INTO THESIS VALUES('CS2403M', 'G105', 1020, 'Advanced Algorithms', 'Algorithms', '2024-02-01', 'MS');
INSERT INTO THESIS VALUES('CS2403M', 'G105', 1020, 'Advanced Algorithms', 'Algorithms', '2024-02-01', 'PhD');
INSERT INTO THESIS VALUES('CS2403M', 'G105', 1015, 'AI for Healthcare', 'AI and ML', '2024-02-01', 'PhD');
INSERT INTO THESIS VALUES('IT2502P', 'G105', 1011, 'Sorting Algorithms', 'Algorithms', '2024-03-15', 'MS');
INSERT INTO THESIS VALUES('ME2503P', 'G105', 1012, 'Graph Algorithms', 'Algorithms', '2024-04-20', 'PhD');
INSERT INTO THESIS VALUES('CS2402M', 'G102', 1087, 'Machine Learning for Education', 'Data Science', '2024-02-10', 'MS');
INSERT INTO THESIS VALUES('CS2402M', 'G103', 1099, 'Data Mining Techniques', 'Data Science', '2024-03-12', 'MS');
INSERT INTO THESIS VALUES('CS2404M', 'G101', 1010, 'Deep Learning Techniques', 'AI and ML', '2024-05-01', 'PhD');
INSERT INTO THESIS VALUES('IT2505P', 'G102', 1013, 'Ethics in AI', 'AI and ML', '2024-06-10', 'PhD');
INSERT INTO THESIS VALUES('ME2506P', 'G103', 1014, 'Secure Cloud Architectures', 'Cybersecurity', '2024-07-15', 'PhD');
INSERT INTO THESIS VALUES('CS2405M', 'G104', 1016, 'Internet of Things Security', 'Cybersecurity', '2024-08-20', 'PhD');
INSERT INTO THESIS VALUES('CS2406M', 'G105', 1017, 'Quantum Computing Applications', 'Algorithms', '2024-09-25', 'PhD');



-- Create INDICES table
CREATE TABLE INDICES(
    Ikeyword varchar(50),
    Thesisid int,
    PRIMARY KEY(Ikeyword),
    FOREIGN KEY(Thesisid) REFERENCES THESIS(Thesisid) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Insert index data
INSERT INTO INDICES VALUES('Artificial Intelligence', 1001);
INSERT INTO INDICES VALUES('Data Analytics', 1002);
INSERT INTO INDICES VALUES('Machine Learning', 1003);
INSERT INTO INDICES VALUES('Cybersecurity', 1004);
INSERT INTO INDICES VALUES('Robotics', 1005);
INSERT INTO INDICES VALUES('Cloud Computing', 1006);
INSERT INTO INDICES VALUES('Robotics', 1007);
INSERT INTO INDICES VALUES('Process Automation', 1008);
INSERT INTO INDICES VALUES('Approximation Algorithms', 1009);
INSERT INTO INDICES VALUES('Graph Theory', 1010);
INSERT INTO INDICES VALUES('Sorting Algorithms', 1011);
INSERT INTO INDICES VALUES('Algorithm Design', 1012);


SELECT * FROM INDICES;

SELECT g.Gid, g.Gname, g.Gresearcharea, g.Gdept, g.Gdesignation, g.Gmailid, g.Gphoneno
FROM GUIDE g
JOIN THESIS t ON g.Gid = t.Gid
JOIN INDICES i ON t.Thesisid = i.Thesisid
WHERE i.Ikeyword = 'Approximation Algorithms';

SELECT G.Gid, G.Gname, G.Gresearcharea, G.Gdept, G.Gdesignation, G.Gmailid, G.Gphoneno, COUNT(T.Thesisid) AS thesis_count
FROM GUIDE G
JOIN THESIS T ON G.Gid = T.Gid
GROUP BY G.Gid, G.Gname, G.Gresearcharea, G.Gdept, G.Gdesignation, G.Gmailid, G.Gphoneno
HAVING thesis_count = (
    SELECT MAX(thesis_count)
    FROM (SELECT COUNT(Thesisid) AS thesis_count FROM THESIS GROUP BY Gid) AS MaxThesisCounts
);

SELECT G.Gid, G.Gname, G.Gresearcharea, G.Gdept, G.Gdesignation, G.Gmailid, G.Gphoneno, COUNT(T.Thesisid) AS thesis_count
FROM GUIDE G
JOIN THESIS T ON G.Gid = T.Gid
GROUP BY G.Gid, G.Gname, G.Gresearcharea, G.Gdept, G.Gdesignation, G.Gmailid, G.Gphoneno
HAVING thesis_count = (
    SELECT MIN(thesis_count)
    FROM (SELECT COUNT(Thesisid) AS thesis_count FROM THESIS GROUP BY Gid) AS MinThesisCounts
);

SELECT s.SRollno, s.Sname, t.Thesiscentralarea, COUNT(t.Thesisid) AS Thesiscount
FROM STUDENT s
JOIN THESIS t ON s.SRollno = t.SRollno
GROUP BY s.SRollno, s.Sname, t.Thesiscentralarea
HAVING COUNT(t.Thesisid) > 1 AND COUNT(DISTINCT t.Gid) > 1;

SELECT G.Gid, G.Gname, G.Gresearcharea, G.Gdept, G.Gdesignation, G.Gmailid, G.Gphoneno
FROM GUIDE G
JOIN THESIS T ON G.Gid = T.Gid
WHERE T.Thesistype = 'PhD'
GROUP BY G.Gid, G.Gname, G.Gresearcharea, G.Gdept, G.Gdesignation, G.Gmailid, G.Gphoneno
HAVING COUNT(DISTINCT T.Thesiscentralarea) > 1;


SELECT I.Ikeyword,
       COUNT(CASE WHEN T.Thesistitle LIKE '%PhD%' THEN 1 END) AS PhD_Thesis_Count,
       COUNT(CASE WHEN T.Thesistitle LIKE '%MS%' THEN 1 END) AS MS_Thesis_Count
FROM INDICES I
JOIN THESIS T ON I.Thesisid = T.Thesisid
GROUP BY I.Ikeyword;

SELECT G.Gid, G.Gname, G.Gresearcharea, G.Gdept, G.Gdesignation, G.Gmailid, G.Gphoneno
FROM GUIDE G
LEFT JOIN THESIS T ON G.Gid = T.Gid AND T.Thesisdate BETWEEN '2023-01-01' AND '2023-12-31'
WHERE T.Thesisid IS NULL;

SELECT Thesiscentralarea, COUNT(Thesisid) AS Thesis_Count
FROM THESIS
GROUP BY Thesiscentralarea
ORDER BY Thesis_Count DESC
LIMIT 1;

SELECT Thesiscentralarea, COUNT(Thesisid) AS Thesis_Count
FROM THESIS
GROUP BY Thesiscentralarea
ORDER BY Thesis_Count ASC
LIMIT 1;

SELECT G.Gid, G.Gname, G.Gresearcharea, G.Gdept, G.Gdesignation, G.Gmailid, G.Gphoneno
FROM GUIDE G
JOIN THESIS T ON G.Gid = T.Gid
WHERE T.Thesistitle LIKE '%MS%'
GROUP BY G.Gid, G.Gname, G.Gresearcharea, G.Gdept, G.Gdesignation, G.Gmailid, G.Gphoneno
HAVING COUNT(DISTINCT T.Thesiscentralarea) = 1;

SELECT s.SRollno, s.Sname
FROM STUDENT s
JOIN THESIS t1 ON s.SRollno = t1.SRollno
JOIN THESIS t2 ON s.SRollno = t2.SRollno AND t1.Thesisid <> t2.Thesisid
JOIN INDICES i1 ON t1.Thesisid = i1.Thesisid
JOIN INDICES i2 ON t2.Thesisid = i2.Thesisid
WHERE i1.Ikeyword <> i2.Ikeyword
GROUP BY s.SRollno, s.Sname
HAVING COUNT(DISTINCT t1.Thesisid) > 1;