Tutorial  4.00230

additional operators in SQL

What is an Operator?

An operator is a symbol specifying an action that is performed on one or more expressions. Operators are represented by special characters or by keywords and manipulate individual data items to return a result. The data items are called operands or arguments.

SQL allows you to include comparison, logical, or arithmetic operators in your SQL statements. Some operators and their usage are included in the table below.

So, What additional Operators are in SQL?

Statements

From a general perspective ,SQL consists entirely of structured statements, with which all data in the database is added, modified, and removed.  SQL statements always begin with a command. A command is a word, or group of words, that describes what action the statement will initiate. The command can be called the verb of the SQL statement, as it always describes an action to be taken. Statements typically contain one or more clauses, which are formal modifiers that further describe the function of the SQL statement.

           Types of SQL Statements

• Data definition language (DDL) statements
• Data manipulation language (DML) statements
• Transaction control statements
• Session control statements

Data Definition Language (DDL) Statements

Data definition language (DDL) statements enable you to perform these tasks:

• Create, alter, and drop schema objects
• Grant and revoke privileges and roles
• Analyze information on a table, index, or cluster
• Establish auditing options
• Add comments to the data dictionary

The CREATE, ALTER, and DROP commands require exclusive access to the specified object. For example, an ALTER TABLE statement fails if another user has an open transaction on the specified table.

The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table.

Data Manipulation Language (DML) Statements

Data manipulation language (DML) statements query and manipulate data in existing schema objects. These statements do not implicitly commit the current transaction. The data manipulation language statements are:

CALL	LOCK TABLE
DELETE	MERGE
EXPLAIN PLAN	SELECT
INSERT	UPDATE

Transaction Control Statements

Transaction control statements manage changes made by DML statements. The transaction control statements are:

COMMIT	SAVEPOINT
ROLLBACK	SET TRANSACTION

Session Control Statements

Session control statements dynamically manage the properties of a user session. These statements do not implicitly commit the current transaction.

Functions

Functions are similar to operators in that they manipulate data items and return a result. Functions differ from operators in the format of their arguments. This format enables them to operate on zero, one, two, or more arguments:

function(argument, argument, ...)

If you call a SQL function with a null argument, then the SQL function automatically returns null. The only SQL functions that do not necessarily follow this behavior are CONCAT, NVL, and REPLACE.

Number Functions

Number functions accept numeric input and return numeric values. Most of these functions return values that are accurate to 38 decimal digits. The transcendental functions COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH are accurate to 36 decimal digits. The transcendental functions ACOS, ASIN, ATAN, and ATAN2 are accurate to 30 decimal digits.

Datetime Functions

Datetime functions operate on values of the DATE data type. All datetime functions return a datetime or interval value of DATE data type, except the MONTHS_BETWEEN function, which returns a number.

Conversion Functions

Conversion functions convert a value from one data type to another. Generally, the form of the function names follows the convention data type TO data type. The first data type is the input data type. The second data type is the output data type.

Aggregate Functions

Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where it divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.

Many (but not all) aggregate functions that take a single argument accept these clauses:

• DISTINCT causes an aggregate function to consider only distinct values of the argument expression.
• ALL causes an aggregate function to consider all values, including all duplicates.

For example, the DISTINCT average of 1, 1, 1, and 3 is 2. The ALL average is 1.5. If you specify neither, then the default is ALL.

All aggregate functions except COUNT(*) and GROUPING ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null. COUNT never returns null, but returns either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.

Analytic Functions

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic clause. For each row, a "sliding" window of rows is defined. The window determines the range of rows used to perform the calculations for the "current row". Window sizes can be based on either a physical number of rows or a logical interval such as time.

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.

Object Reference Functions

Object reference functions manipulate REFs, which are references to objects of specified object types.

User-Defined Functions

You can write user-defined functions in PL/SQL or Java to provide functionality that is not available in SQL or SQL built-in functions. User-defined functions can appear in a SQL statement anywhere SQL functions can appear, that is, wherever an expression can occur.

For example, user-defined functions can be used in the following:

• The select list of a SELECT statement

• The condition of a WHERE clause

• CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses

• The VALUES clause of an INSERT statement

• The SET clause of an UPDATE statement