Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query
SQL Query [message #380767] Tue, 13 January 2009 04:53 Go to next message
manns
Messages: 59
Registered: March 2006
Location: Bangalore
Member

Hi,
table having student_name, subject and marks columns
max marks - 100
subjects are physics , chemistry , maths
now how to get single seelct stmt in which i get ...student_name , %of total makrks and the third column as PASS or FAIL.

Conditin for pass is that shtdent shud get minimum 50 in each subject
Re: SQL Query [message #380769 is a reply to message #380767] Tue, 13 January 2009 05:20 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
i think you have to use case in a sql statement.
Re: SQL Query [message #380770 is a reply to message #380767] Tue, 13 January 2009 05:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a Test case: create table and insert statements along with the result you want with these data.
Use SQL*Plus and copy and paste what you already tried.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Hint: have a look at MIN function.
Also define "%of total makrks".

Regards
Michel
Re: SQL Query [message #380771 is a reply to message #380767] Tue, 13 January 2009 05:23 Go to previous messageGo to next message
akumar.pune
Messages: 2
Registered: January 2009
Location: Pune
Junior Member

Hey you can use this query.You have to change table/column name as per you database design.

select stud_name as "Student Name", ((sum(marks)*1.000/(select count(sub_id)*100 as tot from tblsubject)) *100) as "Percentage",
(case when ((sum(marks)*1.000/(select count(sub_id)*100 as tot from tblsubject)) *100) > 50 then 'Pass' else 'Fail' end) as "Pass/Fail"
from tblmarks group by stud_name

-Amrendra
Re: SQL Query [message #380773 is a reply to message #380771] Tue, 13 January 2009 05:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Re: SQL Query [message #380774 is a reply to message #380767] Tue, 13 January 2009 05:27 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I will give you a clue how to do it.

You need an aggregate function (AVG) to function percentage of marks.

You need another aggregate function (MIN) preferabbly to find out the marks and enclose it in a case statement and check if this value is less than 50 then output 'FAIL' else output 'PASS'.

Check this link for more information about aggregate functions.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#sthref962

Try it by yourself.


Regards

Raj
Re: SQL Query [message #380775 is a reply to message #380771] Tue, 13 January 2009 05:29 Go to previous messageGo to next message
akumar.pune
Messages: 2
Registered: January 2009
Location: Pune
Junior Member

Hey this is another query which logically makes the status fail if one get marks less than 50 in any one of the subject.

select sanme as "Student Name", ((sum(marks)*1.000/(select count(subid)*100 as tot from #sub)) *100) as "Percentage",
(case when ((sum(case when marks < 50 then -500 else marks end )*1.000/(select count(subid)*100 as tot from #sub)) *100) > 50 then 'Pass' else 'Fail' end) as "Pass/Fail"
from #marks group by sanme


-Amrendra
Re: SQL Query [message #380776 is a reply to message #380775] Tue, 13 January 2009 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And here repeating what you should follow:
Michel Cadot wrote on Tue, 13 January 2009 12:26
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.

Regards
Michel

Re: SQL Query [message #380780 is a reply to message #380771] Tue, 13 January 2009 05:38 Go to previous messageGo to next message
manns
Messages: 59
Registered: March 2006
Location: Bangalore
Member

This will not solve this problem since if some one is scoring more then 90% but score less then 50 in 1 subject also should mark as fail.
Re: SQL Query [message #380782 is a reply to message #380780] Tue, 13 January 2009 05:52 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

This will not solve this problem since if some one is scoring more then 90% but score less then 50 in 1 subject also should mark as fail.


Could you post how you arrived at the conclusion ?

Regards

Raj
Re: SQL Query [message #380797 is a reply to message #380782] Tue, 13 January 2009 07:02 Go to previous messageGo to next message
thangs123
Messages: 1
Registered: January 2008
Junior Member
Try this,

select student_name,pert,decode(pass,3,'PASS','FAIL') from
(select student_name,sum(marks),round((sum(marks)/300)*100,0) Pert,sum(decode(sign(marks-50),1,1,0,1,-1,0)) pass
from stud1 group by student_name)
Re: SQL Query [message #380798 is a reply to message #380797] Tue, 13 January 2009 07:12 Go to previous message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
same remark for you:
Michel Cadot wrote on Tue, 13 January 2009 12:26
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.

Regards
Michel

Previous Topic: Swap values of two rows.
Next Topic: extracting multiple Numbers from String Column..
Goto Forum:
  


Current Time: Sat Nov 02 13:37:20 CDT 2024