Saturday, August 27, 2016

Oracle SQL Date Functions



Date Functions

SYSDATE :     This function returns date and time. SYSDATE returns time and date from the servers operating system

CURRENT_DATE : This function returns the date from the user's session time zone.

CURRENT_TIMESTAMP : This function returns the date and time from the user's session time zone.

SESSIONTIMEZONE : This function returns session time zone.


Arithmetic with Dates:
Operation                   Result                                     Description
date + number             Date                             Adds a number of days to a date
date – number             Date                             Subtracts a number of days from a date
date – date                  Number of days           Subtracts one date from another
date + number/24        Date                             Adds a number of hours to a date


MONTHS_BETWEEN(date1, date2): Finds the number of months between date1
and date2. The result can be positive or negative. If date1 is later than date2, the
result is positive; if date1 is earlier than date2, the result is negative. The noninteger
part of the result represents a portion of the month.

ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n
must be an integer and can be negative.

NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week
('char') following date. The value of char may be a number representing a day or a
character string.

LAST_DAY(date): Finds the date of the last day of the month that contains date
The preceding list is a subset of the available date functions. ROUND and TRUNC number
functions can also be used to manipulate the date values as shown below:

ROUND(date[,'fmt']): Returns date rounded to the unit that is specified by the
format model fmt. If the format model fmt is omitted, date is rounded to the nearest day.

TRUNC(date[, 'fmt']): Returns date with the time portion of the day truncated to
the unit that is specified by the format model fmt. If the format model fmt is omitted,
date is truncated to the nearest day.











Oracle SQL Number Functions



Number Functions

• ROUND:         Rounds value to a specified decimal
• TRUNC:         Truncates value to a specified decimal
• CEIL:              Returns the smallest whole number greater than or equal to a specified number
• FLOOR:          Returns the largest whole number equal to or less than a specified number
• MOD:             Returns remainder of division


Function Purpose

