select query with condition [Resolved]

Posted by Shamseena under Sql Server on 11/7/2012 | Points: 10 | Views : 1546 | Status : [Member] | Replies : 6
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




Responses

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

Up
0
Down

Resolved
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
hi..

Your Welcome Sir...........

Sourabh07

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

Login to post response