Varsha Hulikantimutt's Blog

Some very use full Oracle SQL Queries.

Varsha Hulikantimutt's photo
Varsha Hulikantimutt
·

2 min read

  • Alter Datatype of an empty or Null valued column.
ALTER TABLE table_name MODIFY(column_name data_type);
  • Include new columns in an existing table.
ALTER TABLE table_name
  ADD (column_1  column_definition,
      column_2  column_definition,
      ...
      column_n  column_definition);
  • Create Table statement.
CREATE TABLE table_name (
    column_1 datatype,
    column_2 datatype,
    column_3 datatype,
   .... 
    column_n datatype);
  • The Oracle BETWEEN condition is used to retrieve values within a range in a SELECT, INSERT, UPDATE, or DELETE statement.

SELECT *
FROM table_name
WHERE column_name BETWEEN value_1 AND value_2;

is equivalent to 


SELECT *
FROM table_name
WHERE column_name >= value_1
AND column_name<= value_2;
  • Update a value in the table
UPDATE table_name
SET 
column_1 = value_1, 
column_2 = value_2, ...
column_n = value_n
WHERE condition;
  • Create a sequence 
CREATE SEQUENCE sequence_name
START WITH initial_value
INCREMENT BY increment_value
MINVALUE minimum value
MAXVALUE maximum value
CYCLE|NOCYCLE ;

sequence_name: Name of the sequence.

initial_value: starting value from where the sequence starts. Initial_value should be greater than or equal to minimum value and less than equal to maximum value.

increment_value: Value by which sequence will increment itself. Increment_value can be positive or negative.

minimum_value: Minimum value of the sequence. maximum_value: Maximum value of the sequence.

cycle: When sequence reaches its set_limit it starts from beginning.

nocycle: An exception will be thrown if sequence exceeds its max_value.

  • Alter table name

ALTER TABLE table_name
RENAME TO new_table_name;
OracleSQL