Useful SQL statements in ORACLE
2013-02-21 / ( 67 )
Useful commands in ORACLE
Between MySQL and Oracle are many similarities but also differences. Here is a short list containing some simple Oracle statements in which you can find these differences. One interesting particularly
is that for getting last id you can use an ORACLE SEQUENCE or just a SELECT MAX statement. And a very important thing, always before table alteration, don't forget to check if SEQUENCES and TRIGGERS exist!
Examples:
// Create a trigger
CREATE SEQUENCE SEQ_SOMETABLE;
// Empty table
DELETE FROM SOMETABLE WHERE FIELD > 1
// Truncate
TRUNCATE TABLE SOMETABLE;
// Update table values
UPDATE SOMETABLE SET FIELD = 'VALUE' WHERE ID > 1
// Update data type of db field
ALTER TABLE SOMETABLE MODIFY (FIELD1 char(200), FIELD2 nummer(20));
// Add new column someid
ALTER TABLE SOMETABLE ADD (SOMEID char(200));
// Get next value with sequence trigger
SELECT seq_SOMETABLE.NEXTVAL FROM dual;
// Get last id - alternative for next value with sequence trigger
SELECT * FROM SOMETABLE WHERE SOMEID = (SELECT MAX(SOMEID) FROM SOMETABLE)
// Drop sequence trigger
DROP SEQUENCE oe.SOMETABLE_seq;
// Drop trigger
DROP TRIGGER hr.TABLE;
// Find duplicates
SELECT FIELD, COUNT(FIELD) from SOMETABLE GROUP BY FIELD HAVING COUNT (FIELD) > 1 ORDER BY COUNT(FIELD) DESC;
// Make a join between tables
SELECT * FROM TABLE1 TB1 LEFT JOIN TABLE2 TB2 ON TB1.FIELD = TB2.FIELD
// Using REGEX
SELECT * FROM TABLE1 WHERE REGEXP_LIKE(FIELD,'^[0-9]{1}[a-zA-Z](.*)$')";
// GET LIMIT 20
SELECT col FROM tbl WHERE rownum<=20;
More Oracle answers can be found on these websites:
Oracle GROUP BY & HAVING Clauses
http://psoug.org/reference/group_by.html
Regular expressions in Oracle
http://www.adp-gmbh.ch/ora/sql/re/index.html
Oracle's explain plan
http://www.adp-gmbh.ch/ora/explainplan.html
Oracle GROUP BY HAVING tips
http://www.dba-oracle.com/t_oracle_group_by_having.htm
Oracle Truncate Table
http://psoug.org/reference/truncate.html
Difference between TRUNCATE, DELETE and DROP commands
http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands