By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
/* DATA SETUP */
CREATE TABLE time_slot(
time_slot_id VARCHAR(100) NOT NULL,
day VARCHAR(100) NOT NULL,
start_hour int NOT NULL,
start_min int NOT NULL,
end_hour int NOT NULL,
end_min int NOT NULL,
PRIMARY KEY (time_slot_id, day, start_hour, start_min)
);
CREATE TABLE department(
dept_name VARCHAR(100) NOT NULL,
building VARCHAR(100) NOT NULL,
budget DECIMAL(10, 2) UNSIGNED,
PRIMARY KEY(dept_name)
);
CREATE TABLE student(
ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
dept_name VARCHAR(100) NOT NULL,
tot_cred SMALLINT NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);
CREATE TABLE instructor(
ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
dept_name VARCHAR(100) NOT NULL,
salary DECIMAL(10, 2) UNSIGNED NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);
SELECT c.title AS Course_Title, s.name AS Student_Name
FROM course AS c
INNER JOIN takes AS t ON c.course_id = t.course_id
INNER JOIN student AS s ON t.ID = s.ID
INNER JOIN
(SELECT course_id
FROM takes JOIN student ON takes.id = student.ID
GROUP BY course_id
HAVING COUNT(*) > 1
) AS courses_2plus_students ON c.course_id = courses_2plus_students.course_id;
| Course_Title | Student_Name |
|---|---|
| Intro. to Computer Science | Zhang |
| Intro. to Computer Science | Shankar |
| Intro. to Computer Science | Levy |
| Intro. to Computer Science | Williams |
| Intro. to Computer Science | Brown |
| Intro. to Computer Science | Bourikas |
| Intro. to Computer Science | Levy |
| Game Design | Shankar |
| Game Design | Williams |
| Robotics | Shankar |
| Robotics | Bourikas |
| Image Processing | Levy |
| Image Processing | Brown |
| Database System Concepts | Zhang |
| Database System Concepts | Shankar |
SELECT DISTINCT ts.time_slot_id
FROM time_slot ts
WHERE ts.`day` = 'F'
AND ts.start_hour >= 12 and ts.start_hour < 18;
| time_slot_id |
|---|
| D |
| G |
SELECT DISTINCT sec.course_id
FROM section sec
INNER JOIN time_slot ts ON sec.time_slot_id = ts.time_slot_id
WHERE ts.`day` = 'F'
AND ts.start_hour >= 12 and ts.start_hour < 18;
| course_id |
|---|
| CS-315 |
| MU-199 |