Oracle ( SQL )

DDL
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

◦CREATE - to create objects in the database
◦ALTER - alters the structure of the database
◦DROP - delete objects from the database
◦TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
◦COMMENT - add comments to the data dictionary
◦RENAME - rename an object

DML
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

◦SELECT - retrieve data from the a database
◦INSERT - insert data into a table
◦UPDATE - updates existing data within a table
◦DELETE - deletes all records from a table, the space for the records remain
◦MERGE - UPSERT operation (insert or update)
◦CALL - call a PL/SQL or Java subprogram
◦EXPLAIN PLAN - explain access path to data
◦LOCK TABLE - control concurrency

DCL

Data Control Language (DCL) statements. Some examples:

◦GRANT - gives user's access privileges to database
◦REVOKE - withdraw access privileges given with the GRANT command

TCL

Note: DML statements can be rollbacked where DDL are autocommit.
DML commands can't be rollback when a DDL command is executed immediately after a DML. DDL after DML means "auto commit". The changes will return on disk not on the buffer. If the changes return on the buffer it is possible to rollback not from the disk.

DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

DELETE
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

SQL> DELETE FROM emp WHERE job = 'CLERK';
SQL> COMMIT;

TRUNCATE
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

SQL> TRUNCATE TABLE emp;

DROP
The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

SQL> DROP TABLE emp;

From Oracle 10g a table can be "undropped". Example:
SQL> FLASHBACK TABLE emp TO BEFORE DROP;

Flashback complete.
=============================================================================
How does one eliminate duplicates rows from a table?
Method 1:
SQL> DELETE FROM table_name A WHERE ROWID > (
2 SELECT min(rowid) FROM table_name B
3 WHERE A.key_values = B.key_values);
Delete all rowids that is BIGGER than the SMALLEST rowid value (for a given key).
--------------------------------------------------------------------------
Method 2:
SQL> create table table_name2 as select distinct * from table_name1;
SQL> drop table table_name1;
SQL> rename table_name2 to table_name1;
This method is usually faster. However, remember to recreate all indexes, constraints, triggers, etc. on the table when done.
---------------------------------------------------------------------------
Method 3:
SQL> delete from my_table t1
SQL> where exists (select 'x' from my_table t2
SQL> where t2.key_value1 = t1.key_value1
SQL> and t2.key_value2 = t1.key_value2
SQL> and t2.rowid > t1.rowid);
-----------------------------------------------------------------------------
deleting duplicate rows from a table
This statement deletes rows from the emp table where duplicate values of last_name appear.


SQL>delete from emp e

where empno in(select empno from emp d
where d.last_name=e.last_name

minus

select empno from emp f
where f.last_name=e.last_name
and rownum=1)
-----------------------------------------------------------------------
Using dense_rank()
Col1 and col2 are the primary key columns. To provide the correct SQL to use alias name "ln" for f dense_rank in the statement:


delete from
where rowid in (select rn from (Select rowid rn,
dense_rank() over (partition by col1,col2.. order by rowid) ln
from )
where ln <> 1)
-------------------------------------------------------------------------
Another example using the dense_rank() function:
SQL>delete from table_name
where rowid in
(select rn from
(select rowid rn, dense_rank() over (partition by col1, col2, ..
order by rowid) from table_name ) where rn <> 1
)
===========================================================================
How does one select the LAST N rows from a table?

Get the bottom 10 employees based on their salary


SELECT ename, sal
FROM ( SELECT ename, sal, RANK() OVER (ORDER BY sal) sal_rank
FROM emp )
WHERE sal_rank <= 10;

Select the employees getting the lowest 10 salaries


SELECT ename, sal
FROM ( SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal) sal_dense_rank
FROM emp )
WHERE sal_dense_rank <= 10;
For Oracle 8i and above, one can get the bottom N rows using an inner-query with an ORDER BY clause:


SELECT *
FROM (SELECT * FROM my_table ORDER BY col_name_1)
WHERE ROWNUM < 10;
Use this workaround for older (8.0 and prior) releases:


SELECT *
FROM my_table a
WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)
FROM my_table b
WHERE b.maxcol <= a.maxcol)
ORDER BY maxcol;
========================================================================
How does one add a day/hour/minute/second to a date value?
The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples:


SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;

SYSDATE SYSDATE+1/24 SYSDATE+1/1440 SYSDATE+1/86400
-------------------- -------------------- -------------------- --------------------
03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13
The following format is frequently used with Oracle Replication:


select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;

NOW NOW_PLUS_30_SECS
-------------------- --------------------
03-JUL-2005 16:47:23 03-JUL-2005 16:47:53
Here are a couple of examples:


Extra date functions
Don't forget there is:

add_month(sysdate, 1) -- adds a month to the current date

addmonth(sysdate, 12) -- adds a year taking into account leap years
===============================================================================
How does one add a column to the middle of a table?
Oracle only allows columns to be added to the end of an existing table. Example:


SQL> CREATE TABLE tab1 ( col1 NUMBER );

Table created.

SQL> ALTER TABLE tab1 ADD (col2 DATE);

Table altered.

SQL> DESC tab1
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER
COL2 DATE
Nevertheless, some databases also allow columns to be added to an existing table after a particular column (i.e. in the middle of the table). For example, in MySQL the following syntax is valid:

ALTER TABLE tablename ADD columnname AFTER columnname;

Oracle does not support this syntax. However, it doesn't mean that it cannot be done.

Workarounds:

1. Create a new table and copy the data across.


SQL> RENAME tab1 TO tab1_old;

