Home » Other » Training & Certification » QUERY
QUERY [message #271230] Sun, 30 September 2007 23:51 Go to next message
romi
Messages: 67
Registered: October 2006
Member
Hi all,

I have 2 queries as follows:-

1) i want to print a name 'yellow' as follows with sql query.

y
e
l
l
o
w


2) i want a table like this with sql query

5*1 = 5
5*2 = 10
5*3 = 15
........
........
5*10 = 50

how can i find these 2 query, plz help me out.

Thanks
Re: QUERY [message #271231 is a reply to message #271230] Sun, 30 September 2007 23:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The homework tutor is down the near the bottom of this forum.
Re: QUERY [message #271233 is a reply to message #271230] Sun, 30 September 2007 23:57 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Have a look at loop, For Loop.
Re: QUERY [message #271236 is a reply to message #271230] Mon, 01 October 2007 00:06 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

2nd one, You want a table! Are you searching for this?
SQL> declare p number:=0; begin
  2   for k in 1 .. 10 loop  DBMS_OUTPUT.PUT_LINE( '5 *' ||k ||'='||5*k); end loop;
  3  end;
  4  /
PL/SQL procedure successfully completed.

SQL> set serverout on;
SQL> /
5 *1=5
5 *2=10
5 *3=15
5 *4=20
5 *5=25
5 *6=30
5 *7=35
5 *8=40
5 *9=45
5 *10=50

PL/SQL procedure successfully completed.

Re: QUERY [message #271239 is a reply to message #271230] Mon, 01 October 2007 00:17 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

1st one,
SQL> declare a varchar2(100); b number; 
begin a:='&a'; for i in 1 .. length(a) loop 
dbms_output.put_line(substr(a,i,1)); end loop; end;
  2  /
Enter value for a: Hello
old   1: declare a varchar2(100); b number; begin a:='&a'; .
for i in 1 .. length(a) loop dbms_output.put_line(substr(a,i,1)); end loop; end;
new   1: declare a varchar2(100); b number; 
begin a:='Hello'; for i in 1 .. length(a) loop
 dbms_output.put_line(substr(a,i,1)); end loop; end;

H
e
l
l
o

PL/SQL procedure successfully completed
.

[Updated on: Mon, 01 October 2007 00:18]

Report message to a moderator

Re: QUERY [message #271247 is a reply to message #271239] Mon, 01 October 2007 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Arju,

1/ Don't do other homework
2/ The question is "i want a table like this with sql query", not PL/SQL

romi,

Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

If you use search, both solutions are already in the site.

Regards
Michel
Re: QUERY [message #271286 is a reply to message #271247] Mon, 01 October 2007 02:44 Go to previous message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
I won't provide a spoonfed answer but I will push you in the right direction. My tips:
- For both queries you'd be needing a row generator.
- For the decomposition of the string you'd be looking at SUBSTR

Let's see what you can come up with. Start with the row generator. you'll find tons of examples on the board. In fact, your first question is answered here somewhere.

MHE
Previous Topic: Comparing date from select statement
Next Topic: pl/sql for portfolio profit and loss
Goto Forum:
  


Current Time: Wed Apr 24 17:11:28 CDT 2024