Grade A in at least two transcripts and at least three transcripts sql query-CodePudding

Consider two tables student(ssn, name, major) and transcript(ssn,grade) where ssn is primary key in student and foreign key in transcript. How do we find the students who received A in at least two transcripts with major CS. I have tried using having, group by to get the solution, but we shouldn't use group by and having. For three I have just replaced with the number at the end. Thank you for the answer in advance.

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

WHERE grade_count.number_of_grades >= 2

CodePudding user response：

Just a few variants:

SELECT
  s.ssn,
  s.name,
  s.major
FROM student s
     outer apply (
        SELECT 
          count(*) cnt_a
        FROM transcript t
        WHERE t.grade = 'A'
        and t.ssn=s.ssn
     ) a
where major = 'CS'
and a.cnt_a>=2

SELECT
  s.ssn,
  s.name,
  s.major
FROM student s
where major = 'CS'
and 2<=(SELECT 
          count(*)
        FROM transcript t
        WHERE t.grade = 'A'
        and t.ssn=s.ssn)

NB. CBO can decide to decorrelate your lateral (outer apply) to join with group-by. See "lateral view decorrelation" transformation.

CodePudding user response：

You can join twice to the transcript table:

SELECT s.ssn
FROM   student s
       INNER JOIN transcript t1
       ON (s.ssn = t1.ssn)
       INNER JOIN transcript t2
       ON (s.ssn = t2.ssn AND t1.ROWID <> t2.ROWID)
WHERE  s.major = 'CS'
AND    t1.grade = 'A'
AND    t2.grade = 'A'