1.1 SQL Operators Overview

SQL Operators
1.1 SQL Operators Overview

An operator manipulates individual data items and returns a result. The data items are called operands or arguments. Operators are represented by special characters or by keywords. For example, the multiplication operator is represented by an asterisk (*) and the operator that tests for nulls is represented by the keywords IS NULL. There are two general classes of operators: unary and binary. Oracle Database Lite SQL also supports set operators.
1.1.1 Unary Operators

A unary operator uses only one operand. A unary operator typically appears with its operand in the following format. operator operand
1.1.2 Binary Operators

A binary operator uses two operands. A binary operator appears with its operands in the following format. operand1 operator operand2
1.1.3 Set Operators

Set operators combine sets of rows returned by queries, instead of individual data items. All set operators have equal precedence. Oracle Database Lite supports the following set operators. UNION UNION ALL INTERSECT MINUS
The levels of precedence among the Oracle Database Lite SQL operators from high to low are listed in Table 2-1. Operators listed on the same line have the same level of precedence. Table 2-1 Levels of Precedence of the Oracle Database Lite SQL Operators Precedence Level 1 2 3 SQL Operator Unary + - arithmetic operators, PRIOR operator * / arithmetic operators Binary + - arithmetic operators, || character operators
Visit:
www.gcreddy.com for QTP & SQL Information
Precedence Level 4 5 6 7
SQL Operator All comparison operators NOT logical operator AND logical operator OR logical operator
1.1.4 Other Operators

Other operators with special formats accept more than two operands. If an operator receives a null operator, the result is always null. The only operator that does not follow this rule is CONCAT.
1.2 Arithmetic Operators

Arithmetic operators manipulate numeric operands. The '-' operator is also used in date arithmetic. Supported arithmetic operators are listed in Table 2-2. Table 2-2 Arithmetic Operators Operator + (unary) - (unary) / * + Description Makes operand positive Negates operand Division (numbers and dates) Multiplication Addition (numbers and dates) Subtraction (numbers and dates) Example SELECT +3 FROM DUAL; SELECT -4 FROM DUAL; SELECT SAL / 10 FROM EMP; SELECT SAL * 5 FROM EMP; SELECT SAL + 200 FROM EMP; SELECT SAL - 100 FROM EMP;
1.3 Character Operators

Character operators used in expressions to manipulate character strings are listed in Table 2-3. Table 2-3 Character Operators Operator Description || Concatenates character strings Example SELECT 'The Name of the employee is: ' || ENAME FROM EMP;
Visit:
1.3.1 Concatenating Character Strings

