Re: Student SQL question

>1. In what cases is there a need to convert a number to a string or a
string to a number. Could you give some practical examples?

If you want the output to have different formatting, you would convert a number to a string. For example, a numeric value of '4' will show as '4', but if you want it as '004', do this:

select lpad(to_char(column_name,3,'0')) from table_name;

In other words, numerics don't have leading zeros.

If you have a character value of '4' (or '004') and you want to do some mathmatical operation on it, you need to make it a number first:

select to_number(column_name)*100 from table_name;

> 2. Once a table is created, a column in that table cannot be deleted

True. You have to drop the table and then re-create the table. Save the existing rows in another table first and then re-insert them in the new table. I think that's dumb, but what can you do? I don't know why Oracle won't let you drop a column. One would think it's the easiest thing to do, as opposed to modifying an existing column.

>or a datatype of a column cannot be changed

Not true. But all rows have to have a null value for the column in order to do it.

>the tablename cannot be changed (as far as I know)

Haven't tried that myself, but I saw a post where someone else answered it.

Another restriction on modifying a table is creating a unique index on it when existing dta would cause the index to be violated. You also can't decrease the length of a column unless all rows for that column are null and if you make a column non-null, every row must have a value in the column. Look up the "alter table" command in a reference book for more on this kind of stuff.
--
Matt Brennan
SQL*Tools Specialist
GERS Retail Systems
9725-C Scranton Road
San Diego, California 92121
1-800-854-2263
mbrennan_at_gers.com
(Original email address is spam-blocked.) Received on Mon May 18 1998 - 11:21:38 CDT