Thread: about sql

hi ragav

sorry for the distrubends

i have posted what i created and what i have done

please check that and give solution


''create table c (name varchar(20),id int)

insert into c values('dss',32)

alter table c modify name not null''

hi ragav

thnaks for your commitment

i can add or drop constraint when creates the table with not null .

but i did n't modify null to not null or vies versa.

i have one doubt that will versions play mojor role about querry?

this is my problem.........

ok if is time permits ..............update your view .


thank you all.............

hi,

sorry i went to have lunch.

well, the version will not have any effect.

and yes, Kalayama the query 'alter table c alter column name not null' will not work on Oracle, i am not sure about SQL server, as i never worked on it.

for not null we have to use Alter...... modify.....

well, it was a nice question, after so much of gap i referred to the basic concepts. and it was good to revise.

hope other people must have got the benefit.

cheers,
RSY

hi,

hey good thing to know kalayama. i read that MSDN link. and u are correct for sql server alter...modify is not used.

now aarusa will search for us.
sorry aarusa. i gave u the suggestion pertaining to oracle and u were working on SQL server.

sorry for troubling u. from next time i will check the Type of database.

good learning at the end.

cheers

RSY

hi folks,

i am aarusa,

once i would like to tell thnks for you all.

i got answer for that.

when i used this querry that works,

alter table alter column not null.

thanks for alll................and one more thing

1.what is the use of index ?
2.how can i create that give with one example?

hi good morning friends,

Indexes are optional structures associated with tables used to speed query execution. an index is created if there is frequent retrieval of fewer than 10-15% of the rows in a large table and columns are referenced frequently in the WHERE clause.

well it increases query speed but reduce update speed.

Oracle automatically update indexes.
Concatenated index can be max. on 16 columns.

well , its a very huge topic. so its not possible to write about it here. u will have to read it on net or go through books.

i will give very simple example:

SQL> desc d1;
Name Null? Type
------------------------------- -------- ----
ID NUMBER
NAME VARCHAR2(20)

SQL> create index id on d1(id);

Index created.

u can see this index features with this query:

SQL> select * from user_objects where object_name = 'ID';


SQL> drop index id;

Index dropped.

i will give a link some of the material is there:

http://www.techonthenet.com/oracle/indexes.php

and its better u go through other material also, understans how index is created, then how it works during query execution. because there are around 32 types of indexes( i am not sure about this number).

and one more thing, what ever i am writing its for ORACLE.

regards,
RSY

Hi Aaarusa,
u were using MS-SQL 2000 i think..
U told as u created one table with 2 column, just

Execute this Query for Making existing Column from NULL to NOT NULL

Alter table
Alter Column Datatype not null

this'll work.. If this not works let me know.. For creating Constraint, let me know wat constraint u need..

Regards
Vijay

hi vasu.

i got answer for that.

now i want details about index

if is it possible please explain me wilth small example

thank you...........................

hi folks,

can i see weather how many tables are in one data base by using querrry?

because if i want to see that i have to be go object browser .........

Hi aarusa,

this query'll give u list of all userdefined tables,
--to list all tables
select distinct ist.* from information_schema.tables as ist
inner join sysobjects as so on ist.table_name = so.name
where so.status > 0

-- to list all table name along with columns
select distinct isc.* from information_schema.columns as isc
inner join sysobjects as so on isc.table_name = so.name
where so.status >0
order by isc.table_name, isc.ordinal_position


u can even paste this query in shortcut keys like (eg., ctrl+4).. In later when ever u reqd u can get it easily...

Regards
vijay

Hi aarusa,
There are 2 kinds of Indexes, Clustered index and Non-Clustered Index.

Clustered index
Only one can create on One table
This'll get create when ever u were creating Constraint like primary Key or Unique


Non-Clustered index
Many can create on One table
This we can create many on the same table based on the Column (OR column combination if reqd)
Syntax:
Create Index On (Column1)
Create Index On (Column1, Column2, ....)

If Still more Reqd, give me the scenario and i'l explain u in detail as much i can
Regards
Vijay

no jusr use the easy way
(inside the table design in Enterprise Manager)
you can use constrait check in properties
and jost right like:
1- i='1'or i='2'
2- like[1-2]

thats mean just enetr the code with out alter but do that before enter the all data to table