CODEWITHSUNDEEP
sqlsql-server
Salik Naqi
Salik Naqi

Reputation: 25

How to see co-relation between course grades?

I am new to SQL Server. Please help me find the Co-relation between the grades for two courses.

I want to find that which Student got 'A' Grade in 'Computer Programming' who had an 'A' grade in Introduction to Computer too.

This is how the data looks like:

RollNum | CGPA | Status     | Name                              | Grade
410     | 2.6  | Completed  | Introduction to Computer Science  | A  
410     | 2.6  | Completed  | Computer Programming              | A-  
422     | 3.2  | Completed  | Introduction to Computer Science  | A 
422     | 3.2  | Completed  | Computer Programming              | A  
223     | 3.52 | Completed  | Introduction to Computer Science  | A 
223     | 3.52 | Completed  | Computer Programming              | A 
521     | 1.2  | Completed  | Introduction to Computer Science  | B+ 
521     | 1.2  | Completed  | Computer Programming              | A-
....
....

this is the Query that I am Writing:

SELECT [RollNum],[CGPA],[Status],[Name],[FinalGrade]
 FROM db
     where Name ='Introduction to Computer Science' and FinalGrade='A' 
  and (Name='Computer Programming' and FinalGrade= 'A' )

Please help me, Thanks in Advance.

Upvotes: 0

Views: 78

Answers (3)

Taryn
Taryn

Reputation: 247680

You can use the following:

select RollNum
from db
where [Name] in ('Introduction to Computer Science', 'Computer Programming')
  and [Grade] = 'A'
group by RollNum
having count(distinct name) = 2

This is known as Relational Division and will return the student RollNum where they took both classes and received an A in each class.

See SQL Fiddle with Demo

If you want more than just the RollNum, then you can use the above query in a WHERE EXISTS:

select [RollNum], [CGPA], [Status], [Name], [Grade]
from db d1
where exists (select RollNum
              from db d2
              where [Name] in ('Introduction to Computer Science', 'Computer Programming')
                and [Grade] = 'A'
                and d1.rollnum = d2.rollnum
              group by RollNum
              having count(distinct name) = 2);

See SQL Fiddle with Demo

Upvotes: 2

Tim Lentine
Tim Lentine

Reputation: 7862

SELECT qCP.*
FROM (SELECT RollNum, CGPA, Status, Name, FinalGrade
      FROM db
      WHERE Name = 'Computer Programming' 
      AND FinalGrade = 'A') qCP
INNER JOIN 
    (SELECT RollNum 
     FROM db 
     WHERE Name = 'Introduction to Computer Science' 
     AND FinalGrade = 'A') qIntro
ON qCP.RollNum = qIntro.RollNum

Upvotes: 1

JohnHC
JohnHC

Reputation: 11195

If using SQL server, I would use

Select [RollNum],[CGPA],[Status],[Name],[FinalGrade]
from db
where [Name] in ('Introduction to Computer Science', 'Computer Programming')
and [FinalGrade] = 'A'

Upvotes: 1

Related Questions