Query to get the students who have passed all the 5 subjects-CodePudding

The table is something as given below.

SNO     SNAME   SUBJECT      MARKS      RESULT      
100     Ramesh  MATHS         90        PASS
100     Ramesh  PHYSICS       82        PASS
100     Ramesh  CHEMISTRY     99        PASS
100     Ramesh  BIOLOGY       34        FAIL
100     Ramesh  ZOOLOGY       60        PASS
200     Saran   MATHS         75        PASS
200     Saran   PHYSICS       32        FAIL
200     Saran   CHEMISTRY     28        FAIL
200     Saran   BIOLOGY       30        FAIL
200     Saran   ZOOLOGY       31        FAIL
300     Gokul   MATHS         75        PASS
300     Gokul   PHYSICS       65        PASS
300     Gokul   CHEMISTRY     78        PASS
300     Gokul   BIOLOGY       67        PASS
300     Gokul   ZOOLOGY       56        PASS

We have to

select the SNO, SNAME who have passed all the five subjects
Select the SNO, SNAME who have passed at least 2 subjects

CodePudding user response：

WITH TEST_DATA(SNO,SNAME,SUBJECT,MARKS,RESULT  )AS
(
  SELECT 100,     'Ramesh' , 'MATHS',         90 ,       'PASS' UNION ALL
  SELECT 100,     'Ramesh',  'PHYSICS',       82 ,       'PASS'UNION ALL
  SELECT 100,     'Ramesh' , 'CHEMISTRY',     99 ,       'PASS'UNION ALL
  SELECT 100,     'Ramesh',  'BIOLOGY' ,      34 ,       'FAIL'UNION ALL
  SELECT 100,     'Ramesh',  'ZOOLOGY' ,      60 ,       'PASS'UNION ALL
  SELECT 200,     'Saran' ,  'MATHS'   ,      75 ,       'PASS'UNION ALL
  SELECT 200,     'Saran' ,  'PHYSICS' ,      32 ,       'FAIL'UNION ALL
  SELECT 200,     'Saran' ,  'CHEMISTRY',     28 ,       'FAIL'UNION ALL
  SELECT 200,     'Saran' ,  'BIOLOGY'  ,     30 ,       'FAIL'UNION ALL
  SELECT 200,     'Saran' ,  'ZOOLOGY'  ,     31 ,       'FAIL'UNION ALL
  SELECT 300,     'Kumar' ,  'MATHS'    ,     75 ,       'PASS'UNION ALL
  SELECT 300,     'Kumar' ,  'PHYSICS'  ,     65 ,       'PASS'UNION ALL
  SELECT 300,     'Kumar' ,  'CHEMISTRY' ,    78 ,       'PASS'UNION ALL
  SELECT 300,     'Kumar',   'BIOLOGY'   ,    67,        'PASS'UNION ALL
  SELECT 300,     'Kumar',   'ZOOLOGY'   ,    56,        'PASS'
)
SELECT T.SNO,T.SNAME
FROM TEST_DATA AS T
WHERE T.RESULT='PASS'
GROUP BY T.SNO,T.SNAME
HAVING COUNT(DISTINCT SUBJECT)=5

CodePudding user response：

SET @passed=2; /* ... or the desired count as 5 */
SELECT sno, sname
FROM students
WHERE result='pass'
GROUP BY sno, sname
HAVING COUNT(result)>=@passed