Using Partition Over in Oracle

SELECT ID, OTHER_ID, DATE_VALUE, ROW_NUMBER() OVER (PARTITION BY OTHER_ID ORDER BY DATA_VALUE desc) R,COUNT(*) OVER (PARTITION BY OTHER_ID) FROM SOME_TABLE


**Row_Number gives the row number of the row in a table after partition by other_id
**Count(*) gives the count value of the rows partitioned by other_id

Using WITH, DECODE functions in sql

       
 WITH Q1 AS
(SELECT unique_id,status from table group by unique_id having count(unique_id)=1)

SELECT * FROM Q1
WHERE unique_id IN
            (SELECT unique_id FROM Q1 WHERE  status = 'Approve'
            AND EXISTS (SELECT 1 FROM Q1 subq1
                        WHERE Q1.emp_id = subq1.emp_id
                        AND TRUNC(Q1.status_date) < TRUNC(subq1.insert_date)) 
            )                 
ORDER BY Q1.unique_id,decode(status,'Approve',1),status_date;
       

How to insert data into a table on Oracle database

       
INSERT INTO TABLE_NAME (Column1, Column2, Column3)

     Select TABLE1_SEQ.NEXTVAL, NewColumn2, NewColumn3

  FROM TABLE1  WHERE Column4 IN (X,Y) AND SomeCondition;
       
 

How to create tables with sequences and triggers on Oracle database

In this example I work on 2 tables EXAMPLE_DETAILS and EXAMPLE_DETAILS_HISTORY

The scenario is, whenever a row in EXAMPLE_DETAILS gets updated or deleted, the old record should be sent to history table (EXAMPLE_DETAILS_HISTORY)

 CREATE TABLE SCHEMA.EXAMPLE_DETAILS
  (
  DETAIL_ID  INTEGER    NOT NULL,
  UNIQUE_ID             INTEGER ,
  TABLE_NAME            VARCHAR2(30 BYTE),
  EXAMPLE_DATE          DATE,
  EXAMPLE_REF           INTEGER,  
  EXAMPLE_COMMENTS      VARCHAR2(4000 BYTE),
  CONSTRAINT DETAIL_ID_PK PRIMARY KEY (DETAIL_ID)
  )

CREATE SEQUENCE SCHEMA.EXAMPLE_DETAILS_SEQ
  START WITH 199
  MAXVALUE 999999999999999999999999999
  MINVALUE 0
  NOCYCLE
  NOCACHE
  NOORDER;

CREATE OR REPLACE TRIGGER EXAMPLE_HISTORY_TRIGGER
   BEFORE DELETE 
   ON EXAMPLE_DETAILS
   FOR EACH ROW
BEGIN
   INSERT INTO EXAMPLE_DETAILS_HISTORY (EXAMPLE_HISTORY_ID,
                                         UNIQUE_ID,
                                         TABLE_NAME,
                                         HISTORY_DATE,
                                         EXAMPLE_DATE,
                                         EXAMPLE_ACTION_REF,
                                         EXAMPLE_COMMENTS)
        VALUES (EXAMPLE_HISTORY_SEQ.NEXTVAL,
                :old.UNIQUE_ID,
                :old.TABLE_NAME,
                SYSDATE,
                :EXAMPLE_DATE,
                :EXAMPLE_REF,
                :EXAMPLE_COMMENTS);
END;
/
CREATE OR REPLACE TRIGGER EXAMPLE_DETAILS_TRG BEFORE insert OR update ON EXAMPLE_DETAILS
  FOR EACH ROW
BEGIN
  :new.EXAMPLE_DATE := sysdate ;
END;
/
CREATE OR REPLACE TRIGGER EXAMPLE_DETAILS_UPDATETRG AFTER update ON EXAMPLE_DETAILS
  FOR EACH ROW
BEGIN
IF :OLD.EXAMPLE_REF!=:NEW.EXAMPLE_REF OR :OLD.EXAMPLE_COMMENTS!=:NEW.EXAMPLE_COMMENTS
THEN
  INSERT INTO EXAMPLE_DETAILS_HISTORY (EXAMPLE_HISTORY_ID,
                                        UNIQUE_ID,
                                        TABLE_NAME,
                                        HISTORY_DATE,
                                        EXAMPLE_DATE,
                                         EXAMPLE_ACTION_REF,
                                         EXAMPLE_COMMENTS)
        VALUES ( EXAMPLE_HISTORY_SEQ.NEXTVAL,
                :old.UNIQUE_ID,
                :old.TABLE_NAME,
                SYSDATE,
                :old. EXAMPLE_DATE,
                :old. EXAMPLE_ACTION_REF,
                :old. EXAMPLE_COMMENTS);
  END IF;
END;
/
********************************************************************************************

CREATE TABLE SCHEMA.EXAMPLE_DETAILS_HISTORY
(
  EXAMPLE_HISTORY_ID INTEGER    NOT NULL,
  UNIQUE_ID         INTEGER NOT NULL,
  TABLE_NAME        VARCHAR2(30 BYTE),
  HISTORY_DATE           DATE,
  EXAMPLE_DATE           DATE,
  EXAMPLE_REF            INTEGER,  
  EXAMPLE_COMMENTS VARCHAR2(4000 BYTE),    
  CONSTRAINT HISTORY_ID_PK PRIMARY KEY (EXAMPLE_HISTORY_ID)     
)


CREATE SEQUENCE SCHEMA.EXAMPLE_DETAILS_HISTORY_SEQ
  START WITH 199
  MAXVALUE 999999999999999999999999999
  MINVALUE 0
  NOCYCLE
  NOCACHE
  NOORDER;     
 

Export and Import Data from Excel to Tables using TOAD

http://arifgulzar.blogspot.com/2012/06/exportimport-data-fromto-oracleexcel.html

Update multiple table rows from another table SQL

   UPDATE table1 t1
   SET (column1,column2) = (SELECT t2.value1 ,t2.value2
                         FROM table2 t2
                        WHERE t1.id=t2.id)
   WHERE EXISTS (
      SELECT 1
      FROM table2 t2
      WHERE WHERE t1.id=t2.id )
 

http://stackoverflow.com/questions/7030699/oracle-sql-update-with-data-from-another-table