Aug 5, 2010

SQL Functions - Oracle

SQL Functions












Types of Functions






















Single Row Functions - 




1. Single row functions:
2. Manipulate data items
3. Accept arguments and return one value
4. Act on each row returned
5. Return one result per row
6. May modify the data type
7. Can be nested
8. Accept arguments which can be a column or an expression 
















Case manipulation functions -

1. LOWER 
(e.g. LOWER('SQL') -> sql)
2. UPPER
(e.g. UPPER('sql Course')  -> SQL COURSE)
3. INITCAP
(e.g. INITCAP('sql') -> Sql)

Character manipulation functions -

1. CONCAT
(e.g. 
CONCAT('Hello', 'World') -> HelloWorld)

2. SUBSTR
(e.g. 
SUBSTR('HelloWorld',1,5) -> Hello)
3. LENGTH
(e.g. 
LENGTH('HelloWorld') -> 10)
4. INSTR
(e.g. 
INSTR('HelloWorld', 'W') -> 6)

5. LPAD | RPAD
(e.g. 
LPAD(salary,10,'*') -> *****24000)
(e.g 
RPAD(salary, 10, '*') -> 24000*****)
6. TRIM
(e.g. 
TRIM('H' FROM 'HelloWorld') -> elloWorld)
7. REPLACE

Number Functions - 

1. ROUND 
(e.g 
ROUND(45.926, 2) -> 45.93)
2. TRUNC
(e.g. 
TRUNC(45.926, 2) -> 45.92)
3. MOD
(e.g. 
MOD(1600, 300) -> 100)


Dates functions - 

Conversion functions - 

1. Implicit data type conversion
- From varchar2/char to number


From varchar2/char to date


- From date to varchar2


- From number to varchar2



2. explicit data type conversion

- From varchar2/char to number using TO_NUMBER


From varchar2/char to date using TO_DATE


- From date to varchar2 using TO_CHAR


- From number to varchar2 using TO_CHAR

Aug 4, 2010

Basics - Table in Oracle

Table - Basic unit of storage; composed of rows  and columns

Naming Rules - 

1. Table names and column names:
2. Must begin with a letter
3•3. Must be 1–30 characters long
4. Must contain only A–Z, a–z, 0–9, _, $, and #
5. Must not duplicate the name of another object owned by the same user
6. Must not be an Oracle server reserved word

CREATE TABLE statement -

1. You must have:
CREATE TABLE privilege
A storage area
2. You specify:
Table name
Column name, column data type, and column size

Refer other user's table - 

Tables belonging to other users are not in the user’s schema.
You should use the owner’s name as a prefix to those tables.
 

For example :

CREATE TABLE dept (deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13));
Table created.

DESCRIBE dept

Name    Null?  type
DEPTNO         NUMBER(2)
DNAME          VARCHAR2(14)
LOC            VARCHAR2(13)