Common Oracle statement files (1)

Source: Internet
Author: User
Tags rounds

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


Case-manipulation functions



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.


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;



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.


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]


DECODE(Specific to Oracle syntax)

DECODE (col | expression, search1, resut1

[, Search2, result2,…,]

[, Default])

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.