Here is a SQL query that should give you the desired result:

```sql
SELECT c.name
FROM courses c
JOIN grades g ON c.id = g.course_id
JOIN students s ON s.id = g.student_id
WHERE g.grade = 50
GROUP BY c.id, c.name
ORDER BY COUNT(s.id) DESC, c.name ASC
LIMIT 1;
```

Here's a step-by-step explanation of the query:

1. `JOIN` the `courses`, `grades`, and `students` tables on their respective `id` fields. This combines the tables into one table where each row contains information from all three tables for a specific student-course-grade combination.

2. Filter out rows where the grade is less than 50 using the `WHERE` clause. This ensures that only passing students are considered.

3. `GROUP BY` the `id` and `name` fields of the `courses` table. This groups the combined table by course, so that each group contains all the students enrolled in a specific course.

4. `ORDER BY` the count of students in each group in descending order, and then by the course name in ascending order. This sorts the groups so that the group with the most students comes first, and in case of a tie, the course with the lexicographically smaller name comes first.

5. Finally, `LIMIT 1` is used to only select the first row, which is the most popular course.

Question

Here is a SQL query that should give you the desired result:

```sql
SELECT c.name
FROM courses c
JOIN grades g ON c.id = g.course_id
JOIN students s ON s.id = g.student_id
WHERE g.grade >= 50
GROUP BY c.id, c.name
ORDER BY COUNT(s.id) DESC, c.name ASC
LIMIT 1;
```

Here's a step-by-step explanation of the query:

1. `JOIN` the `courses`, `grades`, and `students` tables on their respective `id` fields. This combines the tables into one table where each row contains information from all three tables for a specific student-course-grade combination.

2. Filter out rows where the grade is less than 50 using the `WHERE` clause. This ensures that only passing students are considered.

3. `GROUP BY` the `id` and `name` fields of the `courses` table. This groups the combined table by course, so that each group contains all the students enrolled in a specific course.

4. `ORDER BY` the count of students in each group in descending order, and then by the course name in ascending order. This sorts the groups so that the group with the most students comes first, and in case of a tie, the course with the lexicographically smaller name comes first.

5. Finally, `LIMIT 1` is used to only select the first row, which is the most popular course.

Knowee AI · Accepted Answer

Here is a SQL query that should give you the desired result:

```sql
SELECT c.name
FROM courses c
JOIN grades g ON c.id = g.course_id
JOIN students s ON s.id = g.student_id
WHERE g.grade >= 50
GROUP BY c.id, c.name
ORDER BY COUNT(s.id) DESC, c.name ASC
LIMIT 1;
```

Here's a step-by-step explanation of the query:

1. `JOIN` the `courses`, `grades`, and `students` tables on their respective `id` fields. This combines the tables into one table where each row contains information from all three tables for a specific student-course-grade combination.

2. Filter out rows where the grade is less than 50 using the `WHERE` clause. This ensures that only passing students are considered.

3. `GROUP BY` the `id` and `name` fields of the `courses` table. This groups the combined table by course, so that each group contains all the students enrolled in a specific course.

4. `ORDER BY` the count of students in each group in descending order, and then by the course name in ascending order. This sorts the groups so that the group with the most students comes first, and in case of a tie, the course with the lexicographically smaller name comes first.

5. Finally, `LIMIT 1` is used to only select the first row, which is the most popular course.

Question

Solution

Similar Questions

Upgrade your grade with Knowee