Introduction to Oracle SQL | Oracle SQL Tutorial

Introduction to Oracle SQL | Oracle SQL Tutorial 

Oracle database is a relational database management system.

It is known as Oracle database, OracleDB or simply Oracle. It is produced and marketed by Oracle Corporation.

Oracle database is the first database designed for enterprise grid computing. The enterprise grid computing provides the most flexible and cost effective way to manage information and applications.

Different editions of Oracle database

Following are the four editions of the Oracle database.

• Enterprise Edition: It is the most robust and secure edition. It offers all features, including superior performance and security.
• Standard Edition: It provides the base functionality for users that do not require Enterprise Edition's robust package.
• Express Edition (XE): It is the lightweight, free and limited Windows and Linux edition.
• Oracle Lite: It is designed for mobile devices.

The Oracle Corporation

Oracle Corporation is the largest software company in the field of database business. Its relational database was the first to support SQL which has since become the industry standard.

Oracle database is one of the most trusted and widely used relational database engines. The biggest rival of Oracle database is Microsoft's SQL Server.

History of Oracle

Oracle was originally developed by Lawrence Ellison (Larry Ellision) and his two friends and former co-worker in 1977. Oracle DB runs on the most major platforms like Windows, UNIX, Linux and Mac OS.

Oracle String Functions

Function	Description
ASCII	Returns the number code that represents the specified character
ASCIISTR	Converts a string in any character set to an ASCII string using the database character set
CHR	Returns the character based on the number code
COMPOSE	Returns a Unicode string
CONCAT	Allows you to concatenate two strings together
Concat with ||	Allows you to concatenate two or more strings together
CONVERT	Converts a string from one character set to another
DECOMPOSE	Accepts a string and returns a Unicode string
DUMP	Returns a varchar2 value that includes the datatype code, the length in bytes, and the internal representation of the expression
INITCAP	Sets the first character in each word to uppercase and the rest to lowercase
INSTR	Returns the location of a substring in a string
INSTR2	Returns the location of a substring in a string, using UCS2 code points
INSTR4	Returns the location of a substring in a string, using UCS4 code points
INSTRB	Returns the location of a substring in a string, using bytes instead of characters
INSTRC	Returns the location of a substring in a string, using Unicode complete characters
LENGTH	Returns the length of the specified string
LENGTH2	Returns the length of the specified string, using UCS2 code points
LENGTH4	Returns the length of the specified string, using UCS4 code points
LENGTHB	Returns the length of the specified string, using bytes instead of characters
LENGTHC	Returns the length of the specified string, using Unicode complete of characters
LOWER	Converts all letters in the specified string to lowercase
LPAD	Pads the left-side of a string with a specific set of characters
LTRIM	Removes all specified characters from the left-hand side of a string
NCHR	Returns the character based on the number code in the national character set
REGEXP_INSTR	Returns the location of a regular expression pattern in a string
REGEXP_REPLACE	Allows you to replace a sequence of characters in a string with another set of characters using regular expression pattern matching
REGEXP_SUBSTR	Allows you to extract a substring from a string using regular expression pattern matching
REPLACE	Replaces a sequence of characters in a string with another set of characters
RPAD	Pads the right-side of a string with a specific set of characters
RTRIM	Removes all specified characters from the right-hand side of a string
SOUNDEX	Returns a phonetic representation (the way it sounds) of a string
SUBSTR	Allows you to extract a substring from a string
TRANSLATE	Replaces a sequence of characters in a string with another set of characters
TRIM	Removes all specified characters either from the beginning or the end of a string
UPPER	Converts all letters in the specified string to uppercase
VSIZE	Returns the number of bytes in the internal representation of an expression

---

---

Oracle Numeric Functions

Function	Description
ABS	Returns the absolute value of a number
ACOS	Returns the arc cosine of a number
ASIN	Returns the arc sine of a number
ATAN	Returns the arc tangent of a number
ATAN2	Returns the arc tangent of n and m
AVG	Returns the average value of an expression
BITAND	Returns an integer representing an AND operation on the bits of expr1 and expr2
CEIL	Returns the smallest integer value that is greater than or equal to a number
COS	Returns the cosine of a number
COSH	Returns the hyperbolic cosine of a number
COUNT	Returns the count of an expression
EXP	Returns e raised to the power of number
FLOOR	Returns the largest integer value that is equal to or less than a number
GREATEST	Returns the greatest value in a list of expressions
LEAST	Returns the smallest value in a list of expressions
LN	Returns the natural logarithm of a number
LOG	Returns the natural logarithm of a number to a specified base
MAX	Returns the maximum value of an expression
MEDIAN	Returns the median of an expression
MIN	Returns the minimum value of an expression
MOD	Returns the remainder of n divided by m
POWER	Returns m raised to the nth power
REGEXP_COUNT	Counts the number of times that a pattern occurs in a string
REMAINDER	Returns the remainder of m divided by n
ROUND	Returns a number rounded to a certain number of decimal places
ROWNUM	Returns a number that represents the order that a row is
SIGN	Returns a value indicating the sign of a number
SIN	Returns the sine of a number
SQRT	Returns the square root of a number
SUM	Returns the summed value of an expression
TAN	Returns the tangent of a number
TANH	Returns the hyperbolic tangent of n
TRUNC	Returns a number truncated to a certain number of decimal places

---

Oracle Date Functions

Function	Description
ADD_MONTHS	Returns a date with a specified number of months added
CURRENT_DATE	Returns the current date in the time zone of the current SQL session as set by the ALTER SESSION command
CURRENT_TIMESTAMP	Returns the current date and time in the time zone of the current SQL session as set by the ALTER SESSION command
DBTIMEZONE	returns the database time zone as a time zone offset or a time zone region name
EXTRACT	Extracts a value from a date or interval value
LAST_DAY	Returns the last day of the month based on a date value
LOCALTIMESTAMP	Returns the current date and time in the time zone of the current SQL session as set by the ALTER SESSION command
MONTHS_BETWEEN	Returns the number of months between date1 and date2
NEW_TIME	Converts a date from time zone1 to a date in time zone2
NEXT_DAY	Returns the first weekday that is greater than a date
ROUND	Returns a date rounded to a specific unit of measure
SESSIONTIMEZONE	Returns the current session's time zone as a time zone offset or a time zone region name
SYSDATE	Returns the current system date and time on your local database
SYSTIMESTAMP	Returns the current system date and time (including fractional seconds and time zone) on your local database
TRUNC	Returns a date truncated to a specific unit of measure
TZ_OFFSET	Returns the time zone offset of a value

SHARES

Share

Pin it

Tweet

Share

Share

Share

Buffer

Print

About Mariano

I'm Ethan Mariano a software engineer by profession and reader/writter by passion.I have good understanding and knowledge of AngularJS, Database, javascript, web development, digital marketing and exploring other technologies related to Software development.