Encontre o aluno com a nota mais alta em cada estado, usando Álgebra Relacional

Na verdade, não me sinto muito confortável com álgebra relacional, então, farei isso primeiro usando SQL padrão e depois usarei uma ferramenta chamada RelaX - calculadora de álgebra relacional 0.18.2 para fazer a tradução.

Primeiro, a tabela que você escreveu, vou chamá-la de alunos, defini-la e preenchê-la com:

CREATE TABLE students
(
    id INTEGER PRIMARY KEY,
    grade INTEGER,
    state TEXT
) ;

INSERT INTO students
    (id, grade, state)
VALUES
    (1, 83, 'CA'),
    (2, 94, 'TX'),
    (3, 92, 'WA'),
    (4, 78, 'CA') ;

O RelaX traduzirá isso em um conjunto de dados, representado pelas seguintes tuplas:

group: Joan (imported from SQL)

students = {
    id:number, grade:number, state:string
    1        , 83          , 'CA'        
    2        , 94          , 'TX'        
    3        , 92          , 'WA'        
    4        , 78          , 'CA'        
}

Para encontrar o que você procura, primeiro precisamos de uma tabela com tuplas no formato (state, grade), tendo a nota máxima de cada estado. Essa consulta é feita, em SQL com um MAX(grade)per stateusando um GROUPs BY state. Você escreveria assim:

SELECT
    state, max(grade) AS grade
FROM 
    students AS s2 
GROUP BY
    state ;

Em seguida, você precisa JOINdessa tabela (que se chama max_grades) para studentsaquela, e você faz isso com ONestados iguais e nota igual (ou seja: a nota máxima por estado) ...

SELECT
    s1.id
FROM
    students AS s1
    JOIN 
    (
        SELECT
            state, max(grade) AS grade
        FROM 
            students
        GROUP BY
            state
    ) AS max_grades 
    ON s1.state = max_grades.state AND s1.grade = max_grades.grade

... e isso é traduzido pelo RelaX para a seguinte expressão e resposta de álgebra relacional:

π _s1.id ρ _s1 alunos ⨝ _{s1.state = max_grades.state e s1.grade = max_grades.grade} ρ _{max_grades} ( π _{state, grade} γ _{estado; MAX(nota)→nota} ρ _s2 alunos)

s1.id
1
2
3

NOTA 1:

1. Se vários alunos de um estado tivessem a nota máxima, essa expressão retornaria TODOS eles, não apenas um arbitrário desse estado.

Alternativo:

Se você não puder GROUP BY, você pode usar outra construção:

SELECT DISTINCT
    id
FROM
    students
EXCEPT
SELECT
    s1.id
FROM
    students AS s1
    JOIN students AS s2 ON s1.state = s2.state AND s1.grade < s2.grade

Isso vai um pouco mais de acordo com o seu pensamento original, embora eu pessoalmente ache menos claro ...

A tradução para álgebra relacional é:

π _id alunos - π _s1.id ρ _s1 alunos ⨝ _{s1.estado = s2.estado e s1.série < s2.série} ρ _s2 alunos

EDIT: não li com atenção e dei uma resposta para SQL, caso o pôster se interesse. Por favor, deixe-me saber se você não está interessado e então eu vou excluir.

SQL: Acho que usar uma tabela derivada funciona. Vamos chamar sua mesa de 'Alunos':

SELECT id FROM Students JOIN
  (SELECT State, Max(Grade) AS MaxGrade FROM Students GROUP BY State ) AS T 
  ON T.State=Students.State  
WHERE Students.Grade >= T.MaxGrade
 ;