Will I need to do a join of 4 tables to fulfill this SQL query or is there a simpler method?

I currently need to do the following:

Find the names of all course and the students enrolled on them which take place on a Friday afternoon and have at least 2 students enrolled on it.

I am thinking what I should do is Join the tables titled student, takes, course, section and time_slot together and from there do the SQL query, but this seems overkill to me.

First I would get a list of all the courses and all of the students names that take the courses by joining the takes and students tables.

Then, I would find all of the courses that take place on a Friday afternoon using the time_slot_id, which would have to be “D” OR “F” OR “G” and the day would be “F” to signify Friday. Then I would match the timeslot with the section.

After that I am stuck.

Here is what I have tried:

SELECT name, course_ID FROM takes JOIN student ON takes.id = student.ID WHERE count(course_ID) > 1;

and

SELECT course_id
FROM takes JOIN student ON takes.id = student.ID
GROUP BY course_id
HAVING COUNT(*) > 1;

The bottom query works, but does not have the names of the students and the top one is a lot closer to what I would need, but returns an error.

Here is the full mySQL code that I have.

CREATE TABLE time_slot(
    time_slot_id VARCHAR(100) NOT NULL,
    day VARCHAR(100) NOT NULL,
    start_hour TIME NOT NULL,
    start_min TIME NOT NULL,
    end_hour TIME NOT NULL,
    end_min TIME 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)
    );

CREATE TABLE course(
    course_id VARCHAR(100) NOT NULL,
    title VARCHAR(255) NOT NULL,
    dept_name VARCHAR(100) NOT NULL,
    cedits SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY (course_id),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name)
    );

CREATE TABLE prereq(
    course_id VARCHAR(100) NOT NULL,
    prereq_id VARCHAR(100) NOT NULL,
    PRIMARY KEY(course_id, prereq_id),
    FOREIGN KEY(course_id) REFERENCES course(course_id)
    );


CREATE TABLE classroom(
    building VARCHAR(100) NOT NULL,
    room_no VARCHAR(100) NOT NULL,
    capacity SMALLINT NOT NULL,
    PRIMARY KEY (building, room_no)
    );


CREATE TABLE section(
    course_id VARCHAR(100) NOT NULL,
    sec_id VARCHAR(100) NOT NULL,
    semester VARCHAR(100) NOT NULL,
    year SMALLINT UNSIGNED NOT NULL,
    building VARCHAR(100) NOT NULL,
    room_no VARCHAR(100) NOT NULL,
    time_slot_id VARCHAR(100) NOT NULL,
    PRIMARY KEY (course_id, sec_id, semester, year),
    FOREIGN KEY (course_id) REFERENCES course(course_id),
    FOREIGN KEY (building,room_no) REFERENCES classroom(building,room_no),
    FOREIGN KEY (time_slot_id) REFERENCES time_slot(time_slot_id)
    );

CREATE TABLE takes(
    ID INT UNSIGNED,
    course_id VARCHAR(100) NOT NULL,
    sec_id VARCHAR(100) NOT NULL,
    semester VARCHAR(100) NOT NULL,
    year SMALLINT UNSIGNED NOT NULL,
    grade VARCHAR(100),
    PRIMARY KEY (ID, course_id, sec_id, semester, year),
    FOREIGN KEY (ID) REFERENCES student(ID),
    FOREIGN KEY (course_id,sec_id,semester,year) REFERENCES section(course_id,sec_id,semester,year)

    );


CREATE TABLE teaches(
    ID INT UNSIGNED,
    course_id VARCHAR(100), 
    sec_id VARCHAR(100),
    semester VARCHAR(100),
    year SMALLINT UNSIGNED,
    PRIMARY KEY(ID, course_id, sec_id, semester, year),
    FOREIGN KEY(ID) REFERENCES instructor(ID),
FOREIGN KEY (course_id,sec_id,semester,year) REFERENCES section(course_id,sec_id,semester,year)
    );

CREATE TABLE advisor(
    s_id INT UNSIGNED NOT NULL,
    i_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (s_id),
    FOREIGN KEY (s_id) REFERENCES student(ID),
    FOREIGN KEY (i_id) REFERENCES instructor(ID)
    );


