Some Useful Oracle SQL-1

Creating  Table :
=> create table tableName(ColumnName dataType, ColumnName dataType,…);

Inserting  Data To A Table :
=> insert  into tablename(ColumnName, ColumnName,…………..)
values(_,_,…);

or, insert into tableName values(__,___,………);

Example:
=> insert into CUST_MSTR(CUST_NO,FNAME,MNAME,LNAME,DOB_INC)
values(‘C1’,’Kazi’,’Mohammad Marufur’,’Rashid’,
to_date(‘16/05/1983 10.55 A.M.’,’DD/MM/YY HH:MI A.M.’));

Updating All Rows:
=> update tableName
set  columnName=___
where columnName=__;

Removal of Specific Rows:
=> delete from tableName where tableName=___;

Removal of Specific Data Oriented  Rows :=> delete from tableName where tableName like ‘Ma%’;

Removal of all Rows :
=> delete from tableName;

Dropping Table:
=> drop table tableName;
Truncating Table:=> truncate table tableName;
Rename Table:
=> rename OldTableName to NewTableName;

Displaying The Table Structure:
=> describe tableName;

Getting All Table Name From A Schema:
=> Select * from tab;

Dropping A Column:
=> alter table tableName drop columnName;

Adding New Column:
=> alter table tableName add(columnName dataType(size));

Some Select Statement:
=> select * from tableName;
=> select a.columnName ,b.columnName from table1Name a, table2Name b where a.columnName=b.columnName ;
here a,b=alias.

=> select distinct * from tableName ;
=> select distinct columnName from tableName ;

=> select to_char(sysdate,'dd/mm/yy')as today from dual;

One  Example :
Here UNI_HOSPITAL=Schema Name , ABOUT_US, USER_DETAILS = Table Name

CREATE TABLE UNI_HOSPITAL.ABOUT_US
(
  ABOUT_US_OID      NUMBER PRIMARY KEY,

  ABOUT_US_TITLE    VARCHAR2(500 BYTE),

  ABOUT_US_DETAILS  VARCHAR2(500 BYTE),

  IS_ACTIVE         VARCHAR2(15 BYTE)           DEFAULT 'Y',

  INSERT_BY         NUMBER                      DEFAULT 0,

  INSERT_DATE       DATE,

  UPDATE_BY         NUMBER                      DEFAULT 0,

  UPDATE_DATE       DATE,

  DELETE_BY         NUMBER                      DEFAULT 0,

  DELETE_DATE       DATE,

  IS_DELETED        VARCHAR2(15 BYTE)           DEFAULT 'N',

  FOREIGN KEY (USER_OID) REFERENCES USER_DETAILS(USER_OID)
)

Transaction Commit:
=> commit

Transaction Save Point:
=> savepoint savePointName;

Transaction Rollback:
=> rollback to savePointName;Example :
=> INSERT INTO UH_MEDIA_CENTER (MEDIA_CENTER_OID,MEDIA_CENTER_TITLE);
VALUES (6,'New Title inserted successfully...'); 
=> savepoint UNI_HOS_SAVE_POINT;
=> rollback to UNI_HOS_SAVE_POINT;
=> commit;

Creating Index :
Simple Index:
=> create index indexName on tableName(columnName) ;
Example : create index about_us on about_us(ABOUT_US_OID) ;

Composite Index :
=> create index indexName on tableName(columnName, columnName) ;
Example : create index about_us on about_us(ABOUT_US_OID,ABOUT_US_TITLE) ;

Unique Index :
=> create unique index indexName on tableName(columnName) ;Example : create unique index about_us on about_us(ABOUT_US_OID) ;Example : create unique index about_us on about_us(ABOUT_US_OID,ABOUT_US_TITLE) ;

Dropping Index :

=> drop index indexName;
Example : drop index about_us;

Creating Sequence:

“ create sequence sequenceName

 increment by 1

 start with 1

 maxvalue 100 or nomaxvalue

 minvalue 0 or nominvalue

 cycle or nocycle

 cache 1 or nocycle

 order or noorder ”

Example :

=> “create sequence sequenceName

 increment by 1

 start with 1

 maxvalue 999999999999999

 minvalue 0

 cycle

 cache 20

 order ;"

Dropping sequence:

 => drop sequence sequenceName;