Home » SQL & PL/SQL » SQL & PL/SQL » How to convert this particular SQL query to PL/SQL query
How to convert this particular SQL query to PL/SQL query [message #422576] Thu, 17 September 2009 08:25 Go to next message
wolverine82
Messages: 6
Registered: September 2009
Junior Member
I basically need to create an anonymous block that will display each student's first name, last name and the count of students who scored less on test 1 than the student.

So basically we need to find the count of students who have scored less than that particular student and we do it for all students in the table.

So for this particular query i designed my code in SQL

select g1.gr_fname, g1.gr_lname, count(*)
from grade g1, grade g2
where g1.gr_t1 > g2.gr_t1 and
g1.std_code = g2.std_code
group by g1.gr_fname, g1.gr_lname;

But i am unable to get the logic as to how to execute it in PL/SQL,I am missing something a minor detail to execute the same in PL/SQL

could someone please help me with the logic.

Thanks
Re: How to convert this particular SQL query to PL/SQL query [message #422577 is a reply to message #422576] Thu, 17 September 2009 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We are not here to do your homework.
You have to first post your tries and explain why they don't work. Then we can help you to get further.

Before 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, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Thu, 17 September 2009 08:31]

Report message to a moderator

Re: How to convert this particular SQL query to PL/SQL query [message #422579 is a reply to message #422577] Thu, 17 September 2009 08:34 Go to previous messageGo to next message
wolverine82
Messages: 6
Registered: September 2009
Junior Member
I did this in PL/SQL but i know i am wrong because i am not getting any output and i am missing logic.

SET SERVEROUTPUT ON

DECLARE

LV_COUNT NUMBER(5) :=0;
LV_GR_T1 GRADE.GR_T1%TYPE;
LV_GR_TT GRADE.GR_T1%TYPE;
LV_FNAME GRADE.GR_FNAME%TYPE;
LV_LNAME GRADE.GR_LNAME%TYPE;
CURSOR CURSOR_GRADE IS SELECT GR_FNAME, GR_LNAME, GR_T1 FROM GRADE;

BEGIN


OPEN CURSOR_GRADE;
FETCH CURSOR_GRADE INTO LV_FNAME, LV_LNAME, LV_GR_T1;

WHILE CURSOR_GRADE%FOUND LOOP

IF (LV_GR_T1 < LV_GR_TT)

THEN

LV_COUNT := LV_COUNT + 1;



END IF;

DBMS_OUTPUT.PUT_LINE(LV_FNAME || LV_LNAME || LV_COUNT);

FETCH CURSOR_GRADE INTO LV_FNAME, LV_LNAME, LV_GR_T1;


END LOOP;

CLOSE CURSOR_GRADE;
END;
Re: How to convert this particular SQL query to PL/SQL query [message #422584 is a reply to message #422579] Thu, 17 September 2009 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i am not getting any output

Execute "set serveroutput on"

Quote:
and i am missing logic

What does this mean? You don't understand what you wrote?

Once again, read guide and use code tags.

Regards
Michel
Re: How to convert this particular SQL query to PL/SQL query [message #422591 is a reply to message #422584] Thu, 17 September 2009 10:08 Go to previous messageGo to next message
wolverine82
Messages: 6
Registered: September 2009
Junior Member
No issue ,I figured out the solution on my own.

Thanks
Re: How to convert this particular SQL query to PL/SQL query [message #422597 is a reply to message #422591] Thu, 17 September 2009 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And it is?

Forum Guide:
If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.

Regards
Michel

[Updated on: Thu, 17 September 2009 11:07]

Report message to a moderator

Re: How to convert this particular SQL query to PL/SQL query [message #422598 is a reply to message #422591] Thu, 17 September 2009 11:26 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
wolverine82 wrote on Thu, 17 September 2009 20:38
No issue ,I figured out the solution on my own.

Right...
Thread in OTN Forum: How to convert this SQL query to PL/SQL
./fa/1987/0/
Re: How to convert this particular SQL query to PL/SQL query [message #422609 is a reply to message #422598] Thu, 17 September 2009 16:23 Go to previous messageGo to next message
wolverine82
Messages: 6
Registered: September 2009
Junior Member
> set serveroutput on
SQL> declare
  2  cursor cursor_grade is (select g1.gr_fname, g1.gr_lname, count(*) count
  3                            from grade g1, grade g2
  4                   where g1.gr_t1 > g2.gr_t1 
  5               and g1.std_code <> g2.std_code
  6               group by g1.gr_fname, g1.gr_lname);
  7  begin
  8  dbms_output.put_line(rpad('FIRST NAME',15) || rpad('LAST NAME', 15) || 'COUNT'); 
  9  dbms_output.put_line(rpad('-', 35, '-'));
 10  for i in cursor_grade
 11  loop
 12  dbms_output.put_line(rpad(i.gr_fname, 15) || rpad(i.gr_lname,15) || i.count);
 13  end loop;
 14  end;


[Mod Edit: Added code tags]

[Updated on: Fri, 18 September 2009 01:09] by Moderator

Report message to a moderator

Re: How to convert this particular SQL query to PL/SQL query [message #422622 is a reply to message #422609] Thu, 17 September 2009 23:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 17 September 2009 15:29
...
Before 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, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

Re: How to convert this particular SQL query to PL/SQL query [message #423192 is a reply to message #422576] Tue, 22 September 2009 10:05 Go to previous messageGo to next message
yoteksin
Messages: 3
Registered: September 2009
Junior Member
Basically someone else is done your homework for you. Great!!!!!!1
Re: How to convert this particular SQL query to PL/SQL query [message #423193 is a reply to message #423192] Tue, 22 September 2009 10:11 Go to previous messageGo to next message
wolverine82
Messages: 6
Registered: September 2009
Junior Member
i did the sql query on my own and was trying to find out as to how to do it in PL/SQL and i thought forums were here for help and what is the problem with you yoteksin...it is as if u had helped me Laughing, when someone else helped me i was genuinely trying to learn the concept and was looking for some help and all you guys are bent of criticizing rather than helping looks like u have some issues.
Re: How to convert this particular SQL query to PL/SQL query [message #423204 is a reply to message #423193] Tue, 22 September 2009 12:24 Go to previous messageGo to next message
yoteksin
Messages: 3
Registered: September 2009
Junior Member
Yes you are right , forums are here for help not for doing your homework or your assignment.
Re: How to convert this particular SQL query to PL/SQL query [message #423206 is a reply to message #423204] Tue, 22 September 2009 12:40 Go to previous messageGo to next message
wolverine82
Messages: 6
Registered: September 2009
Junior Member
let me ask u one thing why do u barge into unnecessary issues without anyone asking their opinion from u and do u always be such a pain for all others also by poking your nose where it is not required and did u know that it was a multiple part of questions and i solved all of them myself and i was having trouble in getting one solution and it has lot of ones before them and lot of questions after them.

and u know what now i realized as to why i am wasting my time trying to explain something to a person to whom i have no reason to give any explanation

and first try to help someone before putting comments on unnecessary issues as i had seen this is your 2nd post only...
Re: How to convert this particular SQL query to PL/SQL query [message #423208 is a reply to message #423206] Tue, 22 September 2009 13:12 Go to previous message
yoteksin
Messages: 3
Registered: September 2009
Junior Member
i have just signed up to this forum, you are again right but not for doing homework for you. For people who really need help or learn new solutions if i can. All i can say is good luck with your study . Remember nobody will do your job when you are in a work environment.

Previous Topic: PLSQL - Fetch all matching rows when input is NULL and specific row when input is NOT NULL
Next Topic: how to load data from excel sheet to oracle table
Goto Forum:
  


Current Time: Fri Dec 02 18:18:51 CST 2016

Total time taken to generate the page: 0.22656 seconds