Common Oracle statement files (1)

Basic Select Statement

SELECT * | {[DISTINCT] column | expression [alias],...} FROM table;

Using Arithmetic Operators

SELECT 12 * (column + 10) + 200 FROM table;

Using the Concatenation Operator

SELECT columnA | columnB FROM table;

Using Literal Character Strings

SELECT columnA | 'plus '| columnB as "ACombineB" FROM table;

Eliminating Duplicate Rows

Select distinct column_name FROM table;

Displaying Table Structure

DESC [RIBE] tablename;

 

Restricting and Sorting Data

Limiting the rows selected

SELECT * | {[DISTINCT] column | expression [alias],...} FROM table [WHERE condition (s)] [order by {column, expr, alias} [ASC | DESC];

Using comparison conditions

SELECT * FROM table WHERE columnA <> 'sample ';

Comparison Conditions: =, >,>=, <,<=, <>,... AND ..., IN (set), LIKE, IS NULL

Using the LIKE condition

Using the LINK condition to perform wildcard searches of valid search string values.

Search conditions can contain either literal characters or numbers:

% Denotes zero or invalid characters.

_ Denotes one character.

You can use the ESCAPE identifier to search for the actual % and _ symbols.

SELECT column_name from table where column_name LIKE '% S \ _ % 'escape '\';

Using the NULL conditions

SELECT * FROM table WHERE column_name is null;

Order by Clause

Sort rows with order by clause [ASC: ascending order, default; DESC: descending order]

The ordery by clause comes last in the select statement, you can sort by a column that is not in the select list and can sort by multiple columns.

 

Single-Row Functions

Character functions

Character-manipulation functions

CONCAT, SUBSTR, LENGTH, INSTR, LPAD | RPAD, TRIM, REPLACE

Case-manipulation functions

LOWER, UPPER, INITCAP

Note:

INSERT(Column | expression, 'string ", [, m], [n])

Returns the numberic position of a named string. optionally, you can provide a position m to start searching, and the occurrence n of the string. m and n default to 1, meaning start the search at the beginning of the seach and report the first occurrence.

LPAD(Column | expression, n, 'string ')RPAD(Collum | expression, n, 'string ')

Pads the character value right-justified/left-justified to a total width of n character positions.

SUBSTR (column | expression, m, [, n])

Returns specified characters from character values starting at character position m, n characters long (If m is negative, the count starts from the end of the character value. if n is omitted, all characters to the end of the string are returned .)

Number Functions

ROUND: Rounds value to specified decimalROUND(32.987, 2) print 32.99ROUND(32.987,-1) print 30ROUND(32.987,-1) print 33

TRUNC: Truncates value to specified decimalTRUNC(32.987, 2) print 32.98

MOD: Returns remainder of devisionMOD(1500,400) print 300

Note: ROUND (column | expression, n) Rounds the column, expression, or values to n decimal places, or, if n is omitted, no decimal places. (If n is negative, numbers to left of the decimal point are rounded .)

Oracle Date Format

Oracle database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds. the default display and input format for any date is DD-MON-RR.However, dates are not stored in the database in this format. all components of the date and time are stored.

SYSDATEFunction

SYSDATEIs a date function that returns the current database server date and time. you can use SYSDATE just as you wocould use any other column name. it is customary to select SYSDATE from a dummy table called DUAL.

Elements of the Date Format Model

YYYY full year in numbers

YERA Year spelled out

MM Two-digit value for month

MONTH Full name of the month

MON Three-letter abbreviation of the month

DY Three_letter abbreviation of the day of the week

DAY Full name of the day of the week

DD Numeric day of the month

Demo: SELECT to_char (sysdate, 'yyyy/YEAR/MM/MONTH/MON/DY/DAY/DD, HH24: MI: SS am') from dual;

DateFunction

MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, TRUNC

Conversion Functions

Implict data type conversion

Explicit data type conversionTO_CHAR, TO_NUMBER, TO_DATE

Nesting Functions

Single-row functions can be nested to any level.

Nested functions are evaluated from deepest level to the least deep level.

GeneralFunctions

NVL(Expr1, expr2) convercnt a null value to an actual value.

NVL2(Exprolactin, expr2, expr3) if exprolactin is not null, NVL2 returns expr2, else returns expr3.

NULLIF(Expr1, expr2) compares two expressions and returns null if they are equal, or the first expression if they are not equal

COALESCE(Exprolactin, expr2 ,..., Exprn) return the first non-null expression in the expressions list

Conditional Expressions

CASE(Compiles with ansi SQL)

CASE expr WHEN comparsion_expr1 then return_expr1

[WHEN comparsion_expr2 then return_expr2

......

ELSE else_expr]

END

DECODE(Specific to Oracle syntax)

DECODE (col | expression, search1, resut1

[, Search2, result2,…,]

[, Default])