insert into classroom values ('Packard', '101', '500');
insert into classroom values ('Painter', '514', '10');
insert into classroom values ('Taylor', '3128', '70');
insert into classroom values ('Watson', '100', '30');
insert into classroom values ('Watson', '120', '50');
insert into department values ('Biology', 'Watson', '90000');
insert into department values ('Comp. Sci.', 'Taylor', '100000');
insert into department values ('Elec. Eng.', 'Taylor', '85000');
insert into department values ('Finance', 'Painter', '120000');
insert into department values ('History', 'Painter', '50000');
insert into department values ('Music', 'Packard', '80000');
insert into department values ('Physics', 'Watson', '70000');
insert into course values ('BIO-101', 'Intro. to Biology', 'Biology', '4');
insert into course values ('BIO-301', 'Genetics', 'Biology', '4');
insert into course values ('BIO-399', 'Computational Biology', 'Biology', '3');
insert into course values ('CS-101', 'Intro. to Computer Science', 'Comp. Sci.', '4');
insert into course values ('CS-190', 'Game Design', 'Comp. Sci.', '4');
insert into course values ('CS-315', 'Robotics', 'Comp. Sci.', '3');
insert into course values ('CS-319', 'Image Processing', 'Comp. Sci.', '3');
insert into course values ('CS-347', 'Database System Concepts', 'Comp. Sci.', '3');
insert into course values ('EE-181', 'Intro. to Digital Systems', 'Elec. Eng.', '3');
insert into course values ('FIN-201', 'Investment Banking', 'Finance', '3');
insert into course values ('HIS-351', 'World History', 'History', '3');
insert into course values ('MU-199', 'Music Video Production', 'Music', '3');
insert into course values ('PHY-101', 'Physical Principles', 'Physics', '4');
insert into instructor values ('10101', 'Srinivasan', 'Comp. Sci.', '65000');
insert into instructor values ('12121', 'Wu', 'Finance', '90000');
insert into instructor values ('15151', 'Mozart', 'Music', '40000');
insert into instructor values ('22222', 'Einstein', 'Physics', '95000');
insert into instructor values ('32343', 'El Said', 'History', '60000');
insert into instructor values ('33456', 'Gold', 'Physics', '87000');
insert into instructor values ('45565', 'Katz', 'Comp. Sci.', '75000');
insert into instructor values ('58583', 'Califieri', 'History', '62000');
insert into instructor values ('76543', 'Singh', 'Finance', '80000');
insert into instructor values ('76766', 'Crick', 'Biology', '72000');
insert into instructor values ('83821', 'Brandt', 'Comp. Sci.', '92000');
insert into instructor values ('98345', 'Kim', 'Elec. Eng.', '80000');
insert into time_slot values ('A', 'M', '8', '0', '8', '50');
insert into time_slot values ('A', 'W', '8', '0', '8', '50');
insert into time_slot values ('A', 'F', '8', '0', '8', '50');
insert into time_slot values ('B', 'M', '9', '0', '9', '50');
insert into time_slot values ('B', 'W', '9', '0', '9', '50');
insert into time_slot values ('B', 'F', '9', '0', '9', '50');
insert into time_slot values ('C', 'M', '11', '0', '11', '50');
insert into time_slot values ('C', 'W', '11', '0', '11', '50');
insert into time_slot values ('C', 'F', '11', '0', '11', '50');
insert into time_slot values ('D', 'M', '13', '0', '13', '50');
insert into time_slot values ('D', 'W', '13', '0', '13', '50');
insert into time_slot values ('D', 'F', '13', '0', '13', '50');
insert into time_slot values ('E', 'T', '10', '30', '11', '45');
insert into time_slot values ('E', 'R', '10', '30', '11', '45');
insert into time_slot values ('F', 'T', '14', '30', '15', '45');
insert into time_slot values ('F', 'R', '14', '30', '15', '45');
insert into time_slot values ('G', 'M', '16', '0', '16', '50');
insert into time_slot values ('G', 'W', '16', '0', '16', '50');
insert into time_slot values ('G', 'F', '16', '0', '16', '50');
insert into time_slot values ('H', 'W', '10', '0', '12', '30');
insert into section values ('BIO-101', '1', 'Summer', '2009', 'Painter', '514', 'B');
insert into section values ('BIO-301', '1', 'Summer', '2010', 'Painter', '514', 'A');
insert into section values ('CS-101', '1', 'Fall', '2009', 'Packard', '101', 'H');
insert into section values ('CS-101', '1', 'Spring', '2010', 'Packard', '101', 'F');
insert into section values ('CS-190', '1', 'Spring', '2009', 'Taylor', '3128', 'E');
insert into section values ('CS-190', '2', 'Spring', '2009', 'Taylor', '3128', 'A');
insert into section values ('CS-315', '1', 'Spring', '2010', 'Watson', '120', 'D');
insert into section values ('CS-319', '1', 'Spring', '2010', 'Watson', '100', 'B');
insert into section values ('CS-319', '2', 'Spring', '2010', 'Taylor', '3128', 'C');
insert into section values ('CS-347', '1', 'Fall', '2009', 'Taylor', '3128', 'A');
insert into section values ('EE-181', '1', 'Spring', '2009', 'Taylor', '3128', 'C');
insert into section values ('FIN-201', '1', 'Spring', '2010', 'Packard', '101', 'B');
insert into section values ('HIS-351', '1', 'Spring', '2010', 'Painter', '514', 'C');
insert into section values ('MU-199', '1', 'Spring', '2010', 'Packard', '101', 'D');
insert into section values ('PHY-101', '1', 'Fall', '2009', 'Watson', '100', 'A');
insert into teaches values ('10101', 'CS-101', '1', 'Fall', '2009');
insert into teaches values ('10101', 'CS-315', '1', 'Spring', '2010');
insert into teaches values ('10101', 'CS-347', '1', 'Fall', '2009');
insert into teaches values ('12121', 'FIN-201', '1', 'Spring', '2010');
insert into teaches values ('15151', 'MU-199', '1', 'Spring', '2010');
insert into teaches values ('22222', 'PHY-101', '1', 'Fall', '2009');
insert into teaches values ('32343', 'HIS-351', '1', 'Spring', '2010');
insert into teaches values ('45565', 'CS-101', '1', 'Spring', '2010');
insert into teaches values ('45565', 'CS-319', '1', 'Spring', '2010');
insert into teaches values ('76766', 'BIO-101', '1', 'Summer', '2009');
insert into teaches values ('76766', 'BIO-301', '1', 'Summer', '2010');
insert into teaches values ('83821', 'CS-190', '1', 'Spring', '2009');
insert into teaches values ('83821', 'CS-190', '2', 'Spring', '2009');
insert into teaches values ('83821', 'CS-319', '2', 'Spring', '2010');
insert into teaches values ('98345', 'EE-181', '1', 'Spring', '2009');
insert into student values ('00128', 'Zhang', 'Comp. Sci.', '102');
insert into student values ('12345', 'Shankar', 'Comp. Sci.', '32');
insert into student values ('19991', 'Brandt', 'History', '80');
insert into student values ('23121', 'Chavez', 'Finance', '110');
insert into student values ('44553', 'Peltier', 'Physics', '56');
insert into student values ('45678', 'Levy', 'Physics', '46');
insert into student values ('54321', 'Williams', 'Comp. Sci.', '54');
insert into student values ('55739', 'Sanchez', 'Music', '38');
insert into student values ('70557', 'Snow', 'Physics', '0');
insert into student values ('76543', 'Brown', 'Comp. Sci.', '58');
insert into student values ('76653', 'Aoi', 'Elec. Eng.', '60');
insert into student values ('98765', 'Bourikas', 'Elec. Eng.', '98');
insert into student values ('98988', 'Tanaka', 'Biology', '120');
insert into takes values ('00128', 'CS-101', '1', 'Fall', '2009', 'A');
insert into takes values ('00128', 'CS-347', '1', 'Fall', '2009', 'A-');
insert into takes values ('12345', 'CS-101', '1', 'Fall', '2009', 'C');
insert into takes values ('12345', 'CS-190', '2', 'Spring', '2009', 'A');
insert into takes values ('12345', 'CS-315', '1', 'Spring', '2010', 'A');
insert into takes values ('12345', 'CS-347', '1', 'Fall', '2009', 'A');
insert into takes values ('19991', 'HIS-351', '1', 'Spring', '2010', 'B');
insert into takes values ('23121', 'FIN-201', '1', 'Spring', '2010', 'C+');
insert into takes values ('44553', 'PHY-101', '1', 'Fall', '2009', 'B-');
insert into takes values ('45678', 'CS-101', '1', 'Fall', '2009', 'F');
insert into takes values ('45678', 'CS-101', '1', 'Spring', '2010', 'B+');
insert into takes values ('45678', 'CS-319', '1', 'Spring', '2010', 'B');
insert into takes values ('54321', 'CS-101', '1', 'Fall', '2009', 'A-');
insert into takes values ('54321', 'CS-190', '2', 'Spring', '2009', 'B+');
insert into takes values ('55739', 'MU-199', '1', 'Spring', '2010', 'A-');
insert into takes values ('76543', 'CS-101', '1', 'Fall', '2009', 'A');
insert into takes values ('76543', 'CS-319', '2', 'Spring', '2010', 'A');
insert into takes values ('76653', 'EE-181', '1', 'Spring', '2009', 'C');
insert into takes values ('98765', 'CS-101', '1', 'Fall', '2009', 'C-');
insert into takes values ('98765', 'CS-315', '1', 'Spring', '2010', 'B');
insert into takes values ('98988', 'BIO-101', '1', 'Summer', '2009', 'A');
insert into takes values ('98988', 'BIO-301', '1', 'Summer', '2010', null);
insert into advisor values ('00128', '45565');
insert into advisor values ('12345', '10101');
insert into advisor values ('23121', '76543');
insert into advisor values ('44553', '22222');
insert into advisor values ('45678', '22222');
insert into advisor values ('76543', '45565');
insert into advisor values ('76653', '98345');
insert into advisor values ('98765', '98345');
insert into advisor values ('98988', '76766');
insert into prereq values ('BIO-301', 'BIO-101');
insert into prereq values ('BIO-399', 'BIO-101');
insert into prereq values ('CS-190', 'CS-101');
insert into prereq values ('CS-315', 'CS-101');
insert into prereq values ('CS-319', 'CS-101');
insert into prereq values ('CS-347', 'CS-101');
insert into prereq values ('EE-181', 'PHY-101');

