Select Atleast one and at least two in SQL-CodePudding

I have two tables student(ssn,name,major) transcript(ssn,grade) what is the query to return

received grade A in at least one course and major CS

received grade A in at least two courses and major CS.

For one course I have tried the below query but I'm not getting the result as expected

Select * from student s inner join transcript t on s.ssn = t.ssn where s.major = 'cs' and t.grade = 'A'

help appreciated thank you.

CodePudding user response：

The following nested query might help for your second question

SELECT
  s.ssn,
  s.name,
  s.major
FROM student s
INNER JOIN (
  SELECT 
    ssn,
    COUNT(grade) as number_of_A_grades
  FROM transcript
  WHERE grade = 'A'
  GROUP BY ssn
) A_grades_count
ON A_grades_count.ssn = s.ssn

WHERE A_grades_count.number_of_A_grades >= 2

CodePudding user response：

You can use a ranking function like ROW_NUMBER() to avoid aggregate functions to achieve this:

SELECT 
  a.ssn, 
  a.name, 
  a.major
FROM (SELECT 
        s.ssn, 
        s.name, 
        s.major,
        ROW_NUMBER() OVER (PARTITION BY s.ssn ORDER BY s.ssn ASC) AS rn
      FROM student s
      INNER JOIN transcript t ON s.ssn = t.ssn 
      WHERE t.grade = 'A' AND s.major = 'cs') a
WHERE a.rn >= 2
ORDER BY a.ssn ASC

Fiddle here.