select query with condition [Resolved]

Posted by Shamseena under Sql Server on 11/7/2012 | Points: 10 | Views : 2339 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi All,

I have a table with the details of 10 students marklist in 5 subjects.Maximum mark 100 and pass mark is 50.Please help me to write query for to select

Student_Marklist Table fields are

Student_Id StudentName Subject Mark

1 Raj Biology 76
2 Karan English 45
...... .......... ......... ........

like this 10 students X 5 subjects ,ie totally 50records are there.From this table i want

1.Details of students who all are passed in all subject ie ( Each subject mark>50.)

2. Details of students who failed in min 3 or less subjects

3. details of students who are failed in 4 or more subjects

please help me .I am new to sql

Thanks in Advance

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Sourabh07 on: 11/7/2012 [Member] Starter | Points: 50

0	hi.. please check the following answers and change it accordingly...... I am getting the student id with the following query, rest you wil get the complete detail of the student using the Inner Join with the Studnet Detail table....you are having in your database..... Details of students who all are passed in all subject ie ( Each subject mark>50.) Select distinct A.Student_Id from Student_Marklist A Inner Join ( Select Student_Id,COUNT(Student_Id) as numberofsubjects from Student_Marklist where isnull(mark,0)>50 group by Student_Id) B on A.Student_Id=B.Student_Id and B.numberofsubjects = (Select COUNT(distinct subject) from Student_Marklist) Details of students who failed in min 3 or less subjects Select distinct A.Student_Id from Student_Marklist A Inner Join ( Select Student_Id,COUNT(Student_Id) as numberofsubjects from Student_Marklist where isnull(mark,0)<=50 group by Student_Id) B on A.Student_Id=B.Student_Id and B.numberofsubjects <= 3 details of students who are failed in 4 or more subjects Select distinct A.Student_Id from Student_Marklist A Inner Join ( Select Student_Id,COUNT(Student_Id) as numberofsubjects from Student_Marklist where isnull(mark,0)<=50 group by Student_Id) B on A.Student_Id=B.Student_Id and B.numberofsubjects >= 4 Sourabh07 Shamseena, if this helps please login to Mark As Answer. \| Alert Moderator

hi..

please check the following answers and change it accordingly......

I am getting the student id with the following query, rest you wil get the complete detail of the student using the Inner Join with the Studnet Detail table....you are having in your database.....

Details of students who all are passed in all subject ie ( Each subject mark>50.)

Select distinct A.Student_Id from Student_Marklist A

Inner Join (

Select Student_Id,COUNT(Student_Id) as numberofsubjects from Student_Marklist

where isnull(mark,0)>50

group by Student_Id) B on A.Student_Id=B.Student_Id and B.numberofsubjects = (Select COUNT(distinct subject) from Student_Marklist)

Details of students who failed in min 3 or less subjects

Select distinct A.Student_Id from Student_Marklist A

Inner Join (

Select Student_Id,COUNT(Student_Id) as numberofsubjects from Student_Marklist

where isnull(mark,0)<=50

group by Student_Id) B on A.Student_Id=B.Student_Id and B.numberofsubjects <= 3

details of students who are failed in 4 or more subjects

Select distinct A.Student_Id from Student_Marklist A

Inner Join (

Select Student_Id,COUNT(Student_Id) as numberofsubjects from Student_Marklist

where isnull(mark,0)<=50

group by Student_Id) B on A.Student_Id=B.Student_Id and B.numberofsubjects >= 4

Sourabh07

Shamseena, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sourabh07 on: 11/7/2012 [Member] Starter | Points: 25

0	hi.. will you please attach the tables.....so that it is easy for us to completely get into it... Sourabh07 Shamseena, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sandhyab on: 11/7/2012 [Member] Starter | Points: 25

0	Hi, Once go through this http://stackoverflow.com/questions/10569985/sql-query-for-student-mark-functionality Thanks & Regards Shamseena, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Shamseena on: 11/7/2012 [Member] Starter | Points: 25

0	My requirment and this answer are too different. Shamseena, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Shamseena on: 11/7/2012 [Member] Starter | Points: 25

0	Really Superb....My Hearty Thanks to your effort..Please keep posting Shamseena, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sourabh07 on: 11/8/2012 [Member] Starter | Points: 25

0	hi.. Your Welcome Sir........... Sourabh07 Shamseena, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts