Thursday, 17 September 2015

sql

1. select
      select <column_names>
      from <<table_name>>
      where <condition>
      [and|or] <condition>
      group by <column_name>
      order by <column_name> [asc, desc]


2. select distinct
      select distinct <column_name>
      from <table_name>


3. where
      where <column_name> in ('value1', 'value2', ...)
      where <column_name> between 'value1' and'value2'
      where <column_name> like {pattern}


4. sum()    
      select sum(<column_name>)       //return a value
      from <table_name>
      where <condition>;

      select <column_name1>,<column_name2>, sum(aggregate_expression)
      from <table_name>
      where <condition>
      group by <column_name1>, <column_name2>;
  • If extra column in involved in select, then a group by is needed

5. group by
      select <column_name1>, <column_name2>, func(column_name3)
      from <table_name>
      group by <column_name1>, <column_name2>
  • group by is used when aggregate function is used in the select statement
  • group by really means "for each unique" or "for each unique combination of ..."
      select service, sum(fraud_vol), sum(fraud_val) from

      (select biz_svc_cd as service, volume as fraud_vol, value as fraud_val
      from x2p_fraud_daily
      where fraud_tran_date >= to_date('01/02/2016', 'dd/mm/yyyy')
      and fraud_tran_date <= to_date('29/02/2016', 'dd/mm/yyyy')
      and instr_tp = 'pmnt')

      group by service
      order by service asc


6. having
      select <column_name1>, sum(<column_name2>)
      from <table_name>
      group by <column_name1>
      having sum(column_name2) > 1500
  • having is used when aggregate function is used in the select statement

7. update
      update <table_name>
      set <column_name1>=value1, <column_name2>=value2,...
      where <column_name3>=value3;


8. delete
      delete from <table_name>
      where <column_name1>=value1;


9. insert into
      insert into <table_name>
      values (value1,value2,value3,...);

      insert into <table_name> (column_name1,column_name2,column_name3,...)
      values (value1,value2,value3,...);


10. count
      select count(*)
      from <table_name>;

      select count(distinct <column_name>)
      from <table_name>;

      select count(<column_name>)
      from <table_name>
      where <column_name1>=value1;


11. join
  • join is based on foreign key concept. 
  • based on a column match between table a and b, it combines columns of a and b on a condition
  • inner join only return rows that meet the condition
  • left join return all rows from table a
  • right join return all rows from table b
  • full join return all rows from table a and b
  • self join is to find relationship between rows in the same table (self join create cartesian product of table with itself)
      select yr, mon, fraud_reason, sum(val) from
           (select extract(year from fraud_tran_date) as yr, extract(month from fraud_tran_date) as mon, value as val, description as fraud_reason
           from x2p_fraud_daily
           left join lo_x2p_fraud_rsn
           on x2p_fraud_daily.fraud_rsn_id=lo_x2p_fraud_rsn.id
           where fraud_tran_date>= to_date('01/02/2016', 'dd/mm/yyyy')
           and fraud_tran_date<= to_date('29/02/2016', 'dd/mm/yyyy'))
      group by yr, mon, fraud_reason
      order by yr desc, mon asc, fraud_reason;


12. common command

      create table new_table as select * from old_table;
      insert into table_1 select * from table_2;
      select extract(year from fraud_tran_date) as yr, extract(month from fraud_tran_date) as mon
      where fraud_tran_date >= to_date('01/02/2016', 'dd/mm/yyyy')
      and sender in ('ctbaau2sxxx', 'ctbaau2sbca', 'ctbaau2sxb1', 'ctbaauc1bca', 'ctbaauc1boc')

13. bulk delete with 50000 in one batch to avoid performance issue

      begin
      loop
      delete /*+ parallel (table1, 4) */  from table1
           where event_date < to_date('[date]','mm/dd/yyyy') and rownum < 50000;
           exit when sql%rowcount < 49999;
           commit;
      end loop;
      commit;
      end;


14. select into             //backup a table by creating a new one

      select *
      into backup_table
      from table;


15. merge into

      merge into x2p_fraud_entry using
          (select
                  x2p_fraud_entry_staging.id,
                  x2p_fraud_entry_staging.version,
                  x2p_fraud_entry_staging.transaction_key,
            from x2p_fraud_entry_staging
            left outer join x2p_settlement_transaction
                on x2p_settlement_transaction.tx_id_key = x2p_fraud_entry_staging.transaction_key
          ) txsrc
          on (x2p_fraud_entry.id = txsrc.id)
          when matched then update set
                --x2p_fraud_entry.id = txsrc.id, -- no need to update the id
                x2p_fraud_entry.version = txsrc.version,
          when not matched then insert
          (
           id,
           version,
          )
          values
          (
           txsrc.id,
           txsrc.version,
          );


16. coalesce
  • returns the first non-NULL expression among its arguments
  • so if expression 1 is null value, expressions 2 can become its default value
      coalesce("expression 1", "expressions 2", ...)


17. to_timestamp
  • convert char string to timestamp
  • select to_timestamp('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')
       from dual;
18. use with .. as to calculate percentage

      with mytable as
          (select .... from ...)
      select column_1/(select sum(column_1) from mytable)
      from mytable

19. indexing
  • content -> address

20. join query result with table

      select c1, c2 from t1
      left join (select c3, c4 from t2) t3
      on t1.c5 = t3.c6

21. query lookup with multiple column
      select
        d1.val as column1,
        d2.val as column2
      from table1 t
      join data d1 on ( d1.dataId = t.col1 )
      join data d2 on ( d2.dataId = t.col2 )


22. comment
      --


23. count(*) vs count(p.product_id)
  • count(*) counts the number of rows
  • count(p.product_id) counts the number of non-null product_id values

reference:
1. zentut sql tutorial

No comments:

Post a Comment