0

I'm learning SQL from a book and I'm trying to do some exercices on join queries. The only problem that I'm facing is that all of my join queries are not working while they seem well.

These are the tables:

    students(student_id,student_names,student_age)
    courses_students(course_id,student_id)
    courses(course_id,course_schedule,course_room,teacher_id)
    teachers(teacher_id,teacher_names)

The query is "which courses have more than 5 students enrolled?"

Here is what I've done:

select 
  course_name,
  count(select count(*) from courses) as count 
from students,courses,courses_students 
where students.student_id = courses_students.student_id,
courses.course_id = courses_students.course_id 
and count > 5

And the other one is "what are the names of students enrolled in at least 2 courses scheduled for the same hours?"

My query:

select student_name,schedule 
from students,courses,courses_students 
where students.student_id = courses_students.student_id,
courses.course_id = courses_students.course_id 
and count > 2
  • 1
    1) Aggregate functions (COUNT() in your case) needs in GROUP BY clause; 2) The conditions in WHERE clause must be combined using logical operators (AND, OR), not by comma; 2) Output field name (count in your case) cannot be used in WHERE clause, it can be used in HAVING clause. – Akina Jan 29 at 8:02
  • In JOIN queries, please qualify each column with the table it comes from. – Rick James Jan 30 at 18:42
0

For the first question "which courses have more than 5 students enrolled?"

try the following query

select a.course_id,b.course_name,count(a.student_id) as student_count from courses_students a join courses b on a.course_id=b.course_id group by a.course_id having student_count>5;

For the second question "what are the names of students enrolled in at least 2 courses scheduled for the same hours"

try this

select a.student_id,a.student_name,b.course_schedule,count(b.course_id) as course_count from students a join courses b join courses_students c on b.course_id=c.course_id and a.student_id=b.student_id group by a.student_id,b.course_schedule having course_count>1;
1

I would start with:

SELECT course_id
FROM courses_students
GROUP BY course_id
HAVING COUNT(student_id) > 5

The result of this query is a new relation (table, derived table), which you can use as any other table. Let's call this corses_5_students

SELECT c.*
FROM courses as c
JOIN (
    SELECT course_id
    FROM courses_students
    GROUP BY course_id
    HAVING COUNT(student_id) > 5
) AS corses_5_students
    ON corses_5_students.course_id = c.course_id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.