Home » SQL & PL/SQL » SQL & PL/SQL » allocating grades (oracle 9i)
allocating grades [message #327928] Wed, 18 June 2008 07:00 Go to next message
ssnvsrikanth
Messages: 4
Registered: June 2008
Junior Member
Hi Every one,

I need the query for allocating grades to the students in a table basing on the avg of the marks they got.

Regards,
srikatnh
Re: allocating grades [message #327929 is a reply to message #327928] Wed, 18 June 2008 07:05 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Post what you have tried and what you are trying to achieve??

Regards,
Rajat Ratewal
Re: allocating grades [message #327933 is a reply to message #327928] Wed, 18 June 2008 07:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think SQL will allow you to achieve this.
To be more precise a SELECT will be sufficient if you just want to display the grade but you should use UPDATE if you want to record it in the table and if the table contains a column for this.

Regards
Michel
Re: allocating grades [message #327948 is a reply to message #327929] Wed, 18 June 2008 07:32 Go to previous messageGo to next message
ssnvsrikanth
Messages: 4
Registered: June 2008
Junior Member
Hi rajatratewal,

update student_marks s set grade='A' where (select (s.eng+s.math+s.science)/3 AVG >75 from student_marks s);

QL> select * from student_marks;

ID ENG MATH SCIENCE
--------- ---------- ---------- ----------
100 65 98 75
101 45 87 35
102 87 90 65
103 65 78 56
104 35 76 56
here for this records i want to allocate the grades bsing on their avg's. I used the update command but i dont know how to proceed.


Regards
srikanth
Re: allocating grades [message #327950 is a reply to message #327948] Wed, 18 June 2008 07:34 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
You cannot update a column that doesn't exist in the table. Please make your question more clear.
Re: allocating grades [message #327952 is a reply to message #327948] Wed, 18 June 2008 07:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also 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) and align the columns in result.
Use the "Preview Message" button to verify.

Post a test case: create table and insert statements and the result you want with these data.

Regards
Michel
Re: allocating grades [message #327954 is a reply to message #327933] Wed, 18 June 2008 07:37 Go to previous messageGo to next message
ssnvsrikanth
Messages: 4
Registered: June 2008
Junior Member
Hi michel,

i want to place a seperate column in my table and i want to allocate the grades basing the averages i.e, 70%-75% as grade A like that.how i have to approach?this is the table and fields


select * from student_marks;

ID ENG MATH SCIENCE
--------- ---------- ---------- ----------
100 65 98 75
101 45 87 35
102 87 90 65
103 65 78 56
104 35 76 56
Re: allocating grades [message #327986 is a reply to message #327954] Wed, 18 June 2008 09:16 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You add columns to tables with the ALTER TABLE command.

As for your SQL, here's something to start with. If you've got a question more specific than 'Please do my homework for me', then feel free to come back to us:
SQL> with src as (select 100 id,  65 eng, 98 math, 75 science from dual union all
  2               select 101, 45, 87, 35 from dual union all
  3               select 102, 87, 90, 65 from dual union all
  4               select 103, 65, 78, 56 from dual union all
  5               select 104, 35, 76, 56 from dual)
  6  SELECT id, (eng+math+science)/3 avg_mark
  7  FROM   src;

        ID   AVG_MARK
---------- ----------
       100 79.3333333
       101 55.6666667
       102 80.6666667
       103 66.3333333
       104 55.6666667
Previous Topic: Need to remove logic for inserting multiple rows
Next Topic: Sub Query Assistance
Goto Forum:
  


Current Time: Wed Dec 07 18:27:21 CST 2016

Total time taken to generate the page: 0.06149 seconds