CREATE DATABASE CAMPUS_CONNECT_DB;
USE CAMPUS_CONNECT_DB;
-- Create tables
CREATE TABLE STUDENT (
Snum INTEGER PRIMARY KEY,
Sname VARCHAR(50),
Major VARCHAR(50),
Level VARCHAR(2),
Age INTEGER
);
CREATE TABLE FACULTY (
Fid INTEGER PRIMARY KEY,
Fname VARCHAR(50),
Deptid INTEGER
);
CREATE TABLE CLASS (
Cname VARCHAR(50) PRIMARY KEY,
Meets_at VARCHAR(50),
Room VARCHAR(10),
Fid INTEGER,
FOREIGN KEY (Fid) REFERENCES FACULTY(Fid)
);
CREATE TABLE ENROLLED (
Snum INTEGER,
Cname VARCHAR(50),
PRIMARY KEY (Snum, Cname),
FOREIGN KEY (Snum) REFERENCES STUDENT(Snum),
FOREIGN KEY (Cname) REFERENCES CLASS(Cname)
);
-- Insert sample data
INSERT INTO STUDENT (Snum, Sname, Major, Level, Age) VALUES
(1, 'Krishna', 'Computer Science', 'JR', 20),
(2, 'Rama', 'Mathematics', 'JR', 21),
(3, 'Narasimha', 'Physics', 'SR', 22),
(4, 'Hari', 'Chemistry', 'JR', 20),
(5, 'Ganesha', 'Biology', 'JR', 21);
INSERT INTO FACULTY (Fid, Fname, Deptid) VALUES
(1, 'Shiva', 101),
(2, 'Manjunatha', 102),
(3, 'Rudra', 103),
(4, 'Harshith', 104);
INSERT INTO CLASS (Cname, Meets_at, Room, Fid) VALUES
('Physics101', 'Monday 10:00 AM', 'R128', 1),
('Math201', 'Wednesday 2:00 PM', 'R129', 2),
('Chemistry301', 'Friday 4:00 PM', 'R128', 3),
('Biology401', 'Tuesday 11:00 AM', 'R130', 4),
('Computer501', 'Thursday 1:00 PM', 'R131', 1),
('DBMS', 'Monday 10:00 AM', 'R132', 2),
('OS', 'Thursday 1:00 PM', 'R130', 1),
('DS', 'Thursday 1:00 PM', 'R132', 1),
('Java', 'Thursday 1:00 PM', 'R129', 1);
INSERT INTO ENROLLED (Snum, Cname) VALUES
(1, 'Physics101'),
(1, 'DBMS'),
(2, 'Math201'),
(3, 'Chemistry301'),
(4, 'Biology401'),
(5, 'Computer501');
-- i. Find the names of all Juniors (level = JR) who are enrolled in a class taught by Prof. Harshith
SELECT S.Sname, F.Fname
FROM FACULTY F JOIN CLASS C ON F.Fid = C.FiD
JOIN ENROLLED E ON C.Cname = E.Cname
JOIN STUDENT S ON E.Snum = S.Snum
WHERE S.Level = "JR" AND F.Fname = "Harshith";
-- ii. Find the names of all classes that either meet in room R128 or have five or more Students enrolled.
SELECT DISTINCT Cname
FROM CLASS
WHERE Room = 'R128'
OR Cname IN (
SELECT Cname
FROM ENROLLED
GROUP BY Cname
HAVING COUNT(*) >= 5
);
-- iii. Find the names of all students who are enrolled in two classes that meet at the same time.
SELECT DISTINCT s.Sname
FROM STUDENT s
INNER JOIN ENROLLED e1 ON s.Snum = e1.Snum
INNER JOIN CLASS c1 ON e1.Cname = c1.Cname
INNER JOIN ENROLLED e2 ON s.Snum = e2.Snum
INNER JOIN CLASS c2 ON e2.Cname = c2.Cname
WHERE c1.Meets_at = c2.Meets_at AND e1.Cname != e2.Cname;
-- iv. Find the names of faculty members who teach in every room in which some class is taught.
select fname from faculty
where fid in
(select fid from class group by fid having count(*)>=(select count(distinct room)from class)) ;
