ORACLE 9I COMMANDS

Oracle 9i Commands


To create table
Create table (value1 datatype,……);

To insert to table
Insert into values(data1,……);

To view the all the data table
Select * from ;

To view some column
Select from ;

To update data in the table
Update set =data to update where =data to find;

To delete from table
Delete from where =data to find;

To drop the table;
Drop table ;

To set primary key
When you create the table
Create table (value1 datatype primary key,…..);
After creating the table
Alter table add constraint primary key(column-name>;

To set foreign key
When creating the table
Create table (value1 datatype, constraint foreign key(column-name) references );
After creating the table
Alter table add constraint foreign key(column-name) \
References ;

To set the check constraint
When creating the table
1-column type
Create table (column1 datatype constraint
check(column1 operator rule));
2-table type
Create table (column1 datatype, column2 datatype,constraint
check(column1 rule column2 );

after the table created
alter table add constraint check(column-name rule);

to set not null
when creating the table
create table (column1 datatype not null);
to set unique key
when create the table
create table (column1 datatype unique);
after creating the table
alter table add constraint unique(column-name);

to use joins
1-inner join
select column1-table1,column1-table2 from ,
where =;
2-right join
select column1-table1,column1-table2 from ,
where (+)=;
3-left join
select column1-table1,column1-table2 from ,
where =(+);
3-self join
select a.column1,b.column1 from a, b where
a.column1 > b.column1 order by ;

to view the sum
select sum(column-name) from ;

to view the average
select avg(column-name) from ;

to view max
select max(column-name) from ;

to view min
select min(column-name> from ;

to view some character from the column
select substr(column-name,1,3) from ;

to view in upper case
select upper(column name) from ;

to view in lower case
select lower(column-name) from ;

to view the first latter in capital
select initcap(column-name) from ;

to remove spaces from left side of the column
select * from where ltrim(column-name);
to remove spaces from right sode of the column
select * from where rtrim(column-name);

to view the system date
select sysdate from dual;

to view the structure of the table
desc

to add new column to the table
alter table add datatype;

to modify the column in table
alter table modify datatype

to view data using in operator
select from where in(‘value1’,’value2’);
to view data using not in operator
select from where not in(‘value1’,’value2’);

to create sequence
create sequence
increment by
start with
maxvalue ;
/
you can use cycle after the maxvalue so when its finished it can repeat

to modify the sequence
alter sequence
increment by
start with
maxvalue ;
/

to use sequence
insert into (sequence-name.nextval);

to create synonyms
create synonym for username.table-name;

to drop the synonym
drop synonym ;

to drop the constrain
alter table drop constraint ;

to drop the primary key
alter table drop primary key
but if the primary key was referenced to a foreign key you cant drop

to make the constraint enabled or disabled
alter table enable constraint ;
alter table disable constraint ;

to put the up title on the report
ttitle ‘the data you want to write’
to set it off
ttitle off

to put the bottom title
btitle ‘the data you want to write’
to set it of
btitle off

to let the sql print report
set serveroutput on

to save to the tables
set autocommit on

to set the line size for the table
set linesize value

to set how many rows to be displayed
set pagesize value

to set the number of the column width
set numwidth value

to set the format of the column
column format a
to set break between the rows
break on skip on report

to set for average for report
before to set average it must have break on
the on-value must match the compute on value
break on skip
compute avg of on ;

to set for sum for report
before to set sum it must have break on
break on skip
compute sum of on ;