Answer

OK… let’s build this up using your strategy.

First I would get a list of all the courses and all of the students names that take the courses by joining the takes and students tables.

Your query below is the correct start for this – it finds all the courses which have more than one student.

SELECT course_id
FROM takes JOIN student ON takes.id = student.ID
GROUP BY course_id
HAVING COUNT(*) > 1;

An important caveat to the above is that it doesn’t matter which semester/etc the students do it – just that the course has had more than one enrolled in it at any time, in any timeslot, etc.

To get the other info (names of courses and students) you’d just link this to the course table and takes & student tables e.g.,

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;

Of course, you could change the above so that the sub-query is in the WHERE clause, or in a CTE.

Note that there is a more efficient way of doing the above using Windowed functions, but I’m guessing that’s a bridge too far at the moment.

However, as you said in your strategy – this isn’t the entire answer – we need to filter out the courses.

Then, I would find all of the courses that take place on a Friday afternoon using the time_slot_id, which would have to be “D” OR “F” OR “G” and the day would be “F” to signify Friday. Then I would match the timeslot with the section.

I’m assuming you want the timeslots to be calculated rather than hard-coded (in case you add new timeslots e.g., one that runs on Monday, Tuesday, Thursday mornings). As such, I’ll define an ‘afternoon’ slot as one that starts at or after 12:00pm but before 6:00pm.