With Oracle Database Lite, you can concatenate character strings with the following results. This returns the following output. CONCAT(CONCAT(ENAME ------------------------KING is a PRESIDENT BLAKE is a MANAGER CLARK is a MANAGER JONES is a MANAGER FORD is a ANALYST SCOTT is a ANALYST 6 rows selected. Concatenating two character strings results in another character string. Oracle Database Lite preserves trailing blanks in character strings by concatenation, regardless of the strings' datatypes. Oracle Database Lite provides the CONCAT character function as an alternative to the vertical bar operator. For example, SELECT CONCAT (CONCAT (ENAME, ' is a '),job) FROM EMP WHERE SAL > 2000;
Oracle Database Lite treats zero-length character strings as nulls. When you concatenate a zero-length character string with another operand the result is always the other operand. A null value can only result from the concatenation of two null strings.
1.4 Comparison Operators

Comparison operators used in conditions that compare one expression with another are listed in Table 2-4. The result of a comparison can be TRUE, FALSE, or UNKNOWN. Table 2-4 Comparison Operators Operator = Description Equality test. Example SELECT ENAME "Employee" FROM EMP WHERE SAL = 1500; SELECT ENAME FROM EMP WHERE SAL ^= 5000;
!=, ^=, <> Inequality test.
Visit:
Operator >
Description Greater than test.
Example SELECT ENAME "Employee", JOB "Title" FROM EMP WHERE SAL > 3000; SELECT * FROM PRICE WHERE MINPRICE < 30; SELECT * FROM PRICE WHERE MINPRICE >= 20; SELECT ENAME FROM EMP WHERE SAL <= 1500; SELECT * FROM EMP WHERE ENAME IN ('SMITH', 'WARD');
< >= <= IN
Less than test. Greater than or equal to test. Less than or equal to test. "Equivalent to any member of" test. Equivalent to "=ANY".
ANY/ SOME Compares a value to returned by a query. !=, >, <, <= or >=. the query returns no NOT IN
each value in a list or SELECT * FROM DEPT Must be preceded by =, WHERE LOC = SOME Evaluates to FASLE if ('NEW YORK','DALLAS'); rows. SELECT * FROM DEPT WHERE LOC NOT IN ('NEW YORK', 'DALLAS');
Equivalent to "!=ANY". Evaluates to FALSE if any member of the set is NULL.
ALL
Compares a value with every value in a list SELECT * FROM emp or returned by a query. Must be preceded by WHERE sal >= ALL (1400, =, !=, >, <, <= or >=. Evaluates to TRUE if 3000); the query returns no rows. SELECT ENAME, JOB FROM EMP WHERE SAL BETWEEN 3000 AND 5000;
[NOT] [Not] greater than or equal to x and less BETWEEN x than or equal to y. and y EXISTS
TRUE if a sub-query returns at least one row. SELECT * FROM EMP WHERE EXISTS (SELECT ENAME FROM EMP WHERE MGR IS NULL);
x [NOT] TRUE if x does [not] match the pattern y. SELECT * FROM EMP LIKE y Within y, the character "%" matches any WHERE ENAME LIKE '%E [ESCAPE z] string of zero or more characters except null. %'; The character "_" matches any single character. Any character following ESCAPE is interpreted literally, useful when y contains a percent (%) or underscore (_). IS [NOT] NULL Tests for nulls. This is the only operator that should be used to test for nulls. SELECT * FROM EMP WHERE COMM IS NOT NULL AND SAL > 1500;
Visit:
1.5 Logical Operators

Logical operators which manipulate the results of conditions are listed in Table 2-5. Table 2-5 Logical Operators Operator Description NOT Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN. Example SELECT * FROM EMP WHERE NOT (job IS NULL) SELECT * FROM EMP WHERE NOT (sal BETWEEN 1000 AND 2000) AND Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE; otherwise returns UNKNOWN. Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise, returns UNKNOWN. SELECT * FROM EMP WHERE job='CLERK' AND deptno=10 SELECT * FROM emp WHERE job='CLERK' OR deptno=10
OR
1.6 Set Operators

Set operators which combine the results of two queries into a single result are listed in Table 2-6. Table 2-6 Set Operators Operator UNION Description Example
Returns all distinct rows selected SELECT * FROM by either query. (SELECT ENAME FROM EMP WHERE JOB = 'CLERK' UNION SELECT ENAME FROM EMP WHERE JOB = 'ANALYST');
UNION ALL
Returns all rows selected by either query, including all duplicates.
SELECT * FROM (SELECT SAL FROM EMP WHERE JOB = 'CLERK' UNION SELECT SAL FROM EMP WHERE
Visit:
Operator
Description
Example JOB = 'ANALYST');
INTERSECT and INTERSECT ALL
Returns all distinct rows selected SELECT * FROM orders_list1 by both queries. INTERSECT SELECT * FROM orders_list2
MINUS
Returns all distinct rows selected SELECT * FROM (SELECT SAL by the first query but not the FROM EMP WHERE JOB = second. 'PRESIDENT' MINUS SELECT SAL FROM EMP WHERE JOB = 'MANAGER');
Visit:

1.1 SQL Operators Overview

Uploaded by

Copyright:

Available Formats

1.1 SQL Operators Overview

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

1.1 SQL Operators Overview

Uploaded by

Copyright:

Available Formats

SQL Operators

1.1 SQL Operators Overview

1.1.1 Unary Operators

1.1.2 Binary Operators

1.1.3 Set Operators

www.gcreddy.com for QTP & SQL Information

1.1.4 Other Operators

1.2 Arithmetic Operators

1.3 Character Operators

www.gcreddy.com for QTP & SQL Information

1.3.1 Concatenating Character Strings

1.4 Comparison Operators

!=, ^=, <> Inequality test.

www.gcreddy.com for QTP & SQL Information

Description Greater than test.

< >= <= IN

Equivalent to "!=ANY". Evaluates to FALSE if any member of the set is NULL.

www.gcreddy.com for QTP & SQL Information

1.5 Logical Operators

1.6 Set Operators

Returns all rows selected by either query, including all duplicates.

www.gcreddy.com for QTP & SQL Information

Example JOB = 'ANALYST');

INTERSECT and INTERSECT ALL

www.gcreddy.com for QTP & SQL Information

You might also like