SQL select if duplicates with max value

Question

Use a SQL statement to find out the names of students whose marks are over 80 in all of their courses

Name course mark

Alice Art 81

Alice Math 74

Ben Art 76

Ben Math 90

Charles Art 81

Charles Math 100

Charles English 90

Answer 1

There is a simpler sql to answer the question.using function MIN()

The sql below filters the results to only include students

 whose minimum mark is greater than 80

SELECT Name

FROM STUDENTS

GROUP BY Name

HAVING MIN(mark) > 80;

Answer 2

Unfortunately, Rize's query is incorrect. His answer is

SELECT Name

FROM students

WHERE mark > 80

GROUP BY Name

HAVING COUNT(DISTINCT course) = (SELECT COUNT(DISTINCT course)

FROM students)

The problem with Rize's answer is that the subquery has no WHERE clause. The subquery should give us the number of courses for the same student as in the outer query, not the total number of courses for all students.

With the given data, Rize's subquery always returns 3, the total number of courses in the students table. But Ben and Alice each took 2 classes, so even if all their marks are > 80, their value of COUNT(DISTINCT course) is 2, and Ben and Alice can never be returned by this query.

The minimal fix for Rize's query is to put in the missing WHERE clause so that the subquery give us the number of courses for the same student in the outer query.

SELECT s1.Name

FROM students s1

WHERE s1.mark > 80

GROUP BY s1.Name

HAVING COUNT(DISTINCT course) = (SELECT COUNT(DISTINCT course)

FROM students s2

WHERE s2.Name = s1.Name);

This converts the subquery into a correlated subquery, which requires the use of table aliases.

Here's another way to do this problem:

SELECT S1.Name FROM

(SELECT Name, COUNT(DISTINCT course) As Total

FROM students

WHERE mark > 80

GROUP BY Name) AS S1

INNER JOIN

(SELECT Name, COUNT(DISTINCT course) AS Total

FROM students

GROUP BY Name) AS S2

ON S1.Name = S2.Name

WHERE S1.Total = S2.Total;

In this version, I have two subqueries. Subquery #1 gets each Name, along with the number of courses with marks > 80 for that Name. Subquery #2 gets each Name, along with the number of courses that Name.

I then join the output of the two subqueries on the Name, and filter out Names where the number of courses with marks > 80 is not the same as the total number of courses.

And here's yet another version:

SELECT DISTINCT Name

FROM students

EXCEPT

SELECT DISTINCT Name

FROM students

WHERE mark <= 80

Some databases use "MINUS" instead of "EXCEPT". Both are used to remove rows in the output from the second query that are also in the output of the first query.

It is common for more than one query to accomplish the same goal.

Answer 3

Assuming the table name is "students", the SQL statement to find out the names of students whose marks are over 80 in all of their courses is:

SELECT Name

FROM students

WHERE mark > 80

GROUP BY Name

HAVING COUNT(DISTINCT course) = (SELECT COUNT(DISTINCT course) FROM students)

This query uses the GROUP BY and HAVING clauses to group the data by student name and count the number of distinct courses for each student, then filters for only those students who have marks greater than 80 in all of their courses.