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 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 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
17. to_timestamp
with mytable as
(select .... from ...)
select column_1/(select sum(column_1) from mytable)
from mytable
19. indexing
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
--
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
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;
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