We can get the relevant timeslots via the following

SELECT    DISTINCT ts.time_slot_id
  FROM    time_slot ts
  WHERE   ts.`day` = 'F'
          AND ts.start_hour >= 12 and ts.start_hour < 18;

The above should result in ‘D’ and ‘G’ (note that F does not count according to your data). I used DISTINCT in the above to remove duplicates in case a given time slot has a double class e.g., 2 classes in a given afternoon.

We then want the relevant course_ids – which we get from joining the above to section (and removing the reference to time_slot_id as we no longer need it).

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;

Now, all we need to do is to filter out the courses from above, using the sections from above. So the SQL to get the (initial) answer is…

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
        INNER JOIN
           (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
            ) AS Friday_afternoon_courses on c.course_id = Friday_afternoon_courses.course_id;

Once again, you could use WHERE or CTEs etc instead of the sub-query.

Here is a db<>fiddle with each of the steps from above. Note the first section is hidden – it contains your data structures and data (but with the course times set to int rather than time).

Note – and this is a big note – you need to be very careful about what this represents.

• Courses may be delivered in different time slots, and in different semesters. For example, CS-101 is delivered in Fall 2009 in time_slot H; it is also delivered in Spring 2010 with time_slot F.
• The above answer does not care about this. As long as a course has more than one student (at any time), and has at one stage been delivered on a Friday afternoon, then it qualifies. The students won’t necessarily be in class together at the same time – or indeed, you can have students who didn’t even attend on a Friday afternoon (e.g., they were taking the course in another stream/timeslot).

While my answer may give the initial literal answer to the question, the more meaningful question interpretation (“which classes on Friday afternoon have more than 1 student in them at the same time?”) is probably what they’re asking.

And as this looks like homework – I suggest that modifying the above should be your next step. The reason I chose sub-queries to do the filtering in the above answer, is that I find it easier to then modify then for different filtering.

PS Here’s a hint for how/what to modify: The answer above does all its work around the ‘course’ table e.g., it finds students by course, time slots by course, etc. Instead, you may want to focus on the ‘section’ table instead as that provides data about courses at specific times.