Table renamed.

SQL> CREATE TABLE tab1 AS SELECT 0 AS col1, col1 AS col2 FROM tab1_old;

Table created.

2. Use the DBMS_REDEFINITION package to change the structure on-line while users are workining.

Here is another workaround:

create table emptest as select empno, 1 as id, 'x' as emp_name, ename from emp;

In the code above, replace 1 as id, 'x' as emp_name with your new columns.

SQL>create table test (ename varchar2(20),salary number);
Table created

SQL>desc test;
Name Null? Type
---------------------- ----------- ---------------
ENAME VARCHAR2(20) SALARY NUMBER

[i]SQL>rename test to test1;
Table renamed
[ii]SQL>create table test2 (id varchar2(20));
Table created
[iii]SQL>create table test as(select test1.ename,test2.id,test1.salary from test1,test2);
Table created
.....................................................................................
SQL>desc test;
Name Null? Type
-------------------------------------------------------------------------------------
ENAME VARCHAR2(20)
ID VARCHAR2(20)
SALARY NUMBER
====================================================================================
How does one drop/ rename a columns in a table?
SQL> update t1 set column_to_drop = NULL;
SQL> rename t1 to t1_base;
SQL> create view t1 as select >specific columns> from t1_base;

SQL> create table t2 as select >specific columns> from t1;
SQL> drop table t1;
SQL> rename t2 to t1;

Rename a column

From Oracle9i one can RENAME a column from a table. Look at this example:

ALTER TABLE tablename RENAME COLUMN oldcolumn TO newcolumn;

Workarounds for older releases:

Use a view with correct column names:

rename t1 to t1_base;
create view t1 >column list with new name> as select * from t1_base;
Recreate the table with correct column names:

create table t2 >column list with new name> as select * from t1;
drop table t1;
rename t2 to t1;
Add a column with a new name and drop an old column:

alter table t1 add ( newcolame datatype );
update t1 set newcolname=oldcolname;
alter table t1 drop column oldcolname;

Multiple columns:

alter table emp drop (sal,deptno);
===========================================================================
What is the difference between VARCHAR, VARCHAR2 and CHAR data types?
CHAR

CHAR should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store varibale length strings, it will waste a lot of disk space.


SQL> CREATE TABLE char_test (col1 CHAR(10));

Table created.

SQL> INSERT INTO char_test VALUES ('qwerty');

1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM char_test;

COL1 LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty 10 Typ=96 Len=10: 113,119,101,114,116,121,32,32,32,32
Note: ASCII character 32 is a blank space.


VARCHAR

Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.


SQL> CREATE TABLE varchar_test (col1 VARCHAR2(10));

Table created.

SQL> INSERT INTO varchar_test VALUES ('qwerty');

1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM varchar_test;

COL1 LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty 6 Typ=1 Len=6: 113,119,101,114,116,121

VARCHAR2

VARCHAR2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.


SQL> CREATE TABLE varchar2_test (col1 VARCHAR2(10));

Table created.

SQL> INSERT INTO varchar2_test VALUES ('qwerty');

1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM varchar2_test;

COL1 LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty 6 Typ=1 Len=6: 113,119,101,114,116,121

VARCHAR vs. VARCHAR2
1. VARCHAR is going to be replaced by VARCHAR2 in next version. So, Oracle suggests the use VARCHAR2 instead of VARCHAR while declaring datatype.

2. VARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters.

3. If we declare datatype as VARCHAR then it will occupy space for NULL values, In case of VARCHAR2 datatype it will not occupy any space.

CHAR vs. VARCHAR
VARCHAR is used to store variable length character strings up to 4000 characters. But, remember CHAR is faster than VARCHAR - some times up to 50% faster.
===============================================================================
How does one implement IF-THEN-ELSE logic in a SELECT statement?
CASE Expressions

From Oracle 8i one can use CASE statements in SQL. Look at this example:


SELECT ename, CASE WHEN sal = 1000 THEN 'Minimum wage'
WHEN sal > 1000 THEN 'Over paid'
ELSE 'Under paid'
END AS "Salary Status"
FROM emp;

DECODE() Function

The Oracle decode function acts like a procedural statement inside an
SQL statement to return different values or columns based on the values of
other columns in the select statement. Examples:


select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown')
from employees;

select a, b, decode( abs(a-b), a-b, 'a > b',
0, 'a = b',
'a < b') from tableX;
Note: The decode function is not ANSI SQL and is rarely implemented
in other RDBMS offerings. It is one of the good things about Oracle,
but use it sparingly if portability is required.


GREATEST() and LEAST() Functions


select decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B',
'B is greater than A')...


select decode( GREATEST(A,B),
A, decode(A, B, 'A NOT GREATER THAN B', 'A GREATER THAN B'),
'A NOT GREATER THAN B')...

NVL() and NVL2() Functions

NVL and NVL2 can be used to test for NULL values.

NVL(a,b) == if 'a' is null then return 'b'.


SELECT nvl(ename, 'No Name')
FROM emp;
NVL2(a,b,c) == if 'a' is not null then return 'b' else return 'c'.


SELECT nvl2(ename, 'Do have a name', 'No Name')
FROM emp;

COALESCE() Function

COALESCE() returns the first expression that is not null. Example:


SELECT 'Dear '||COALESCE(preferred_name, first_name, 'Sir or Madam')
FROM emp2;

NULLIF() Function

NULLIF() returns a NULL value if both parameters are equal in value. The following query would return NULL:


SELECT NULLIF(ename, ename)
FROM emp;