ROUND(column|expression, n) -Rounds the column, expression, or value to n decimal
places or, if n is omitted, no decimal places (If n isnegative, numbers to the left of decimal point are rounded.

TRUNC(column|expression, n) -Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero


MOD(m,n) -Returns the remainder of m divided by n


CEIL FLOOR MOD
CEIL FLOOR MOD

ROUND
ROUND

TRUNC
TRUNC

Oracle SQL Character Functions

Character Functions

LOWER :Converts the character strings to lower case characters
syntax :LOWER(string)

UPPER: Converts the character strings to upper case characters
syntax :UPPER(string)

INITCAP : Converts the character strings to first character as upper case and rest as lower case
syntax :INITCAP(string)

CONCAT :Joints two values together
syntax :CONCAT(string1, string2)

SUBSTR : Extract a string of determined length from string
syntax :SUBSTR (char, position [, substring_length ])

LENGTH : Shows the length of a string
syntax :LENGTH(string)

INSTR : Finds the numeric position of a named character
syntax :INSTR (string , substring [, position [, occurrence]])

LPAD: Returns an expression left-padded to the length of n characters with a character
expression
syntax: LPAD (text-exp , length [, pad-exp])

RPAD: Returns an expression right-padded to the length of n characters with a character
expression

syntax: RPAD (text-exp , length [, pad-exp])




INITCAP
INITCAP

INSTR
INSTR

LENTGH
LENGTH

LOWER
LOWER

LPAD
LPAD

rpad
RPAD

SUBSTR
SUBSTR

UPPER
UPPER


Oracle SQL Functions

SQL Functions

Functions perform specific action and returns results

There are two types of functions .

Single Row Functions  :  return one result for each row
Multi Row Functions : return one result for multiple rows.


Character Functions
LOWER
UPPER
INITCAP
CONCAT
SUBSTR
LENGTH
LPAD
RPAD
TRIM
REPLACE
Number Functions
ROUND
TRUNC
CEIL
FLOOR
MOD
Date Functions
SYSDATE
CURRENT_DATE
CURRENT_TIMESTAMP
MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY
ROUND
TRUNC
Conversion Functions
TO_CHAR
TO_DATE
TO_NUMBER
NVL
NVL2
NULLIF
COALESC
CASE
Searched CASE
DECODE
General Functions
Group Functions
AVG
COUNT
SUM
MIN
MAX
LISTAGG
STDDEV
VARIANCE
Grouping Rows
GROUP By
HAVING

Analytical Functions

SQL- ORDER BY


ORDER By Clause

When we execute an SQL query , the order of rows returned is not defined or unknown.
To sort the rows using some criteria , use ORDER BY clause.

By default when we use ORDER BY in SQL , rows are ordered in ascending order,
 i.e. ORDER BY  column_name  ASC.

To sort rows in descending order use ORDER BY column_name DESC.

You can also use the position of the column in the select statement instead of column_name in the order by.

 Numeric values are displayed with the lowest values first (for example, 1 to 999).

 Date values are displayed with the earliest value first (for example, 01-JAN-92 before
01-JAN-95).

 Character values are displayed in the alphabetical order (for example, “A” first and “Z”
last).

 Null values are displayed last for ascending sequences and first for descending
sequences.


 You can also sort by a column that is not in the SELECT list.


Examples :







Friday, August 26, 2016

Oracle SQL Logical Operators

Logical operators

Logical operators are used to combine two or more conditions to produce a single result.  A row  will be returned  if  the overall result of the condition is TRUE.

Type of Logical Operators

AND
OR
NOT


An example of each will be explained below;

















Rules of precedence of Operators:
Precedence in which order the expressions are evaluated, you can override by using parentheses around the expressions.

Rules of Precedence
You can use parentheses to override rules of precedence.
1 Arithmetic operators
2 Concatenation operator
3 Comparison conditions
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 Not equal to
7 NOT logical operator
8 AND logical operator
9 OR logical operatoreani








Sunday, August 21, 2016

Oracle SQL LIKE Operator

LIKE Operator:

Use like operator to perform the search on column values. Search can be on numbers or characters.
%  matches zero or more characters

_  matches one character



Oracle SQL - IN Operator

IN

Use in operator to check if the value exists in the set  of values.
The set of values can be in any random order.

IN operator can be used with any data type. If character or data type values are used in the list, enclose them with single quotes  ' ' .

Examples:























list of values can be from a select query.




























If Values in the IN list are character type, enclose them in single quotes.


Oracle SQL - BETWEEN ... AND

BETWEEN ...AND

Use BETWEEN .. AND operator to display rows based on the range of values.

Values will have an upper limit and lower limit. The lower limit should be  specified first.

If the upper limit is specified first, then the SQL query will result in no matching rows.

Example :



























Oracle SQL Comparison Operators

Comparison operators :
Below is the list of comparison operators available in  Oracle SQL.

=                             equal to
>                             greater than
>=                           greater than or equal to
<                             less than
<=                           less than or equal to
BETEEEN AND        between two values
IN                            match any of the list of values
LIKE                        match a pattern
IS NULL                   is a null value


Examples:

equal to  :
SELECT *
FROM employees
WHERE hire_date='10-AUG-2007';


less than:
SELECT employee_id, first_name, phone_number, hire_date, salary
  FROM employees
 WHERE salary < 5000;

less than or equal to :
Below query gives the results less than or equal to salary 5000
SELECT employee_id, first_name, phone_number, hire_date, salary
  FROM employees
 WHERE salary <= 5000;

greater than :
SELECT employee_id, first_name, phone_number, hire_date, salary
  FROM employees
 WHERE salary > 5000;

greater than or equal to:
Below query gives the results greater  than or equal to salary 5000
SELECT employee_id, first_name, phone_number, hire_date, salary
  FROM employees
WHERE salary >= 5000;